Source large MySQL dump with Python script

Question

I have a huge (1GB+) database dump that I want to load into new databases on other servers. I tried parsing it line by line and executing each into mysql, but it unfortunately doesn't split lines evenly into commands and just fails on the incomplete ones.

filename='/var/test.sql'
fp = open(filename)
while True:
        a = fp.readline()
        if not a:
           break
        cursor.execute(a) #fails most of the time

It is also way too large to load the entire thing into memory call that. Furthermore, the python MySQLdb module does not support the source command.

EDITED

File includes a bunch of insert and create statements. Where its failing is on the inserts of large tables that contain raw text. There are all sorts of semi-colons and newlines in the raw text so hard to split commands based on that.

Please translate "huge" into megabytes. Please show what test.sql looks like. — John Machin
– John Machin, Commented May 13, 2011 at 12:46
A quick silly question: Doesn't mySQL supply an executable program that will do bulk create and insert statements from your file (presumably a lot faster than a Python script that parsed your file and did it a statement at a time)? — John Machin
– John Machin, Commented May 14, 2011 at 0:13

linuts · Accepted Answer · 2011-05-14 18:48:38Z

1

Any reason you can't spawn out a process to do it for you?

import subprocess

fd = open(filename, 'r')
subprocess.Popen(['mysql', '-u', username, '-p{}'.format(password), '-h', hostname, database], stdin=fd).wait()

You may want to tailor that a little as the password will be exposed to ps.

answered May 14, 2011 at 18:48

linuts

6,7985 gold badges37 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rjmunro · Accepted Answer · 2011-05-14 23:22:22Z

1

Assuming queries do end on line boundaries, you could just add lines together until they make a complete query.

Something like:

filename='/var/test.sql'
fp = open(filename)
lines = ''
while True:
        a = fp.readline()
        if not a:
           break
        try:
           cursor.execute(lines + a)
           lines = ''
        except e:
           lines += a

If it's only insert statements, you could look for lines ending ; and with the next line starting "INSERT".

filename='/var/test.sql'
fp = open(filename)
lines = ''
while True:
        a = fp.readline()
        if not a:
           break
        if lines.strip().endswith(';') and a.startswith('insert'):
           cursor.execute(lines)
           lines = a
        else:
           lines += a
# Catch the last one
cursor.execute(lines)

edit: replaced trim() with strip() & realised we don't need to execute the line a in second code example.

edited May 14, 2011 at 23:22

answered May 13, 2011 at 12:19

rjmunro

28.3k21 gold badges113 silver badges135 bronze badges

3 Comments

JiminyCricket Over a year ago

Thanks RJ. Good idea, I tried that but the dumps contain text that include semi-colons and newlines

rjmunro Over a year ago

@john machin @Xavier, yes, I meant strip. I wanted to take the newlines away before lines ended with ;. Unfortunately, python is not the language I've been programming with the most recently.

rjmunro Over a year ago

@JiminyCricket It has lines that end in ";" where the next line starts with "insert" in the data. What about the first method that just tries adding more lines to the query until it works?

anhtran · Accepted Answer · 2011-05-14 18:10:23Z

0

I think, sometimes, we should choose the other ways to do the job effectively. I prefer to use this stuff for large data: http://www.mysqldumper.net/

answered May 14, 2011 at 18:10

anhtran

2,0544 gold badges27 silver badges55 bronze badges

Collectives™ on Stack Overflow

Source large MySQL dump with Python script

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related