Is there are standard way to store a database schema outside a python app

Question

I am working on a small database application in Python (currently targeting 2.5 and 2.6) using sqlite3.

It would be helpful to be able to provide a series of functions that could setup the database and validate that it matches the current schema. Before I reinvent the wheel, I thought I'd look around for libraries that would provide something similar. I'd love to have something akin to RoR's migrations. xml2ddl doesn't appear to be meant as a library (although it could be used that way), and more importantly doesn't support sqlite3. I'm also worried about the need to move to Python 3 one day given the lack of recent attention to xml2ddl.

Are there other tools around that people are using to handle this?

Answer 1

You can find the schema of a sqlite3 table this way:

import sqlite3
db = sqlite3.connect(':memory:')
c = db.cursor()
c.execute('create table foo (bar integer, baz timestamp)')
c.execute("select sql from sqlite_master where type = 'table' and name = 'foo'")
r=c.fetchone()
print(r)
# (u'CREATE TABLE foo (bar integer, baz timestamp)',)

Answer 2

Take a look at SQLAlchemy migrate. I see no problem using it as migration tool only, but comparing of configuration to current database state is experimental yet.

Answer 3

I use this to keep schemas in sync.

Keep in mind that it adds a metadata table to keep track of the versions.

Answer 4

South is the closest I know to RoR migrations. But just as you need Rails for those migrations, you need django to use south.

Answer 5

Not sure if it is standard but I just saved all my schema queries in a txt file like so (tables_creation.txt):

CREATE TABLE "Jobs" (
    "Salary"    TEXT,
    "NumEmployees"  TEXT,
    "Location"  TEXT,
    "Description"   TEXT,
    "AppSubmitted"  INTEGER,
    "JobID" INTEGER NOT NULL UNIQUE,
    PRIMARY KEY("JobID")
);

CREATE TABLE "Questions" (
    "Question"  TEXT NOT NULL,
    "QuestionID"    INTEGER NOT NULL UNIQUE,
    PRIMARY KEY("QuestionID" AUTOINCREMENT)
);

CREATE TABLE "FreeResponseQuestions" (
    "Answer"    TEXT,
    "FreeResponseQuestionID"    INTEGER NOT NULL UNIQUE,
    PRIMARY KEY("FreeResponseQuestionID"),
    FOREIGN KEY("FreeResponseQuestionID") REFERENCES "Questions"("QuestionID")
);

...

Then I used this function taking advantage of the fact that I made each query delimited by two newline characters:

def create_db_schema(self):
    db_schema = open("./tables_creation.txt", "r")
    sql_qs = db_schema.read().split('\n\n')
    c = self.conn.cursor()
    for sql_q in sql_qs:
        c.execute(sql_q)