`Programing Error, Table doesn't exist` using alembic bulk_insert

Question

I am trying to use alembic to create a new table and use bulk_insert to add new entries. I am following the bulk_insert example here. Here is the code that I am running:

from alembic import op
import sqlalchemy as sa
from sqlalchemy.sql import table, column

def upgrade():
    status_reviews = table(
        'status',
        column('status_order', sa.Integer),
        column('status_name', sa.String(64))
        )

    op.bulk_insert(status_reviews,
        [
            {'status_order': 1, 'status_name': 'New'},
            {'status_order': 2, 'status_name': 'Reviewed'},
            {'status_order': 3, 'status_name': 'Design'},
        ]
    )

I get the following error when trying to run it:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1146, u"Table 'partsdb.review_status_definitions' doesn't exist") 'INSERT INTO review_status_definitions (status_order, status_name) VALUES (%s, %s)' ((1, 'New'), (2, 'Reviewed'), (3, 'Design')

I know that the base alembic stuff is working because I am able to add other tables (without giving entries). When I check the database in MySQL Workbench, the status table does not show up at all, which makes me think it is actually failing on the create table command.

Answer 1

I think you're using the wrong syntax to create a table using alembic. You're defining the table schema there, which is how you'd set it up in Python code (and create via db.create_all()), but you create tables in alembic using a different syntax:

def upgrade():
    op.create_table('categories',
      sa.Column('id', sa.Integer(), primary_key=True),
      sa.Column('name', sa.String(length=100), nullable=False),
      sa.Column('modified', sa.DateTime(), nullable=True),
      sa.Column('created', sa.DateTime(), nullable=True),
    )
    for category in ["example", "another example"]:
      op.execute("INSERT INTO categories (name, modified, created) VALUES ('%s', '%s', '%s')" % (category, datetime.utcnow(), datetime.utcnow()))

Alembic does have an autogenerate feature where it will read in your table schema defined in Python, diff it against the database and spit out a upgrade file for you. It sounds like it might be overkill for you now, but it's good to know about.