dump table and data with mysqldb

David M. Wilson dw-google.com at botanicus.net
Thu Nov 6 12:19:22 CET 2003

"Hoang" <tr at jotsite.com> wrote in message news:<NRdqb.698$FS7.557 at 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,

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

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

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

		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:

	print "#"
	print "# Dumping data for table", table
	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 + ","
			print row_sql + ";"


More information about the Python-list mailing list