dump table and data with mysqldb

[David M. Wilson]

"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,

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