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:
> 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/bf1e5680-18a4-4387-9639-2f3abe6d194e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
