Friday, March 14

Using python to execute a .sql file for mysql

The task is to execute one import.sql file to import several data files into mysql. The trick is that it is known some files will be missing, so the execution of the import.sql will have error, but we want to ignore the error, import whatever exists in the harddrive.

There are two ways to do that:

1, Execute mysql command line, feed in the file as pipe input:
    f = open("import.sql")
    process = Popen(['mysql', '--force'], stdout=PIPE, stdin=f, stderr=PIPE)
    out, err = process.communicate()
The trick is to use '--force' argument of mysql to ignore the error. This is equivalent to:
mysql --force < import.sql
The err will have the error output for missing data file, then you can write that into log file.
2, Execute mysql  command line client, then execute "source import.sql":
    from subprocess import Popen, PIPE
    process = Popen('mysql', stdout=PIPE, stdin=PIPE, stderr=PIPE)
    out, err = process.communicate(input="source import.sql")
Last time I checked, this way was not working very well. It always failed at the missing data file step.
Reference: : "--force is intended to be used in Batch Mode only."