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]

Re: dump table and data with mysqldb

"Hoang" <tr@jotsite.com > wrote in message news:<EhYpb.138 59$uF5.6372@new ssvr14.news.pro digy.com>...
[color=blue]
> 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.[/color]

Here's a start..



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


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


def get_structure_s ql(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_schem a(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_s ql(db).iteritem s():
print "#"
print "# Dumping schema for table", table
print "#"
print
print create_def
print
print



PS: You are aware of mysqldump, right?

[Steve Holden]

Re: dump table and data with mysqldb

"Hoang" <tr@jotsite.com > wrote in message
news:EhYpb.1385 9$uF5.6372@news svr14.news.prod igy.com...[color=blue]
> 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.
>[/color]

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

[Hoang]

Re: dump table and data with mysqldb

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]

Re: dump table and data with mysqldb

"Hoang" <tr@jotsite.com > wrote in message news:<NRdqb.698 $FS7.557@newssv r14.news.prodig y.com>...
[color=blue]
> "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.[/color]

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_s ql(db).iteritem s():
print "#"
print "# Dumping schema for table", table
print "#"
print
print create_def
print
print

if dump_data:
dump_data_sql(d b, table)


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


def dump_data_sql(d b, table):
cursor = db.cursor()
colnames = get_column_name s(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(curso r):
row_sql = " (%s)" % (', '.join(db.escap e(row)))

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

print
print