Sqlalchemy Connection Pool On Multiple Threads

First I create a simple table import threading from sqlalchemy import create_engine from sqlalchemy import Column, Integer, Float, String, Date, DateTime, Boolean, select from sqla

Solution 1:

I am not able to add() or add_all() at the session() level. If the engine() is created with additional args, the following works.

It is serializing calls to sqlite3 db tables I believe, which is not ideal but necessary for sqlite.

engine = create_engine('sqlite://', connect_args={'check_same_thread' : False})
conn = engine.connect()

metadata = MetaData(engine)
table = Table('inventory',
              metadata,
              Column('item_no', Integer, primary_key=True, autoincrement=True),
              Column('desc', String(255), nullable=False),
              Column('volume', Integer, nullable=False)
              )

metadata.create_all()

some_inventory = [{'item_no' : 0, 'desc' : 'toy crane', 'volume' : 12},
                  {'item_no' : 1, 'desc' : 'puddle jumper', 'volume' : 2},
                  {'item_no' : 2, 'desc' : 'pet snake', 'volume' : 1},
                  {'item_no' : 3, 'desc' : 'bowling ball', 'volume' : 4},
                  {'item_no' : 4, 'desc' : 'spinning top', 'volume' : 3},
                  {'item_no' : 5, 'desc' : 'pumpkin', 'volume' : 2}]


thread_0 = threading.Thread(target=insert_inventory_table, args=(conn, table, some_inventory[0:3]))
thread_1 = threading.Thread(target=insert_inventory_table, args=(conn, table, some_inventory[3:]))

thread_0.start()
thread_1.start()