On Wed, May 15, 2019 at 5:23 AM Diego Quintana <daquinta...@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.
>
> Thanks!
>
> 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/bf1e5680-18a4-4387-9639-2f3abe6d194e%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%2BRjkXHCfHd5frL1Ww8PXWwoDdumx64LkcJRO93vpQwuVyDJSg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
