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.

Choke

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()

Detachment

Thanks but I was hoping to avoid creating a temporary dict - I want to avoid having to remember the column names everywhere in my code. And isn't it redundant to create this dict when I have the data already in 2D tuple? But let's assume that I only know the data type and order of the columns, is there a simple way to insert this positionally? – Choke

you'd need to use a string INSERT statement in conjunction with "?" for parameters, which gets sent more directly to the pysqlite DBAPI. The SQLA insert() construct is always going to treat things as dictionaries internally anyway. If performance is the goal then straight DBAPI access would be needed. If portability, then just use a function like dict(zip(((c.key for c in table.c), row)) to create the dictionary. – Claypan

Edited my answer to use raw SQL, uses multirow insert. – Detachment

Ok, if that's the way it's gotta be, c'est la vie. Thanks for the info! – Choke

@eclaird - thank you for this solution. I tried to follow your solution since I have a similar issue but I got an error. I created a post here (#15628631). I would kindly appreciate your guidance. Thank you. – Stagecoach

@Claypan I also have a similar problem. I followed the solution provided by eclaird but I ran into syntax errors. (#15628631). I would kindly appreciate your help. Thank you. – Stagecoach

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

Moreover

Thanks for providing a lower-overhead bulk insert option! – Coakley

This is an ORM based solution. – Goose

@Goose It might help to explain what that means, for the new people. I understand now, but I didn't when I wrote this answer. – Moreover

this is by using orm but what if we need this in raw sql? – Fatherless