How can I reset a MySQL database without using mysqldump in Python

Question

How can I dump a MySQL database without using mysqldump, just using Python, including the table structure?

Answer 1

I solved this problem.

import MySQLdb import os import datetime con = MySQLdb.connect(host='localhost', user='root', passwd='password', db='test') cur = con.cursor() cur.execute("SHOW TABLES") data = "" tables = [] for table in cur.fetchall(): tables.append(table[0]) for table in tables: data += "DROP TABLE IF EXISTS `" + str(table) + "`;" cur.execute("SHOW CREATE TABLE `" + str(table) + "`;") data += "\n" + str(cur.fetchone()[1]) + ";\n\n" cur.execute("SELECT * FROM `" + str(table) + "`;") for row in cur.fetchall(): data += "INSERT INTO `" + str(table) + "` VALUES(" first = True for field in row: if not first: data += ', ' data += '"' + str(field) + '"' first = False data += ");\n" data += "\n\n" now = datetime.datetime.now() filename = str(os.getenv("HOME")) + "/backup_" + now.strftime("%Y-%m-%d_%H:%M") + ".sql" FILE = open(filename,"w") FILE.writelines(data) FILE.close() 

It seems to work well from a little testing.

Answer 2

I would advise MySQLdb - it provides MySQL API for Python.

However, most likely, the API makes an indirect mysqldump call. You do not want to call mysqldump directly or in general?

You can also check "Dump Tables and Data" , where the exact procedure for resetting the structure and data of the table (down the page) without MySQLdb is displayed.