Hello! I'm trying to seed my database using my first migration script, and
I have trouble understanding what `op.get_bind()` returns, and mainly the
difference between `Connection` and `engine.connection()`. Say I have a
migration script that creates a table called `cities` and then it performs
a `COPY FROM CSV` using `engine.raw_connection`.
The method `insert_default_csv` is failing in (note that line might not be
the exact one)
File "/src/app/migrations/versions/d054d8692328_initial_migration.py",
line 91, in insert_default_csv
cursor.copy_expert(cmd, f)
psycopg2.ProgrammingError: relation "cities" does not exist
This is probably due to the fact that the `bind` object the class `City`
was created with and `bind.engine` used in that method are pointing to
different places. This can be tested with the bit
logger.info(bind.engine.connect() == bind) # INFO [alembic] False
logger.info(bind.engine.dialect.has_table(bind.engine.connect(), "cities"))
# INFO [alembic] False
logger.info(bind.engine.dialect.has_table(bind, "cities")) # INFO
[alembic] True
In short what are the differences between these two objects and how can I
access a working engine in Alembic from the context or the bind?
I'm using
Flask-SQLAlchemy==2.4.0
marshmallow-sqlalchemy==0.16.2
SQLAlchemy==1.3.3
alembic==1.0.10
Here is my migration script
"""Initial migration
Revision ID: d054d8692328
Revises:
Create Date: 2019-03-08 09:34:02.836061
"""
import time
import csv
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from pathlib import Path
import logging
from alembic import op, context
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = "d054d8692328"
down_revision = None
branch_labels = None
depends_on = None
DATA_FOLDER = Path.cwd().joinpath(f"migrations/data/{revision}/")
logger = logging.getLogger("alembic")
# ─── NON TEMPLATED IMPORTS
──────────────────────────────────────────────────────────────
# ─── LOCAL MODEL DEFINITIONS
────────────────────────────────────────────────────
db = SQLAlchemy()
class TerritoryMixin(object):
"""A Mixin to keep definitions DRY"""
id = db.Column(db.Integer, primary_key=True)
code = db.Column(db.Integer, nullable=False, unique=True)
code_alias = db.Column(db.String(64), nullable=False, unique=True)
name = db.Column(db.String(64), unique=True)
region_code = db.Column(db.Integer, nullable=True)
last_updated = db.Column(db.DateTime(), default=datetime.utcnow)
class InsertionMixin(object):
@classmethod
def insert_default_elements(cls, session):
"""A local implementation of
`my_app.models.mixins.BaseModelMixin.insert_defaults`
This version does NOT check state, and is meant to be handled by
alembic only.
"""
for element in cls._default_elements:
# constructor call
instance = cls(**element)
session.add(instance)
session.commit()
@classmethod
def insert_default_csv(cls, csv_path, engine):
"""A local implementation of
`my_app.models.mixins.BaseModelMixin.insert_from_csv_psql_raw`
This version does NOT check state and is meant to be handled by alembic
only.
"""
SEP = ';'
HEADER = True
logger.debug('using engine: "%s"', engine)
with open(csv_path, "rt") as f:
columns = next(csv.reader(f))[0].split(SEP)
logger.debug('columns: "%s"', columns)
f.seek(0)
coltxt = " (%s)" % ", ".join(columns) if columns else ""
logger.debug('coltxt: "%s"', coltxt)
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute("SET datestyle = 'ISO,DMY'")
cmd = "COPY %s%s FROM STDIN DELIMITER '%s' CSV %s" % (
cls.__tablename__,
coltxt,
SEP,
"HEADER" if HEADER else "", ) # \COPY instead of COPY is
preferred
logger.debug('SQL query: "%s"', cmd)
cursor.copy_expert(cmd, f)
# commit the query
t0 = time.time()
conn.commit()
# take the file pointer back to the beginning so we can read it
again
f.seek(0)
logger.info(
"Table '%s': %i row(s) inserted in %.1f seconds.",
cls.__tablename__,
sum(1 for row in csv.reader(f)), time.time() - t0, )
class City(InsertionMixin, TerritoryMixin, db.Model):
__tablename__ = "cities"
# users = backref from many to many relationship with User model
_default_csv_filename = DATA_FOLDER.joinpath('cities.csv')
# ─── END OF LOCAL MODEL DEFINITIONS
─────────────────────────────────────────────
# ─── BEGIN OF ALEMBIC COMMANDS
──────────────────────────────────────────────────
def upgrade():
schema_upgrades()
data_upgrades()
def downgrade():
schema_downgrades()
data_downgrades()
def schema_upgrades():
bind = op.get_bind()
session = sa.orm.Session(bind=bind)
City.__table__.create(bind)
def schema_downgrades():
op.drop_table("cities")
def data_upgrades():
bind = op.get_bind()
session = sa.orm.Session(bind=bind)
# ─── INSERT DATA DEFINED LOCALLY
────────────────────────────────────────────────
logger.info(bind) # INFO [alembic] <sqlalchemy.engine.base.Connection
object at 0x7fb78b1cc518>
logger.info(session) # INFO [alembic] <sqlalchemy.orm.session.Session
object at 0x7fb77b0b5eb8>
logger.info(bind.engine) # INFO [alembic]
Engine(postgresql+psycopg2://lap:***@postgres:5432/lap)
# https://groups.google.com/forum/#!topic/sqlalchemy/PsBfyWiBgBY
logger.info(bind.engine.connect() == bind) # INFO [alembic] False
logger.info(bind.engine.dialect.has_table(bind.engine.connect(), "cities"))
# INFO [alembic] False
logger.info(bind.engine.dialect.has_table(bind, "cities")) # INFO
[alembic] True
City.insert_default_csv(City._default_csv_filename, engine=bind.engine) #
fails with "cities" table does not exist, WHY?
def data_downgrades():
bind = op.get_bind()
session = sa.orm.Session(bind=bind)
session.drop_all()
# ─── END OF ALEMBIC COMMANDS
────────────────────────────────────────────────────
Thanks!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/cc4d5a88-a8b6-496b-9b18-9d96722d2ed1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
