On Fri, May 10, 2019 at 7:09 AM Diego Quintana <[email protected]> wrote: > > 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?
in your alembic environment, there's an env.py that Alembic creates for you, which we encourage you to modify to suit the needs of your project. Inside of this file, there is a transaction block created which you can see an example at https://github.com/sqlalchemy/alembic/blob/master/alembic/templates/generic/env.py#L63, where it checks out a Connection from an Engine, and then calls an Alembic-specific block called "begin_transaction()" that ensures the Connection has a transaction started which will commit at the end of the block. For background on what Connection and Engine are, see https://docs.sqlalchemy.org/en/13/core/connections.html. So once you get into your migration script, op.get_bind() gives you that Connection object, the one we made on line 63 of the env.py example above. In virtually all cases, in a migration script this would be the thing you use to work with the database connection directly. when OTOH we do the bit wtih "bind.engine.connect()", basically "bind" here is the Connection that's in a transaction; it has an .engine attribute which is, "the Engine object which I came from". Easy enough, but then what you're doing is making *another* Connection by calling connect() again on that engine. This Connection will run SQL in a separate transaction than the one in which your migration script was set up to run within from your env.py file. So if you do things like work with the table you just created, if your database supports what's called "transactional DDL", meaning a "CREATE TABLE" will not be visible until the transaction is committed, this second Connection will not be able to see that new table because it isn't committed to the database yet. hope this helps! > > > 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. -- 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/CA%2BRjkXGFfNJ-Kxwu3G2%2BRiHKwEtjMNSj%2BCLu%2B4opdegKF0oDcA%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
