I am trying to write a script to automatically update the schema of a database. However, for some reason twisted hangs during the second request I make on an adbapi.ConnectionPool. Here is the code:
update.py
import os
import glob
import imp
from twisted.internet import reactor
from twisted.enterprise import adbapi
from twisted.internet import defer
@defer.inlineCallbacks
def get_schema_version(conn):
schema_exists = yield conn.runQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='schema_meta';")
defer.returnValue(0)
def add_schema_files(schemas):
# Finds and imports all schema_*.py files into the list
module_files = glob.glob(os.path.dirname(os.path.abspath(__file__)) + "/schema_*.py")
for mod in module_files:
module_name = os.path.basename(os.path.splitext(mod)[0])
newmod = imp.load_source('%s'%module_name, mod)
schemas.append( (module_name, newmod) )
@defer.inlineCallbacks
def update_schema(conn):
# Update the database schema to the latest version
schema_version = yield get_schema_version(conn)
print "At schema version %d" % schema_version
schemas = []
add_schema_files(schemas)
schemas = sorted(schemas, key=lambda tup: tup[0])
for i in range(schema_version, len(schemas)):
# schemas[0] is v1, schemas[1] is v2, etc
print "Updating to version %d" % (i+1)
yield schemas[i][1].update(conn)
if __name__ == '__main__':
conn = adbapi.ConnectionPool("sqlite3", "data.db", check_same_thread=False)
d = update_schema(conn)
d.addCallback(exit)
reactor.run()
schema_1.py
from twisted.internet import defer
update_query = """
CREATE TABLE schema_meta (
version INT NOT NULL
);
INSERT INTO schema_meta (version) VALUES (1);
"""
@defer.inlineCallbacks
def update(conn):
yield conn.runQuery(update_query)
It hangs on the yield conn.runQuery(update_query)
in schema_1.py.
In addition, when I inturrupt the script, I get the following sqlite error:
sqlite3.Warning: You can only execute one statement at a time.
It turns out that the issue wasn't with twisted, it was with the SQLite query. You can only perform one query at a time, so I updated schema_1.py and it workd.
schema_1.py
from twisted.internet import defer
update_query1 = """
CREATE TABLE schema_meta (
version INT NOT NULL
);
"""
update_query2 = """
INSERT INTO schema_meta (version) VALUES (1);
"""
@defer.inlineCallbacks
def update(dbpool):
yield dbpool.runQuery(update_query1)
yield dbpool.runQuery(update_query2)