Import SQL dump with subprocess

Question

I'm trying to import a .sql dump from disk into MySQL via Python and subprocess. I.e. the equivalent to

mysql -u user -ppassword db < dump.sql

My Python code looks like this (but I have tried a ton of alternatives :)):

proc = subprocess.Popen(
    ("mysql -u %s -p%s database"  % (MYSQL_USER, MYSQL_PASSWORD)).split(),
    stdin=subprocess.PIPE,
    stdout=subprocess.PIPE,
    shell=False)
out, err = proc.communicate('source /tmp/dump.sql')

The application finishes successfully, but there are no rows imported to MySQL. I have also tried pipe the dump.sql like this:

proc = subprocess.Popen(
    ("mysql -u %s -p%s database < /tmp/dump.sql"  % (MYSQL_USER, MYSQL_PASSWORD)).split(),
    stdin=subprocess.PIPE,
    stdout=subprocess.PIPE,
    shell=False)
out, err = proc.communicate()

If important, when I set shell=True I get ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO))

Can anyone please point me in the right direction?

Answer 1

If you come to this page from Google, please note, that sigi's answer will work, but it will load all dump file into memory and if it's too big to fit, it will fail.

Here's how I do it:

with open(dump_filename, 'r') as f: 
       command = ['mysql', '-u%s' % db_settings['USER'], '-p%s' % db_settings['PASSWORD'], db_settings['NAME']]
       proc = subprocess.Popen(command, stdin = f)
       stdout, stderr = proc.communicate()

It do the same, but memory consumption is minimal, because the dump is streamed right to the stdin of mysql.

Answer 2

You are using Popen.communicate() wrong.

import subprocess

proc = subprocess.Popen(["mysql", "--user=%s" % USER, "--password=%s" % PASS, "database"],
                        stdin=subprocess.PIPE,
                        stdout=subprocess.PIPE)
out, err = proc.communicate(file("/tmp/dump.sql").read())