1

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?

Ivo Flipse
  • 10,222
  • 18
  • 50
  • 63
  • Paste the SQL export statements into phpmyadmin. (Unless this is a job you are trying to script and repeat meny times). – Ian May 17 '11 at 19:27
  • @Ian, I'm still changing and tweaking my application and database a lot, but since not everyone testing my application is technical enough, I want to make sure its done properly. – Ivo Flipse May 17 '11 at 19:32
  • In that case, you will have to applythe changes yourself. Record the changes you make in a text file as you work, and apply them as you apply the releases of your code. I HIGHLY recommend using a VCS such as GIT to keep the releases straight. – Ian May 17 '11 at 20:15

3 Answers3

3

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  |
unmounted
  • 33,530
  • 16
  • 61
  • 61
2

You could try the sqlparse module to split up the file into individual statements, and then execute each one.

You can also manipulate the SQL tokens in advanced ways, for example stripping out all comments (except those inside functions). You could use this technique to parse the table names from a CREATE TABLE statement and check for the table's existence.

Here's an example of splitting up the statements:

import sqlparse

queries = '''
CREATE TABLE foo (id INTEGER);
CREATE TABLE bar (id INTEGER, name VARCHAR);
SELECT foo FROM bar WHERE id IN (1,2,3);
DELETE FROM bar WHERE id IN (3, 4);
-- a comment
DELETE FROM bar WHERE name IN ('chaos','atlas');
'''

for i, stmt, in enumerate(sqlparse.split(queries)):
    sql = stmt.strip()
    if not sql:
        continue
    print i, sql

Output:

0 CREATE TABLE foo (id INTEGER);
1 CREATE TABLE bar (id INTEGER, name VARCHAR);
2 SELECT foo FROM bar WHERE id IN (1,2,3);
3 DELETE FROM bar WHERE id IN (3, 4);
4 -- a comment
DELETE FROM bar WHERE name IN ('chaos','atlas');
Community
  • 1
  • 1
samplebias
  • 37,113
  • 6
  • 107
  • 103
  • if I try your code sample on my built code, it returns one big blob, rather than the separate statement. Any idea how to tweak it so it only returns the separate sql statements? – Ivo Flipse May 18 '11 at 09:56
1

Just read the file and execute the sql strings

I'm confused as to what else you need to do. If the table already exists the create table statement won't run. Any alter statements would run.

If you need to compare the current schema to the schema you're trying to update it to, this is a harder problem than "parsing" the file implies and you'll probably want to use some kind of migration framework (south for django is one of my favorites) or a schema comparison tool.

marr75
  • 5,666
  • 1
  • 27
  • 41
  • Care to elaborate on how to read the file? Because I couldn't figure out how split the file into separate sql strings. And would you have any more information about how to compare schema's, as I have no experience with it. – Ivo Flipse May 17 '11 at 19:40
  • Sql statements are delimited by semi-colons, are they not? You shouldn't have any reason to have to run them individually though, you could just open the file, read it, and run the string. As far as comparing schema, it's a much more complicated problem than a stackoverflow answer will be able to cover. There's an entire industry of software companies who make sql tools that feature schema and or data comparison tools. – marr75 May 17 '11 at 19:50
  • Ah, I didn't know it was that easy. I tried the read + execute and it worked perfectly. As for the schema part, I'll just have to drop tables if they aren't the same (the data doesn't have to be kept) and rebuilt them. `Any alter statements would run.` Would it just be the same as my built code and only alter when something is different? – Ivo Flipse May 17 '11 at 20:04
  • 1
    If you don't need the data, I would just drop the database and create it from scratch each time. I've worked at shops that have a huge upgrade script that uses if statements selecting from schema tables to see if they should add a column or not, and really, it's a mess. Since you're using python, you could use an ORM (like django's ORM) and a migration library (like south, read more here: http://south.aeracode.org/docs/about.html) to keep your databases up to date. – marr75 May 17 '11 at 20:21
  • I think that option is actually the safest, because trying to migrate all the data (at this stage) will cost me more work than it takes to redo the processing. – Ivo Flipse May 17 '11 at 20:25