I'm working on an application to manage my streaming media for my
Roku player using
sqlite3 and
Python for everything. One thing I've
learned over the years is that your SQL schema always changes. Once
your code is in production you always have something you need to
change about it, whether it's adding a new column, changing a type
or tables to support new features. I wanted a way to automatically
update the database schema when a new version of the code is
installed. I don't want to jump into the complexity of using
SQLAlchemy and
migrate so I came
up with this simple method. The database has a table named schema
with a single row with the current schema version in it. This class
checks the current version and executes any missing commands,
bringing it up to the latest version.
class DbSchema(object):
"""
Database schema creation and modification
"""
# Schema revisions, rev[0], etc. is a list of SQL operations to run to
# bring the database up to date.
sql = [""" create table users(id INTEGER PRIMARY KEY, username UNIQUE, password, email);
create table sources(id INTEGER PRIMARY KEY, name, type, path UNIQUE);
create table schema(version);
insert into schema(version) values(1);
""",
# Add default values
""" insert into users(username, password) values("admin","badpassword");
update schema set version=2;
""",
# Add media table
""" create table media(
id INTEGER PRIMARY KEY,
name,
path
);
update schema set version=3;
""",
]
def __init__(self, database):
self.database = database
def upgrade(self):
"""
Upgrade the database to the current schema version
"""
# Get the current schema version number
conn = sqlite3.connect(self.database)
conn.row_factory = sqlite3.Row
cur = conn.cursor()
try:
cur.execute("select version from schema")
version = cur.fetchone()['version']
except:
version = 0
if len(self.sql) > version:
for update in self.sql[version:]:
cur.executescript(update)
cur.close()
conn.close()
Initialize it with the path to your database, and then call the
upgrade() method on it.
schema = DbSchema("./library.db")
schema.upgrade()
Add new tables, changes to old tables, default data, etc. to new strings in
the sql list. Make sure to update the schema table's version as the last step
for each revision. As long as you only make database changes via this class you
will ensure that the tables are ready for your new code when each new revision
is released. This version is being used with sqlite3, but works just as well
with mysql or postgresql.
There are comments.