[sqlalchemy] Re: Savepoints and expiry
* Michael Bayer [2014-02-12 09:19]: > On Feb 12, 2014, at 1:49 AM, Wolfgang Schnerring 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.
[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.
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.
Re: [sqlalchemy] array types using OracleDialect
On Feb 12, 2014, at 6:11 PM, Amos Smith 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
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] Prepared Statements in Postgresql
On Feb 12, 2014, at 4:07 PM, Claudio Freire 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] override relationship in subclass
On Feb 12, 2014, at 1:53 PM, 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. 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 Wed, Feb 12, 2014 at 5:03 PM, Tony Locke 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] 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.com > . > > To unsubscribe from this group, send email to > > sqlalchemy+...@googlegroups.com . > > 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] 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 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
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 > > 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
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] Could not locate column in row for column
And you updated pymysql? Or no? Sent from my iPhone > On Feb 12, 2014, at 10:56 AM, Igal Kreimer wrote: > > 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 > File > "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", > line 74, in > 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 : >> >> On Feb 12, 2014, at 4:49 AM, Igal Kreimer 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 >> > 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 >> > File >> > "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", >> > line 74, in >> > 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. -- 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://grou
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 File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 74, in 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 : > > On Feb 12, 2014, at 4:49 AM, Igal Kreimer 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 > > 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 > > File > "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", > line 74, in > > 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] Could not locate column in row for column
On Feb 12, 2014, at 4:49 AM, Igal Kreimer 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 > > 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 > File > "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", > line 74, in > 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] Savepoints and expiry
On Feb 12, 2014, at 1:49 AM, Wolfgang Schnerring wrote: > On 11 Feb 2014 06:54:22 Michael Bayer wrote: >> On Feb 11, 2014, at 3:44 AM, Wolfgang Schnerring 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] override relationship in subclass
On Feb 11, 2014, at 9:38 PM, Eric Atkin 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] 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 : > On Fri, Feb 7, 2014 at 2:35 PM, Michael Bayer > 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.
[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: 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 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 File "C:\Python33\lib\site-packages\sqlalchemy-0.9.0-py3.3.egg\sqlalchemy\orm\loading.py", line 74, in 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.