Differences between `op.get_bind()` and `op.get_bind().engine.connection()`

[Diego Quintana]

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!

[Mike Bayer]

On Fri, May 10, 2019 at 7:09 AM Diego Quintana <daqui...@gmail.com> 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!

> --
> 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 sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> 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.

[Diego Quintana]

Thanks for your answer. If I got this right, it makes sense to think of two different transactions happening, being the second one not able to "see" what the first has done (e.g. creating tables) until it has finished (i.e. when the migration has already ended). Sort of a race condition, if it's not one.

My original problem is that the method I use to issue a `COPY CSV` to the db relies on

conn = engine.raw_connection()
cursor = conn.cursor()

cursor.copy_expert(cmd, f)

And if I create `conn` from `bind.engine` I'm running the problem that `"cities" does not exist`, which probably happens because of what is being discussed here. What other options do I have? How would I get a raw connection from `context` or `bind`?

Once again, thanks for your time

[Mike Bayer]

On Fri, May 10, 2019 at 10:34 AM Diego Quintana <daqui...@gmail.com> wrote:
>
> Thanks for your answer. If I got this right, it makes sense to think of two different transactions happening, being the second one not able to "see" what the first has done (e.g. creating tables) until it has finished (i.e. when the migration has already ended). Sort of a race condition, if it's not one.
>
> My original problem is that the method I use to issue a `COPY CSV` to the db relies on
>
> conn = engine.raw_connection()
> cursor = conn.cursor()
>
> cursor.copy_expert(cmd, f)
>
>
> And if I create `conn` from `bind.engine` I'm running the problem that `"cities" does not exist`, which probably happens because of what is being discussed here. What other options do I have? How would I get a raw connection from `context` or `bind`?
>
> Once again, thanks for your time

easy enough, when you have the Connection that comes from get_bind(),
there is an accessor on it called ".connection", which is a proxy
object to the actual DBAPI connection. If for some reason that proxy
object is blocking your operation (which it will not for simple
cursor() access), you can call ".connection" on that too, e.g.
op.get_bind().connection.connection.

> --
> 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 sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> 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/f7d6923c-f408-4749-97a0-19355498e2bf%40googlegroups.com.

[Diego Quintana]

Hello again!

This worked, thanks!

I noticed that if I use `bind.connection`, I do not need to call `engine.raw_connection` anymore. I wonder why is this. Is this a proxy to a raw connection or it was never necessary from the start?

Thanks!

Am Freitag, 10. Mai 2019 13:09:04 UTC+2 schrieb Diego Quintana:

[Mike Bayer]

On Wed, May 15, 2019 at 5:23 AM Diego Quintana <daqui...@gmail.com> wrote:
>
> Hello again!
>
> This worked, thanks!
>
> I noticed that if I use `bind.connection`, I do not need to call `engine.raw_connection` anymore. I wonder why is this. Is this a proxy to a raw connection or it was never necessary from the start?

"bind" is itself a Connection object in this case so you are in fact
calling .connection from the Connection object.

> --
> 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 sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> 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/bf1e5680-18a4-4387-9639-2f3abe6d194e%40googlegroups.com.