[sqlalchemy] Re: URGENT: 2014, Commands out of sync; you can't run this command now
On Jul 13, 2007, at 1:47 AM, Arun Kumar PG wrote: Hi Guys, I am getting this error when multiple requests are coming to the application and following this the my server crashes! FYI: I am using MySQLDb library and connextions are managed by sqlalchemy.pool ProgrammingError: (2014, Commands out of sync; you can't run this command now) I know it's a MySQL error but has anyone encountered this while using SQL Alchemy ? Quick help will be appreciated. dont share connections or sessions between threads. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Multi-table inheritance and conflicting id columns
Maybe actually asking some questions would have helped getting
replies :-)
Is this not a bug? I may be wrong, but I would consider this a pretty
serious bug, as it does not raise an error, but silently returns the
wrong object, which could lead to serious data corruption...
If it is, should I just go ahead and open an entry in the tracker?
Thanks,
On Jul 2, 1:34 pm, Yves-Eric [EMAIL PROTECTED] wrote:
Hi all,
I have just spent most of a day tracking down a weird bug in an
application I built on top of a legacy database, in which I use simple
(non-polymorphic) multi-table inheritance. The problem arises when the
parent and child tables share the same name for their primary key
(id in my case), but with different values (each table has its own
sequence).
I illustrate the problem in the runnable script attached below, using
a Person / Employee hierarchy. Most of the time, everything works fine
(Employee alice1 has the right alice Person object). But when an
Employee object is loaded (alice2), for which a Person object with the
same id already exists in the session (bob was loaded, and bob's
person.id happens to be the same as alice's employee.id), then the
wrong parent object is loaded (Employee alice2 has the wrong Person
object bob).
Here is a runnable script that reproduces this bug:
### BEGIN: multitable_inheritance_test.py ###
from sqlalchemy import *
db = create_engine('sqlite://', echo=False)
__meta__ = BoundMetaData(db)
person_table = Table(persons, __meta__,
Column(id, Integer, primary_key=True),
Column(name, String(80)),
)
employee_table = Table(employees, __meta__,
Column(id, Integer, primary_key=True),
Column(salary, Integer),
Column(person_id, Integer, ForeignKey(persons.id)),
)
class Person(object):
def __init__(self, name):
self.name = name
class Employee(Person): pass
__meta__.create_all()
person_mapper = mapper(Person, person_table)
mapper(Employee, employee_table, inherits=person_mapper)
person_insert = person_table.insert()
person_insert.execute(id=1, name='alice')
person_insert.execute(id=2, name='bob')
employee_insert = employee_table.insert()
employee_insert.execute(id=2, salary=250, person_id=1) # alice
employee_insert.execute(id=3, salary=200, person_id=2) # bob
session = create_session()
query = session.query(Employee)
alice1 = query.get(2)
bob = query.get(3)
alice2 = query.get(2)
print 'alice1.name: %s' % alice1.name
print 'alice2.name: %s' % alice2.name
### END ###
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multi-table inheritance and conflicting id columns
On Jul 13, 2007, at 3:50 AM, [EMAIL PROTECTED] wrote:
i played a bit with your thing...
how do u expect the id column to appear in the Employee actualy?
do u expect to get the base person' one, or the child one? or both?
looking from the child, yes, the child one will obscure the parent
one, but from the parent.. u'll never get the child one.
yeah actually, playing with this some more, hes right. even with
the fix i put in, your mapper still is munging the two distinct
id values into one column attribute, id, which still creates
problems. you still have to:
a. name the columns differently
b. join them together on a foreign key
c. name them distinctly on your mapper:
person_mapper = mapper(Person, person_table)
mapper(Employee, employee_table, inherits=person_mapper, properties=
{'pid':person_table.c.id, 'eid':employee_table.c.id})
since the logical mapping of the object is a join between the two
tables, those two columns conflict in the columns clause of such a join.
this is a thorny issue which probably deserves a little bit of
documentation.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: URGENT: 2014, Commands out of sync; you can't run this command now
On Jul 13, 2007, at 2:27 AM, Arun Kumar PG wrote: I create an orm session as soon as the request comes in and store the reference to the same in the curent thread i.e. threading.currentThread().session = new_session. This session is stored in the current thread so that I can get the same session across all DAO objects. so basically ever DAO in the request chain can simply get the session by saying threading.currenrThread.session and use it. Finally, once the request is over this session object is removed from the current thread i.e. del session. I can see that during multiple request the thread ids are different so I believe that all of them are having their own copy of session. Further, I am using pool module of sqlalchemy from where a connection is returned to orm session. My guess is that somewhere in that connection management things are getting schewed up - there was a bug like this at one time in the pool, but it was fixed probably a year ago, and nobody has reported this issue since. are the objects which you load from the session being shared between threads ? i.e. a second thread issues a lazy-load operation on an object's attribute ? that counts as multi-threaded session access. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pagination problems using SQLAlchemy MSSQL in Pylons - no OFFSET
Patch is attached again
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
792c792,793
---
self._select_state = {}
796,799c797,800
if select.limit:
s += TOP %s % (select.limit,)
if select.offset:
raise exceptions.InvalidRequestError('MSSQL does not support
LIMIT with an offset')
---
#if select.limit:
#s += TOP %s % (select.limit,)
#if select.offset:
#raise exceptions.InvalidRequestError('MSSQL does not support
LIMIT with an offset')
846a848,883
# TODO: put a real copy-container on Select and copy, or somehow make
this
# not modify the Select statement
if self._select_state.get((select, 'visit'), False):
# cancel out the compiled order_by on the select
if hasattr(select, order_by_clause):
self.strings[select.order_by_clause] =
ansisql.ANSICompiler.visit_select(self, select)
return
if select.limit is not None or select.offset is not None:
self._select_state[(select, 'visit')] = True
# to use ROW_NUMBER(), an ORDER BY is required.
orderby = self.strings[select.order_by_clause]
if not orderby:
orderby = select.oid_column
self.traverse(orderby)
orderby = self.strings[orderby]
if not hasattr(select, '_mssql_visit'):
select.append_column(sql.literal_column(ROW_NUMBER() OVER
(ORDER BY %s) % orderby).label(ms_rn))
select._mssql_visit = True
limitselect = sql.select([c for c in select.alias('_mssql').c if
c.key!='ms_rn'])
if select.offset is not None:
limitselect.append_whereclause(ms_rn %d % select.offset)
if select.limit is not None:
limitselect.append_whereclause(ms_rn=%d %
(select.limit + select.offset))
else:
limitselect.append_whereclause(ms_rn=%d % select.limit)
self.traverse(limitselect)
self.strings[select] = self.strings[limitselect]
#print self.strings[select], select.offset, select.limit
self.froms[select] = self.froms[limitselect]
else:
ansisql.ANSICompiler.visit_select(self, select)
848c885
super(MSSQLCompiler, self).visit_select(select)
---
#super(MSSQLCompiler, self).visit_select(select)
[sqlalchemy] Re: Pagination problems using SQLAlchemy MSSQL in Pylons - no OFFSET
On Jul 13, 2007, at 4:07 AM, Esceo wrote: Hi all, attached is an experimental patch for mssql.py merged with r2891: this is a direct steal from the oracle implementation of limit + offset using row_number() what I would like to find out are the following: the oracle implementation has visit_compound_select marked as todo, what is the implication of that? (my guess is that limit+offset won't work for join models?) visit_compound_select is for unions. most times when we use unions with the ORM, an enclosing select is issued around the UNIONs so it doesnt matter. joins are OK. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pagination problems using SQLAlchemy MSSQL in Pylons - no OFFSET
Thank you for the extremely speedy response :) Are we aware of any other impacts of oracle's offset+limit implication? On 13/07/07, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 13, 2007, at 4:07 AM, Esceo wrote: Hi all, attached is an experimental patch for mssql.py merged with r2891: this is a direct steal from the oracle implementation of limit + offset using row_number() what I would like to find out are the following: the oracle implementation has visit_compound_select marked as todo, what is the implication of that? (my guess is that limit+offset won't work for join models?) visit_compound_select is for unions. most times when we use unions with the ORM, an enclosing select is issued around the UNIONs so it doesnt matter. joins are OK. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Multi-table inheritance and conflicting id columns
i played a bit with your thing... how do u expect the id column to appear in the Employee actualy? do u expect to get the base person' one, or the child one? or both? looking from the child, yes, the child one will obscure the parent one, but from the parent.. u'll never get the child one. i added this after query=...: $ for r in query.select(): print r.id, r.salary, r.name, r.person_id 1 250 alice 1 2 200 bob 2 do u see that .id and person_id become same? some more: $ for c in empl_mapper.props['id'].columns: $ print c persons.id employees.id so the 'id' column maps to 2 columns, and the one that the mapper is using (i guess the first) is the person.id. u have to do it in some another way, try renaming one of the columns to something else. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Multi-table inheritance and conflicting id columns
On Jul 13, 2007, at 2:26 AM, Yves-Eric wrote: Maybe actually asking some questions would have helped getting replies :-) sorry, i totally did not see this message on july 2 ? Is this not a bug? I may be wrong, but I would consider this a pretty serious bug, as it does not raise an error, but silently returns the wrong object, which could lead to serious data corruption... there is a bug here. theres a commit in r2891 which fixes it, at least for your case. a further fix will need to be applied pending ticket #185 for deeper inheritance situations. there are three ways to work around the bug right now. 1. the normal way, which is why most people dont have this problem. create the schema with a shared primary key. this is how table inheritance is usually done. person_table = Table(persons, __meta__, Column(id, Integer, primary_key=True), Column(name, String(80)), ) employee_table = Table(employees, __meta__, Column(id, Integer, ForeignKey(persons.id), primary_key=True), Column(salary, Integer), ) 2. otherwise, you have two primary key columns here: persons.id and employees.id. they are not synonymous with each other; therefore your PK is composite of both of those, for the Employee class. so the two ways to work around the bug are: a. specify the primary key to the mapper explicitly: person_mapper = mapper(Person, person_table) mapper(Employee, employee_table, inherits=person_mapper, primary_key= [person_table.c.id, employee_table.c.id]) When doing this, you have a composite PK on employee. test code looks like: alice1 = query.get([1,2]) bob = query.get([2,3]) alice2 = query.get([1,2]) b. name the columns differently, since the bug is a name collision thats occuring (rev 2891 removes this name collision by representing a composite PK as a non-keyed set) person_table = Table(persons, __meta__, Column(id, Integer, primary_key=True), Column(name, String(80)), ) employee_table = Table(employees, __meta__, Column(employee_id, Integer, primary_key=True), Column(salary, Integer), Column(person_id, Integer, ForeignKey(persons.id)), ) again, you need to use composite PKs for access: alice1 = query.get([1,2]) bob = query.get([2,3]) alice2 = query.get([1,2]) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Pagination problems using SQLAlchemy MSSQL in Pylons - no OFFSET
Hi all,
attached is an experimental patch for mssql.py merged with r2891:
this is a direct steal from the oracle implementation of limit +
offset using row_number()
what I would like to find out are the following:
the oracle implementation has visit_compound_select marked as todo,
what is the implication of that? (my guess is that limit+offset won't
work for join models?)
and are there any other shortcomings with the current patch?
Cheers
Lei
792c792,793
---
self._select_state = {}
796,799c797,800
if select.limit:
s += TOP %s % (select.limit,)
if select.offset:
raise exceptions.InvalidRequestError('MSSQL does not
support LIMIT with an offset')
---
#if select.limit:
#s += TOP %s % (select.limit,)
#if select.offset:
#raise exceptions.InvalidRequestError('MSSQL does not support
LIMIT with an offset')
846a848,883
# TODO: put a real copy-container on Select and copy, or somehow make
this
# not modify the Select statement
if self._select_state.get((select, 'visit'), False):
# cancel out the compiled order_by on the select
if hasattr(select, order_by_clause):
self.strings[select.order_by_clause] =
ansisql.ANSICompiler.visit_select(self, select)
return
if select.limit is not None or select.offset is not None:
self._select_state[(select, 'visit')] = True
# to use ROW_NUMBER(), an ORDER BY is required.
orderby = self.strings[select.order_by_clause]
if not orderby:
orderby = select.oid_column
self.traverse(orderby)
orderby = self.strings[orderby]
if not hasattr(select, '_mssql_visit'):
select.append_column(sql.literal_column(ROW_NUMBER() OVER
(ORDER BY %s) % orderby).label(ms_rn))
select._mssql_visit = True
limitselect = sql.select([c for c in select.alias('_mssql').c if
c.key!='ms_rn'])
if select.offset is not None:
limitselect.append_whereclause(ms_rn %d % select.offset)
if select.limit is not None:
limitselect.append_whereclause(ms_rn=%d %
(select.limit + select.offset))
else:
limitselect.append_whereclause(ms_rn=%d % select.limit)
self.traverse(limitselect)
self.strings[select] = self.strings[limitselect]
#print self.strings[select], select.offset, select.limit
self.froms[select] = self.froms[limitselect]
else:
ansisql.ANSICompiler.visit_select(self, select)
848c885
super(MSSQLCompiler, self).visit_select(select)
---
#super(MSSQLCompiler, self).visit_select(select)
On Jul 11, 9:43 am, BruceC [EMAIL PROTECTED] wrote:
Thank you all for looking into this - most appreciated!
On Jul 10, 6:13 pm, Paul Johnston [EMAIL PROTECTED] wrote:
Hi,
if 2005 supports ROW_NUMBER() OVER ORDER BY like oracle does, then yes
this could be a possible feature enhancement.
Another approach that occured to me is doing TOP (limit+offset) then seeking
the cursor. For server-side cursors this is probably nearly as efficient as
LIMIT/OFFSET.
I've raised #639 so we don't forget about this.
Paul- Hide quoted text -
- Show quoted text -
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] PROBLEMS WITH TIMESTAMP variable in MySql
Hi, I've been using SQLAlchemy since a month. I'm having some
problems with a one-to-many relationship. I have 2 tables: one for
users and one for users's emails. The problem arises when I want to
put a timestamp in the email table in order to see when a given
account was created. Here are the tables, my code, and the error:
User Table:
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Email Table:
CREATE TABLE `email` (
`id` int(11) NOT NULL auto_increment,
`email` varchar(100) default NULL,
`user_id` int(11) NOT NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `fk_user_id` (`user_id`),
CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user`
(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
##CODE
user_table = Table('user', metadata, autoload=True)
email_table = Table('email', metadata, autoload=True)
class User(object):
pass
class Email(object):
pass
session_context = SessionContext(create_session)
assign_mapper(session_context, User, user_table,
properties={'emails':relation(Email)})
assign_mapper(session_context, Email, email_table)
obj = User()
obj.get(1).emails
Traceback (most recent call last):
File stdin, line 1, in ?
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/
attributes.py, line 53, in __get__
return self.get(obj)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/
attributes.py, line 217, in get
values = callable_()
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/
strategies.py, line 222, in lazyload
result = session.query(self.mapper,
with_options=options).select_whereclause(self.lazywhere,
order_by=order_by, params=params)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/query.py,
line 309, in select_whereclause
return self._select_statement(statement, params=params)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/query.py,
line 798, in _select_statement
return self.execute(statement, params=params, **kwargs)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/query.py,
line 694, in execute
result = self.session.execute(self.mapper, clauseelement,
params=params)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/session.py,
line 183, in execute
return self.connection(mapper,
close_with_result=True).execute(clause, params, **kwargs)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 444, in execute
return Connection.executors[c](self, object, *multiparams,
**params)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 484, in execute_clauseelement
return self.execute_compiled(elem.compile(engine=self.__engine,
parameters=param), *multiparams, **params)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 503, in execute_compiled
proxy(str(compiled), parameters)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 499, in proxy
self._execute_raw(statement, parameters, cursor=cursor,
context=context)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 547, in _execute_raw
self._execute(cursor, statement, parameters, context=context)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 566, in _execute
raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (ValueError) invalid literal for
int(): 7- 'SELECT email.last_modified AS email_last_modified,
email.user_id AS email_user_id, email.id AS email_id, email.email AS
email_email \nFROM email \nWHERE %s = email.user_id ORDER BY
email.id' [1L]
Sorry for putting too much data, but I'm a little desperate, I've
been trying to do this for a whole week and I can't find useful
information about this error. I only found that this exception is
when a query is not very well formed or something like that. Please
help me as soon as you can, thanks in advance for reading this post.
regards,
Roberto Zapata
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: PROBLEMS WITH TIMESTAMP variable in MySql
Disclaimer: I'm no SQLAlchemy guru.
On Fri, Jul 13, 2007 at 01:53:48PM -, robertz23 wrote:
Hi, I've been using SQLAlchemy since a month. I'm having some
problems with a one-to-many relationship. I have 2 tables: one for
users and one for users's emails. The problem arises when I want to
put a timestamp in the email table in order to see when a given
account was created. Here are the tables, my code, and the error:
[...]
user_table = Table('user', metadata, autoload=True)
email_table = Table('email', metadata, autoload=True)
class User(object):
pass
class Email(object):
pass
session_context = SessionContext(create_session)
assign_mapper(session_context, User, user_table,
properties={'emails':relation(Email)})
assign_mapper(session_context, Email, email_table)
obj = User()
obj.get(1).emails
It looks like you are first creating a new User instance and then
calling .get(1) on it. I'd rather expect this to work:
emails = User.get(1).emails
Or following the query syntax that seems to be upcoming with 0.4:
emails = User.query.get(1).emails
Kindly
Christoph
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: URGENT: 2014, Commands out of sync; you can't run this command now
also send along a full stack trace so at least it can be seen where this is occuring. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: URGENT: 2014, Commands out of sync; you can't run this command now
On Jul 13, 2007, at 4:42 AM, Arun Kumar PG wrote: Any thoughts? my thoughts are, either the bug still remains in SA's connection pool and everyone is magically avoiding the problem somehow (when it was fixed, people who observed the problem all the time noted that it went away completely, and we also have some threading tests to double check it), or your site is receiving tremendously huge impact so that a more rare condition comes out more frequently, or theres something subtle in your app making this happen, or you need to ensure that youre on SA 0.3.8 and the latest version of MySQLDB. so far I can only look into the first point above and will confer with some developers today on this. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: URGENT: 2014, Commands out of sync; you can't run this command now
Thx Michael. Well, I don't think that I am doing that. To give you a picture of the object model this is how the hierarchy is: BaseOrmDao (+GetSession() this returns the session attached to the current thread) ^ | Request -PreProcessor - Controller - Manager - DaoFactory - DAOs | V (orm.session attached to thread here) To clarify the DaoFactory will return a new DAO object back to the manager always, that means a DAO object. Also, a new instance of Controller and Manager is made per request. To answer your question there is no point in the communication where two threads share the same object. (Not sure if SQLAlchemy does so when mapped objects are used in the DAO layer ?) Any thoughts? On 7/13/07, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 13, 2007, at 2:27 AM, Arun Kumar PG wrote: I create an orm session as soon as the request comes in and store the reference to the same in the curent thread i.e. threading.currentThread().session = new_session. This session is stored in the current thread so that I can get the same session across all DAO objects. so basically ever DAO in the request chain can simply get the session by saying threading.currenrThread.session and use it. Finally, once the request is over this session object is removed from the current thread i.e. del session. I can see that during multiple request the thread ids are different so I believe that all of them are having their own copy of session. Further, I am using pool module of sqlalchemy from where a connection is returned to orm session. My guess is that somewhere in that connection management things are getting schewed up - there was a bug like this at one time in the pool, but it was fixed probably a year ago, and nobody has reported this issue since. are the objects which you load from the session being shared between threads ? i.e. a second thread issues a lazy-load operation on an object's attribute ? that counts as multi-threaded session access. -- Cheers, - A --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: PROBLEMS WITH TIMESTAMP variable in MySql
i cant reproduce that on this end. created the tables, inserted a
row into each, script returns fine (note its using a long value, i
thought that might be an issue, but its not). make sure youre on the
latest MySQLDB driver. also not sure if an older MySQL might be
problematic as well here (though i doubt it, its a very basic example).
On Jul 13, 2007, at 9:53 AM, robertz23 wrote:
Hi, I've been using SQLAlchemy since a month. I'm having some
problems with a one-to-many relationship. I have 2 tables: one for
users and one for users's emails. The problem arises when I want to
put a timestamp in the email table in order to see when a given
account was created. Here are the tables, my code, and the error:
User Table:
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Email Table:
CREATE TABLE `email` (
`id` int(11) NOT NULL auto_increment,
`email` varchar(100) default NULL,
`user_id` int(11) NOT NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `fk_user_id` (`user_id`),
CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user`
(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
##CODE
user_table = Table('user', metadata, autoload=True)
email_table = Table('email', metadata, autoload=True)
class User(object):
pass
class Email(object):
pass
session_context = SessionContext(create_session)
assign_mapper(session_context, User, user_table,
properties={'emails':relation(Email)})
assign_mapper(session_context, Email, email_table)
obj = User()
obj.get(1).emails
Traceback (most recent call last):
File stdin, line 1, in ?
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/
attributes.py, line 53, in __get__
return self.get(obj)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/
attributes.py, line 217, in get
values = callable_()
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/
strategies.py, line 222, in lazyload
result = session.query(self.mapper,
with_options=options).select_whereclause(self.lazywhere,
order_by=order_by, params=params)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/query.py,
line 309, in select_whereclause
return self._select_statement(statement, params=params)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/query.py,
line 798, in _select_statement
return self.execute(statement, params=params, **kwargs)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/query.py,
line 694, in execute
result = self.session.execute(self.mapper, clauseelement,
params=params)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/session.py,
line 183, in execute
return self.connection(mapper,
close_with_result=True).execute(clause, params, **kwargs)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 444, in execute
return Connection.executors[c](self, object, *multiparams,
**params)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 484, in execute_clauseelement
return self.execute_compiled(elem.compile(engine=self.__engine,
parameters=param), *multiparams, **params)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 503, in execute_compiled
proxy(str(compiled), parameters)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 499, in proxy
self._execute_raw(statement, parameters, cursor=cursor,
context=context)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 547, in _execute_raw
self._execute(cursor, statement, parameters, context=context)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 566, in _execute
raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (ValueError) invalid literal for
int(): 7- 'SELECT email.last_modified AS email_last_modified,
email.user_id AS email_user_id, email.id AS email_id, email.email AS
email_email \nFROM email \nWHERE %s = email.user_id ORDER BY
email.id' [1L]
Sorry for putting too much data, but I'm a little desperate, I've
been trying to do this for a whole week and I can't find useful
information about this error. I only found that this exception is
when a query is not very well formed or something like that. Please
help me as soon as you can, thanks in advance for reading this post.
regards,
Roberto Zapata
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
[sqlalchemy] Re: Pagination problems using SQLAlchemy MSSQL in Pylons - no OFFSET
On Jul 13, 2007, at 4:20 AM, lei you wrote: Thank you for the extremely speedy response :) Are we aware of any other impacts of oracle's offset+limit implication? it has issues with DISTINCT as well. there is ticket #536 for this which I spent some time with but not enough to understand what was going on there exactly...if you care to look into that and help clarify for me that would help. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: URGENT: 2014, Commands out of sync; you can't run this command now
Sure Michael I will get back on this in a while as I am researching on this. Thanks for your support. I hope this gets resolved sooner as I am very much dependent on this and the application is really critical and should be up in next couple days! Thanks and i will get back on this in next couple hours. On 7/13/07, Michael Bayer [EMAIL PROTECTED] wrote: also send along a full stack trace so at least it can be seen where this is occuring. -- Cheers, - A --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Weird problem in SqlSoup
The latest trunk seems to have fixed this issue.
Thanks
On Jul 12, 6:22 pm, jason kirtland [EMAIL PROTECTED] wrote:
bbull wrote:
I have some code that returns what I'm calling a table object.
return getattr(self.dbc, tablename)
where self.dbc is an SqlSoup object and tablename is the name of the
table.
[..]
TypeError: type() argument 1 must be string, not unicode
For some reason, the line mapname = 'Mapped' +
_selectable_name(selectable) in sqlsoup.py creates a unicode object
instead of a string object when 'entity' is passed in. If I put a
simple str() around the code (mapname = str('Mapped' +
_selectable_name(selectable))), it works with no problem.
Any reason why only this one particular case would cause this problem?
Try that again with the latest from trunk.
-j
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Multi-table inheritance and conflicting id columns
i played a bit with your thing... how do u expect the id column to appear in the Employee actualy? do u expect to get the base person' one, or the child one? or both? looking from the child, yes, the child one will obscure the parent one, but from the parent.. u'll never get the child one. yeah actually, playing with this some more, hes right. even with the fix i put in, your mapper still is munging the two distinct id values into one column attribute, id, which still creates problems. you still have to: a. name the columns differently b. join them together on a foreign key c. name them distinctly on your mapper: would it be enough for him to - rename the property in mapper (i guess not) - rename/alias the column that SA is using (and not the one in the DB) - ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: PROBLEMS WITH TIMESTAMP variable in MySql
Thanks for the answers. I've tried what Christoph told, but returns
the same error. What I'm trying now is to override that column to be
a DateTime type because in this case SQLAlchemy doesn't give me any
errors, so I think you are right Michael, is because of the driver.
Can tell me how to override this column so that SQLAlchemy can see it
as a datetime. I have tried this, I do not know if this is correct or
not:
email_table = Table('email', metadata, Column('last_modified',
DateTime()), autoload=True)
Thanks again,
Roberto Zapata
On Jul 13, 10:14 am, Michael Bayer [EMAIL PROTECTED] wrote:
i cant reproduce that on this end. created the tables, inserted a
row into each, script returns fine (note its using a long value, i
thought that might be an issue, but its not). make sure youre on the
latest MySQLDB driver. also not sure if an older MySQL might be
problematic as well here (though i doubt it, its a very basic example).
On Jul 13, 2007, at 9:53 AM, robertz23 wrote:
Hi, I've been using SQLAlchemy since a month. I'm having some
problems with a one-to-many relationship. I have 2 tables: one for
users and one for users's emails. The problem arises when I want to
put a timestamp in the email table in order to see when a given
account was created. Here are the tables, my code, and the error:
User Table:
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Email Table:
CREATE TABLE `email` (
`id` int(11) NOT NULL auto_increment,
`email` varchar(100) default NULL,
`user_id` int(11) NOT NULL,
`last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `fk_user_id` (`user_id`),
CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `user`
(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
##CODE
user_table = Table('user', metadata, autoload=True)
email_table = Table('email', metadata, autoload=True)
class User(object):
pass
class Email(object):
pass
session_context = SessionContext(create_session)
assign_mapper(session_context, User, user_table,
properties={'emails':relation(Email)})
assign_mapper(session_context, Email, email_table)
obj = User()
obj.get(1).emails
Traceback (most recent call last):
File stdin, line 1, in ?
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/
attributes.py, line 53, in __get__
return self.get(obj)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/
attributes.py, line 217, in get
values = callable_()
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/
strategies.py, line 222, in lazyload
result = session.query(self.mapper,
with_options=options).select_whereclause(self.lazywhere,
order_by=order_by, params=params)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/query.py,
line 309, in select_whereclause
return self._select_statement(statement, params=params)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/query.py,
line 798, in _select_statement
return self.execute(statement, params=params, **kwargs)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/query.py,
line 694, in execute
result = self.session.execute(self.mapper, clauseelement,
params=params)
File /usr/lib/python2.3/site-packages/sqlalchemy/orm/session.py,
line 183, in execute
return self.connection(mapper,
close_with_result=True).execute(clause, params, **kwargs)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 444, in execute
return Connection.executors[c](self, object, *multiparams,
**params)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 484, in execute_clauseelement
return self.execute_compiled(elem.compile(engine=self.__engine,
parameters=param), *multiparams, **params)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 503, in execute_compiled
proxy(str(compiled), parameters)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 499, in proxy
self._execute_raw(statement, parameters, cursor=cursor,
context=context)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 547, in _execute_raw
self._execute(cursor, statement, parameters, context=context)
File /usr/lib/python2.3/site-packages/sqlalchemy/engine/base.py,
line 566, in _execute
raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (ValueError) invalid literal for
int(): 7- 'SELECT email.last_modified AS email_last_modified,
email.user_id AS email_user_id, email.id AS email_id, email.email AS
email_email \nFROM email \nWHERE %s = email.user_id ORDER BY
email.id' [1L]
Sorry for putting too much data, but I'm a little desperate, I've
[sqlalchemy] Re: PROBLEMS WITH TIMESTAMP variable in MySql
On Jul 13, 2007, at 3:41 PM, robertz23 wrote:
Thanks for the answers. I've tried what Christoph told, but returns
the same error. What I'm trying now is to override that column to be
a DateTime type because in this case SQLAlchemy doesn't give me any
errors, so I think you are right Michael, is because of the driver.
Can tell me how to override this column so that SQLAlchemy can see it
as a datetime. I have tried this, I do not know if this is correct or
not:
email_table = Table('email', metadata, Column('last_modified',
DateTime()), autoload=True)
that is correct although the mysql reflection should detect that type
from the TIMESTAMP column type regardless.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
