Re: [sqlalchemy] MetaData().reflect() fails on engine with non-default isolation level
On Mon, Jul 8, 2019, at 6:08 AM, Gunnar Þór Magnússon wrote:
> Hello,
>
> Some legacy code at work that I don't fully understand (and whose authors are
> all long gone) does the following to eventually insert some values into the
> table it gets:
>
> meta = sqlalchemy.MetaData(bind=session.get_bind())
> meta.reflect()
> table = sqlalchemy.Table(TableName.__tablename__, meta, autoload=True)
>
> The meta.reflect() call fails if the session comes from a sessionmaker bound
> to a copy of an engine that has a non-default isolation level, for example:
>
> engine = sqlalchemy.create_engine(setup)
> maker =
> sqlalchemy.orm.sessionmaker(bind=engine.execution_options(isolation_level='SERIALIZABLE'))
> session = maker()
>
> The error message it gives is "AttributeError: 'OptionEngine' object has no
> attribute 'engine'". The reason seems to be that in this case,
> session.get_bind() returns an OptionEngine, which seems to be a facade around
> the original Engine; it proxies to the original one.
oh that definitely would be a bug.
>
> See below for a minimal complete example [A] with a Conda environment [B].
>
> I can get around this by checking whether the get_bind() call gives me an
> OptionEngine, and pull the proxied Engine out of the object if it does, but
> that involves using internal SQLAlchemy object variables and might break in
> the future.
>
> 1. Should what I'm doing here work at all? That is, should OptionEngine
> define 'engine' and have it resolve to whatever original Engine it was
> created from?
>
> 2. If not, and what I've inherited is horrifying, does anyone have ideas on
> how to make it better? The only comments around this piece of code say they
> do this dance to be able to insert values into columns that are in the DB
> table but not the SQLAlchemy ORM table.
you have a workaround in place now right? I will commit a fix for this today
https://github.com/sqlalchemy/sqlalchemy/issues/4754
>
> Thank you,
> Gunnar
>
> [A] Minimal example (done here in sqlite3, also works on SQL Server):
>
> import sqlalchemy
> import sqlalchemy.orm
> import sqlalchemy.ext.declarative
>
> Base = sqlalchemy.ext.declarative.declarative_base()
>
> class Foo(Base):
> __tablename__ = 'foos'
> id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
> val = sqlalchemy.Column(sqlalchemy.Integer)
>
> if __name__ == '__main__':
> db = sqlalchemy.create_engine('sqlite:///')
> Foo.metadata.create_all(db)
>
> maker =
> sqlalchemy.orm.sessionmaker(bind=db.execution_options(isolation_level='SERIALIZABLE'))
> session = maker()
>
> bind = session.get_bind()
> meta = sqlalchemy.MetaData(bind=bind)
> meta.reflect()
> table = sqlalchemy.Table(Foo.__tablename__, meta, autoload=True)
>
> vals = list(range(100))
> ins = table.insert().values(vals)
> session.execute(ins)
> session.commit()
> session.close()
>
> [B] Conda environment that reproduces the problem in the example:
>
> # Name Version Build Channel
> _libgcc_mutex 0.1 main
> ca-certificates 2019.5.15 0
> certifi 2019.6.16 py37_0
> libedit 3.1.20181209 hc058e9b_0
> libffi 3.2.1 hd88cf55_4
> libgcc-ng 9.1.0 hdf63c60_0
> libstdcxx-ng 9.1.0 hdf63c60_0
> ncurses 6.1 he6710b0_1
> openssl 1.1.1c h7b6447c_1
> pip 19.1.1 py37_0
> python 3.7.3 h0371630_0
> readline 7.0 h7b6447c_5
> setuptools 41.0.1 py37_0
> sqlalchemy 1.3.5 py37h7b6447c_0
> sqlite 3.28.0 h7b6447c_0
> tk 8.6.8 hbc83047_0
> wheel 0.33.4 py37_0
> xz 5.2.4 h14c3975_4
> zlib 1.2.11 h7b6447c_3
>
>
>
> --
> 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/d81b41c0-5e38-4601-a487-c763d3896b91%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
Re: [sqlalchemy] programming error (psycopg2.ProgrammingError) can't adapt type 'Point'
On Mon, Jul 8, 2019, at 4:45 AM, Mauro Mussin wrote:
> I have a postgres-postgis dB with a geometry:point field: if I insert the
> values directly
> INSERT INTO points(coordinates) VALUES (ST_GeomFromText('POINT(10.809003
> 54.097834)',4326));
>
> no issue, but if I use this sequence (Python)
> *->import data in a dataframe: data contains lat and long columns;*
> *-> trasform the lon,lat column in a shapely.geometry.point;*
> *->create a geodataframe (gdf)*
> *->connect to dB with psycopg2 and sqlalchemy*
> **
> gdf.to_sql(', conn,if_exists='append',index=False,schema=)
>
>
> I got this error:
Is this with geoalchemy? Otherwise I don't know where you are getting the Point
class from. I'm not familiar with "gdf" however if it creates SQLAlchemy
expressions, it needs to ensure that an insert construct refers to Table
metadata that includes a Point-handling datatype, such as
https://geoalchemy-2.readthedocs.io/en/latest/types.html#geoalchemy2.types.Geometry,
which will produce the proper coercions from a "Point" object to the
appropriate string/object representation for psycopg2 to accept.
Alternatively, psycopg2 also allows for native data handlers, again since I
don't know what "gdf" is (googling was inconclusive), they might have
recommendations to do it this way, not sure. "gdf" is where you need to get
assistance.
>
> --
> ProgrammingError Traceback (most recent call last)
> /opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in
> _execute_context(self, dialect, constructor, statement, parameters, *args)
> * 1223* self.dialect.do_executemany(
> -> 1224 cursor, statement, parameters, context
> * 1225* )
> /opt/conda/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py
> in do_executemany(self, cursor, statement, parameters, context)
> *751* else:
> --> 752 cursor.executemany(statement, parameters)
> *753*
> ProgrammingError: can't adapt type 'Point'
>
>
> Any hints?
>
>
> --
> 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/0a501873-7f27-45d1-93ec-c08aa0922a30%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/dc75ba95-bc33-4204-9342-5ec9cbf7928a%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: not the same number of columns in the 2 queries of the recursive request
are you able to set echo="debug" on your create_engine(), view the SQL being
emitted as well as the results being returned, and then ensure the SQL
statement and results are what you are looking for? once you have that we can
make sure the ORM interprets these results correctly.
On Sun, Jul 7, 2019, at 4:02 PM, Olivier SAINT-EVE wrote:
> hello,
>
> thank you a lot for your answer.
>
> I modified my request and now it compiles, but I do not get the expected
> result...
> the method should stop when it encounters a 'Territoire' which is 'region',
> but it lists all the elements, even those of scale (type) 'ville' (I mean
> town) which shhould be removed from the result.
>
> here is a small code reproducing the error:
>
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import relationship, sessionmaker, aliased
> from sqlalchemy.schema import Table
>
> Base = declarative_base()
>
> parent_child = Table('parent_child', Base.metadata,
> Column('parent_id', ForeignKey('territoire.id'), primary_key=True),
> Column('child_id', ForeignKey('territoire.id'), primary_key=True))
>
>
> class Territoire(Base):
> __tablename__ = 'territoire'
> id = Column(Integer, primary_key=True)
> name = Column(String)
> scale = Column(String)
>
> children = relationship(
> 'Territoire',
> secondary=parent_child,
> back_populates='parents',
> primaryjoin=parent_child.c.parent_id == id,
> secondaryjoin=parent_child.c.child_id == id,
>
> )
>
> parents = relationship(
> 'Territoire',
> secondary=parent_child,
> primaryjoin=parent_child.c.child_id==id,
> secondaryjoin=parent_child.c.parent_id==id,
> back_populates='children'
> )
>
> def __repr__(self):
> chaine1= "" % (self.id, self.name, self.scale)
> return chaine1
>
> def __init__(self, name, scale):
> self.name = name
> self.scale = scale
> self.parents=[]
> self.children=[]
>
>
> if __name__ == "__main__":
> engine = create_engine('postgresql://postgres:admin@localhost/territoires2',
> echo=True)
> # Base = declarative_base()
> Session = sessionmaker(bind=engine)
> Session.configure(bind=engine)
> Base.metadata.create_all(engine)
> session = Session()
>
> # print("territoire table="+Territoire.__table__)
> france = Territoire(name="france", scale="pays")
> metz = Territoire(name='metz', scale='ville')
> grand_est = Territoire(name='Grand Est', scale='region')
>
> metz.parents.append(grand_est)
> grand_est.parents.append(france)
> var = Territoire('Var', 'region')
> france.children.append(var)
> sete = Territoire('sete', 'ville')
> var.children.append(sete)
> session.add_all([france, metz, grand_est])
> session.add_all([var,sete])
>
> # +++
>
> # **
> session.commit()
> # **
>
> # for t in session.query(Territoire):
> # print(t)
>
> element0 = aliased(Territoire)
> sub_territories = session.query(element0.id,element0.name,element0.scale). \
> filter(element0.id == 1). \
> filter(element0.scale != 'region'). \
> cte(name='sub_territories', recursive=True)
>
> # st_alias = aliased(sub_territories,name="sa")
> relation = aliased(parent_child, name="rel")
> porteur = aliased(Territoire, name="p")
> rec=aliased(sub_territories,name="rec")
>
> sub_territories = sub_territories.union(
> session.query(porteur.id,porteur.name,porteur.scale). \
> # filter(porteur.id == relation.c.child_id). \
> filter(porteur.id == relation.c.child_id). \
> filter(relation.c.parent_id == sub_territories.c.id). \
> filter(sub_territories.c.scale != 'region'))
>
> # sub_territories = sub_territories.union(corps)
>
> print(sub_territories)
>
> q = session.query(Territoire).select_from(sub_territories)
> print('solution =' + str(q.all()))
>
> my problem seems to be logical, I will try to solve it, but maybe someone
> could help me to go faster!
>
> thank you
>
>
>
>
>
> Le dimanche 7 juillet 2019 15:29:19 UTC+2, Olivier SAINT-EVE a écrit :
>> I created this recursive query:
>> `element0 = aliased(Territoire)
sub_territories = session.query(element0, element0.id). \
filter(element0.id == 1). \
filter(element0.scale != 'Region'). \
cte(name='sub_territories', recursive=True)
st_alias = aliased(sub_territories)
relation = aliased(parent_child)
porteur = aliased(Territoire)
corps = session.query(porteur, relation.c.child_id). \
join(porteur, porteur.id == relation.c.child_id). \
join(st_alias, relation.c.parent_id == st_alias.c.id). \
filter(st_alias.c.scale != 'Region')
sub_territories = sub_territories.union(corps)`
>> which should give all the children of element0 recursively, stopping at the
>> 'Region' level(items are linked by a many-to-many
[sqlalchemy] MetaData().reflect() fails on engine with non-default isolation level
Hello,
Some legacy code at work that I don't fully understand (and whose authors
are all long gone) does the following to eventually insert some values into
the table it gets:
meta = sqlalchemy.MetaData(bind=session.get_bind())
meta.reflect()
table = sqlalchemy.Table(TableName.__tablename__, meta, autoload=True)
The meta.reflect() call fails if the session comes from a sessionmaker
bound to a copy of an engine that has a non-default isolation level, for
example:
engine = sqlalchemy.create_engine(setup)
maker = sqlalchemy.orm.sessionmaker(bind=engine.execution_options(
isolation_level='SERIALIZABLE'))
session = maker()
The error message it gives is "AttributeError: 'OptionEngine' object has no
attribute 'engine'". The reason seems to be that in this case,
session.get_bind() returns an OptionEngine, which seems to be a facade
around the original Engine; it proxies to the original one.
See below for a minimal complete example [A] with a Conda environment [B].
I can get around this by checking whether the get_bind() call gives me an
OptionEngine, and pull the proxied Engine out of the object if it does, but
that involves using internal SQLAlchemy object variables and might break in
the future.
1. Should what I'm doing here work at all? That is, should OptionEngine
define 'engine' and have it resolve to whatever original Engine it was
created from?
2. If not, and what I've inherited is horrifying, does anyone have ideas on
how to make it better? The only comments around this piece of code say they
do this dance to be able to insert values into columns that are in the DB
table but not the SQLAlchemy ORM table.
Thank you,
Gunnar
[A] Minimal example (done here in sqlite3, also works on SQL Server):
import sqlalchemy
import sqlalchemy.orm
import sqlalchemy.ext.declarative
Base = sqlalchemy.ext.declarative.declarative_base()
class Foo(Base):
__tablename__ = 'foos'
id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
val = sqlalchemy.Column(sqlalchemy.Integer)
if __name__ == '__main__':
db = sqlalchemy.create_engine('sqlite:///')
Foo.metadata.create_all(db)
maker = sqlalchemy.orm.sessionmaker(bind=db.execution_options(
isolation_level='SERIALIZABLE'))
session = maker()
bind = session.get_bind()
meta = sqlalchemy.MetaData(bind=bind)
meta.reflect()
table = sqlalchemy.Table(Foo.__tablename__, meta, autoload=True)
vals = list(range(100))
ins = table.insert().values(vals)
session.execute(ins)
session.commit()
session.close()
[B] Conda environment that reproduces the problem in the example:
# NameVersion Build Channel
_libgcc_mutex 0.1main
ca-certificates 2019.5.15 0
certifi 2019.6.16py37_0
libedit 3.1.20181209 hc058e9b_0
libffi3.2.1hd88cf55_4
libgcc-ng 9.1.0hdf63c60_0
libstdcxx-ng 9.1.0hdf63c60_0
ncurses 6.1 he6710b0_1
openssl 1.1.1c h7b6447c_1
pip 19.1.1 py37_0
python3.7.3h0371630_0
readline 7.0 h7b6447c_5
setuptools41.0.1 py37_0
sqlalchemy1.3.5py37h7b6447c_0
sqlite3.28.0 h7b6447c_0
tk8.6.8hbc83047_0
wheel 0.33.4 py37_0
xz5.2.4h14c3975_4
zlib 1.2.11 h7b6447c_3
--
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/d81b41c0-5e38-4601-a487-c763d3896b91%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] programming error (psycopg2.ProgrammingError) can't adapt type 'Point'
I have a postgres-postgis dB with a geometry:point field: if I insert the
values directly
INSERT INTO points(coordinates) VALUES (ST_GeomFromText('POINT(10.809003
54.097834)',4326));
no issue, but if I use this sequence (Python)
*->import data in a dataframe: data contains lat and long columns;*
*-> trasform the lon,lat column in a shapely.geometry.point;*
*->create a geodataframe (gdf)*
*->connect to dB with psycopg2 and sqlalchemy*
gdf.to_sql(', conn,if_exists='append
',index=False,schema=)
I got this error:
--ProgrammingError
Traceback (most recent call
last)/opt/conda/lib/python3.7/site-packages/sqlalchemy/engine/base.py in
_execute_context(self, dialect, constructor, statement, parameters, *args)
1223 self.dialect.do_executemany(-> 1224
cursor, statement, parameters, context 1225 )
/opt/conda/lib/python3.7/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py
in do_executemany(self, cursor, statement, parameters, context)751
else:--> 752 cursor.executemany(statement, parameters)753
ProgrammingError: can't adapt type 'Point'
Any hints?
--
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/0a501873-7f27-45d1-93ec-c08aa0922a30%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] "No such polymorphic_identity" when models in different modules
Got it.
As I like to "eat the pudding":
x.py:
class Meta(type):
print("Here is Meta")
def __new__(cls, name, bases, dct):
print("meta.new")
return super().__new__(cls, name, bases, dct)
class Foo(metaclass=Meta):
print("Here is Foo")
y.py:
from x import Foo
class What(Foo):
print("Here's What, a Foo")
Giving as output on python y.py:
Here is Meta
Here is foo
meta.new
Here's What, a Foo
meta.new
Showing that the meta class indeed is called on construction of "What":
hence there you have the mechanics of class registration.
Now that's covered I've to say I'm not too happy importing module's with no
apparent use, but that seems the nature of the beast.
Kind regards,
Nacho
> The pattern from a software architecture point of view was identified by
> Martin Fowler as the "registry" pattern,
> https://martinfowler.com/eaaCatalog/registry.html, however this pattern
> doesn't say much about the mechanics used.The registry pattern refers
> to linking the creation of a class or object to its automatic inclusion in
> some semi-global or in some cases global registry, which is one SQLAlchemy
> uses a lot, in some cases explicitly and in other cases only behind the
> scenes.
>
> The declarative metaclass contains a function which scans your class for
> attributes, builds a Table from these attributes which is associated with
> the MetaData collection that the metaclass has access towards (this is
> registry pattern #1). Then, the mapper() function is invoked against the
> class you created along with this Table, which creates a new Mapper object
> that is added to a global, weak-referencing collection inside the
> sqlalchemy.orm.mapper module (this is registry pattern #2). When the
> mapper "inherits" from another one as seems to be the case here, that
> inheriting mapper is also amended to include this new mapper in its
> collection of subclasses.
>
> The overall "mapper configure" step which you may have seen scans through
> this registry of mapper objects and makes sure all the mappers that refer
> to each other, usually through the relationship() linkage, are fully linked
> and have been found.
>
>
>
--
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/bac20779-de45-4889-9a03-25f0d4fd20b5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
