[sqlalchemy] Could not locate column in row for column
Hello sqlalchemy team.
im a new user to the alchemy, and doing the tutorial on ur site.
ran ur tutorial on adding information to databases and querying against a
virtual db (sqlite in memory)
after i thought i got that, i tried testing against a real db - mysql db.
im using the driver of pymysql version 0.4 (i know its kinda old, but when
was using newer version i had other bugs, not related to this topic).
my code is as follows:
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from CpuTable import CpuTable
def read_from_DB():
engine = create_engine('mysql+pymysql://***@***/test', echo=True)
Session = sessionmaker(bind=engine)
session=Session()
#insert = CpuTable(id='15',name= 'igal')
#session.add(insert)
#session.commit()
print()
print(str(session.query(CpuTable.id.label('id')).all()))
for cpu_id,cpu_name in session.query(CpuTable).all():
print(cpu_id,cpu_name)
my stack trace is as follows:
C:\Python33\python.exe J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py
2014-02-12 11:23:57,193 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2014-02-12 11:23:57,193 INFO sqlalchemy.engine.base.Engine ()
2014-02-12 11:23:57,196 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'character_set%%'
2014-02-12 11:23:57,196 INFO sqlalchemy.engine.base.Engine ()
2014-02-12 11:23:57,198 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'sql_mode'
2014-02-12 11:23:57,198 INFO sqlalchemy.engine.base.Engine ()
2014-02-12 11:23:57,199 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-02-12 11:23:57,199 INFO sqlalchemy.engine.base.Engine SELECT cpu.id AS
id
FROM cpu
2014-02-12 11:23:57,199 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py,
line 69, in __getitem__
KeyError: sqlalchemy.sql.elements.Label object at 0x03C6FDA0
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py, line 39, in
module
read_from_DB()
File J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py, line 26, in
read_from_DB
print(str(session.query(CpuTable.id.label('id')).all()))
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py,
line 2264, in all
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 75, in instances
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 75, in listcomp
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 74, in listcomp
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py,
line 3440, in proc
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py,
line 71, in __getitem__
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py,
line 317, in _key_fallback
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for
column 'cpu.id'
when debugging this issue, i managed to find out that i do get the result
from the db, only if the quesry was select table.id it returns as
table_id. (i talked with some friends using this, and they confirmed it
happens on their hand as well, only their sqlalchemy wrapper knows how to
handle with this, while mine throws this error) . the commented out insert
lines are to check i had connection to the db, and also to check it inserts
the data, and it does, the query part is the problem. also while testing
this against the sqlite in memory db it does work.
hence my question is what can be the problem when working against mysql db
with pymysql driver?
tried searching the web for 1 day for similar problems couldnt find even 1.
my sqlalchemy version was 0.9.1 and i downgraded it to 0.9 and still the
problem persists.
thx
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Session remove/close MySQL
Thx all NullPool solve my problem create_engine(cnx_str, poolclass=NullPool) 2014-02-07 19:11 GMT+01:00 Claudio Freire klaussfre...@gmail.com: On Fri, Feb 7, 2014 at 2:35 PM, Michael Bayer mike...@zzzcomputing.com wrote: The connection pool, if in use, will then not actually close the connection if it is to remained pooled, it calls rollback() as part of the pool release mechanism. Recent versions of SQLAlchemy allow this to show up in the engine logs like any other rollback, so you probably wouldn't have noticed. And *this* is what was not happening. Somehow, transactions remained open on the database (I checked). that kind of thing generally happens to people when they aren't cleaning up their sessions, or are using awkward engine/connection patterns. the pool has had a lot of bugs fixed but I haven't seen a bug where the pool isn't emitting the rollback when the connection is marked closed. There was an awkward pattern involved: using the session's connection as returned by Session.connection() manually to issue some textual SQL. Other than that, normal thread-local session stuff. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] override relationship in subclass
On Feb 11, 2014, at 9:38 PM, Eric Atkin eat...@certusllc.us wrote:
Hi,
I want to override a relationship in a subclass to relate to a subclass of
the base attributes' related class. Perhaps an example of how I thought it
should work:
{{{
class Load(Base):
__tablename__ = 'load'
__mapper_args__ = {
'polymorphic_identity':'load',
'polymorphic_on':'polymorphic_type',
}
id = Column(Integer, primary_key=True)
polymorphic_type = Column(Text, nullable=False)
source_id = Column(Integer, ForeignKey('source.id'))
source = relationship('Source')
class Production_Load(Load):
__tablename__ = 'production_load'
__mapper_args__ = { 'polymorphic_identity':'production_load' }
id = Column(Integer, ForeignKey('load.id'), primary_key=True)
source_id = Column(Integer, ForeignKey('measured_source.id'))
source = relationship('Measured_Source')
class Source(Base):
__tablename__ = 'source'
__mapper_args__ = {
'polymorphic_identity':'source',
'polymorphic_on':'polymorphic_type',
}
id = Column(Integer, primary_key=True)
polymorphic_type = Column(Text, nullable=False)
class Measured_Source(Source):
__tablename__ = 'measured_source'
__mapper_args__ = { 'polymorphic_identity':'measured_source' }
id = Column(Integer, ForeignKey('source.id'), primary_key=True)
}}}
As you can see, we have Load.source - Source and I want
Production_Load.source - Measured_Source, but when I import the models, I
get the following warning:
{...}/env/lib/python2.7/site-packages/sqlalchemy/orm/properties.py:1028:
SAWarning: Warning: relationship 'source' on mapper
'Mapper|Production_Load|production_load' supersedes the same relationship on
inherited mapper 'Mapper|Load|load'; this can cause dependency issues during
flush
and when I try to use Production_Load.source (class level attr) in a query, I
get the following error:
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator'
object associated with Production_Load.source has an attribute 'conversion'
Is such a thing possible, even with a re-factor of the models?
“conversion” sounds like an attribute name on your end, but generally being
able to supersede a relationship like that when the inheritance is not
“concrete” is not supported. you’d need to name it to something else.
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Savepoints and expiry
On Feb 12, 2014, at 1:49 AM, Wolfgang Schnerring w...@gocept.com wrote: On 11 Feb 2014 06:54:22 Michael Bayer mike...@zzzcomputing.com wrote: On Feb 11, 2014, at 3:44 AM, Wolfgang Schnerring w...@gocept.com wrote: parent = session.query(Parent).first() self.assertEqual(1, len(parent.children)) session.begin_nested() session.delete(parent.children[0]) self.assertEqual(0, len(parent.children)) My point is, the last assertion fails, which I find both surprising and inconvenient. ;) I'd be grateful for any insights you have about this. I’m pretty sure I mentioned this was what it seemed like you were describing. this is the “delete() on an object doesn’t remove it from all collections in which it is contained”. it’s not related to savepoints and you can read about how to work with this behavior here: http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#session-deleting-from-collections I know that flush does not trigger expiry. ;) I was wondering whether savepoints qualified as being a stronger boundary than flush and thus might be worthy of triggering expiry. But I guess that answers my question then: the current behaviour *is* intentional, and if I want expire_all then I'll just have to call it myself (which is fine, I guess). well I’d look into using events if you’d like every begin_nested() to issue an expiry. The after_transaction_create should be a good event to use: http://docs.sqlalchemy.org/en/rel_0_9/orm/events.html#sqlalchemy.orm.events.SessionEvents.after_transaction_create - check if the given SessionTransaction is “nested” by seeing if it has a non-None ._parent attribute. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Could not locate column in row for column
On Feb 12, 2014, at 4:49 AM, Igal Kreimer igal.k...@gmail.com wrote:
Hello sqlalchemy team.
im a new user to the alchemy, and doing the tutorial on ur site.
ran ur tutorial on adding information to databases and querying against a
virtual db (sqlite in memory)
after i thought i got that, i tried testing against a real db - mysql db.
im using the driver of pymysql version 0.4 (i know its kinda old, but when
was using newer version i had other bugs, not related to this topic).
OK well definitely get on the latest pymysql, that driver is not super mature
so you want to be on the latest. I tried 0.6.1 here and can’t reproduce your
issue.
Traceback (most recent call last):
File J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py, line 39, in
module
read_from_DB()
File J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py, line 26, in
read_from_DB
print(str(session.query(CpuTable.id.label('id')).all()))
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py,
line 2264, in all
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 75, in instances
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 75, in listcomp
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 74, in listcomp
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py,
line 3440, in proc
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py,
line 71, in __getitem__
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py,
line 317, in _key_fallback
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column
'cpu.id’
yes that’s a totally strange issue that I cannot reproduce, running SQLA 0.9.0
/ py3.3 / pymysql.
one thing that *may* be going wrong is that I see you’re on windows, and there
might be case sensitivity issues happening. Though I’m not really sure how, I
can use any name for the label and of course it works.
here’s the test case I’m using, you get the same results with this?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
e = create_engine(mysql+pymysql://scott:tiger@localhost/test, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
sess.add_all([A(), A(), A()])
print(sess.query(A.id.label('id')).all())
print(sess.query(A.id.label('foobar')).all())
signature.asc
Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Could not locate column in row for column
yes it does. exactly the same problem, copy pasted ur code and received:
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py,
line 2264, in all
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 75, in instances
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 75, in listcomp
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 74, in listcomp
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py,
line 3440, in proc
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py,
line 71, in __getitem__
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py,
line 317, in _key_fallback
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for
column 'a.id'
2014-02-12 16:26 GMT+02:00 Michael Bayer mike...@zzzcomputing.com:
On Feb 12, 2014, at 4:49 AM, Igal Kreimer igal.k...@gmail.com wrote:
Hello sqlalchemy team.
im a new user to the alchemy, and doing the tutorial on ur site.
ran ur tutorial on adding information to databases and querying against
a virtual db (sqlite in memory)
after i thought i got that, i tried testing against a real db - mysql db.
im using the driver of pymysql version 0.4 (i know its kinda old, but
when was using newer version i had other bugs, not related to this topic).
OK well definitely get on the latest pymysql, that driver is not super
mature so you want to be on the latest. I tried 0.6.1 here and can't
reproduce your issue.
Traceback (most recent call last):
File J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py, line 39,
in module
read_from_DB()
File J:/working_dir/TLM/lib/DB_Wrapper/Lab_DB/DB_Reader.py, line 26,
in read_from_DB
print(str(session.query(CpuTable.id.label('id')).all()))
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py,
line 2264, in all
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 75, in instances
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 75, in listcomp
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py,
line 74, in listcomp
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\query.py,
line 3440, in proc
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py,
line 71, in __getitem__
File
C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\engine\result.py,
line 317, in _key_fallback
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for
column 'cpu.id'
yes that's a totally strange issue that I cannot reproduce, running SQLA
0.9.0 / py3.3 / pymysql.
one thing that *may* be going wrong is that I see you're on windows, and
there might be case sensitivity issues happening. Though I'm not really
sure how, I can use any name for the label and of course it works.
here's the test case I'm using, you get the same results with this?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
e = create_engine(mysql+pymysql://scott:tiger@localhost/test, echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
sess.add_all([A(), A(), A()])
print(sess.query(A.id.label('id')).all())
print(sess.query(A.id.label('foobar')).all())
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] override relationship in subclass
Couldn't it be handled with a mixin?
{{{
class _LoadCore(Base):
whatever you want for both classes here
pass
class Load(_LoadCore):
__tablename__ = 'load'
__mapper_args__ = {
'polymorphic_identity':'load',
'polymorphic_on':'polymorphic_type',
}
id = Column(Integer, primary_key=True)
polymorphic_type = Column(Text, nullable=False)
source_id = Column(Integer, ForeignKey('source.id'))
source = relationship('Source')
class Production_Load(_LoadCore):
__tablename__ = 'production_load'
__mapper_args__ = { 'polymorphic_identity':'production_load' }
id = Column(Integer, ForeignKey('load.id'), primary_key=True)
source_id = Column(Integer, ForeignKey('measured_source.id'))
source = relationship('Measured_Source')
}}
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] override relationship in subclass
Yeah sorry I missed that. conversion is an attribute on Measured_Source.
So the intent is that a Production_Load is a Load with its own additional
attributes over Load as well as a constraint that its source is a
Measured_Source which has its own attribute extensions over Source. One of
the goals here is to add that constraint enforcement. I was able to make it
work with the following hybrid_method and hybrid_method.expression, but the
isinstance(Production_Load.source, Measured_Source) enforcement is missing.
Eric
{{{
class Production_Load(Load):
__tablename__ = 'production_load'
__mapper_args__ = { 'polymorphic_identity':'production_load' }
id = Column(Integer, ForeignKey('load.id'), primary_key=True)
top = Column(Numeric, nullable=False)
bottom = Column(Numeric, nullable=False)
@hybrid_method
def delta(self):
return (self.top-self.bottom)*self.source.conversion if self.source
else None
@gdelta.expression
def delta(self):
# not sure about the performance here
return
(self.top-self.bottom)*select([Measured_Source.conversion]).where(Measured_Source.id==self.source_id).label('delta')
}}}
On Wednesday, February 12, 2014 7:17:08 AM UTC-7, Michael Bayer wrote:
On Feb 11, 2014, at 9:38 PM, Eric Atkin eat...@certusllc.us javascript:
wrote:
Hi,
I want to override a relationship in a subclass to relate to a subclass
of the base attributes' related class. Perhaps an example of how I thought
it should work:
{{{
class Load(Base):
__tablename__ = 'load'
__mapper_args__ = {
'polymorphic_identity':'load',
'polymorphic_on':'polymorphic_type',
}
id = Column(Integer, primary_key=True)
polymorphic_type = Column(Text, nullable=False)
source_id = Column(Integer, ForeignKey('source.id'))
source = relationship('Source')
class Production_Load(Load):
__tablename__ = 'production_load'
__mapper_args__ = { 'polymorphic_identity':'production_load' }
id = Column(Integer, ForeignKey('load.id'), primary_key=True)
source_id = Column(Integer, ForeignKey('measured_source.id'))
source = relationship('Measured_Source')
class Source(Base):
__tablename__ = 'source'
__mapper_args__ = {
'polymorphic_identity':'source',
'polymorphic_on':'polymorphic_type',
}
id = Column(Integer, primary_key=True)
polymorphic_type = Column(Text, nullable=False)
class Measured_Source(Source):
__tablename__ = 'measured_source'
__mapper_args__ = { 'polymorphic_identity':'measured_source' }
id = Column(Integer, ForeignKey('source.id'), primary_key=True)
}}}
As you can see, we have Load.source - Source and I want
Production_Load.source - Measured_Source, but when I import the models, I
get the following warning:
{...}/env/lib/python2.7/site-packages/sqlalchemy/orm/properties.py:1028:
SAWarning: Warning: relationship 'source' on mapper
'Mapper|Production_Load|production_load' supersedes the same relationship
on inherited mapper 'Mapper|Load|load'; this can cause dependency issues
during flush
and when I try to use Production_Load.source (class level attr) in a
query, I get the following error:
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator'
object associated with Production_Load.source has an attribute 'conversion'
Is such a thing possible, even with a re-factor of the models?
“conversion” sounds like an attribute name on your end, but generally
being able to supersede a relationship like that when the inheritance is
not “concrete” is not supported. you’d need to name it to something else.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] override relationship in subclass
@gdelta.expression is a typo. Should be @delta.expression.
On Wednesday, February 12, 2014 11:53:05 AM UTC-7, Eric Atkin wrote:
Yeah sorry I missed that. conversion is an attribute on Measured_Source.
So the intent is that a Production_Load is a Load with its own additional
attributes over Load as well as a constraint that its source is a
Measured_Source which has its own attribute extensions over Source. One of
the goals here is to add that constraint enforcement. I was able to make it
work with the following hybrid_method and hybrid_method.expression, but the
isinstance(Production_Load.source, Measured_Source) enforcement is missing.
Eric
{{{
class Production_Load(Load):
__tablename__ = 'production_load'
__mapper_args__ = { 'polymorphic_identity':'production_load' }
id = Column(Integer, ForeignKey('load.id'), primary_key=True)
top = Column(Numeric, nullable=False)
bottom = Column(Numeric, nullable=False)
@hybrid_method
def delta(self):
return (self.top-self.bottom)*self.source.conversion if
self.source else None
@gdelta.expression
def delta(self):
# not sure about the performance here
return
(self.top-self.bottom)*select([Measured_Source.conversion]).where(Measured_Source.id==self.source_id).label('delta')
}}}
On Wednesday, February 12, 2014 7:17:08 AM UTC-7, Michael Bayer wrote:
On Feb 11, 2014, at 9:38 PM, Eric Atkin eat...@certusllc.us wrote:
Hi,
I want to override a relationship in a subclass to relate to a subclass
of the base attributes' related class. Perhaps an example of how I thought
it should work:
{{{
class Load(Base):
__tablename__ = 'load'
__mapper_args__ = {
'polymorphic_identity':'load',
'polymorphic_on':'polymorphic_type',
}
id = Column(Integer, primary_key=True)
polymorphic_type = Column(Text, nullable=False)
source_id = Column(Integer, ForeignKey('source.id'))
source = relationship('Source')
class Production_Load(Load):
__tablename__ = 'production_load'
__mapper_args__ = { 'polymorphic_identity':'production_load' }
id = Column(Integer, ForeignKey('load.id'), primary_key=True)
source_id = Column(Integer, ForeignKey('measured_source.id'))
source = relationship('Measured_Source')
class Source(Base):
__tablename__ = 'source'
__mapper_args__ = {
'polymorphic_identity':'source',
'polymorphic_on':'polymorphic_type',
}
id = Column(Integer, primary_key=True)
polymorphic_type = Column(Text, nullable=False)
class Measured_Source(Source):
__tablename__ = 'measured_source'
__mapper_args__ = { 'polymorphic_identity':'measured_source' }
id = Column(Integer, ForeignKey('source.id'), primary_key=True)
}}}
As you can see, we have Load.source - Source and I want
Production_Load.source - Measured_Source, but when I import the models, I
get the following warning:
{...}/env/lib/python2.7/site-packages/sqlalchemy/orm/properties.py:1028:
SAWarning: Warning: relationship 'source' on mapper
'Mapper|Production_Load|production_load' supersedes the same relationship
on inherited mapper 'Mapper|Load|load'; this can cause dependency issues
during flush
and when I try to use Production_Load.source (class level attr) in a
query, I get the following error:
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator'
object associated with Production_Load.source has an attribute 'conversion'
Is such a thing possible, even with a re-factor of the models?
“conversion” sounds like an attribute name on your end, but generally
being able to supersede a relationship like that when the inheritance is
not “concrete” is not supported. you’d need to name it to something else.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Prepared Statements in Postgresql
Hi, just to confirm, the executemany() method in pg8000 does use prepared statements. Cheers, Tony. On Friday, 15 January 2010 17:16:09 UTC, Michael Bayer wrote: mozillalives wrote: Hello Everyone, I am new to both sqlalchemy and elixir, but I have been using them for the past couple of weeks and I really like them. But I have a question about prepared statements for Postgresql. For one specific application, I am doing a bunch of inserts (200,000+). From what I can tell, it looks like these are not prepared statements. I rewrote the code to issue prepared statements and this cuts the insertion time in half, but the code is crude. My question's are: how did you use prepared statements in Python if you don't know that psycoopg2 uses prepared statements ? was this in another language or did you implement a raw socket connection to your database ? Is there a way to tell sqlalchemy or the engine (which would be psycopg2, correct?) to use prepared statements? to efficiently execute the same statement many times, use the executemany style of execution - the tutorial describes this at http://www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multiple-statements . I don't think that psycopg2 actually uses prepared statements for this purpose but I am not sure. The DBAPI executemany() method is used. I've noticed some opinions online indicating that psycopg2 does not have prepared statement support (e.g. - http://www.cerias.purdue.edu/site/blog/post/beware_sql_injections_due_to_missing_prepared_statement_support/ ) the comment at the bottom of that post ultimately references a psycopg2 message from 2007 so you'd need to ask the psycopg2 folks for updated information. However psycopg2 can do an executemany with great efficiency as it is using methodologies for which you'd have to ask them, so if they don't use PG's actual prepared mechanism, its probably unnecessary. psycopg2 is an extremely mature and high performing product. - can I plug another engine into sqlalchemy that does? there's the pg8000 engine which may or may not do this. But its written in pure python, is not as fast as psycopg2, and is very new and not widely used since its author doesn't seem to promote it very much (but it is a very well written library). If I can't do any of the above and just need to prepare the statements manually, is there at least a method in sqlalchemy to properly quote my data before sending it to postgres? Despite some of the fud-like links mentioned on that blog, SQLAlchemy, as it says on the website since the day we launched 5 years ago, always uses bind parameters, in all cases, for all literal values, everywhere. We do not and have never quoted anything within SQLA as that is left up to the services provided by the DBAPI. DBAPI does not have prepared statement API. It has executemany(), for which the underlying implementation may or may not use prepared statements + server-level bind processing as an implementation detail. Psycopg2 handles the quoting in this case. cx_oracle, OTOH, uses Oracle's native data binding facilities provided by OCI. DBAPI abstracts this detail away. Thanks, Phil -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Prepared Statements in Postgresql
On Wed, Feb 12, 2014 at 5:03 PM, Tony Locke tlo...@tlocke.org.uk wrote: I've noticed some opinions online indicating that psycopg2 does not have prepared statement support (e.g. - http://www.cerias.purdue.edu/site/blog/post/beware_sql_injections_due_to_missing_prepared_statement_support/) the comment at the bottom of that post ultimately references a psycopg2 message from 2007 so you'd need to ask the psycopg2 folks for updated information. However psycopg2 can do an executemany with great efficiency as it is using methodologies for which you'd have to ask them, so if they don't use PG's actual prepared mechanism, its probably unnecessary. psycopg2 is an extremely mature and high performing product. What it doesn't support is libpq's wire protocol for prepared statements. But you can prepare and execute statements by issuing the corresponding SQL (that will use the wire protocol for SQL execution, which is a tad less efficient but still more efficient than separate queries). psycopg2's executemany isn't much more sophisticated than multiple separate queries since it internally does exactly that. It may be a tad faster since it's done in C, but I doubt the difference is significant. But, if you want an executemany that works in psycopg2 as it would in pg8000, you can PREPARE and then executemany the EXECUTE queries. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] override relationship in subclass
On Feb 12, 2014, at 1:53 PM, Eric Atkin eat...@certusllc.us wrote: Yeah sorry I missed that. conversion is an attribute on Measured_Source. So the intent is that a Production_Load is a Load with its own additional attributes over Load as well as a constraint that its source is a Measured_Source which has its own attribute extensions over Source. One of the goals here is to add that constraint enforcement. I was able to make it work with the following hybrid_method and hybrid_method.expression, but the isinstance(Production_Load.source, Measured_Source) enforcement is missing. well you’d make two relationships and your hybrid method would make the choice between which one is being accessed. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Prepared Statements in Postgresql
On Feb 12, 2014, at 4:07 PM, Claudio Freire klaussfre...@gmail.com wrote: But, if you want an executemany that works in psycopg2 as it would in pg8000, you can PREPARE and then executemany the EXECUTE queries. I’ve worked a lot with pg8000 including that I’ve given them very broad architectural changes towards the goal of greater performance, but still as a pure Python driver unless you’re using pypy, it still has dramatically more overhead than psycopg2 on the Python side. So it’s kind of a tossup if “prepared + pure Python” vs. “non-prepared but very optimized C” is better in individual cases. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] array types using OracleDialect
Regarding the following:
if the type of the LiteralBindParameter implements “literal_processor()”,
that controls how the literal value is rendered into the statement.
How does one implement the literal_processor() for a new type? Is
literal_processor() method applicable for UserDefinedTypes?
- Original Message -
...
Is there a way to render a udt like class into literal part of query rather
than binding it as a parameter?
there is, but it would kind of disqualify this as something we can add to
SQLAlchemy directly b.c. it’s basically a security hazard (as unlikely as it is
in this case).
essentially if the “literal_binds” flag is passed through to the compiler it
will render bound parameters inline. This flag can be set on a per-element
basis using the recipe below:
from sqlalchemy.sql.expression import BindParameter
from sqlalchemy.ext.compiler import compiles
class LiteralBindParam(BindParameter):
pass
@compiles(LiteralBindParam)
def literal_bind(element, compiler, **kw):
kw['literal_binds'] = True
return compiler.visit_bindparam(element, **kw)
from sqlalchemy.sql import select, column
stmt = select([column('x'), column('y')]).\
where(column('x') == 5).\
where(column('y') == LiteralBindParam(None, 7))
print stmt
will print:
SELECT x, y
WHERE x = :x_1 AND y = 7
from there, if the type of the LiteralBindParameter implements
“literal_processor()”, that controls how the literal value is rendered into the
statement.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] array types using OracleDialect
On Feb 12, 2014, at 6:11 PM, Amos Smith asm...@aseg.com wrote: Regarding the following: if the type of the LiteralBindParameter implements “literal_processor()”, that controls how the literal value is rendered into the statement. How does one implement the literal_processor() for a new type? Is literal_processor() method applicable for UserDefinedTypes? the method is literal_processor: http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#sqlalchemy.types.TypeEngine.literal_processor its new as of 0.9 and applies to any type, including UserDefinedType. If you don’t see it taking effect, that’s a bug. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] array types using OracleDialect
I migrated to 0.9.2 as I understood the literal_processor was new feature.
I always get this error from listing shown below:
def literal_processor(self, dialect):
^
IndentationError: unindent does not match any outer indentation level
ArrayType is obviously just a stub in this listing.
Apologies in advance if this is some obvious Python formatting issue of mine,
I'm still learning this language.
#= Begin Listing =
import os
import sqlalchemy
from sqlalchemy.orm import sessionmaker
from sqlalchemy import MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import func
from sqlalchemy.types import UserDefinedType
from sqlalchemy.sql.expression import BindParameter
from sqlalchemy.ext.compiler import compiles
#
class LiteralBindParam(BindParameter):
pass
@compiles(LiteralBindParam)
def literal_bind(element, compiler, **kw):
kw['literal_binds'] = True
return compiler.visit_bindparam(element, **kw)
#
class ArrayType(UserDefinedType):
def get_col_spec(self):
return ARRAY
def column_expression(self, col):
return None
def literal_processor(self, dialect):
def process(value):
return int_array(1, 2, 3, 4, 5)
return process
#
dbUser = os.environ.get('uid')
dbPwd = os.environ.get('pwd')
oraSID = os.environ.get('sid')
connstr = 'oracle://%s:%s@%s' % (dbUser, dbPwd, oraSID)
#
engine = sqlalchemy.create_engine(connstr)
metadata = MetaData(engine)
Base = declarative_base(metadata=metadata)
dialectMgr = DialectManager()
sessionMaker = sessionmaker(bind=engine)
session = sessionMaker()
a = LiteralBindParam(None, ArrayType())
session.execute(func.some_db_func(a)).scalar()
#= End Listing ===
- Original Message -
Regarding the following:
if the type of the LiteralBindParameter implements “literal_processor()”,
that controls how the literal value is rendered into the statement.
How does one implement the literal_processor() for a new type? Is
literal_processor() method applicable for UserDefinedTypes?
the method is literal_processor:
http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#sqlalchemy.types.TypeEngine.literal_processor
its new as of 0.9 and applies to any type, including UserDefinedType. If you
don’t see it taking effect, that’s a bug.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Twisted + SQLAlchemy
Hello! I'm working with SA from Twisted, and it's not so simple as I though. I red some old topic in this group and didn't find any good solution. I just want to know if there are any approach at present? -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Savepoints and expiry
* Michael Bayer mike...@zzzcomputing.com [2014-02-12 09:19]: On Feb 12, 2014, at 1:49 AM, Wolfgang Schnerring w...@gocept.com wrote: I know that flush does not trigger expiry. ;) I was wondering whether savepoints qualified as being a stronger boundary than flush and thus might be worthy of triggering expiry. But I guess that answers my question then: the current behaviour *is* intentional, and if I want expire_all then I'll just have to call it myself (which is fine, I guess). well I’d look into using events if you’d like every begin_nested() to issue an expiry. The after_transaction_create should be a good event to use: http://docs.sqlalchemy.org/en/rel_0_9/orm/events.html#sqlalchemy.orm.events.SessionEvents.after_transaction_create - check if the given SessionTransaction is “nested” by seeing if it has a non-None ._parent attribute. Excellent! Thank you for the pointer! I must confess I'm not very familiar with the event system (yet), I somehow got stuck about 0.6ish where events were not as nicely accessible as they are now -- so I'm actually looking forward to using those. :) Wolfgang -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
