How to instantiate a table object to bulk_insert rows using alembic / SQLAlchemy

Question

I am trying to use bulk_insert to insert data into an existing table (services) in my Postgres database. How do I instantiate this table object so I can do a bulk_insert with it?

I saw answers like this: Alembic bulk_insert to table with schema but I want to avoid redefining the schema again in the migration.

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


def upgrade():
    """Up migration."""


services = sa.MetaData().Services()

op.bulk_insert(services,
    [   
        {
        'id': 88,
        'name':'Test 1',
        'is_active': 'true',
        'include_in_broker_fee': 'true',
        'is_domestic': 'true',
        'is_international': 'true'
        },
        {
        'id': 89,
        'name':'Test 2',
        'is_active': 'true',
        'include_in_broker_fee': 'true',
        'is_domestic': 'true',
        'is_international': 'true'
        }
   ])

Blackface

Answer 1

In order to update the table as you've shown above you'll need to define it so sqlalchemy knows what to update. Doing this with alchemy's MetaData() object is pretty straightforward, in fact you almost have it. Try something like this:

    from sqlalchemy import Table, MetaData

    meta = MetaData(bind=op.get_bind())
    services = Table('services', meta)

Now that the table is defined you can leverage alchemy's bulk update methods. For this I refer you to this bit of their documentation where they show several examples of bulk_insert_mappings() and bulk_save_objects() --- http://docs.sqlalchemy.org/en/latest/faq/performance.html

Engorge

Answer 2

In case somebody stumbles upon this like I did: Currently for this to work you need to reflect specific tables in your MetaData() object. Underlying database was MySQL.

Working code:

from alembic import op
from sqlalchemy import Table, MetaData

def upgrade():

    # get metadata from current connection
    meta = MetaData(bind=op.get_bind())

    # pass in tuple with tables we want to reflect, otherwise whole database will get reflected
    meta.reflect(only=('some_table',))

    # define table representation
    some_table_tbl = Table('some_table', meta)

    # insert records
    op.bulk_insert(
        some_table_tbl,
        [
            {
                # data here...
            },  # (...)
        ]

Alkene

Thanks didn't work without that in my case. Could you please describe what is happening here exactly? I'm new to sqlalchemy and am figuring out how this giant lib works. Any architecture level description of the inner workings? No idea how I would be able to figure this out on my own. – Novelistic

Worked for me. Good idea to reflect tables from the database because schema definitions might not exist in the code any more when migration is old. – Resonant

If anyone encounters an error like sqlalchemy.exc.InvalidRequestError: Could not reflect: requested table(s) not available in Engine(postgresql+psycopg2://dma_admin:***@localhost:5433/dma): (s, o, m, e, t, a, b, l, e), make sure to include the final comma , prior to the final two closing parentheses at the end of the meta.reflect method call. That bit me. – Ingenerate

thanks for your answer.. getting the metadata from the current database state is a good idea – Bernardabernardi

some_table_tbl = Currency('currencies', meta) TypeError: __init__() takes 1 positional argument but 3 were given Have anyone encountered this error? – Tranship

Answer 3

If you have your tables as models in code, you can also use the __table__ attribute:

from src.models.services import Service

op.bulk_insert(Service.__table__,
    [   
        {
        'id': 88,
        'name':'Test 1',
        'is_active': 'true',
        'include_in_broker_fee': 'true',
        'is_domestic': 'true',
        'is_international': 'true'
        },
        {
        'id': 89,
        'name':'Test 2',
        'is_active': 'true',
        'include_in_broker_fee': 'true',
        'is_domestic': 'true',
        'is_international': 'true'
        }
   ])

Sliver

This is nice example how to access table from Model, however not for migrations (which alembic implies) => during migrations you don't want to be coupled on a Model existence :) – Juliojulis

Answer 4

Update if using SQLAlchemy 2.0, according to documentation "bound data" was removed, which basically means that your code would've been slightly different if using recommended earlier in this post approach. You would simply need to change

from sqlalchemy import Table, MetaData

meta = MetaData(bind=op.get_bind())
services = Table('services', meta)

to

from sqlalchemy import Table, MetaData

meta = MetaData()
# reflect individual table
services = Table('services', meta, autoload_with=op.get_bind())

Dickenson