I've exported my MySQL database to a .txt/.sql file (link) and want to use this to rebuilt the database on other computers. While running the script in HeidiSQL works perfectly fine, I want to make sure all the tables exist and are correct before starting my application.
I could copy paste the built code into my Python code, but the export is a direct representation of my database and shouldn't contain any errors, whereas copy-pasting... So instead I tried parsing the file, but my parsing skills leave a lot to be desired.
Here's a snippet of the code:
CREATE TABLE IF NOT EXISTS `data` (
`dataid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`measurementid` int(10) unsigned NOT NULL DEFAULT '0',
`frame` int(10) unsigned NOT NULL,
`sensor_row` int(10) unsigned NOT NULL,
`sensor_col` int(10) unsigned NOT NULL,
`value` float unsigned NOT NULL,
PRIMARY KEY (`dataid`),
UNIQUE KEY `measurementid_frame_sensor_row_sensor_col` (`measurementid`,`frame`,`sensor_row`,`sensor_col`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
So assuming the database data is already created, I'm looking for a way to parse the built code and create all the required tables if they don't exist or alter them if they've changed.
Any suggestions on how to approach this?
解决方案
You can just execute the sql from the file fully and directly via a python database adapter:
import MySQLdb
sql_dump = open('builtcode.sql', 'r').read()
conn = MySQLdb.connect(db='soans', passwd='*****')
cur = conn.cursor()
cur.execute(sql_dump)
cur.connection.close()
Gives:
mysql> show tables;
+-----------------+
| Tables_in_soans |
+-----------------+
| anamnesis_main |
| averagecontact |