CODEWITHSUNDEEP
pythonmysqlsubprocess
Sebastian Dahlgren
Sebastian Dahlgren

Reputation: 967

Import SQL dump with subprocess

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?

Upvotes: 5

Views: 8901

Answers (2)

anti1869
anti1869

Reputation: 1409

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.

Upvotes: 18

sigi
sigi

Reputation: 6279

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())

Upvotes: 8

Related Questions