[sqlalchemy] Re: something wrong about sqlsoup in mulithread request
I finally solved the problem. I used to create a join in each request, where will produce this problem. Now I create the join once and reuse in each request, everything is OK now. And I do use a universal SqlSoup object. - Original Message - From: Michael Bayer mike...@zzzcomputing.com To: sqlalchemy@googlegroups.com Sent: Sunday, June 07, 2009 11:13 PM Subject: [sqlalchemy] Re: something wrong about sqlsoup in mulithread request that error looks like a collision in a very short section which only occurs during mapper creation (i.e. weakref.iterkeys()) and should be exceedingly difficult to reproduce, unless you are creating new SqlSoup objects on each request.Make sure you're using just one SqlSoup object declared when your program first runs, so that it creates a single set of mappers for the whole process, instead of new mappers for every request. OTOH if you are truly getting this collision with just a handful of mappers in use throughout the app, we can place an additional lock around the two unsynchronized references to _mapper_registry in mapper.py. On Jun 7, 2009, at 6:41 AM, tsangpo wrote: I use the SqlSoup to make it easy to access the database. It's all right when I make the http request one by one. But it run into errors when serveral requests were sent at the same time, and fail to proccess requests anymore. I log the Session, and found that each request exact has different session. The most strange thing is that each time the sqlsoup go down, it produce different error message: When everything is OK, the log is: - 2009-06-07 17:18:33,082 INFO sqlalchemy.engine.base.Engine.0x...a530 BEGIN 2009-06-07 17:18:33,082 INFO sqlalchemy.engine.base.Engine.0x...a530 SELECT user.id AS user_id, user.name AS user_name, user.full_name AS user_full_name, user.password AS user_password, user.active AS user_active FROM user WHERE user.password = %s AND user.name = %s LIMIT 0, 1 2009-06-07 17:18:33,082 INFO sqlalchemy.engine.base.Engine.0x...a530 ['123456', 'eric'] 2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530 SELECT role.id AS role_id, role.name AS role_name, role.description AS role_description, rl_user_role.user_id AS rl_user_role_user_id, rl_user_role.role_id AS rl_user_role_role_id, rl_role_action.role_id AS rl_role_action_role_id, rl_role_action.action_id AS rl_role_action_action_id FROM role INNER JOIN rl_user_role ON role.id = rl_user_role.role_id INNER JOIN rl_role_action ON role.id = rl_role_action.role_id WHERE rl_role_action.action_id = %s AND rl_user_role.user_id = %s 2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530 [1L, 1L] 2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530 SELECT user.id AS user_id, user.name AS user_name, user.full_name AS user_full_name, user.password AS user_password, user.active AS user_active FROM user 2009-06-07 17:18:33,098 INFO sqlalchemy.engine.base.Engine.0x...a530 [] 2009-06-07 17:18:33,115 INFO sqlalchemy.engine.base.Engine.0x...a530 COMMIT -- And error edition 1: - Traceback (most recent call last): File D:\CodingLife\win32\home\workspace\eric\lib\google_appengine\google \appengine\ext\webapp\__init__ .py, line 499, in __call__ handler.get(*groups) File D:\CodingLife\win32\home\workspace\eric\src\eric\database.py, line 44, in _func return func(*args, **kwargs) File D:\CodingLife\win32\home\workspace\eric\src\eric\auth \__init__.py, line 23, in action_metho d if roles == 'everyone' or check_privilege(action_id, users.get_current_user().id): File D:\CodingLife\win32\home\workspace\eric\src\eric\auth \__init__.py, line 67, in check_privil ege db.role.id==db.rl_user_role.role_id) File D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib \sqlalchemy\ext\sqlsoup.py, line 541, in join return self.map(j) File D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib \sqlalchemy\ext\sqlsoup.py, line 531, in map t = class_for_table(selectable, **kwargs) File D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib \sqlalchemy\ext\sqlsoup.py, line 483, in class_for_table for k in mappr.iterate_properties: File D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib \sqlalchemy\orm\mapper.py, line 777, in iterate_properties self.compile() File D:\CodingLife\win32\home\workspace\eric\lib\SQLAlchemy-0.5.3\lib \sqlalchemy\orm\mapper.py, line 651, in compile for mapper in list(_mapper_registry): File D:\CodingLife\win32\native\Python25\lib\weakref.py, line 303, in iterkeys for wr in self.data.iterkeys(): RuntimeError: dictionary changed
[sqlalchemy] Re: insert statment
normal SQL includes named bind parameters. you might be looking for a
raw execution, i.e.:
conn.execute(insert into table (a, b, c) values (?, ?, ?), [(1,2,3),
(4,5,6)])
that will take you right through to the DBAPI but your code will not be
database agnostic.
Ashish Bhatia wrote:
yeah thnks for the help.
one more dbt. do i have to provide values as dict always ie. with
column name. Can i give direct values like we do in normal sql.?
thnks in advance
On Jun 9, 9:02 am, Michael Bayer mike...@zzzcomputing.com wrote:
you need to use the executemany form described
athttp://www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multip...
.
On Jun 8, 2009, at 11:54 PM, Ashish Bhatia wrote:
Sorry, Its my typing mistake :( . I put : insted , . But still my
question yeat remained unanswered. :(
On Jun 8, 7:50 pm, Didip Kerabat did...@gmail.com wrote:
You have Syntax Error here:
('sdsd':'sdsds')
That one should be tuple right?
- Didip -
On Mon, Jun 8, 2009 at 6:14 AM, Ash ashishsinghbha...@gmail.com
wrote:
Hello ,
I am trying to insert in the table using two ways in the values
which
i show below
engine = sqlalchemy.create_engine(to poastgres)
metadata = MetaData()
t1 = Table('master',metadata) # assume master has 2 feilds name ,
city
t1.insert({'name':'','city':'bank'})
engine,execute(t1)
This works for fine me.
If i make values like this
tt = [('asasas','belhium'),('sdsd':'sdsds')]
t1.insert(values=tt)
i get error
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) syntax
error at or near )
LINE 1: INSERT INTO abc () VALUES ()
^
'INSERT INTO abc () VALUES ()' {}
Can any one guide whts wrong... i jnow value is not being passed so
anyother way.
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: insert statment
The insert is looking for a list of dictionaries. What you would like to do
is enter column names once along with lists representing rows of data to
insert.
Here is some code I use to simplify setting up the dictionaries when
inserting a large number of rows. I'm sure others have come up with other
approaches.
==
Build list of dictionaries for insert operation
===
cols = ('deptid','deptname',)
data = ((31,'Sales'),(33,'Engineering'),)
insdata = [dict(zip(cols,datum)) for datum in data]
conn.execute(dept.insert(), insdata)
## or in a single statement ##
conn.execute(dept.insert(), [
dict(zip(('deptid','deptname',), datum)) for datum in (
(31,'Sales'),
(33,'Engineering'),
)]
--
Mike Conley
On Tue, Jun 9, 2009 at 9:31 AM, Michael Bayer mike...@zzzcomputing.comwrote:
normal SQL includes named bind parameters. you might be looking for a
raw execution, i.e.:
conn.execute(insert into table (a, b, c) values (?, ?, ?), [(1,2,3),
(4,5,6)])
that will take you right through to the DBAPI but your code will not be
database agnostic.
Ashish Bhatia wrote:
yeah thnks for the help.
one more dbt. do i have to provide values as dict always ie. with
column name. Can i give direct values like we do in normal sql.?
thnks in advance
On Jun 9, 9:02 am, Michael Bayer mike...@zzzcomputing.com wrote:
you need to use the executemany form described
athttp://
www.sqlalchemy.org/docs/05/sqlexpression.html#executing-multip...
.
On Jun 8, 2009, at 11:54 PM, Ashish Bhatia wrote:
Sorry, Its my typing mistake :( . I put : insted , . But still my
question yeat remained unanswered. :(
On Jun 8, 7:50 pm, Didip Kerabat did...@gmail.com wrote:
You have Syntax Error here:
('sdsd':'sdsds')
That one should be tuple right?
- Didip -
On Mon, Jun 8, 2009 at 6:14 AM, Ash ashishsinghbha...@gmail.com
wrote:
Hello ,
I am trying to insert in the table using two ways in the values
which
i show below
engine = sqlalchemy.create_engine(to poastgres)
metadata = MetaData()
t1 = Table('master',metadata) # assume master has 2 feilds name ,
city
t1.insert({'name':'','city':'bank'})
engine,execute(t1)
This works for fine me.
If i make values like this
tt = [('asasas','belhium'),('sdsd':'sdsds')]
t1.insert(values=tt)
i get error
sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) syntax
error at or near )
LINE 1: INSERT INTO abc () VALUES ()
^
'INSERT INTO abc () VALUES ()' {}
Can any one guide whts wrong... i jnow value is not being passed so
anyother way.
--~--~-~--~~~---~--~~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Mon, Jun 8, 2009 at 2:46 PM, ddorothydidoro...@gmail.com wrote: I have looked into this and considered what you have said. I think I have come up with a potential solution. It seems to be that the most common driver for mssql on non-windows platforms is going to be freeTDS. Since there appears to be no way of knowing what ODBC is using under the hood we should be able to safely assume that they are using freeTDS if not on windows. Further, there should be nominal overhead in setting nocount on. So perhaps the following diff will offer what is needed to address this specific issue. --- sqlalchemy/databases/mssql.py 2009-06-01 13:00:36.0 -0400 +++ sqlalchemy/databases/mssql.py 2009-06-08 15:31:22.0 -0400 @@ -239,7 +239,7 @@ does **not** work around -import datetime, decimal, inspect, operator, re, sys, urllib +import datetime, decimal, inspect, operator, re, sys, urllib, os from sqlalchemy import sql, schema, exc, util from sqlalchemy import Table, MetaData, Column, ForeignKey, String, Integer @@ -982,6 +982,8 @@ super(MSSQLExecutionContext_pyodbc, self).pre_exec() if self.compiled.isinsert and self.HASIDENT and not self.IINSERT \ and len(self.parameters) == 1 and self.dialect.use_scope_identity: + if os.name != 'nt': + self.cursor.execute(SET NOCOUNT ON) self.statement += ; select scope_identity() def post_exec(self): @@ -996,6 +998,8 @@ except pyodbc.Error, e: self.cursor.nextset() self._last_inserted_ids = [int(row[0])] + if os.name != 'nt': + self.cursor.execute(SET NOCOUNT OFF) else: super(MSSQLExecutionContext_pyodbc, self).post_exec() Is it possible to set this nocount off somewhere on the dsn setup configuration file? Or this can only be set at run time? Thanks, Lucas --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Formatting return of 'query'
There are probably multiple ways to do what you are looking for. I would consider simply constructing the query without the .all() and returning the query instance. Callers then simply iterate over the query themselves. No need to parse a string to get the individual columns; they could do something as simple as: for row in call_your_code(whatever parameters are required,...): use row.firstname, row.lastname, etc. in their code If you do execute the query with .all(); callers can iterate on the result in the same way. Another thing to remember is that even if you do generate a list and return it; only a pointer to the list is being returned. The list is not copied to another memory location unless the caller does that themselves, and they should be working with and understand the same memory constraints you are dealing with. -- Mike Conley On Mon, Jun 8, 2009 at 11:34 PM, Harish Vishwanath harish.shas...@gmail.com wrote: Thanks for your thoughts! I was looking for some query apis to get the job done. Query itself is an iterator and I want to construct and pass around the query object itself to my callers. I work on an embedded system, and I dont want to do a query.all(), post process it with required delimiters and send a list in memory coz of memory constraints. Regards, Harish Sent from Bangalore, KA, India On Tue, Jun 9, 2009 at 1:09 AM, phrrn...@googlemail.com phrrn...@googlemail.com wrote: I have something like this to serialize a result-set to delimited file- format. It is not very pretty and probably not at all pythonic but I find it handy. pjjH def as_delimited(q, *args): csvdata = StringIO() w = writer(csvdata, delimiter='|') for i in q.values(*args): w.writerow(i) yield csvdata.getvalue() csvdata.truncate(0) q = session.query(User) for i in as_delimited(q, User.firstname,User.lastname,User.age,User.password): print i, On Jun 8, 10:18 am, Glauco gla...@sferacarta.com wrote: Harish Vishwanath ha scritto: cut How can I modify this query to return something like : [(fname~lname~22~pwd)...] with '~' being preferred delimiter. SA return a list or record, what exactly you are searching for? a string or something else I would like to know if I can return something like above directly from the query itself. something like ? [ '~'.join(x) for x in qry.fetchall() ] Glauco --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
