Home Original page

dump table and data with mysqldb

David M. Wilson dw-google.com at botanicus.net
Wed Nov 5 10:26:05 EST 2003 
"Hoang" <tr at jotsite.com> wrote in message news:<EhYpb.13859$uF5.6372 at 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?
More information about the Python-list mailing list