Sqlalchemy core, insert multiple rows from a tuple instead of dict

Question

I have data in a 2D tuple (or say coming from a Numpy table), and need to insert it into an SQL table. Using Sqlalchemy Core with SQLite, how can I efficiently and simply insert this data into my table?

Take i.e. from @eclaird;

engine = sa.create_engine('sqlite://', echo=True)
metadata = sa.MetaData()

widgets_table = sa.Table('widgets', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('bar', sa.String(50)),
    sa.Column('biz', sa.Boolean),
    sa.Column('baz', sa.Integer),
    )
metadata.create_all(engine)

# Assuming this is the data where None holds place for primary key
my_data = [
    (None, "Test", True, 3),
    (None, "Test", True, 3),
    ]

So far I'm at this point in the docs; so I have;

engine.execute(widgets_table.insert().values((None, "Test", True, 3)))

Which works. But I want to insert many rows at once such as

engine.execute(widgets_table.insert().values(((None, "Test", True, 3), (None, "Test", True, 3))))

But then error;

The 'sqlite' dialect with current database version settings does not support in-place multirow inserts.

Also tried;

insert = widgets_table.insert()

engine.execute(insert, [
                    (None, "Test", True, 3), 
                    (None, "Test", True, 3) 
                    ])

With error;

AttributeError: 'tuple' object has no attribute 'keys'

As a recent convert to SQLalch, I'm a bit lost here.

Answer 1

You're missing some details about your setup so I made something up. Inserting tuples is difficult unless you're inserting the table's primary key too, so why not create a dictionary from your data before insert?

This should work with SQLAlchemy 0.7.6 and later:

import sqlalchemy as sa

engine = sa.create_engine('sqlite://', echo=True)
metadata = sa.MetaData()

widgets_table = sa.Table('widgets', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('foo', sa.String(50)),
    sa.Column('bar', sa.String(50)),
    sa.Column('biz', sa.Boolean),
    sa.Column('baz', sa.Integer),
    )
metadata.create_all(engine)

# Assuming this is your data
values = [
    (None, "Test", True, 3),
    (None, "Test", True, 3),
    ]

with engine.connect() as connection:
    with connection.begin() as transaction:
        try:
            markers = ','.join('?' * len(values[0]))
            ins = 'INSERT INTO {tablename} VALUES ({markers})'
            ins = ins.format(tablename=widgets_table.name, markers=markers)
            connection.execute(ins, values)
        except:
            transaction.rollback()
            raise
        else:
            transaction.commit()

Answer 2

Hello from the future!

In 2021, with SQLAlchemy 1.4 being the stable release and 2.0 on the horizon, your original attempt where you used insert().values on a list of lists (or tuples) should work fine.

I am also relatively new to SQLAlchemy, so I can't rightly say what changed—and perhaps the change was not in SQLAlchemy, but with the sqlite3 DB-API library which ships with Python. I should note I'm using Python 3.7.12, in that case.

import sqlalchemy as sa

engine = sa.create_engine('sqlite://', echo=True)
metadata = sa.MetaData()

widgets_table = sa.Table('widgets', metadata,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('bar', sa.String(50)),
    sa.Column('biz', sa.Boolean),
    sa.Column('baz', sa.Integer),
)

metadata.create_all(engine)

my_data = [
    (None, "Test", True, 3),
    (None, "Test", True, 3),
]

# works fine
engine.execute(widgets_table.insert().values(my_data))

And then:

>>> engine.execute(sa.select(widgets_table)).all()
[(1, 'Test', True, 3), (2, 'Test', True, 3)]

---

See also: python sqlalchemy insert multiple lines in a tuple data structure