On Fri, May 10, 2019 at 10:34 AM Diego Quintana <daquinta...@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.
>
> Am Freitag, 10. Mai 2019 13:09:04 UTC+2 schrieb 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!
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@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.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@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/CA%2BRjkXHc3N6R1830dCFwrtj%2B%2Bvp-aRZTC3iNdu-zfNgfcQdYMA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
