dump table and data with mysqldb

H

Hoang

does anyone know how to dump the table structure and data from a mysql
database? I am connected to the database externally so "SELECT * FROM
database INTO OUTFILE file" doesn't work for me. However, external tools
have been able to do it (Mascon). I presume this might be a common thing
where people need to backup their databases programatically from MySQLdb.

Hoang Do
http://jotsite.com
 
D

David M. Wilson

Hoang said:
does anyone know how to dump the table structure and data from a mysql
database? I am connected to the database externally so "SELECT * FROM
database INTO OUTFILE file" doesn't work for me.

Here's a start..



def get_table_list(cursor):
cursor.execute("SHOW TABLES")
return [ table for table, in cursor ]


def get_table_schema(cursor, table):
cursor.execute("SHOW CREATE TABLE %s" % (table))
return cursor.fetchone()[1]


def get_structure_sql(db):
c = db.cursor()
c.execute("SET OPTION SQL_QUOTE_SHOW_CREATE=1")
schemas = {}

for table in get_table_list(c):
schemas
= get_table_schema(c, table)

return schemas


def get_db_name(db):
cursor = db.cursor()
cursor.execute("SELECT DATABASE()")
return cursor.fetchone()[0]


def dump_structure_sql(db):
print "#"
print "# Dumping schema for database", get_db_name(db)
print "#"
print
print

for table, create_def in get_structure_sql(db).iteritems():
print "#"
print "# Dumping schema for table", table
print "#"
print
print create_def
print
print



PS: You are aware of mysqldump, right?
 
S

Steve Holden

Hoang said:
does anyone know how to dump the table structure and data from a mysql
database? I am connected to the database externally so "SELECT * FROM
database INTO OUTFILE file" doesn't work for me. However, external tools
have been able to do it (Mascon). I presume this might be a common thing
where people need to backup their databases programatically from MySQLdb.

Take a look at

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/81189

That'll show you how to access the structure, though I don't know if it's
really the sort of thing you want.

regards
 
H

Hoang

Thanks David for the little code snippets. They help in figuring out the
table structures. Now if I can get the output of:
"SELECT * FROM database INTO OUTFILE file"
to redirect to across the network rather than the local file-system.
mysqldump also puts it into the local FS. There might be no recourse other
than having to recreate the INSERT statements in your own code.

Hoang Do
 
D

David M. Wilson

Hoang said:
"SELECT * FROM database INTO OUTFILE file"
to redirect to across the network rather than the local file-system.
mysqldump also puts it into the local FS. There might be no recourse other
than having to recreate the INSERT statements in your own code.

I think someone can't be bothered reading the documentation. :)
Delete your dump_structure_sql and replace with this code. To the best
of my knowledge this replicates the part of MySQLdump that you
require. dump_data_sql could be broken out into another function or
two, but for speed I left it as it is below.


Hope this helps,

David.
PS: it outputs in MySQL 'extended' INSERT format, which is apparently
slightly faster than multiple INSERTs, but may not work with other
databases.



def dump_sql(db, dump_data = False):
print "#"
print "# Dumping schema for database", get_db_name(db)
print "#"
print
print

for table, create_def in get_structure_sql(db).iteritems():
print "#"
print "# Dumping schema for table", table
print "#"
print
print create_def
print
print

if dump_data:
dump_data_sql(db, table)


def get_column_names(cursor, table):
cursor.execute("DESCRIBE %s" % (table))
return [ row[0] for row in cursor ]


def dump_data_sql(db, table):
cursor = db.cursor()
colnames = get_column_names(cursor, table)
colnames_sql = ', '.join(colnames)

count = cursor.execute("SELECT %s FROM %s" % (colnames_sql, table))

if count == 0:
return


print "#"
print "# Dumping data for table", table
print "#"
print

print "INSERT INTO %s(%s) VALUES" % (table, colnames_sql)


count -= 1
for index, row in enumerate(cursor):
row_sql = " (%s)" % (', '.join(db.escape(row)))

if index < count:
print row_sql + ","
else:
print row_sql + ";"

print
print
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top