dump table and data with mysqldb

[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

[David M. Wilson]

"Hoang" <[e-mail]> wrote in message news:<Eh******************@newssvr14.news.prodigy. com>...

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[table] = 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?

[Steve Holden]

"Hoang" <[e-mail]> wrote in message
news:Eh******************@newssvr14.news.prodigy.c om...

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/Coo...n/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
--
Steve Holden http://www.holdenweb.com/
Python Web Programming http://pydish.holdenweb.com/pwp/

[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

[David M. Wilson]

"Hoang" <[e-mail]> wrote in message news:<NR***************@newssvr14.news.prodigy.com >...

"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