[sqlalchemy] Re: Something wrong with pool. Got Connection is already closed None None with copy_from and big file.
On Nov 4, 7:13 pm, Michael Bayer mike...@zzzcomputing.com wrote:
copy_from() probably creates some state that is not compatible with the
connection being used afterwards for subsequent operations, or
alternatively copy_from() is not compatible with some previous state.
The pool does nothing special to the connections which it stores except
calling rollback() when they are returned.
If you can try to isolate the issue to an exact sequence of events (i.e.,
don't use a Session or ORM - just use an engine and connect()) that would
reveal more about what's going on.
Now I try copy_from without Session or ORM, use engine only and
everything is ok :) What does it mean? :)
engine = create_engine(conf['sqlalchemy.url'])
connection = engine.raw_connection()
connection.cursor().cursor.copy_from(f, i.table, sep=chr(int(conf
['import.separator'])), columns=map(str, i.fields.split(',')))
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=.
Re: [sqlalchemy] Re: Something wrong with pool. Got Connection is already closed None None with copy_from and big file.
On Nov 21, 2009, at 8:51 AM, sector119 wrote:
On Nov 4, 7:13 pm, Michael Bayer mike...@zzzcomputing.com wrote:
copy_from() probably creates some state that is not compatible with the
connection being used afterwards for subsequent operations, or
alternatively copy_from() is not compatible with some previous state.
The pool does nothing special to the connections which it stores except
calling rollback() when they are returned.
If you can try to isolate the issue to an exact sequence of events (i.e.,
don't use a Session or ORM - just use an engine and connect()) that would
reveal more about what's going on.
Now I try copy_from without Session or ORM, use engine only and
everything is ok :) What does it mean? :)
engine = create_engine(conf['sqlalchemy.url'])
connection = engine.raw_connection()
connection.cursor().cursor.copy_from(f, i.table, sep=chr(int(conf
['import.separator'])), columns=map(str, i.fields.split(',')))
do some addtional things on the connection ? commit/rollback etc. ? bind it
to a Session and do some stuff ? not sure.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=.
Re: [sqlalchemy] Re: Something wrong with pool. Got Connection is already closed None None with copy_from and big file.
On Nov 21, 2009, at 8:51 AM, sector119 wrote:
On Nov 4, 7:13 pm, Michael Bayer mike...@zzzcomputing.com wrote:
copy_from() probably creates some state that is not compatible with the
connection being used afterwards for subsequent operations, or
alternatively copy_from() is not compatible with some previous state.
The pool does nothing special to the connections which it stores except
calling rollback() when they are returned.
If you can try to isolate the issue to an exact sequence of events (i.e.,
don't use a Session or ORM - just use an engine and connect()) that would
reveal more about what's going on.
Now I try copy_from without Session or ORM, use engine only and
everything is ok :) What does it mean? :)
engine = create_engine(conf['sqlalchemy.url'])
connection = engine.raw_connection()
connection.cursor().cursor.copy_from(f, i.table, sep=chr(int(conf
['import.separator'])), columns=map(str, i.fields.split(',')))
if you want a connection from the pool, and then just throw it away afterwards,
call detach() on it. then you can do whatever weird things with it and it will
be thrown away when you close() it (i.e. not returned to the pool or anything).
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=.
Re: [sqlalchemy] Weird error with update
Excerpts from Mike Conley's message of Sat Nov 21 03:36:07 -0300 2009:
On Fri, Nov 20, 2009 at 5:14 PM, Mariano Mara mariano.m...@gmail.comwrote:
... or, at least, is weird for me :)
Hi everyone. I'm running a pylons controller
with the following instruction:
meta.Session.query(ESMagicNumber).filter(
ESMagicNumber.uuid==request.params['uuid_']).\
update({'last_access':datetime.datetime.now()})
but I'm getting the following error:
(ProgrammingError) syntax error at or near WHERE
LINE 1: UPDATE es_magicnumber SET WHERE es_magicnumber.uuid = E'705...
^ 'UPDATE es_magicnumber SET WHERE es_magicnumber.uuid = %(uuid_1)s'
{'uuid_1': u'705fa1e6-977f-416a-9847-fe8715cc920a'}
I am able to create the same bad SQL, but only if the key in the update
dictionary is not a column in the table being updated. Are you sure
'last_access' is a valid column in ESMagicNumber?
This is because the SET clause is generated from the update dictionary, and
if there are no valid columns, there is nothing to generate and the SET
clause becomes nothing.
I checked that first but turns out I was checking the wrong file :(
Somebody should not work past midnight.
Thank you very much for your answer and sorry for the waste of time.
Mariano
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=.
[sqlalchemy] Re: Subquery questions
On Nov 11, 10:22 am, Michael Bayer mike...@zzzcomputing.com wrote:
On Tue, Nov 10, 2009 at 9:55 PM, jcm jonmast...@gmail.com wrote:
Folks,
I could do with some decent docs on subqueries. I've tried to play
since you got some help here, I'll also note that I was surprised I didn't
have NOT IN in the docs so I added that to the ORM tutorial.
Thanks. But there's still a problem preventing me from actually using
this. And it's the inability for sqlalchemy to insert correct
parentheses in the subquery it generates (which has come up here
before). I've wasted all night coming to the realization that the
problem is SQLA and not my code - it should add '(' ')' around the
select in the subquery. I tried having it return a statement and then
calling self_group, and recreating a new query based on that. But
there's just no getting around the fact that it won't generate the SQL
I want it to. Hopefully, there's some hacky way that we can get it to
DTRT at least for the moment.
For context, what I'm doing is writing a Linux kernel symbol tracking
database for a commercial Linux kernel. The Linux kernel contains
approximately 10,000 exported symbols (think software functions) that
third parties can consume. But not all of these symbols should be used
by third parties, and so it is necessary to limit the symbols to a
set. That set is determined through a collaborative voting process in
which people can vote for given proposed symbol entries in the
database. So we have Symbol (the symbol itself), SymbolList (a list
containing this symbol), SymbolListEntry (an entry on that list), and
other types (there are 54 in total). A Vote is requested from the user
against each SymbolListEntry that they have not voted on. In order to
only present entries that haven't been voted on before, I do things
like (simplified):
unvoted_proposed_add_symbollistentries = DBSession.query
(SymbolListEntry).from_statement(SELECT symbollistentries.* FROM
symbollistentries JOIN states ON
(states.state_id=symbollistentries.state_id) WHERE
symbollistentries.proposed_add=1 AND states.deleted=FALSE and
states.active=FALSE AND symbollistentries.symbollistentry_id NOT IN
(SELECT symbollistentries.symbollistentry_id FROM symbollistentries
JOIN votes ON
(votes.symbollistentry_id=symbollistentries.symbollistentry_id) JOIN
states ON (states.state_id=votes.state_id) JOIN users ON
(users.user_id=states.owner_id) WHERE users.user_id=:user_id AND
votes.revision=symbollistentries.revision)).params
(user_id=user.user_id).all()
Which is ugly. I've managed to kill the from_statements everywhere
else in the app now, but not here. I've since added a few more checks
of state in the above query (nothing is ever deleted, only recorded as
such in 'states', hence the weird stuff). Anyway, here's some example
code close to what I want to do, but MySQL balks on the code that SQLA
emits from this:
symbolListEntryState = aliased(State)
voteState = aliased(State)
previous_votes = DBSession.query
(SymbolListEntry.symbollistentry_id).join
((symbolListEntryState,SymbolListEntry.state)).filter(and_
(symbolListEntryState.deleted==False,symbolListEntryState.active==False)).join
((Vote,SymbolListEntry.votes)).filter
(Vote.revision==SymbolListEntry.revision).join
((voteState,Vote.state)).filter(and_
(voteState.deleted==False,voteState.active==True)).join
((User,voteState.owner)).filter(User.user_id==user.user_id).subquery()
symbollistentries = DBSession.query(SymbolListEntry).join
((symbolListEntryState,SymbolListEntry.state)).filter(and_
(symbolListEntryState.deleted==False,symbolListEntryState.active==False)).filter
(SymbolListEntry.proposed_add==True).filter
(~SymbolListEntry.symbollistentry_id.in_(previous_votes)).all()
return symbollistentries
It generates this:
ProgrammingError: (ProgrammingError) (1064, You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near 'SELECT
symbollistentries.symbollistentry_id \nFROM symbollistentries INNER
JOIN s' at line 5) u'SELECT symbollistentries.symbollistentry_id AS
symbollistentries_symbollistentry_id, symbollistentries.symbol_id AS
symbollistentries_symbol_id, symbollistentries.symbollist_id AS
symbollistentries_symbollist_id, symbollistentries.state_id AS
symbollistentries_state_id, symbollistentries.proposed_add AS
symbollistentries_proposed_add, symbollistentries.proposed_remove AS
symbollistentries_proposed_remove, symbollistentries.revision AS
symbollistentries_revision, symbollistentries.went_live AS
symbollistentries_went_live \nFROM (SELECT
symbollistentries.symbollistentry_id AS symbollistentry_id \nFROM
symbollistentries INNER JOIN states AS states_1 ON
symbollistentries.state_id = states_1.state_id INNER JOIN votes ON
votes.symbollistentry_id = symbollistentries.symbollistentry_id INNER
JOIN states AS states_2 ON votes.state_id = states_2.state_id INNER
JOIN users ON states_2.owner_id =
Re: [sqlalchemy] Re: Subquery questions
On Nov 21, 2009, at 12:30 PM, jcm wrote:
Thanks. But there's still a problem preventing me from actually using
this. And it's the inability for sqlalchemy to insert correct
parentheses in the subquery it generates (which has come up here
before).
I'm not aware of any mailing list message or currently open ticket regarding
any problems at all with parenthesization in current versions of SQLAlchemy.
I've wasted all night coming to the realization that the
problem is SQLA and not my code - it should add '(' ')' around the
select in the subquery. I tried having it return a statement and then
calling self_group, and recreating a new query based on that. But
there's just no getting around the fact that it won't generate the SQL
I want it to. Hopefully, there's some hacky way that we can get it to
DTRT at least for the moment.
Here is entirely idiomatic SQLAlchemy code which approximates the types of
constructs your code snippets below are using - it places both the Query object
into a NOT IN, as well as the select() construct generated by Query.subquery()
within NOT IN, and generates valid SQL, including against the MySQL dialect.
So I cannot reproduce your issue, going back to version 0.5.3. I would
recommend that after confirming the issue with the latest 0.5 version of
SQLAlchemy, provide a full suite of all involved tables and mappers (but no
more), and the exact steps to reproduce (no reflected tables please),
preferably as a single Python script that runs with no dependencies. If
you've been observing the mailing list closely you'd know that genuine bugs of
significant severity are fixed within hours, so this is the best way for your
problem to be solved.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('mysql://scott:ti...@localhost/test', echo=True)
Base = declarative_base(engine)
class Foo(Base):
__tablename__ ='foo'
id = Column('id', Integer, primary_key=True)
data = Column('data', Integer)
bars = relation(Bar)
class Bar(Base):
__tablename__ ='bar'
id = Column('id', Integer, primary_key=True)
foo_id = Column('foo_id', Integer, ForeignKey('foo.id'))
data = Column('data', Integer)
Base.metadata.create_all()
sess = create_session()
query1 = sess.query(Foo.id).join(Foo.bars).filter(Bar.data==5)
query2 = query1.subquery()
# use NOT IN (query)
query3
=sess.query(Foo).join(Foo.bars).filter(Bar.data==6).filter(~Foo.id.in_(query1))
# use NOT IN (query.subquery())
query4
=sess.query(Foo).join(Foo.bars).filter(Bar.data==6).filter(~Foo.id.in_(query2))
print query3
print list(query3)
print query4
print list(query4)
For context, what I'm doing is writing a Linux kernel symbol tracking
database for a commercial Linux kernel. The Linux kernel contains
approximately 10,000 exported symbols (think software functions) that
third parties can consume. But not all of these symbols should be used
by third parties, and so it is necessary to limit the symbols to a
set. That set is determined through a collaborative voting process in
which people can vote for given proposed symbol entries in the
database. So we have Symbol (the symbol itself), SymbolList (a list
containing this symbol), SymbolListEntry (an entry on that list), and
other types (there are 54 in total). A Vote is requested from the user
against each SymbolListEntry that they have not voted on. In order to
only present entries that haven't been voted on before, I do things
like (simplified):
unvoted_proposed_add_symbollistentries = DBSession.query
(SymbolListEntry).from_statement(SELECT symbollistentries.* FROM
symbollistentries JOIN states ON
(states.state_id=symbollistentries.state_id) WHERE
symbollistentries.proposed_add=1 AND states.deleted=FALSE and
states.active=FALSE AND symbollistentries.symbollistentry_id NOT IN
(SELECT symbollistentries.symbollistentry_id FROM symbollistentries
JOIN votes ON
(votes.symbollistentry_id=symbollistentries.symbollistentry_id) JOIN
states ON (states.state_id=votes.state_id) JOIN users ON
(users.user_id=states.owner_id) WHERE users.user_id=:user_id AND
votes.revision=symbollistentries.revision)).params
(user_id=user.user_id).all()
Which is ugly. I've managed to kill the from_statements everywhere
else in the app now, but not here. I've since added a few more checks
of state in the above query (nothing is ever deleted, only recorded as
such in 'states', hence the weird stuff). Anyway, here's some example
code close to what I want to do, but MySQL balks on the code that SQLA
emits from this:
symbolListEntryState = aliased(State)
voteState = aliased(State)
previous_votes = DBSession.query
(SymbolListEntry.symbollistentry_id).join
((symbolListEntryState,SymbolListEntry.state)).filter(and_
(symbolListEntryState.deleted==False,symbolListEntryState.active==False)).join
Re: [sqlalchemy] Re: Subquery questions
On Nov 21, 2009, at 1:19 PM, Michael Bayer wrote: Here is entirely idiomatic SQLAlchemy code which approximates the types of constructs your code snippets below are using - it places both the Query object into a NOT IN, as well as the select() construct generated by Query.subquery() within NOT IN, and generates valid SQL, including against the MySQL dialect. So I cannot reproduce your issue, going back to version 0.5.3. Reproduced with 0.5.2. Here is the CHANGES note in 0.5.3, where the feature was first added: - Query now implements __clause_element__() which produces its selectable, which means a Query instance can be accepted in many SQL expressions, including col.in_(query), union(query1, query2), select([foo]).select_from(query), etc. Here is another feature added in 0.5.3 which allows subquery() to be used in a scalar context without the need to call as_scalar() - An alias() of a select() will convert to a scalar subquery when used in an unambiguously scalar context, i.e. it's used in a comparison operation. This applies to the ORM when using query.subquery() as well. This would strongly suggest your issues are specific to the usage of features which were not yet introduced in the version of SQLAlchemy you're using. Current version of SQLA is now 0.5.6 - 0.5.2 is 10 months out of date. Please confirm your issue is resolved via upgrade. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=.
[sqlalchemy] more than one one to many relation
Hello,
What is the pattern for more than one relation one to many.
I have on parent with 2 childs.
parent_table = Table('parent', metadata,
Column('parent_id', Integer, primary_key=True)
)
child1_table = Table('child1', metadata,
Column('child1_id', Integer, primary_key=True),
Column('parent_id', Integer,ForeignKey(parent_id))
child2_table = Table('child2', metadata,
Column('child1_id', Integer, primary_key=True),
Column('parent_id', Integer,ForeignKey(parent_id))
class Parent(object): pass
class Child1(object): pass
class Child2(object): pass
parent_map = mapper(Parent, parent_table,
properties={'child1':relation(Child1), 'child2':relation(Child2)})
child1_map = mapper(Child1, child1_table)
child2_map = mapper(Child2, child1_table)
This mapper coding doesn't work?
What is the right way to do that?
Thanks
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=.
[sqlalchemy] Getting a join in one object
Hi all. I want to get a join in one object. I have ret = db.query(a, b).select_from(orm.join(a, b, a.a == b.a)).all() and that returns a tuple with an a object in [0] and a b object in [1]. I want to have SA return the object directly, and I want that object to include the joined fields, so that I can access everything with just ret.anything_i_want. Thanks in advance for the help. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=.
