Creating a new schema name during migration with Alembic

[Josie Barth]

Hello,

I'm building an application using Flask as the web framework, PostgreSQL for the database, and Flask-SQLAlchemy to communicate between the two (I'm still very new to all these technologies.)  I want to use Alembic to migrate my SQLAlchemy tables that I've created in a models.py file to PostgreSQL.  I'm using the code from the "Local Migration" section specified in the following tutorial:

https://realpython.com/blog/python/flask-by-example-part-2-postgres-sqlalchemy-and-alembic/

Here is an example of one my tables, where I've attached it to an explicit schema: 

___ 

class Student(db.Model):

    __tablename__ = 'Students'

    # __table_args__ = {u'schema': 'OurHouse'}

    Id = db.Column(db.Integer, primary_key=True, server_default=db.FetchedValue())

    FirstName = db.Column(db.String(50), nullable=False)

    LastName = db.Column(db.String(50), nullable=False)

    Email = db.Column(db.String(62), nullable=False)

    Phone = db.Column(db.String(10), nullable=False)

    IsActive = db.Column(db.Boolean, nullable=False)

    CreatedAt = db.Column(db.DateTime(True), nullable=False)

    UpdatedAt = db.Column(db.DateTime(True), nullable=False)

___

I have no problems doing the first two steps (initializing Alembic and creating the migration):

$ python manage.py db init

$ python manage.py db migrate

I do have an issue when I attempt to run:

$ python manage.py db upgrade

Basically, I get the error that "OurHouse" isn't a schema, and I don't know how to go about creating one in the migration file that is generated.  I'm thinking I need to do something like this:

"""empty message

Revision ID: c6a019bc79e1

Revises: 

Create Date: 2017-03-01 21:55:02.360888

"""

from alembic import op

import sqlalchemy as sa

# revision identifiers, used by Alembic.

revision = 'c6a019bc79e1'

down_revision = None

branch_labels = None

depends_on = None

def upgrade():

      # Not sure if this is possible:

op.create_schema('OurHouse')

     # This is what's given, along with the code for my other tables: 

op.create_table('Students',

    sa.Column('Id', sa.Integer(), server_default=sa.FetchedValue(), nullable=False),

    sa.Column('FirstName', sa.String(length=50), nullable=False),

    sa.Column('LastName', sa.String(length=50), nullable=False),

    sa.Column('Email', sa.String(length=62), nullable=False),

    sa.Column('Phone', sa.String(length=10), nullable=False),

    sa.Column('IsActive', sa.Boolean(), nullable=False),

    sa.Column('CreatedAt', sa.DateTime(timezone=True), nullable=False),

    sa.Column('UpdatedAt', sa.DateTime(timezone=True), nullable=False),

    sa.PrimaryKeyConstraint('Id'),

    schema='OurHouse'

    )

     

Thanks in advance!

[Josie Barth]

One clarifying edit:

Regarding my example table,

 __table_args__ = {u'schema': 'OurHouse'}

should not be commented out.  I was playing around with the migration and I got it to work when I took out all references to the schema 'OurHouse' that I wanted to attach my tables to.  The tables by default migrated to the "public" schema without issue.  Further, if I manually added the schema 'OurHouse' to PostgreSQL before attempting to migrate, I was able to then migrate my tables to the schema without issue.  My question still remains if it's possible to create a new schema name during migration so the tables can also be created without error.  Thanks!

[mike bayer]

You want to just create the schema, op.execute("CREATE SCHEMA OurHouse")

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy-alem...@googlegroups.com
> <mailto:sqlalchemy-alem...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Josie Barth]

Thanks, Mike, that did the trick!

On Thursday, March 2, 2017 at 12:35:31 PM UTC-5, mike bayer wrote:

You want to just create the schema, op.execute("CREATE SCHEMA OurHouse")



On 03/02/2017 10:26 AM, Josie Barth wrote:
> One clarifying edit:
>
> Regarding my example table,
>
>  __table_args__ = {u'schema': 'OurHouse'}
>
> should not be commented out.  I was playing around with the migration
> and I got it to work when I took out all references to the schema
> 'OurHouse' that I wanted to attach my tables to.  The tables by default
> migrated to the "public" schema without issue.  Further, if I manually
> added the schema 'OurHouse' to PostgreSQL before attempting to
> migrate, I was able to then migrate my tables to the schema without
> issue.  My question still remains if it's possible to create a new
> schema name during migration so the tables can also be created without
> error.  Thanks!
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to sqlalchemy-alembic+unsub...@googlegroups.com
> <mailto:sqlalchemy-alembic+unsub...@googlegroups.com>.