StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are a few ways to do this, I will only mention one.</p> <p>It seems like you already track the version within the database. On your application start, you will want to check this version against the running application's version and run any sql scripts that will perform the schema changes.</p> <p><strong>update</strong></p> <p>An example of this in action:</p> <pre><code>import os import sqlite3 as sqlite def make_movie_table(cursor): cursor.execute('CREATE TABLE movies(id INTEGER PRIMARY KEY, title VARCHAR(20), link VARCHAR(20))') def make_series_table(cursor): cursor.execute('CREATE TABLE series(title VARCHAR(30) PRIMARY KEY,series_link VARCHAR(60),number_of_episodes INTEGER,number_of_seasons INTEGER)') def make_episode_table(cursor): cursor.execute('CREATE TABLE episodes(id INTEGER PRIMARY KEY,title VARCHAR(30),episode_name VARCHAR(15), episode_link VARCHAR(40), Date TIMESTAMP, FOREIGN KEY (title) REFERENCES series(title) ON DELETE CASCADE)') def make_version_table(cursor): cursor.execute('CREATE TABLE schema_versions(version VARCHAR(6))') cursor.execute('insert into schema_versions(version) values ("0.1.0")') def create_database(sqlite_file): if not os.path.exists(sqlite_file): connection = sqlite.connect(sqlite_file) cursor = connection.cursor() cursor.execute("PRAGMA foreign_keys = ON") make_movie_table(cursor) make_series_table(cursor) make_episode_table(cursor) make_version_table(cursor) connection.commit() connection.close() def upgrade_database(sqlite_file): if os.path.exists(sqlite_file): connection = sqlite.connect(sqlite_file) cursor = connection.cursor() cursor.execute("select max(version) from schema_versions") row = cursor.fetchone() database_version = row[0] print('current version is %s' % database_version) if database_version == '0.1.0': print('upgrading version to 0.1.1') cursor.execute('alter table series ADD COLUMN new_column1 VARCHAR(10)') cursor.execute('alter table series ADD COLUMN new_column2 INTEGER') cursor.execute('insert into schema_versions(version) values ("0.1.1")') #if database_version == '0.1.1': #print('upgrading version to 0.1.2') #etc cetera connection.commit() connection.close() #I need to add 2 columns to the series table, when the user upgrade the software. if __name__ == '__main__': create_database('/tmp/db.sqlite') upgrade_database('/tmp/db.sqlite') </code></pre> <p>Each upgrade script will take care of <a href="http://www.sqlite.org/lang_altertable.html" rel="nofollow">making your database changes</a>, and then update the version inside the DB to the latest version. Note that we do not use <code>elif</code> statements, this is so that you can upgrade a database over multiple versions if it needs to.</p> <p>There are some caveats to watch out for:</p> <ul> <li>run upgrades <a href="http://docs.python.org/2/library/sqlite3.html#controlling-transactions" rel="nofollow">inside transactions</a>, you will want to rollback on any errors to avoid leaving the database in an unusable state. -- <strong>update</strong> this is incorrect as pointed out, thanks Martijn!</li> <li>avoid renames and column deletes if you can, and if you must, ensure any views using them are updated too.</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload