Problem

You have Alembic migrations. The current migration creates a new table and you want to insert rows into that table using raw SQL.

You are trying to establish a connection with the database using a DBAPI such as psycopg2, but when you try to insert rows you see an error that says something like the table doesn’t exist yet.

Your migration might look something like this:

import psycopg2
import sqlalchemy as sa
from alembic import op

def upgrade():
    op.create_table(
        'person',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=False),
    )

    bind = op.get_bind()
    session = Session(bind=bind)

    people = ['Harry', 'Ron', 'Hermione']

    conn = psycopg2.connect('postgres://username:password@localhost/dbname')
    cursor = conn.cursor()

    insert_person_sql = "INSERT INTO person (name) VALUES ('{name}');"
    for person in people:
        cursor.execute(insert_person_sql.format(name=person)

    conn.commit()

When attempting to run the migration there is a failure on the line cursor.execute(...).

Why does this happen? I believe the reason for this is that Alembic manages migrations as a transaction. The Alembic DB session created the person table, but doesn’t commit the change to the database until the entire migration is complete.

Therefore, the other connection, conn, established with the psycopg2 library is not aware of the person table while the migration is still running.

Solution

If you find yourself in this situation, the solution may be to bind to the Alembic session. Then you can execute SQL in a context that is aware of the new person table.

The new code will look something like this:

import sqlalchemy as sa
from alembic import op
from sqlalchemy.orm import sessionmaker

Session = sessionmaker()

def upgrade():
    op.create_table(
        'person',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=False),
    )

    bind = op.get_bind()
    session = Session(bind=bind)

    people = ['Harry', 'Ron', 'Hermione']

    insert_person_sql = "INSERT INTO person (name) VALUES ('{name}');"
    for person in people:
        session.execute(insert_person_sql.format(name=person)

Note, Alembic components such as the revision and down_revision field have been omitted for brevity, as well as the downgrade() function.