ProgrammingError Thread error in SQLAlchemy

Question

I have a two simple tables in a sqlite db.

from sqlalchemy import MetaData, Table, Column, Integer, ForeignKey, \
    create_engine, String
from sqlalchemy.orm import mapper, relationship, sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///dir_graph.sqlite', echo=True)

session_factory = sessionmaker(bind=engine)
Session = scoped_session(session_factory)
session = Session()

Base = declarative_base()

class NodeType(Base):
    __tablename__ = 'nodetype'
    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)
    nodes = relationship('Node', backref='nodetype')

def __init__(self, name):
    self.name = name

def __repr__(self):
    return "Nodetype: %s" % self.name


class Node(Base):
    __tablename__ = 'node'

    id = Column(Integer, primary_key=True)
    name = Column(String(20), unique=True)
    type_id = Column(Integer,
                 ForeignKey('nodetype.id'))


    def __init__(self, _name, _type_id):
        self.name = _name
        self.type_id = _type_id

Base.metadata.create_all(engine) 

After the run I interact with the interpreter. e.g. n1= Node('Node1',1) to learn about sqlalchemy. After I did a session.commit() and try another statement e.g. n2 = Node('n2',1) I get this error: sqlalchemy.exc.ProgrammingError: (ProgrammingError) SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 3932 and this is thread id 5740 None None.

How can I continue a session after I did a commit ? tnx

Answer 1

SQLite by default prohibits the usage of a single connection in more than one thread. just add connect_args={'check_same_thread': False} parameter to your engine variable like

engine = create_engine('sqlite:///dir_graph.sqlite', connect_args={'check_same_thread': False}, echo=True)

According to sqlite3.connect:

By default, check_same_thread is True and only the creating thread may use the connection. If set False, the returned connection may be shared across multiple threads. When using multiple threads with the same connection writing operations should be serialized by the user to avoid data corruption.