RE: [sqlalchemy] Multiple sessions and data conflicts.

[Jackson, Cameron]

OK, I think I understand. Just to make sure, how about this example:

 from SomeModule import Session, Foo
 session1 = Session()
 session2 = Session()

 data1 = session1.query(Foo).all()
 data2 = session2.query(Foo).all()

 data1[0].bar = 'Baz'
 data1.append(Foo)
 session1.add(data1[-1])
 session1.commit()

 session2.rollback()
 data2 = session2.query(Foo).all()

After all of that, data2[0].bar should equal 'Baz', and data2[-1] should be the 
new Foo that we created and added to data[1], correct? In other words, the 
contents of data1 and data2 should be identical, right?

Cheers,

Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron.jack...@thalesgroup.com.au | 
www.thalesgroup.com.au
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On 
Behalf Of Michael Bayer
Sent: Monday, 19 December 2011 12:51 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Multiple sessions and data conflicts.


On Dec 18, 2011, at 8:25 PM, Jackson, Cameron wrote:


Here is a simplified version of my scenario:
My application has two session objects. One is being used for editing certain 
data, and the other is reading this data, doing some calculations, and 
displaying the results. I've set up a callback kind of system so that whenever 
the first session does a commit, a method is called that does a rollback on the 
second session, and redoes the calculations and display.
I've had bugs crop up where the data I get from querying the second session 
object doesn't match what I know is in the database, but I haven't been able to 
reproduce them consistently yet. So my questions are:
1.   After a session.rollback(), should the data obtained from that 
session's queries match what's in the DB now? Or what was in the DB when the 
session was created? The 
docs
 are not clear on this.
2.   If a rollback is not enough to guarantee that the data from querying a 
session is up to date, then what is? Should I just create a new session 
entirely?

These are the comprehensive docs on rollback:

http://www.sqlalchemy.org/docs/orm/session.html#rolling-back


A rollback expires all attributes that are database mapped, on objects that are 
persistent within the session.   Touching any of these attributes subsequent to 
the expire will cause them to emit SQL to get the new value back.

You might not see what you expect if A. you're accessing an object that isn't 
persistent within that session, such as one that was "new" before the rollback, 
as these get evicted after rollback, or B. you're looking at your own 
attributes which aren't directly database mapped.

As far as "what's in the DB", a new transaction starts after rollback, and 
that's against whatever has been committed to that DB.  If your other 
transaction hasn't committed then you wouldn't see its data.





cheers,
Cam
Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron.jack...@thalesgroup.com.au | 
www.thalesgroup.com.au
- 
DISCLAIMER: This e-mail transmission and any documents, files and previous 
e-mail messages attached to it are private and confidential. They may contain 
proprietary or copyright material or information that is subject to legal 
professional privilege. They are for the use of the intended recipient only. 
Any unauthorised viewing, use, disclosure, copying, alteration, storage or 
distribution of, or reliance on, this message is strictly prohibited. No part 
may be reproduced, adapted or transmitted without the written permission of the 
owner. If you have received this transmission in error, or are not an 
authorised recipient, please immediately notify the sender by return email, 
delete this message and all copies from your e-mail system, and destroy any 
printed copies. Receipt by anyone other than the intended recipient should not 
be deemed a waiver of any privilege or protection. Thales Australia does not 
warrant or represent that this e-mail or any documents, files and previous 
e-mail messages attached are error or virus free. 
-

--
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..

Re: [sqlalchemy] Re: How long should it take to load a data base into memory?

[Krishnakant Mane]

May be I am wrong,
But I guess you have not used stored procedures?
If speed and performance is important along with scalability, then 
forget database independence and use stored procedurs.
I have built a small function called execProc() which I can send you off 
the list.

It just makes use of the sqlalchemy's api and does not involve sessions etc.
Just a connectionless engine (which directly connects to the database ) 
and executes the said stored procedure.
I use one instance of the engine and entire thing happens at the 
database side.

I just get the result proxy object with wich you can obviusly work.
I have seen a big performance bennifit and got control over memory usage 
at the client side because now the ready made queries are executed at 
the database side (postgresql) in my case and just the rows returned.

You, see the overhead of session is removed here.
Happy hacking.
Krishnakant.

On 19/12/11 04:40, Michael Bayer wrote:

On Dec 18, 2011, at 5:53 PM, rivka wrote:


So - actually investigated it thoroughly - and here are the results:

My database size on disk is 362MB and includes the main table and
multiple one to many associated tables. I am querying the main table
(which has little info in itself - mainly id, an integer value and a
string value (mostly less than 100 characters) jointly with a one to
many relationship from the main table.
I run it through ipython and the program starts from ~23MB, and toward
the very end of the query - it soars to 582MB ! And that is when the
query involves only 10 rows out of the total of 1.2M rows in the
data base. So - I am very confused about why the memory explosion.
What occupies those 550MB of memory? surely not the data from the DB
which is probably less than 50MB total...
That explains the behavior that I have observed when attempting larger
queries - the memory explodes and the system shifts to using VM -
which is basically - working with the HD and churning memory and
caches non-stop.

I need to get control over the memory size so that I can hold a larger
query in the memory and work directly with memory.

You'd definitely need to forego using the ORM and build a very memory efficient 
datastructure that suits your needs.

it sounds like you might benefit by reorganizing your data into simple 
dictionaries and lists and just using Redis:  http://redis.io/

Since you're looking for all the data to be "in memory", you should just get it 
out of the relational database as the first step, then work with an optimized structure 
that suits your needs.  redis is designed for this use case.




--
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.

Re: [sqlalchemy] Multiple sessions and data conflicts.

[Michael Bayer]

On Dec 18, 2011, at 8:25 PM, Jackson, Cameron wrote:

> Here is a simplified version of my scenario:
> My application has two session objects. One is being used for editing certain 
> data, and the other is reading this data, doing some calculations, and 
> displaying the results. I've set up a callback kind of system so that 
> whenever the first session does a commit, a method is called that does a 
> rollback on the second session, and redoes the calculations and display.
> I've had bugs crop up where the data I get from querying the second session 
> object doesn't match what I know is in the database, but I haven't been able 
> to reproduce them consistently yet. So my questions are:
> 1.   After a session.rollback(), should the data obtained from that 
> session's queries match what's in the DB now? Or what was in the DB when the 
> session was created? The docs are not clear on this.
> 2.   If a rollback is not enough to guarantee that the data from querying 
> a session is up to date, then what is? Should I just create a new session 
> entirely?

These are the comprehensive docs on rollback:

http://www.sqlalchemy.org/docs/orm/session.html#rolling-back


A rollback expires all attributes that are database mapped, on objects that are 
persistent within the session.   Touching any of these attributes subsequent to 
the expire will cause them to emit SQL to get the new value back.

You might not see what you expect if A. you're accessing an object that isn't 
persistent within that session, such as one that was "new" before the rollback, 
as these get evicted after rollback, or B. you're looking at your own 
attributes which aren't directly database mapped.

As far as "what's in the DB", a new transaction starts after rollback, and 
that's against whatever has been committed to that DB.  If your other 
transaction hasn't committed then you wouldn't see its data.




> cheers,
> Cam
> Cameron Jackson
> Engineering Intern
> Air Operations
> Thales Australia
> Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
> Siddeley Street, Melbourne, VIC 3005, Australia
> Tel: +61 3 8630 4591
> cameron.jack...@thalesgroup.com.au | www.thalesgroup.com.au
> - 
> DISCLAIMER: This e-mail transmission and any documents, files and previous 
> e-mail messages attached to it are private and confidential. They may contain 
> proprietary or copyright material or information that is subject to legal 
> professional privilege. They are for the use of the intended recipient only. 
> Any unauthorised viewing, use, disclosure, copying, alteration, storage or 
> distribution of, or reliance on, this message is strictly prohibited. No part 
> may be reproduced, adapted or transmitted without the written permission of 
> the owner. If you have received this transmission in error, or are not an 
> authorised recipient, please immediately notify the sender by return email, 
> delete this message and all copies from your e-mail system, and destroy any 
> printed copies. Receipt by anyone other than the intended recipient should 
> not be deemed a waiver of any privilege or protection. Thales Australia does 
> not warrant or represent that this e-mail or any documents, files and 
> previous e-mail messages attached are error or virus free. 
> -
> 
> -- 
> 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.

-- 
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] Multiple sessions and data conflicts.

[Jackson, Cameron]

Here is a simplified version of my scenario:
My application has two session objects. One is being used for editing certain 
data, and the other is reading this data, doing some calculations, and 
displaying the results. I've set up a callback kind of system so that whenever 
the first session does a commit, a method is called that does a rollback on the 
second session, and redoes the calculations and display.
I've had bugs crop up where the data I get from querying the second session 
object doesn't match what I know is in the database, but I haven't been able to 
reproduce them consistently yet. So my questions are:

1.   After a session.rollback(), should the data obtained from that 
session's queries match what's in the DB now? Or what was in the DB when the 
session was created? The 
docs
 are not clear on this.

2.   If a rollback is not enough to guarantee that the data from querying a 
session is up to date, then what is? Should I just create a new session 
entirely?
cheers,
Cam
Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron.jack...@thalesgroup.com.au | 
www.thalesgroup.com.au


-
DISCLAIMER: This e-mail transmission and any documents, files and 
previous e-mail messages attached to it are private and confidential.  
They may contain proprietary or copyright material or information that 
is subject to legal professional privilege.  They are for the use of 
the intended recipient only.  Any unauthorised viewing, use, disclosure, 
copying, alteration, storage or distribution of, or reliance on, this 
message is strictly prohibited.  No part may be reproduced, adapted or 
transmitted without the written permission of the owner.  If you have 
received this transmission in error, or are not an authorised recipient, 
please immediately notify the sender by return email, delete this 
message and all copies from your e-mail system, and destroy any printed 
copies.  Receipt by anyone other than the intended recipient should not 
be deemed a waiver of any privilege or protection.  Thales Australia 
does not warrant or represent that this e-mail or any documents, files 
and previous e-mail messages attached are error or virus free.  

-

-- 
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.

Re: [sqlalchemy] How to refer to a live SA transaction from subsequent requests

[Andronikos Nedos]

On Saturday, 17 December 2011 19:57:10 UTC, Michael Bayer wrote:
>
>
> On Dec 17, 2011, at 2:24 PM, Andronikos Nedos wrote:
>
>
> So, if I understand correctly, I need to maintain the Connection object 
> between requests and on the 2nd request bind a session to the existing 
> Connection object and then 
> session.commit() or session.abort() ? The question now is how can I 
> persist the connection object between requests, is in-memory the only 
> option here ?
>
>
> One or both of us might be misunderstanding parts of the background here, 
> but my current take on it is that you want one request to end completely, 
> but for the database to maintain the transaction.  Then you'd like to 
> resume the transaction in a subsequent request.   This approach uses a 
> transaction that was created using PREPARE TRANSACTION along with an XID.   
>  When that technique is used, the database, let's say its Postgresql, will 
> persistently hold onto the transaction noted by an XID after the connection 
> which created it is gone.   An entirely new database connection can resume 
> this transaction by again calling BEGIN PREPARED with the same XID.
>
> So what I'm suggesting is that the string "XID" symbol is the only thing 
> you need to carry across to the second request, in order to pick up again 
> on that same transaction.   You'd never want to hold DBAPI connections or 
> any other kind of resource like that between requests.The XID approach 
> is already quite dangerous, as if your second HTTP request never comes in, 
> you have a dangling transaction in your database, locks and all.   The only 
> way to zap these is to query through pg_stat_activity, get their ids and 
> cancel them with "ROLLBACK PREPARED ".
>
>

You're spot on Michael. I thought we could get away without using a 
DB-driven TPC as we don't need to commit to multiple databases on the 
backend,  but it seems
our requirements are best solved with the machinery offered by TPC.  

As for stale transactions, not much we can do about it, other than use a 
timeout to identify and terminate what appear to be a stale txns.

Thanks again,
Andronikos

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/8s0yVwcpPGkJ.
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.

Re: [sqlalchemy] Re: How long should it take to load a data base into memory?

[Michael Bayer]

On Dec 18, 2011, at 5:53 PM, rivka wrote:

> So - actually investigated it thoroughly - and here are the results:
> 
> My database size on disk is 362MB and includes the main table and
> multiple one to many associated tables. I am querying the main table
> (which has little info in itself - mainly id, an integer value and a
> string value (mostly less than 100 characters) jointly with a one to
> many relationship from the main table.
> I run it through ipython and the program starts from ~23MB, and toward
> the very end of the query - it soars to 582MB ! And that is when the
> query involves only 10 rows out of the total of 1.2M rows in the
> data base. So - I am very confused about why the memory explosion.
> What occupies those 550MB of memory? surely not the data from the DB
> which is probably less than 50MB total...
> That explains the behavior that I have observed when attempting larger
> queries - the memory explodes and the system shifts to using VM -
> which is basically - working with the HD and churning memory and
> caches non-stop.
> 
> I need to get control over the memory size so that I can hold a larger
> query in the memory and work directly with memory.

You'd definitely need to forego using the ORM and build a very memory efficient 
datastructure that suits your needs.

it sounds like you might benefit by reorganizing your data into simple 
dictionaries and lists and just using Redis:  http://redis.io/

Since you're looking for all the data to be "in memory", you should just get it 
out of the relational database as the first step, then work with an optimized 
structure that suits your needs.  redis is designed for this use case.


-- 
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: How long should it take to load a data base into memory?

[rivka]

So - actually investigated it thoroughly - and here are the results:

My database size on disk is 362MB and includes the main table and
multiple one to many associated tables. I am querying the main table
(which has little info in itself - mainly id, an integer value and a
string value (mostly less than 100 characters) jointly with a one to
many relationship from the main table.
I run it through ipython and the program starts from ~23MB, and toward
the very end of the query - it soars to 582MB ! And that is when the
query involves only 10 rows out of the total of 1.2M rows in the
data base. So - I am very confused about why the memory explosion.
What occupies those 550MB of memory? surely not the data from the DB
which is probably less than 50MB total...
That explains the behavior that I have observed when attempting larger
queries - the memory explodes and the system shifts to using VM -
which is basically - working with the HD and churning memory and
caches non-stop.

I need to get control over the memory size so that I can hold a larger
query in the memory and work directly with memory.

Any suggestions?

Thanks for all your wonderful help and responsiveness. It is highly
highly appreciated and if I ever finish my thesis (in economics - if
you can believe it... though I am an engineer in origine..) - I will
definitely mention you on the opening page

Thanks again

Rivka

On Dec 18, 10:39 am, Michael Bayer  wrote:
> On Dec 18, 2011, at 1:11 PM, rivka wrote:
>
>
>
>
>
>
>
>
>
> > Hi,
>
> > I have a database with 1.2M rows at this point (in the main table -
> > and even more rows in the one to many associated tables). I am trying
> > to load the main table joined with a column from one of the associated
> > table:
>
> > query = db.session.query(Employee).\
> >                        options(joinedload(Employee.boss)).\
>
> > filter(and_(Employee.id>=FIRST_PATENT,Employee.id >                        order_by(Employee.id)
>
> > I have a MacBook Air. So far it has taken over 12 hours and it is
> > still not done.
> > Any idea  how long such a process can take and how to accelerate it?
>
> There is no way to say.   A single row here could itself be 200 bytes or 1M 
> in size.   The speed of the network is not known.  There are dozens of 
> factors which can affect such an operation.
>
> The 12 hour run time here suggests the program is in an exponentially-sized 
> loop of some kind, like a cartesian product.   It's likely just churning.
>
> You can't naively just run your program all day and hope it finishes.  You 
> need to look at the SQL being emitted, determine if that SQL is correct and 
> appropriate, check how much data you actually want to load, etc.

-- 
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: RowProxy to plist?

[Grashopa]

On Dec 18, 1:29 pm, Grashopa  wrote:
> I want to return a plist to my iPhone app - what is the best way to go
> from a RowProxy to a simple list of lists which I could then convert
> to a plist.

Here is the following code which works. Using the RowProxy didn't work
as an argument to writePlistToString. My longs are timestamps
(time.time()*1e6)

ll = []
for row in rows:
l = []
for col in row:
if isinstance(col,long):
dt = datetime.datetime.utcfromtimestamp(col/1e6)
l.append(dt)
else:
l.append(col)
ll.append(l)
ret = biplist.writePlistToString(ll)

-- 
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.

Re: [sqlalchemy] Error on insert, warning from mysqldb

[Lee Hinde]

Thanks, Michael. I found it the one place I hadn't looked 12 times. :-)

On Dec 18, 2011, at 10:51 AM, Michael Bayer wrote:

> 
> On Dec 18, 2011, at 1:25 PM, Lee Hinde wrote:
> 
>> I'm brand new to sqlalchemy,  and reasonably new to python. I'm working on 
>> migrating data from one MySQL database to another at Amazon's RDS. 
>> 
>> I have one TinyInt field,'Error' and if the value in that field is 1, then I 
>> get a warning from MySQLdb : 
>> 
>> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>>  Warning: Incorrect integer value: 'appuser_id' for column 'Error' at row 1
>> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>>  Warning: Incorrect integer value: '' for column 'Error' at row 1
>> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>>  Warning: Incorrect integer value: 'TEST-USER-WITH-FULL-PREFS-AND-FAVES' for 
>> column 'Error' at row 1
>> 
>> The incorrect value being reported is from the varchar User_ID field.  
>> Everything but the Error field gets migrated fine, including the User_ID 
>> field which this warning would suggest is in the wrong column. If Error == 
>> 0, no warning is issued.
> 
> This means you're assigning a string value to an integer field in a 
> statement.   When you do your thing with wrl(...fields...), make sure all the 
> fields match up to their types/names correctly.
> 
> you can also put a @validates to check for it:
> 
> class MyClass(Base):
>...
> 
>   @validates('Error')
>   def check_for_int(self, key, value):
>if not isinstance(value, int):
>raise TypeError("integer expected here")
>return value
> 
> see http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators
> 
> 
> 
> 
> 
> 
>> 
>> Any pointers appreciated.
>> 
>> The structures of the sending and receiving tables are identical. As 
>> reported by sqlalchemy's reflection tool:
>> 
>> UUID_PK VARCHAR(36)
>> Web_Request_Headers TEXT
>> Web_Request_Body TEXT
>> Current_Machine VARCHAR(40)
>> HTTP_StatusSent INTEGER(11)
>> ResponseBody MEDIUMTEXT
>> Full_Log_Message TEXT
>> Remote_Address VARCHAR(60)
>> basic_auth_username VARCHAR(30)
>> Request_Method VARCHAR(12)
>> Request_URI VARCHAR(60)
>> Request_Protocol VARCHAR(12)
>> Time_To_Process_Request INTEGER(11)
>> User_ID VARCHAR(36)
>> Error TINYINT(4)
>> Added_Timestamp VARCHAR(16)
>> Processing_Time_Milliseconds INTEGER(11)
>> mysql_timestamp TIMESTAMP
>> 
>> I have this model.py:
>> 
>> from sqlalchemy.ext.declarative import declarative_base
>> 
>> Base = declarative_base()
>> 
>> from sqlalchemy import Column, Integer, String, Text, DateTime, SmallInteger
>> 
>> class wrl(Base):
>>   __tablename__ = 'web_request_log'
>>   UUID_PK = Column(String(36),primary_key=True)
>>   Web_Request_Headers =  Column(Text)
>>   Web_Request_Body  =  Column(Text)
>>   Current_Machine = Column(String(40))
>>   HTTP_StatusSent  = Column(Integer)
>>   ResponseBody = Column(Text)
>>   Full_Log_Message = Column(Text)
>>   Remote_Address = Column(String(60))
>>   basic_auth_username  = Column(String(30))
>>   Request_Method  = Column(String(12))
>>   Request_URI  = Column(String(60))
>>   Request_Protocol  = Column(String(12))
>>   Time_To_Process_Request  = Column(Integer)
>>   User_ID  = Column(String(36))
>>   Error  = Column(SmallInteger)
>>   Added_Timestamp  = Column(String(16))
>>   Processing_Time_Milliseconds = Column(Integer)
>>   mysql_timestamp  = Column(DateTime)
>> 
>> 
>>   def 
>> __init__(self,UUID_PK,Web_Request_Headers,Web_Request_Body,Current_Machine,HTTP_StatusSent,ResponseBody,Full_Log_Message,
>>  Remote_Address,
>>   
>> basic_auth_username,Request_Method,Request_URI,Request_Protocol,Time_To_Process_Request,User_ID,Error,Added_Timestamp,Processing_Time_Milliseconds,
>>   mysql_timestamp):
>>self.UUID_PK = UUID_PK
>>self.Web_Request_Headers = Web_Request_Headers
>>self.Web_Request_Body = Web_Request_Body
>>self.Current_Machine = Current_Machine
>>self.HTTP_StatusSent = HTTP_StatusSent
>>self.ResponseBody = ResponseBody
>>self.Full_Log_Message = Full_Log_Message
>>self.Remote_Address = Remote_Address
>>self.basic_auth_username = basic_auth_username
>>self.Request_Method = Request_Method
>>self.Request_URI = Request_URI
>>self.Request_Protocol = Request_Protocol
>>self.Time_To_Process_Request = Time_To_Process_Request
>>self.User_ID = User_ID
>>self.Error = User_ID
>>self.Added_Timestamp = Added_Timestamp
>>self.Processing_Time_Milliseconds = Processing_Time_Milliseconds
>>self.mysql_timestamp = mysql_timestamp  
>> 
>> 
>> And I have this method:  
>> 
>> #login and query stuff, which is working fine, removed;
>> 
>> for x in move_me:
>>   wrl_rec = wrl(x.UUID_PK,
>>   

Re: [sqlalchemy] Copying Data base from disk to memory, working from memory and backing up to disk

[Michael Bayer]

On Dec 18, 2011, at 1:56 PM, rivka wrote:

> For the sake of speed - I would like to work with my database in
> memory (disk access is a killer). So - I would like to load my
> database from disk to memory - without going through a query.all() and
> then - while working from memory - occasionally commit to the disk (or
> - when executing a commit - somehow have it commit to both the memory
> an disk versions).

If you use a SQLite :memory: database, you'll get the effect of the database 
being loaded into memory, but still you will have the Python overhead of the 
ORM, which adds a lot of automation to the task and thus takes time.   Using 
SQLAlchemy Core will perform lots faster,. and then using pysqlite directly 
will run in the blink of an eye as it's 100% written in C

The attached script illustrates the difference between ORM, Core, and raw 
cursor, using CPython:

querying:
done ! it took 50 sec
querying with Core instead of ORM:
done ! it took 12 sec
Querying with a raw sqlite cursor:
done ! it took 1 sec

Here's with Pypy 1.7, results are slightly more egalitarian

querying:
done ! it took 23 sec
querying with Core instead of ORM:
done ! it took 7 sec
Querying with a raw sqlite cursor:
done ! it took 4 sec



> 
> Is it possible with using sqlite or do I need the big bucks to go get
> a copy of commercial grade db software?

You never need "commercial grade" anything these days, Postgresql is as 
industrial strength as anyone would need and it's free.  That said, SQLite is a 
very fast performer too, especially with the :memory: 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 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

from sqlalchemy import create_engine

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class MyObject(Base):
__tablename__ = "object"
id = Column(Integer, primary_key=True)

e = create_engine("sqlite:///file.db")

Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)
for i in xrange (1200):
# insert with ORM:
#s.add_all([
#MyObject(id=i * 1000 + j) for j in xrange(1000)
#])
#s.flush()

# insert with Core:
s.execute(MyObject.__table__.insert(), 
[{} for j in xrange(1000)]
)
print "inserted %d"  % (i * 1000)
s.commit()

import time
print "querying:"
now = time.time()
obj = s.query(MyObject).all()

print "done ! it took %d sec" % ( time.time() - now)
assert len(obj) == 120, len(obj)

print "querying with Core instead of ORM:"
now = time.time()

obj = [
(row.id,) for row in 
s.execute(MyObject.__table__.select())
]
print "done ! it took %d sec" % ( time.time() - now)
assert len(obj) == 120, len(obj)

print "Querying with a raw sqlite cursor:"
now = time.time()

conn = e.raw_connection()
cursor = conn.cursor()
cursor.execute("select * from object")
obj = [
(row[0], ) for row in cursor.fetchall()
]
print "done ! it took %d sec" % ( time.time() - now)
assert len(obj) == 120, len(obj)

[sqlalchemy] Copying Data base from disk to memory, working from memory and backing up to disk

[rivka]

For the sake of speed - I would like to work with my database in
memory (disk access is a killer). So - I would like to load my
database from disk to memory - without going through a query.all() and
then - while working from memory - occasionally commit to the disk (or
- when executing a commit - somehow have it commit to both the memory
an disk versions).

Is it possible with using sqlite or do I need the big bucks to go get
a copy of commercial grade db software?

Rivka

-- 
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.

Re: [sqlalchemy] Error on insert, warning from mysqldb

[Michael Bayer]

On Dec 18, 2011, at 1:25 PM, Lee Hinde wrote:

> I'm brand new to sqlalchemy,  and reasonably new to python. I'm working on 
> migrating data from one MySQL database to another at Amazon's RDS. 
> 
> I have one TinyInt field,'Error' and if the value in that field is 1, then I 
> get a warning from MySQLdb : 
> 
> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>  Warning: Incorrect integer value: 'appuser_id' for column 'Error' at row 1
> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>  Warning: Incorrect integer value: '' for column 'Error' at row 1
> /Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
>  Warning: Incorrect integer value: 'TEST-USER-WITH-FULL-PREFS-AND-FAVES' for 
> column 'Error' at row 1
> 
> The incorrect value being reported is from the varchar User_ID field.  
> Everything but the Error field gets migrated fine, including the User_ID 
> field which this warning would suggest is in the wrong column. If Error == 0, 
> no warning is issued.

This means you're assigning a string value to an integer field in a statement.  
 When you do your thing with wrl(...fields...), make sure all the fields match 
up to their types/names correctly.

you can also put a @validates to check for it:

class MyClass(Base):
...

   @validates('Error')
   def check_for_int(self, key, value):
if not isinstance(value, int):
raise TypeError("integer expected here")
return value

see http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators






> 
> Any pointers appreciated.
> 
> The structures of the sending and receiving tables are identical. As reported 
> by sqlalchemy's reflection tool:
> 
> UUID_PK VARCHAR(36)
> Web_Request_Headers TEXT
> Web_Request_Body TEXT
> Current_Machine VARCHAR(40)
> HTTP_StatusSent INTEGER(11)
> ResponseBody MEDIUMTEXT
> Full_Log_Message TEXT
> Remote_Address VARCHAR(60)
> basic_auth_username VARCHAR(30)
> Request_Method VARCHAR(12)
> Request_URI VARCHAR(60)
> Request_Protocol VARCHAR(12)
> Time_To_Process_Request INTEGER(11)
> User_ID VARCHAR(36)
> Error TINYINT(4)
> Added_Timestamp VARCHAR(16)
> Processing_Time_Milliseconds INTEGER(11)
> mysql_timestamp TIMESTAMP
> 
> I have this model.py:
> 
> from sqlalchemy.ext.declarative import declarative_base
> 
> Base = declarative_base()
> 
> from sqlalchemy import Column, Integer, String, Text, DateTime, SmallInteger
> 
> class wrl(Base):
>__tablename__ = 'web_request_log'
>UUID_PK = Column(String(36),primary_key=True)
>Web_Request_Headers =  Column(Text)
>Web_Request_Body  =  Column(Text)
>Current_Machine = Column(String(40))
>HTTP_StatusSent  = Column(Integer)
>ResponseBody = Column(Text)
>Full_Log_Message = Column(Text)
>Remote_Address = Column(String(60))
>basic_auth_username  = Column(String(30))
>Request_Method  = Column(String(12))
>Request_URI  = Column(String(60))
>Request_Protocol  = Column(String(12))
>Time_To_Process_Request  = Column(Integer)
>User_ID  = Column(String(36))
>Error  = Column(SmallInteger)
>Added_Timestamp  = Column(String(16))
>Processing_Time_Milliseconds = Column(Integer)
>mysql_timestamp  = Column(DateTime)
> 
> 
>def 
> __init__(self,UUID_PK,Web_Request_Headers,Web_Request_Body,Current_Machine,HTTP_StatusSent,ResponseBody,Full_Log_Message,
>  Remote_Address,
>
> basic_auth_username,Request_Method,Request_URI,Request_Protocol,Time_To_Process_Request,User_ID,Error,Added_Timestamp,Processing_Time_Milliseconds,
>mysql_timestamp):
> self.UUID_PK = UUID_PK
> self.Web_Request_Headers = Web_Request_Headers
> self.Web_Request_Body = Web_Request_Body
> self.Current_Machine = Current_Machine
> self.HTTP_StatusSent = HTTP_StatusSent
> self.ResponseBody = ResponseBody
> self.Full_Log_Message = Full_Log_Message
> self.Remote_Address = Remote_Address
> self.basic_auth_username = basic_auth_username
> self.Request_Method = Request_Method
> self.Request_URI = Request_URI
> self.Request_Protocol = Request_Protocol
> self.Time_To_Process_Request = Time_To_Process_Request
> self.User_ID = User_ID
> self.Error = User_ID
> self.Added_Timestamp = Added_Timestamp
> self.Processing_Time_Milliseconds = Processing_Time_Milliseconds
> self.mysql_timestamp = mysql_timestamp  
> 
> 
> And I have this method:  
> 
> #login and query stuff, which is working fine, removed;
> 
> for x in move_me:
>wrl_rec = wrl(x.UUID_PK,
>x.Web_Request_Headers,
>x.Web_Request_Body,
>x.Current_Machine,
>x.HTTP_StatusSent,
>x.ResponseBody,
>x.Full_Log_Message,
>x.Re

[sqlalchemy] Error on insert, warning from mysqldb

[Lee Hinde]

I'm brand new to sqlalchemy,  and reasonably new to python. I'm working on 
migrating data from one MySQL database to another at Amazon's RDS. 

I have one TinyInt field,'Error' and if the value in that field is 1, then I 
get a warning from MySQLdb : 

/Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
 Warning: Incorrect integer value: 'appuser_id' for column 'Error' at row 1
/Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
 Warning: Incorrect integer value: '' for column 'Error' at row 1
/Library/Python/2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg/MySQLdb/cursors.py:206:
 Warning: Incorrect integer value: 'TEST-USER-WITH-FULL-PREFS-AND-FAVES' for 
column 'Error' at row 1

The incorrect value being reported is from the varchar User_ID field.  
Everything but the Error field gets migrated fine, including the User_ID field 
which this warning would suggest is in the wrong column. If Error == 0, no 
warning is issued.

Any pointers appreciated.

The structures of the sending and receiving tables are identical. As reported 
by sqlalchemy's reflection tool:

UUID_PK VARCHAR(36)
Web_Request_Headers TEXT
Web_Request_Body TEXT
Current_Machine VARCHAR(40)
HTTP_StatusSent INTEGER(11)
ResponseBody MEDIUMTEXT
Full_Log_Message TEXT
Remote_Address VARCHAR(60)
basic_auth_username VARCHAR(30)
Request_Method VARCHAR(12)
Request_URI VARCHAR(60)
Request_Protocol VARCHAR(12)
Time_To_Process_Request INTEGER(11)
User_ID VARCHAR(36)
Error TINYINT(4)
Added_Timestamp VARCHAR(16)
Processing_Time_Milliseconds INTEGER(11)
mysql_timestamp TIMESTAMP

I have this model.py:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String, Text, DateTime, SmallInteger

class wrl(Base):
__tablename__ = 'web_request_log'
UUID_PK = Column(String(36),primary_key=True)
Web_Request_Headers =  Column(Text)
Web_Request_Body  =  Column(Text)
Current_Machine = Column(String(40))
HTTP_StatusSent  = Column(Integer)
ResponseBody = Column(Text)
Full_Log_Message = Column(Text)
Remote_Address = Column(String(60))
basic_auth_username  = Column(String(30))
Request_Method  = Column(String(12))
Request_URI  = Column(String(60))
Request_Protocol  = Column(String(12))
Time_To_Process_Request  = Column(Integer)
User_ID  = Column(String(36))
Error  = Column(SmallInteger)
Added_Timestamp  = Column(String(16))
Processing_Time_Milliseconds = Column(Integer)
mysql_timestamp  = Column(DateTime)


def 
__init__(self,UUID_PK,Web_Request_Headers,Web_Request_Body,Current_Machine,HTTP_StatusSent,ResponseBody,Full_Log_Message,
 Remote_Address,

basic_auth_username,Request_Method,Request_URI,Request_Protocol,Time_To_Process_Request,User_ID,Error,Added_Timestamp,Processing_Time_Milliseconds,
mysql_timestamp):
 self.UUID_PK = UUID_PK
 self.Web_Request_Headers = Web_Request_Headers
 self.Web_Request_Body = Web_Request_Body
 self.Current_Machine = Current_Machine
 self.HTTP_StatusSent = HTTP_StatusSent
 self.ResponseBody = ResponseBody
 self.Full_Log_Message = Full_Log_Message
 self.Remote_Address = Remote_Address
 self.basic_auth_username = basic_auth_username
 self.Request_Method = Request_Method
 self.Request_URI = Request_URI
 self.Request_Protocol = Request_Protocol
 self.Time_To_Process_Request = Time_To_Process_Request
 self.User_ID = User_ID
 self.Error = User_ID
 self.Added_Timestamp = Added_Timestamp
 self.Processing_Time_Milliseconds = Processing_Time_Milliseconds
 self.mysql_timestamp = mysql_timestamp  


And I have this method:  

#login and query stuff, which is working fine, removed;

for x in move_me:
wrl_rec = wrl(x.UUID_PK,
x.Web_Request_Headers,
x.Web_Request_Body,
x.Current_Machine,
x.HTTP_StatusSent,
x.ResponseBody,
x.Full_Log_Message,
x.Remote_Address,
x.basic_auth_username,
x.Request_Method,
x.Request_URI,
x.Request_Protocol,
x.Time_To_Process_Request,
x.User_ID,
x.Error,
x.Added_Timestamp,
x.Processing_Time_Milliseconds,
x.mysql_timestamp)
try:
aws.add(wrl_rec)
print 'added %s' %  x.UUID_PK
except Exception, E:
print 'error %s' %  x.UUID_PK
print E
else:
rs.delete(x)
try:
aws.commit()  # if we can commit the aws push, then it's ok to commit the 
delete on RS
except Exception, E:
print 'error %s' %  E
else:
rs.commit()





-- 
You received this messa

[sqlalchemy] RowProxy to plist?

[Grashopa]

I want to return a plist to my iPhone app - what is the best way to go
from a RowProxy to a simple list of lists which I could then convert
to a plist.

-- 
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.

Re: [sqlalchemy] How long should it take to load a data base into memory?

[Michael Bayer]

On Dec 18, 2011, at 1:11 PM, rivka wrote:

> Hi,
> 
> I have a database with 1.2M rows at this point (in the main table -
> and even more rows in the one to many associated tables). I am trying
> to load the main table joined with a column from one of the associated
> table:
> 
> query = db.session.query(Employee).\
>options(joinedload(Employee.boss)).\
> 
> filter(and_(Employee.id>=FIRST_PATENT,Employee.idorder_by(Employee.id)
> 
> I have a MacBook Air. So far it has taken over 12 hours and it is
> still not done.
> Any idea  how long such a process can take and how to accelerate it?

There is no way to say.   A single row here could itself be 200 bytes or 1M in 
size.   The speed of the network is not known.  There are dozens of factors 
which can affect such an operation.

The 12 hour run time here suggests the program is in an exponentially-sized 
loop of some kind, like a cartesian product.   It's likely just churning.

You can't naively just run your program all day and hope it finishes.  You need 
to look at the SQL being emitted, determine if that SQL is correct and 
appropriate, check how much data you actually want to load, etc.   


-- 
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] How long should it take to load a data base into memory?

[rivka]

Hi,

I have a database with 1.2M rows at this point (in the main table -
and even more rows in the one to many associated tables). I am trying
to load the main table joined with a column from one of the associated
table:

query = db.session.query(Employee).\
options(joinedload(Employee.boss)).\
 
filter(and_(Employee.id>=FIRST_PATENT,Employee.idhttp://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] How to work with python3.2 and mysql?

[Michael Bayer]

On Dec 18, 2011, at 6:29 AM, Florent Angebault wrote:

> Hello.
> 
> I tried many different configurations to connect to mysql5 server using 
> python3.2 and sqlalchemy 0.7.4 but couldn't have success.
> I always end with an error due to the fact that some string data is of type 
> 'bytes' instead of 'str'.
> 
> Below are 2 test cases that both fail.
> The first one uses PyMySQL3-0.5 and the second one uses 
> mysql-connector-0.3.2-devel.
> I don't know if I made mistakes with my configuration, or if it's a bug I 
> should report.
> 
> If I made a mistake, I'd be glad if someone could give me a link to the 
> appropriate documentation.
> If it's a bug, I don't know where I should report it: sqlalchemy, 
> mysqlconnector or pymysql project?


OK well take a look at supported DBs:

http://www.sqlalchemy.org/docs/core/engines.html#supported-databases

The only drivers supported for MySQL + Py3K are OurSQL and 
MySQL-connector/python.  In particular OurSQL runs pretty well and is on our 
continuous integration environment, with not quite all but most tests passing.

As far as pymysql, that it says "development" there means we just haven't 
gotten to test this driver with SQLAlchemy yet.   There's typically some 
configuration flags that need to be made in the dialect to give the driver what 
it expects.   It looks like there's some "encode" flags on that driver that 
just need to be flipped off when Py3K is in use.

I tried getting some of this to work but there seem to be other 
incompatibilities with PyMysql, namely it's raising exceptions in a way that's 
incompatible when Python 3 is run, breaking our ability to extract the error 
code correctly as "exception.args[0]" (I've added ticket: 
https://github.com/petehunt/PyMySQL/issues/94 for that).  Also the fact that 
it's published as two separate packages on Pypi (there should be just one 
package that uses 2to3 for Py3K), both of which appear to have problems with 
the Python "tarfile" module, isn't inspiring too much confidence at the moment. 
  So I'd stick with one of the other packages for now.




> 
> Here are two of my many attempts.
> (they are also visible here: http://bpaste.net/show/21027/)
> >>> from sqlalchemy import create_engine
> >>> engine = create_engine('sqlite:///:memory:')
> >>> engine.execute("select 'cassé !'").fetchall()
> [('cassé !',)]
> >>> engine = create_engine('mysql+pymysql://guest@192.168.222.1/test')
> >>> engine.execute("select 'cassé !'").fetchall()
> Traceback (most recent call last):
>   File "", line 1, in 
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
>  line 2298, in execute
> connection = self.contextual_connect(close_with_result=True)
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
>  line 2342, in contextual_connect
> self.pool.connect(),
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
>  line 210, in connect
> return _ConnectionFairy(self).checkout()
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
>  line 371, in __init__
> rec = self._connection_record = pool._do_get()
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
>  line 697, in _do_get
> con = self._create_connection()
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
>  line 174, in _create_connection
> return _ConnectionRecord(self)
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
>  line 259, in __init__
> pool.dispatch.first_connect.exec_once(self.connection, self)
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/event.py",
>  line 262, in exec_once
> self(*args, **kw)
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/event.py",
>  line 271, in __call__
> fn(*args, **kw)
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/strategies.py",
>  line 167, in first_connect
> dialect.initialize(c)
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/dialects/mysql/base.py",
>  line 1889, in initialize
> default.DefaultDialect.initialize(self, connection)
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/default.py",
>  line 176, in initialize
> self._get_default_schema_name(connection)
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/dialects/mysql/base.py",
>  line 1854, in _get_default_schema_name
> return connection.execute('SELECT DATABASE()').scalar()
>   File 
> "/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
>  line 1405, in execute
> p

[sqlalchemy] How to work with python3.2 and mysql?

[Florent Angebault]

Hello.

I tried many different configurations to connect to mysql5 server using 
python3.2 and sqlalchemy 0.7.4 but couldn't have success.
I always end with an error due to the fact that some string data is of type 
'bytes' instead of 'str'.

Below are 2 test cases that both fail.
The first one uses PyMySQL3-0.5 and the second one uses 
mysql-connector-0.3.2-devel.
I don't know if I made mistakes with my configuration, or if it's a bug I 
should report.

If I made a mistake, I'd be glad if someone could give me a link to the 
appropriate documentation.
If it's a bug, I don't know where I should report it: sqlalchemy, 
mysqlconnector or pymysql project?

Here are two of my many attempts.
(they are also visible here: http://bpaste.net/show/21027/)

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:')
>>> engine.execute("select 'cassé !'").fetchall()
[('cassé !',)]
>>> engine = create_engine('mysql+pymysql://guest@192.168.222.1/test')
>>> engine.execute("select 'cassé !'").fetchall()
Traceback (most recent call last):
  File "", line 1, in 
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
 line 2298, in execute
connection = self.contextual_connect(close_with_result=True)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
 line 2342, in contextual_connect
self.pool.connect(),
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
 line 210, in connect
return _ConnectionFairy(self).checkout()
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
 line 371, in __init__
rec = self._connection_record = pool._do_get()
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
 line 697, in _do_get
con = self._create_connection()
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
 line 174, in _create_connection
return _ConnectionRecord(self)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
 line 259, in __init__
pool.dispatch.first_connect.exec_once(self.connection, self)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/event.py",
 line 262, in exec_once
self(*args, **kw)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/event.py",
 line 271, in __call__
fn(*args, **kw)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/strategies.py",
 line 167, in first_connect
dialect.initialize(c)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/dialects/mysql/base.py",
 line 1889, in initialize
default.DefaultDialect.initialize(self, connection)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/default.py",
 line 176, in initialize
self._get_default_schema_name(connection)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/dialects/mysql/base.py",
 line 1854, in _get_default_schema_name
return connection.execute('SELECT DATABASE()').scalar()
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
 line 1405, in execute
params)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
 line 1582, in _execute_text
statement, parameters
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
 line 1639, in _execute_context
context)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/default.py",
 line 332, in do_execute
cursor.execute(statement, parameters)
  File 
"/usr/local/lib/python3.2/dist-packages/PyMySQL3-0.5-py3.2.egg/pymysql/cursors.py",
 line 105, in execute
query = query % escaped_args
TypeError: unsupported operand type(s) for %: 'bytes' and 'tuple'
>>> engine = create_engine('mysql+mysqlconnector://guest@192.168.222.1/test')
>>> engine.execute("select 'cassé !'").fetchall()
Traceback (most recent call last):
  File "", line 1, in 
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
 line 2298, in execute
connection = self.contextual_connect(close_with_result=True)
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/engine/base.py",
 line 2342, in contextual_connect
self.pool.connect(),
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
 line 210, in connect
return _ConnectionFairy(self).checkout()
  File 
"/usr/local/lib/python3.2/dist-packages/SQLAlchemy-0.7.4-py3.2.egg/sqlalchemy/pool.py",
 line 371, in __init__
rec = self._connection_record = pool._

Re: [sqlalchemy] Re: Strange behavior from Association object in many to many relationships

[Michael Bayer]

The core of the issue is that this:

ub.book = book

places UserBook into the Session via cascades.  Usually this is convenient.   
However, it can be tailored to behave more accurately.

WIthout any changes, for this use case I usually just construct association 
objects with all their state at once:

UserBook(book=mybook, user=myuser)

Another method is to attach "book" after user.reads has been accessed:

user.reads.append(ub)
ub.book = book

In some cases, when I have a lot going on that i need autoflush to not get in 
the way, I'll turn it off temporarily:

session.autoflush = False

session.autoflush = True

some recipes for that are at 
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush

Then finally, you can make "ub.book = book" not actually place UserBook in the 
session by setting cascade_backrefs=False, docs for that at 
http://www.sqlalchemy.org/docs/orm/session.html#controlling-cascade-on-backrefs 


On Dec 18, 2011, at 7:05 AM, Manav Goel wrote:

> Thanks for the answer.
> Yes this comes in trace
> File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/
> site-packages/sqlalchemy/orm/query.py", line 2031, in __iter__
>self.session._autoflush()
> 
> What is the workaround this?
> 
> On Dec 17, 10:32 pm, Michael Bayer  wrote:
>> On Dec 17, 2011, at 6:26 AM,ManavGoelwrote:
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>>> Hi
>>>   I created a many to many relation between two tables as shown
>>> in the tutorial using Association object as I have extra fields in
>>> connecting table.
>>> I have table User(user_id), Book(book_id) and UserBook(user_id,
>>> book_id)
>> 
>>> This code gave me Integrity error of UserBook.user_id of being null.
>>> Basically it is not user id of user automatically to UserBook object :
>> 
>>> def con(user, mybook):
>>> ub = UserBook()
>>> ub.book = mybook
>>> user.reads.append(ub)
>>> session.commit()
>> 
>>> But this code worked :
>>> def con(user, mybook):
>>> ub = UserBook()
>>> ub.book = mybook
>>> ub.user = user
>>> user.reads.append(ub)
>>> session.commit()
>> 
>>> Why the first one is not working?
>> 
>> It's not certain without a full example + stack trace, however often this 
>> occurs because of autoflush (perhaps when user.reads hits the DB to load 
>> itself first).  The stack trace would illustrate if the integrity error is 
>> due to a premature autoflush (you'd see _autoflush() in the trace), and 
>> there are ways to get around that issue.
> 
> -- 
> 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.
> 

-- 
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: Strange behavior from Association object in many to many relationships

[Manav Goel]

Thanks for the answer.
Yes this comes in trace
File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/
site-packages/sqlalchemy/orm/query.py", line 2031, in __iter__
self.session._autoflush()

What is the workaround this?

On Dec 17, 10:32 pm, Michael Bayer  wrote:
> On Dec 17, 2011, at 6:26 AM,ManavGoelwrote:
>
>
>
>
>
>
>
>
>
> > Hi
> >       I created a many to many relation between two tables as shown
> > in the tutorial using Association object as I have extra fields in
> > connecting table.
> > I have table User(user_id), Book(book_id) and UserBook(user_id,
> > book_id)
>
> > This code gave me Integrity error of UserBook.user_id of being null.
> > Basically it is not user id of user automatically to UserBook object :
>
> > def con(user, mybook):
> >     ub = UserBook()
> >     ub.book = mybook
> >     user.reads.append(ub)
> >     session.commit()
>
> > But this code worked :
> > def con(user, mybook):
> >     ub = UserBook()
> >     ub.book = mybook
> >     ub.user = user
> >     user.reads.append(ub)
> >     session.commit()
>
> > Why the first one is not working?
>
> It's not certain without a full example + stack trace, however often this 
> occurs because of autoflush (perhaps when user.reads hits the DB to load 
> itself first).  The stack trace would illustrate if the integrity error is 
> due to a premature autoflush (you'd see _autoflush() in the trace), and there 
> are ways to get around that issue.

-- 
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: len() of declarative backref

[Jad Kik]

Thank you Micheal !
It is the ~any() I was searching for. I should have found it in the
docs myself, but they are so long I think I'll need a month to read
them all :).
The EXISTS is new to me. I said I'm a newbie, so ... :D

For the len thing, I thought it would be more intuitive to do it like
that, but the any() method is just as "clean" and intuitive.

Thanks a lot.
And I really like SQLAlchemy, it is just great. :)

On Dec 18, 5:43 am, Michael Bayer  wrote:
> On Dec 17, 2011, at 6:56 PM, Jad Kik wrote:
>
>
>
>
>
>
>
>
>
> > Hi,
> > My problem is with using a many-to-many relationship inside a query.
>
> > I wanted to get all the Customers who are associated to a
> > CustomerGroup, I did:
> > session.query(Customer).filter(Customer.groups.contains(group))
>
> > I wanted to get all the Customers who do not belong to any
> > CustomerGroup. I tried that:
> > session.query(Customer).filter(len(Customer.groups) == 0)
> > which threw an exception "TypeError: object of type
> > 'InstrumentedAttribute' has no len()".
> > I understand what that means, but it would be a good thing to have it
> > available, something similar to Customer.groups.contains().
>
> > I can't seem to do such a thing neither:
> > session.query(Customer).filter([some subquery].count() == 0)
> > Then what would [some subquery] be?
>
> > I did manage to do it with normal SQL (on MySQL 5.1):
> > SELECT c2.id FROM customers c2 WHERE (SELECT COUNT(cg.id) FROM
> > customers c, customergroups cg, customer_group ccg WHERE
> > ccg.customer_id=c.id AND ccg.group_id=cg.id AND c.id=c2.id)=0
> > It worked as I expected.
>
> The usual form of this query is relational terms (the set of all customers) 
>  (the set of all customer ids in customer_group with N number 
> of group id).   The second half of it uses GROUP BY to group by the customer 
> id and HAVING to limit the rows, and you'd use join() to join the two 
> together as in the form 
> athttp://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries.
>
> But here you're looking for the count being zero.   So a subquery against 
> customer_group won't have any rows you'd care about.   To query for an 
> exclusion, use WHERE NOT EXISTS.   In the ORM this is the expression 
> ~Customer.groups.any(), just like the third example 
> athttp://www.sqlalchemy.org/docs/orm/internals.html#sqlalchemy.orm.prop   
> The join across the association table is handled here within the subquery.
>
> The correlated subquery approach you have is more amenable to being "packaged 
> up" as a simple WHERE criterion on the Python side, but you'll note the 
> format of the query is less well suited to the typical optimizer.  MySQL's 
> optimizer in particular is awful, so this is an example where 
> len(SomeRelationship) might look tidy on the Python side but is actually not 
> taking the reality of the relational database into account to an appropriate 
> degree.    You could achieve that exact form by using the style 
> athttp://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueriesin 
> conjunction with aliased(Customer) to produce "c2".
>
> I don't know if any of these ways is really "cleaner", except for the ~any() 
> that is only appropriate for the "no groups" comparison.

-- 
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.