Re: [sqlalchemy] Issue DELETE statement with LIMIT

[Warwick Prince]

Hi.  Can you do a sub query with the ones you want to delete selected (with limit) and then delete with an IN clause on the sub query items?Warwick A. PrinceMushroom Systems International Pty. Ltd.On 22 Sep 2023, at 9:16 pm, 'Grennith' via sqlalchemy  wrote:Hi everyone,I'd like to issue a LIMIT for a DELETE statement.By default, this is not possible as far as I can see it. The function limit() is available for SELECT in general however. Searching through documentation, I found a reference to with_dialect_option():https://docs.sqlalchemy.org/en/20/search.html?q=with_dialect_options_keywords=yes=default#Which points to https://docs.sqlalchemy.org/en/20/core/dml.html#sqlalchemy.sql._expression_.UpdateBase.with_dialect_options claiming the function to be available in UpdateBase (although the documentation off given the claimed method is not callable like documented). This was the case in 1.4 (https://github.com/sqlalchemy/sqlalchemy/blob/rel_1_4/lib/sqlalchemy/sql/dml.py#L345) already and also in 2.0 (https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/dml.py#L438).However, trying to call it as per documentation results in an exception being raised:```sqlalchemy.exc.ArgumentError: Argument 'mysql_limit' is not accepted by dialect 'mysql' on behalf of ```This is caused by https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/dialects/mysql/base.py#L2454 not listing sql.Delete explicitly. UpdateBase apparently cannot be referenced either given the import (guessing as it's not imported explicitly in https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/__init__.py or not referenced another way?).However, by adding Delete just like Update will have the following line run fine without an error - but not adding the LIMIT either. My best guess right now would be due to the lack of limit clause handling?```stmt = stmt.with_dialect_options(mysql_limit=limit, mariadb_limit=limit)```where `limit` simply is an integer.Any hints or help is appreciated. I can also raise a ticket on Github :)Best regards



-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f13a8ca6-3e52-4287-a6a4-52b5b4672470n%40googlegroups.com.




-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/978052A5-E996-4839-8DA2-50009A6384AE%40mushroomsys.com.

Re: [sqlalchemy] Using SQLAlchemy Core as Query Builder, not ORM, in CRUD applications

[Warwick Prince]

Hi Matthew

It’s not an unusual approach from my standpoint.  I use a combination of both 
ORM and Core.   For my core uses, I wrote a couple of generic getter and setter 
methods, wrapping up a lot of boilerplate operations.   In my case, it looks 
like this;

result = DBGetColumns(‘tableName’, [‘studentFirstname’, ‘studentSurname’], 
id=studentID)

i.e. DBGetColumns(‘tableName’, [list of columns to return], **column:value to 
use in the where clause AND'ed together)
 
It returns result dict of the column values, or None if no row is found.  For 
my purposes, this can only be used where you expect a fetchone() result.  A 
result array can not be returned at this time and I’ve never really needed it.

In this case, the method takes the kwargs (id=studentID) and builds a where 
clause from it, based on the query it creates on the table.  My environment has 
a lot of ‘built in’ context, so for example, my method can work out from it’s 
operating context the metadata to use to open the table.   Without this 
context, it would look more like this;

result = DBGetColumns(meta, ‘tableName’, [‘studentFirstname’, 
‘studentSurname’], id=studentID)

or maybe 

result = DBGetColumns(tableObject, [‘studentFirstname’, ‘studentSurname’], 
id=studentID)   where tableObject is a Table() object.

Of course, this is for basic selects.  The where clause is always built as 
‘AND’s.  There’s no logic for an OR etc, and no joins.   This could be done of 
course, but after 10 years I’ve never needed it.

Finally, it can also be used to return the actual value of a single column, 
rather than a dict of multi columns.  This is done by supplying the column name 
as a string, rather than a list of strings.   That way, you can build robust 
code with inline queries very easily.

e.g. 
if DBGetColumns(‘tableName’, ‘studentFirstname’, id=studentID) == ‘Fred’:
print ‘Hi Fred'

For update/insert, I have a similar method;

Update existing row based on a match of the keys supplied as kwargs.  In this 
version, if the id (kwargs) have no match, then it will raise an exception.  
The expectation is that you believe that studentID already exists and up want 
to do an update.

DBSetColumns(‘tableName’, {‘studentCategory’: 1, ‘studentOtherThing’: ‘baa’}, 
id=studentID)

Insert a new row specifically.  i.e. I assume this does not exist.  Logic 
dictates that all the mandatory columns and PK columns are included in the 
column dict.  Note there are no kwargs.

newStudentID = DBSetColumns(‘tableName’, {‘studentCategory’: 1, 
‘studentOtherThing’: ‘baa’})

If I included id=studentID in the kwargs, it would attempt an update, and if it 
fails it performs an insert instead.   You can tell be the result what it did.  
newID for an insert, or None if an update.

Finally, returning a complete row

row = DBGetRow(‘tableName’, id=studentID)

Simply returns an SQLA resultRow object based on building a query on the table, 
applying the kwargs as a where clause and returning 1 row, or None.   If there 
is more than one match, then the first one is returned.  The assumption is that 
the dev uses this with an understanding of the data.

I also have a function value return’er

value = DBGetFuncValue(‘tableName’, function, onColumn, **kwargs)

This is useful for quickly and easily getting the MAX, MIN, AVG etc of a column 
based on a select query.

e.g.  (Silly example, but you can see what I mean)

if DBGetFuncValue(‘students’, ‘MAX’, ‘lastTestResult’, studentCategoryID=23) < 
passingGrade:
print “This bunch of students all failed!"

I use these literally all day every day and they have proven to be a real 
asset.  Recently, I wanted to support a way of raising notifications (in my 
case adding things into a Queue.Queue() when operations happened on some 
tables.  I simply updated the central method and it worked, even though there 
were 1000’s of cases where it was used. :-)

With all of the above, there are some more subtle use cases that I have not 
really covered.  e.g. If I pass in a tuple if (‘tableName’, sessionObject) then 
the code will do the insert/update inside a transaction for that session, 
rather than a direct update etc.

Hope you gain some insight and inspiration.   Happy to chat 1:1 if you would 
like more info.

Cheers
Warwick

> On 23 Apr 2021, at 11:51 AM, mkmo...@gmail.com  wrote:
> 
> Hi dAll,
> Some people don't like ORMs and instead use query builders like SQLAlchemy 
> Core (or even raw SQL text) even for something like a CRUD application.
> 
> For sake of argument, let's put aside whether or not this is a good idea. I'm 
> just curious how one would go about this without copy and pasting a large 
> amount of functions for every table in your data model.
> 
> For example if you had two tables, School and Student, you would probably 
> have functions like insert_school, insert_student, update_school, 
> update_student, get_school, get_student, delete_school, delete_student, and 
> etc. where the majority of the 

Re: [sqlalchemy] MySQL Connector

[Warwick Prince]

Hi Mike

Thanks very much.  It’s a shame they broke it so badly!  I’ll change to your 
recommendation immediately.

Cheers
Warwick 

Warwick A. Prince
Mushroom Systems International Pty. Ltd.

> On 5 Mar 2019, at 3:07 am, Mike Bayer  wrote:
> 
>> On Sun, Mar 3, 2019 at 8:40 PM Warwick Prince  
>> wrote:
>> 
>> Hi Community
>> 
>> We’ve been using sqlalchemy with great success for many years.  We’ve been 
>> using the mysqlconnector to connect to my MySQL databases with no issues to 
>> speak of.
>> 
>> My issue is that I recently upgraded to the latest version of the connector 
>> from Oracle which has changed from Python to C.  I’m now regularly receiving 
>> the following error where this has never happened in the past;   MySQL 5.6 
>> BTW.
>> 
>> DatabaseError: (mysql.connector.errors.HashError) Hashed authentication data 
>> is invalid
>> 
>> Obviously, I would assume that this is not a sqlalchemy issue, however 
>> there’s nothing really being spoken about this anywhere else and I wondered 
>> if anyone had had the problem and fixed it - or eventually rolled back to 
>> the very old version of mysqlconnector?
> 
> Unfortunately recent versions of MySQL connector, specifically when
> the C code was added, work very poorly, such that I had to remove
> mysql connector from testing.   I documented a partial list of
> blocking issues I encountered here:
> https://docs.sqlalchemy.org/en/latest/dialects/mysql.html#current-issues
> 
> 
>> 
>> Alternatively, @Mike - could you recommend in your opinion the ‘best’ 
>> connector to use for MySQL based on reliability and performance.
> 
> The best drivers for MySQL are mysqlclient and pymysql, both
> maintained by the same person.  Both drivers work very well and are
> well supported.   mysqlclient is a fork of the original C based
> python-mysql driver which is no longer maintained.
> 
> 
>> 
>> Cheers
>> Warwick
>> 
>> Warwick Prince
>> Managing Director
>> mobile: +61 411 026 992
>> skype: warwickprince
>> phone: +61 7 3102 3730
>> fax:  +61 7 3319 6734
>> web: www.mushroomsys.com
>> 
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>> 
>> http://www.sqlalchemy.org/
>> 
>> To post example code, please provide an MCVE: Minimal, Complete, and 
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
> 
> -- 
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
> 
> http://www.sqlalchemy.org/
> 
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] MySQL Connector

[Warwick Prince]

Hi Community

We’ve been using sqlalchemy with great success for many years.  We’ve been 
using the mysqlconnector to connect to my MySQL databases with no issues to 
speak of.

My issue is that I recently upgraded to the latest version of the connector 
from Oracle which has changed from Python to C.  I’m now regularly receiving 
the following error where this has never happened in the past;   MySQL 5.6 BTW.

DatabaseError: (mysql.connector.errors.HashError) Hashed authentication data is 
invalid

Obviously, I would assume that this is not a sqlalchemy issue, however there’s 
nothing really being spoken about this anywhere else and I wondered if anyone 
had had the problem and fixed it - or eventually rolled back to the very old 
version of mysqlconnector?

Alternatively, @Mike - could you recommend in your opinion the ‘best’ connector 
to use for MySQL based on reliability and performance.

Cheers
Warwick

Warwick Prince 
Managing Director 
mobile: +61 411 026 992
skype: warwickprince   
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com <http://www.mushroomsys.com/>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Session / declarative_base scope

[Warwick Prince]

Hi Mike

I don’t need support very often, but when I do I know I can count on your clear 
and concise responses to save the day.

Everything was SO close - all I needed to do was take my dynamically created 
classes (SalesDocumentLine for example) and force them into the general 
globals() and hey-presto - all fixed.  ‘Knowing’ that the environment had a 
global scope, and that it was thread safe eliminated so many other possible red 
herrings.  I could then focus on why my classes could not be ‘seen’.

Thanks once again.  Best support on the interwebs. ;-)

> On 29 Sep 2016, at 11:48 PM, Mike Bayer <mike...@zzzcomputing.com> wrote:
> 
> 
> 
> On 09/29/2016 01:38 AM, Warwick Prince wrote:
>> Hi Mike
>> 
>> I would like a little insight into the session object, and the
>> declarative_base class.
>> 
>> I have a process running many threads, where each thread may be
>> connected to potentially a different engine/database.  If the database
>> connection between 2 or more threads is the same, then they will share
>> the same engine.  However, they each have their own MetaData objects.
>> 
>> There is a global sessionmaker() that has no binding at that time.
>> When each thread creates its OWN session, then it processes mySession =
>> Session(bind=myThreadsEngine).
>> 
>> The Engines and MetaData part has worked perfectly for years, using
>> basic queries like Table(’some_table', threadMetaData,
>> autoload=True).select().execute().fetchall(). etc.
>> 
>> I’ve started to use the ORM more now, and am using the relationships
>> between the objects.  However, I’m hitting and issue that appears to
>> centre around some shared registry or class variables or something that
>> is causing a conflict.
>> 
>> I’ve made it so each THREAD has is own Base =
>> declarative_base(metadata=theSessionsMetaData)
>> 
>> Then, classes are mapped dynamically based on this new Base, and the
>> columns are autoload’ed.  Again, this is working - sometimes.   There’s
>> some random-like problem that mostly means it does not work when I do a
>> mySession.query(myMappedClassWithRelationships) and I get the following
>> exception being raised;
> 
> so generating new classes in threads can be problematic because the registry 
> of mappers is essentially global state.   Initialization of mappers against 
> each other, which is where your error here is, is mutexed and is overall 
> thread-safe, but still, you need to make sure that all the things that your 
> class needs to be used exist.  Here, somewhere in your program you have a 
> class called SalesDocumentLine, and that class has not been seen by your 
> Python interpreter yet.   That the problem only happens randomly in threads 
> implies some kind of race condition which will make this harder to diagnose, 
> but basically that name has to exist, if your mapping refers to it.   You 
> might want to play with the configure_mappers() call that will cause this 
> initialization to occur at the point you tell it.
> 
> 
> 
> 
>> 
>>  File
>> "C:\Python27\lib\site-packages\dap-2.1.2-py2.7.egg\dap\db\dbutils.py",
>> line 323, in DAPDB_SetColumns
>>query = session.query(mappedClass).filter_by(**whereCriteria)
>>  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 1260, in
>> query
>>return self._query_cls(entities, self, **kwargs)
>>  File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 110, in
>> __init__
>>self._set_entities(entities)
>>  File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 120, in
>> _set_entities
>>self._set_entity_selectables(self._entities)
>>  File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 150, in
>> _set_entity_selectables
>>ent.setup_entity(*d[entity])
>>  File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 3446, in
>> setup_entity
>>self._with_polymorphic = ext_info.with_polymorphic_mappers
>>  File "build\bdist.win32\egg\sqlalchemy\util\langhelpers.py", line 754,
>> in __get__
>>obj.__dict__[self.__name__] = result = self.fget(obj)
>>  File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 1891, in
>> _with_polymorphic_mappers
>>configure_mappers()
>>  File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 2768, in
>> configure_mappers
>>mapper._post_configure_properties()
>>  File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 1708, in
>> _post_configure_properties
>>prop.init()
>>  File "build\bdist.win32\egg\sqlalche

[sqlalchemy] Session / declarative_base scope

[Warwick Prince]

Hi Mike

I would like a little insight into the session object, and the declarative_base 
class.

I have a process running many threads, where each thread may be connected to 
potentially a different engine/database.  If the database connection between 2 
or more threads is the same, then they will share the same engine.  However, 
they each have their own MetaData objects.   

There is a global sessionmaker() that has no binding at that time.   When each 
thread creates its OWN session, then it processes mySession = 
Session(bind=myThreadsEngine).

The Engines and MetaData part has worked perfectly for years, using basic 
queries like Table(’some_table', threadMetaData, 
autoload=True).select().execute().fetchall(). etc.

I’ve started to use the ORM more now, and am using the relationships between 
the objects.  However, I’m hitting and issue that appears to centre around some 
shared registry or class variables or something that is causing a conflict.

I’ve made it so each THREAD has is own Base = 
declarative_base(metadata=theSessionsMetaData)

Then, classes are mapped dynamically based on this new Base, and the columns 
are autoload’ed.  Again, this is working - sometimes.   There’s some 
random-like problem that mostly means it does not work when I do a 
mySession.query(myMappedClassWithRelationships) and I get the following 
exception being raised;

  File "C:\Python27\lib\site-packages\dap-2.1.2-py2.7.egg\dap\db\dbutils.py", 
line 323, in DAPDB_SetColumns
query = session.query(mappedClass).filter_by(**whereCriteria)
  File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 1260, in query
return self._query_cls(entities, self, **kwargs)
  File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 110, in __init__
self._set_entities(entities)
  File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 120, in 
_set_entities
self._set_entity_selectables(self._entities)
  File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 150, in 
_set_entity_selectables
ent.setup_entity(*d[entity])
  File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 3446, in 
setup_entity
self._with_polymorphic = ext_info.with_polymorphic_mappers
  File "build\bdist.win32\egg\sqlalchemy\util\langhelpers.py", line 754, in 
__get__
obj.__dict__[self.__name__] = result = self.fget(obj)
  File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 1891, in 
_with_polymorphic_mappers
configure_mappers()
  File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 2768, in 
configure_mappers
mapper._post_configure_properties()
  File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 1708, in 
_post_configure_properties
prop.init()
  File "build\bdist.win32\egg\sqlalchemy\orm\interfaces.py", line 183, in init
self.do_init()
  File "build\bdist.win32\egg\sqlalchemy\orm\relationships.py", line 1628, in 
do_init
self._process_dependent_arguments()
  File "build\bdist.win32\egg\sqlalchemy\orm\relationships.py", line 1653, in 
_process_dependent_arguments
setattr(self, attr, attr_value())
  File "build\bdist.win32\egg\sqlalchemy\ext\declarative\clsregistry.py", line 
293, in __call__
(self.prop.parent, self.arg, n.args[0], self.cls)
InvalidRequestError: When initializing mapper 
Mapper|SalesDocument|rm_dt_documents, expression 
'SalesDocumentLine.parentID==SalesDocument.id' failed to locate a name ("name 
'SalesDocumentLine' is not defined"). If this is a class name, consider adding 
this relationship() to the  class after 
both dependent classes have been defined.

I understand what this is trying to tell me, however, the classes ARE defined.  
Sometimes the code works perfectly, but mostly not.  If I have ONE Thread 
working and then start up another using exactly the same code, then it will 
probably NOT work but more importantly, the one that WAS working then dies with 
the same error.  Clearly something somewhere is shared - I just can’t find out 
what it is, or how I can separate the code further.

In summary;

one global sessionmaker()
global Session=sessionmaker()
each thread (for the example here) shares an Engine
each thread has it’s OWN session from mySession = Session(bind=e)
each thread has it’s own Base created from 
declarative_base(metadata=threadsMetaData)
I’m declaring two classes in this example. SalesDocument and SalesDocumentLine. 
 The relationships are set up and ‘can’ work on occasion.

In that error, where exactly are they not ‘defined’.  I’ve looked in 
Base.decl_class_registry and both those names are there!  Where else do they 
need to be to be considered ‘declared'?

Any pointers as to the error of my ways would be most appreciated.  :-)

Cheers
Warwick




-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to 

[sqlalchemy] Abort Query

[Warwick Prince]

Hi

If I execute a query, is there a way to abort that query and release the server 
before the query completes?

e.g. 

theTable = Table(‘some_large_table’, metadata, autoload=True)

query = theTable.select()

results = query.execute().fetchall()

Is there a way that perhaps another thread, if handed the query object or 
something else could intervene and kill the query execution?  This is so I can 
handle rogue queries that are taking a very long time - I want to be able to 
kill them before they compete.

Cheers
Warwick

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] multiple databases?

[Warwick Prince]

Hi Richard

There are no problems connecting to multiple database sources in the one 
application - we do it all the time :-)

Cheers
Warwick

On 10 Dec 2013, at 4:08 am, Richard Gerd Kuesters rich...@humantech.com.br 
wrote:

 hi all,
 
 i don't know if anyone have to go through this, but here's a question: is it 
 possible to use multiple databases sources (like postgres and mysql) in a 
 single application?
 
 
 my best regards,
 richard.
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Help on Counting Please

[Warwick Prince]

Hi All

Please excuse this relatively noob question, but I can not for the life of me 
find the answer in docs. (Probably because I don't know what I'm looking for).

I have a table with two columns A and B.   A can have many duplicate values. 
e.g. 

A B

1  a
1  b
1  c
1  d
2  f
2  g
3  z

I want to have a result that returns all the values of A, and how many times 
they appear in the table.

Sample result from above would be;

14
22
31

Clearly, I'm no SQL wizz, so I have the added issue of not knowing the raw SQL, 
let alone SQLAlchemy's take on it.

If you can provide the answer using table.select() type query, rather than the 
ORM equivalent it would be most appreciated.  :-)

Thanks in anticipation.

Cheers
Warwick


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Help on Counting Please

[Warwick Prince]

Hi Anh

Thanks for clearing my head.  I had devised considerably more complex attempts!

So, the answer is;

table.select().group_by(table.c.A).with_only_columns([table.c.A, 
func.count(1).label('count')]).execute().fetchall()

:-)

n 23/10/2013, at 7:29 PM, anh le anh...@gmail.com wrote:

 Hi,
 
 Have you tried:
 
 select A, count(*) from the_table group by A
 
 On Wed, Oct 23, 2013 at 4:17 PM, Warwick Prince
 warwi...@mushroomsys.com wrote:
 Hi All
 
 Please excuse this relatively noob question, but I can not for the life of 
 me find the answer in docs. (Probably because I don't know what I'm looking 
 for).
 
 I have a table with two columns A and B.   A can have many duplicate 
 values. e.g.
 
 A B
 
 1  a
 1  b
 1  c
 1  d
 2  f
 2  g
 3  z
 
 I want to have a result that returns all the values of A, and how many times 
 they appear in the table.
 
 Sample result from above would be;
 
 14
 22
 31
 
 Clearly, I'm no SQL wizz, so I have the added issue of not knowing the raw 
 SQL, let alone SQLAlchemy's take on it.
 
 If you can provide the answer using table.select() type query, rather than 
 the ORM equivalent it would be most appreciated.  :-)
 
 Thanks in anticipation.
 
 Cheers
 Warwick
 
 
 --
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] How-to filter by the time part of a datetime field?

[Warwick Prince]

 Hi,
 
 I've a model with a DateTime column and I need to select rows where the time 
 part of this column is between two hours (something like: select all rows 
 where the date is between 6:00 and 11:00).
 
 I need the date information, rows must be deleted after a couple of days.
 
 I don't know how-to extract the time part of my DateTime field in 
 .filter(). Should I add a Time column  ? Or is it possible to use only the 
 DateTime column?
 
 
 Thanks.
 -- 
 Laurent Meunier laur...@deltalima.net
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.


Hi Laurent

I'm sure there is a better way, but you could always filter using a date/time 
and supply the date part as well (i.e. today) so that you are comparing 
datetime to datetime.  (Something like: select all rows where the datetime is 
between 2013-08-30 06:00:00 and 2013-08-30 11:00:00)  :-)

Cheers
Warwick

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] How-to filter by the time part of a datetime field?

[Warwick Prince]

 Hi Warwick,
 
 On 30/08/2013 14:38, Warwick Prince wrote:
 I'm sure there is a better way, but you could always filter using a 
 date/time and supply the date part as well (i.e. today) so that you are 
 comparing datetime to datetime.  (Something like: select all rows where the 
 datetime is between 2013-08-30 06:00:00 and 2013-08-30 11:00:00)  :-)
 
 Thanks for your suggestion, this could do the trick.
 
 However my rows are split over a lot of days and if I follow your advice I'll 
 end with a lot of between 2013-08-30 06:00:00 and 2013-08-30 11:00:00 (one 
 for each day).  This will certainly work as expected, but I'm looking for a 
 more efficient way of doing this.
 
 
 Thanks.
 -- 
 Laurent Meunier laur...@deltalima.net
 
Ahh - I see.  Oh well, I'm sure someone with infinitely better SQL skills with 
chime in shortly.  :-)

Warwick

 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/groups/opt_out.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Reliable way to read comments from database schema

[Warwick Prince]

 On 06/04/2013 10:46 PM, Michael Bayer wrote:
 There's a long standing ticket to add support for comments, at least at the 
 DDL level.   I don't think anyone has looked into what level of support we 
 get from the various backends as far as reflection.
 
 So its something the library has room for, but it's an open item for now.  
 The four-year-old ticket is http://www.sqlalchemy.org/trac/ticket/1546.
 
 
 
 
 On Jun 4, 2013, at 8:33 PM, Warwick Prince warwi...@mushroomsys.com wrote:
 
 Hi Michael
 
 I'm toying with the idea of embedding some metadata into the comments on 
 columns and/or the table comment.  Is there a way to reliably read the 
 comment from the column definition via reflection across all dialects that 
 would support a comment at a column level?   Also, can I read the comment 
 normally attached to the table definition?
 
 Thanks
 Warwick
 
 
 If this metadata will be used just in sqlalchemy (or python libraries up in 
 the stack), you could use the info parameter when creating columns.

OK - sounds promising.  What actually is the 'info' that it reads/writes?  i.e. 
where is it getting it from/putting it in the DB? (Or does it only reside in 
the Python code?)

Also, I'd like to +1 the task to reflect the comments with support for as many 
dialects as possible :-)   We have developed a large platform using SQLA as the 
ORM, and as part of the maintenance area we have a generic database manager 
that is intended to allow maintenance, viewing, manual row insertion/deletion 
etc (Like MySQL Workbench et al) that is 100% database agnostic.  Comments are 
missing at the moment which is a shame.

 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
 For more options, visit https://groups.google.com/groups/opt_out.
 
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Error during roll back

[Warwick Prince]

Cool - Thanks.  Upgrade on the way..  :-)

Cheers
Warwick

 specifically it occurs when you receive an exception on flush(), but then you 
 keep doing things that change the state of the session before calling 
 rollback().  here's the original test:
 
 http://www.sqlalchemy.org/trac/attachment/ticket/2389/sqlalchemy_rollback_bug.py
 
 
 
 On Mar 17, 2013, at 1:38 AM, Warwick Prince warwi...@mushroomsys.com wrote:
 
 Hi Michael
 
 I have some fairly basic code which is moving data from one DB to another.  
 I have trapped errors on inserts just in case there were unexpected 
 duplicates.  When I go to commit this transaction, MySQL correctly throws an 
 IntegrityError exception: Duplicate Entry which I trap, and perform a 
 rollback.  However, the rollback ALSO receives an IntegrityError exception 
 from MySQL for the same insert query??
 
 
   File C:\Documents and Settings\wprince\Desktop\PY CODE 
 DEVELOPMENT\CESyncSQL\Sync_AU.py, line 1868, in SYNC_CustomersOUT
 session.rollback()
   File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 586, 
 in rollback
 self.transaction.rollback()
   File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 324, 
 in rollback
 self.close()
   File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 351, 
 in close
 self.session.begin()
   File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 557, 
 in begin
 self, nested=nested)
   File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 137, 
 in __init__
 self._take_snapshot()
   File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 197, 
 in _take_snapshot
 self.session.flush()
   File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 1473, 
 in flush
 self._flush(objects)
   File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 1542, 
 in _flush
 flush_context.execute()
   File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line 
 327, in execute
 rec.execute(self)
   File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line 
 471, in execute
 uow
   File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line 2163, 
 in _save_obj
 execute(statement, params)
   File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1358, 
 in execute
 params)
   File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1491, 
 in _execute_clauseelement
 compiled_sql, distilled_params
   File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1599, 
 in _execute_context
 context)
   File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line 1592, 
 in _execute_context
 context)
   File C:\Python27\lib\site-packages\sqlalchemy\engine\default.py, line 
 325, in do_execute
 cursor.execute(statement, parameters)
   File C:\Python27\lib\site-packages\mysql\connector\cursor.py, line 309, 
 in execute
 res = self.db().protocol.cmd_query(stmt)
   File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 
 136, in deco
 return func(*args, **kwargs)
   File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 
 474, in cmd_query
 return self.handle_cmd_result(self._recv_packet())
   File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 
 173, in _recv_packet
 MySQLProtocol.raise_error(buf)
   File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line 
 167, in raise_error
 raise errors.get_mysql_exception(errno,errmsg)
 IntegrityError: (IntegrityError) 1062: Duplicate entry '1231-63565' for key 
 'userID' u'INSERT INTO customer_master bla..
 
 Any ideas?   This is 0.7.1
 
 Cheers
 Warwick
 
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
 For more options, visit https://groups.google.com/groups/opt_out.
  
  
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
 For more options, visit https://groups.google.com/groups/opt_out.
  
  

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https

[sqlalchemy] Error during roll back

[Warwick Prince]

Hi Michael

I have some fairly basic code which is moving data from one DB to another.
 I have trapped errors on inserts just in case there were unexpected
duplicates.  When I go to commit this transaction, MySQL correctly throws
an IntegrityError exception: Duplicate Entry which I trap, and perform a
rollback.  However, the rollback ALSO receives an IntegrityError exception
from MySQL for the same insert query??


  File C:\Documents and Settings\wprince\Desktop\PY CODE
DEVELOPMENT\CESyncSQL\Sync_AU.py, line 1868, in SYNC_CustomersOUT
session.rollback()
  File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 586,
in rollback
self.transaction.rollback()
  File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 324,
in rollback
self.close()
  File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 351,
in close
self.session.begin()
  File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 557,
in begin
self, nested=nested)
  File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 137,
in __init__
self._take_snapshot()
  File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line 197,
in _take_snapshot
self.session.flush()
  File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line
1473, in flush
self._flush(objects)
  File C:\Python27\lib\site-packages\sqlalchemy\orm\session.py, line
1542, in _flush
flush_context.execute()
  File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line
327, in execute
rec.execute(self)
  File C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py, line
471, in execute
uow
  File C:\Python27\lib\site-packages\sqlalchemy\orm\mapper.py, line 2163,
in _save_obj
execute(statement, params)
  File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
1358, in execute
params)
  File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
1491, in _execute_clauseelement
compiled_sql, distilled_params
  File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
1599, in _execute_context
context)
  File C:\Python27\lib\site-packages\sqlalchemy\engine\base.py, line
1592, in _execute_context
context)
  File C:\Python27\lib\site-packages\sqlalchemy\engine\default.py, line
325, in do_execute
cursor.execute(statement, parameters)
  File C:\Python27\lib\site-packages\mysql\connector\cursor.py, line 309,
in execute
res = self.db().protocol.cmd_query(stmt)
  File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line
136, in deco
return func(*args, **kwargs)
  File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line
474, in cmd_query
return self.handle_cmd_result(self._recv_packet())
  File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line
173, in _recv_packet
MySQLProtocol.raise_error(buf)
  File C:\Python27\lib\site-packages\mysql\connector\protocol.py, line
167, in raise_error
raise errors.get_mysql_exception(errno,errmsg)
IntegrityError: (IntegrityError) 1062: Duplicate entry '1231-63565' for key
'userID' u'INSERT INTO customer_master bla..

Any ideas?   This is 0.7.1

Cheers
Warwick

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Re: Inserting Entry Fastest way

[Warwick Prince]

Thanks Russ - took a look and found it very interesting indeed.

Cheers
Warwick

 On Monday, March 11, 2013 4:56:11 PM UTC-4, Arkilic, Arman wrote:
 Hi,
 I am working on a database design that I am required to use lots of tables 
 with one-to-many relationship. As a consequence of the design, I need to 
 insert thousands of entries. I tried session.add(), session.merge, however 
 none of them is fast enough for me to meet the requirements. I was wondering 
 if you can suggest me an efficient way through either ORM or ORM+Core.
 Thanks!
 
 I recently did a presentation structured around getting fast bulk inserts 
 with SQLAlchemy.  You may find it useful:
 
 https://speakerdeck.com/rwarren/a-brief-intro-to-profiling-in-python
 
 Please note that the focus was on profiling, and not on SQLAlchemy.   The 
 SQLAlchemy example just worked out well (with a contrived step or two) as a 
 vehicle for showing different profiling steps/gotchas.  Since the focus was 
 on profiling, the example is quite simple (a single user table)... but you 
 can easily extend on it for your one-to-many tables.
 
 I also didn't 100% scrub the SQLAlchemy code (I threw this together in a 
 hurry), so no yelling at me for bad code. :)
 
 Russ
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To unsubscribe from this group and stop receiving emails from it, send an 
 email to sqlalchemy+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
 For more options, visit https://groups.google.com/groups/opt_out.
  
  

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] MySQL has gone away

[Warwick Prince]

 
 On Jan 7, 2013, at 7:20 PM, Diego Woitasen wrote:
 
 
 I'm back to this :)
 
 My problem was that I'm not closing the session properly.  
 
 My new question is... is there a way to autoclose the session? My example, 
 at the end of the scope?
 
 What's the recommend way to do this?
 
 I've read the FAQ, but it's not very clear for me.
 
 you need to structure your application such that database operations occur 
 within a consistent framework.
 
 This is the simplest when using web frameworks, as all frameworks include 
 some kind of  request end hook - that's where the close of the Session 
 would go.
 
 Looking at your example again, I see you have a print delivery.name, 
 delivery.status after you've done your commit().  That would be a likely 
 source for the new connection here, as accessing those attributes means the 
 Session needs to go back to the database post-commit to get their most recent 
 value.

Hi Michael / Diego - I also have a couple of questions in this area, so I 
though I would jump on this thread.

So, if you close down a Session with Session.close() is the Engine associated 
with that session (e.g. Session(bind=e) ) then freed up as far as the pool etc 
is concerned or is there something else I need to do to the Engine?

If I have a result proxy and it simply gets garbage collected - is that 'freed' 
as far as SQLA is concerned or do I need to specifically do something to clean 
it up (other than exhausting it)?

The issue I'm having is that 'sometimes' my app gets handed a dead connection 
to the MySQL server which tends to make things unhappy.  It is a home grown 
framework, however it is well structured and has clear Entry and Exit points 
for closing down any Session objects etc.  *Somewhere* I am leaving something 
dangling and it's getting closed down by MySQL with a connection timeout.

Finally, I'm using multi Session objects (potentially) within a threaded 
environment.  i.e. Each thread may or may not have one or more Sessions created 
using sessionmaker(). (A second Session would typically be because of some 
nested requirement)  This appears to work fine, or am I missing something?  
Each new Session is bound to the engine directly - and it *could* be a 
different Engine to other Sessions.

Thanks for all your help.

Cheers
Warwick

-- 
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] NOT LIKE

[Warwick Prince]

OK - cool.

I had looked at the first ORM tutorial, but I guess I had glossed over it, as 
it was talking about session.query, and I believed I was looking for something 
lower level than that for the direct table.select.  Obviously not. :-)  Makes 
sense that it would all follow suit, but I was expecting to have to use lower 
level constructs - I'm pleasantly surprised.

Thanks for your seemingly infinite patients!

 On Aug 22, 2012, at 11:38 PM, Warwick Prince wrote:
 
 Thanks Michael
 
 I struggle sometimes to find examples of the simple things, so eventually 
 searched out the like_op as it was in the same place as eq() etc.
 
 So, on that subject - is it better to use query.where(eq(a, b)) or 
 query.where(a==b), or does it make no difference really?  
 
 it makes no difference at all.   
 
 The closest thing we have to a rundown of operators is here: 
 http://docs.sqlalchemy.org/en/rel_0_7/orm/tutorial.html#common-filter-operators
 
 I suppose having an exhaustive list, with lots of links pointing to it, over 
 here: http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html would be 
 helpful.
 
 
 
 
 not sure why the like_op and nolike_op have come into your normal 
 vocabulary here as they are usually just the ops used internally.
 
 LIKE is column.like(other) and NOT LIKE is ~column.like(other).
 
 
 On Aug 22, 2012, at 9:15 PM, Warwick Prince wrote:
 
 Hi 
 
 When creating a basic query, how does one code a NOT LIKE using SA?
 
 I can do this;
 
 query = table.select().where(like_op(table.c.name, 'fred%'))
 
 I can not find a NOT LIKE operator.  The ones there notlike_op and 
 notilike_op raise NotImplemented.
 
 I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, 
 but I just KNOW that's going to blow up when I change databases down the 
 track..
 
 Is there another way?   I've been burnt badly recently changing from MySQL 
 to Postgres with text(), so I'm trying to avoid at all costs! :-)
 
 Cheers
 Warwick
 
 -- 
 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.
 
 
 -- 
 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.
 

-- 
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] NOT LIKE

[Warwick Prince]

Hi 

When creating a basic query, how does one code a NOT LIKE using SA?

I can do this;

query = table.select().where(like_op(table.c.name, 'fred%'))

I can not find a NOT LIKE operator.  The ones there notlike_op and 
notilike_op raise NotImplemented.

I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, but I 
just KNOW that's going to blow up when I change databases down the track..

Is there another way?   I've been burnt badly recently changing from MySQL to 
Postgres with text(), so I'm trying to avoid at all costs! :-)

Cheers
Warwick

-- 
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] NOT LIKE

[Warwick Prince]

Thanks Michael

I struggle sometimes to find examples of the simple things, so eventually 
searched out the like_op as it was in the same place as eq() etc.

So, on that subject - is it better to use query.where(eq(a, b)) or 
query.where(a==b), or does it make no difference really?  

 not sure why the like_op and nolike_op have come into your normal 
 vocabulary here as they are usually just the ops used internally.
 
 LIKE is column.like(other) and NOT LIKE is ~column.like(other).
 
 
 On Aug 22, 2012, at 9:15 PM, Warwick Prince wrote:
 
 Hi 
 
 When creating a basic query, how does one code a NOT LIKE using SA?
 
 I can do this;
 
 query = table.select().where(like_op(table.c.name, 'fred%'))
 
 I can not find a NOT LIKE operator.  The ones there notlike_op and 
 notilike_op raise NotImplemented.
 
 I've placed it in as text('%s NOT LIKE %s') % (column, value) for now, but 
 I just KNOW that's going to blow up when I change databases down the track..
 
 Is there another way?   I've been burnt badly recently changing from MySQL 
 to Postgres with text(), so I'm trying to avoid at all costs! :-)
 
 Cheers
 Warwick
 
 -- 
 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.
 

-- 
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] PG error I don't understand.

[Warwick Prince]

Hi

I have created a simple update like this on a PG database via PG8000;

table=Table('invoice_line_items', meta, autoload=True)
query = table.update()
query = query.where(and_(eq(table.c.InvBook, 'SC'), eq(table.c.InvNum,
12862), eq(table.c.InvLine, 1)))
query = query.values(**data)
query.execute()

I'm getting this error, and looking at the SQL (from the error message), I
can't see what is wrong or why PG is complaining.

('ERROR', '42712', 'table name invoice_line_items specified more than
once') u'UPDATE invoice_line_items SET DocType=%s, LineType=%s,
Complete=%s, Group=%s, Product=%s, SerialNumber=%s,
Description=%s, Warehouse=%s, UnitCode=%s, UnitQty=%s,
Supplier=%s, Active=%s, Customer=%s, BillTo=%s, Date=%s,
ConsignmentFlag=%s, TaxFlag=%s, HiddenFlag=%s, JoinFlag=%s,
OrderQty=%s, ReserveQty=%s, BackorderQty=%s, InvoiceQty=%s,
ShippedQty=%s, Currency=%s, CostEach=%s, SellEach=%s, TotalEx=%s,
Total=%s, InputTax=%s, OutputTax=%s, TaxTotal=%s,
BudgetCostEx=%s, BudgetCostInc=%s, Weight=%s, GLRevenue=%s,
GLCost=%s, GLCostFrom=%s, PriceLevel=%s, OrderType=%s,
StatusCode=%s, ReasonCode=%s, LotNumber=%s, GST=%s, BookingID=%s,
StampDuty=%s, Insurance=%s, PriceSource=%s, SalesRep=%s,
ETADate=%s, CostCentreRevenue=%s, CostCentreCost=%s,
CostCentreCostFrom=%s, RebateRule=%s, CustOrderUnitCode=%s,
CustOrderQty=%s, FreightMode=%s, FreightExEach=%s,
FreightExApplied=%s, GLFreight=%s, BundleGroup=%s,
BundleOrderQty=%s, BundleComponentQty=%s, MinSell=%s,
DiscTaxApplyFlag=%s, yearWeek=%s, yearMonth=%s, yearQuarter=%s,
colour=%s, feeCode=%s, feeTotalEX=%s FROM invoice_line_items WHERE
invoice_line_items.InvBook = %s AND invoice_line_items.InvNum = %s AND
invoice_line_items.InvLine = %s' (u'C', u'P', u'Y', u'FIL', u'OBS3E',
u'', u'ORGANIC SUPREME SKIN ON', u'S01', u'KG', 1.0, u'MANDJ', u'Y',
u'S2550', u'S2550', datetime.date(2007, 6, 5), u'N', u'N', u'N', u'N',
-2.4, 0.0, 0.0, 0.0, 0.0, u'AUD', 0.0, 19.5, -46.8, -46.8, u'', u'N', 0.0,
0.0, 0.0, -2.4, u'101-30010-000', u'101-10530-000', u'101-10530-000', u'L',
u'', u'', u'', u'', 0.0, 0, 0.0, 0.0, u'L', u'SAM', None, u'', u'', u'',
u'', u'KG', -2.4, u'', 0.0, 0.0, u'', u'', 0.0, 0.0, 0.0, u'', u'2007-22',
u'2007-06', u'2007-02', None, None, None, u'SC', 12862, 1)

Many thanks
Warwick

-- 
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] Determining sqa type from dialect type

[Warwick Prince]

 
 On Aug 10, 2012, at 5:19 AM, Warwick Prince wrote:
 
 Hi All
 
 If I have a Column() object, is there a way of determining the sqlalchemy 
 type from the dialect specific type?  e.g. I have a Postgres TIMESTAMP 
 column, and I want to be able to map that back the a sqa DateTime type. 
 
 
 column.type._type_affinity
 
I have moved on now with this information, but I've hit a snag which appears to 
be bug in either core or possibly (more likely) mysqlconnector.

When I ask for column.type._type_affinity on a LONGBLOB column in a MySQL 
database, it returns _Binary which is not correct (Should be LargeBinary).  
Please confirm if this is a core issue, or should I go hunting in the connector 
code?

Cheers
Warwick

-- 
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] Postgres migration issue

[Warwick Prince]

Hi David

Thanks for that - much appreciated :-)

 
 
 Hi
 
 I usually use MySQL to develop on, however I need to work with Postgres for 
 the first time today so I fired it up.  I have a routine which converts a 
 non-SQL database into the database of choice, converting its schema into a 
 new table in the target database using SQA, and then copies all the data in 
 the source database into the new SQL table.   
 
 That all worked fine into the Postgres+pg8000 database.  My problem is when I 
 then attempt to open up a table again using auto reflection I get an error 
 I've never seen before, and I don't get how this can be, given the table was 
 created via sqlalchemy?  The data looks fine in the table, and all columns 
 are created as I expected (converting to the correct Postrgres column types 
 etc.
 
 Error when I issue   t = Table('my_table', meta, autoload=True) is; (sorry 
 about the screen shot, I'm working in a bad RDP client and can't cut/paste 
 into my Mac. :-(
 
 PastedGraphic-1.png
 
 So it appears to be having some problem in the reflection, but I can't see 
 why - I hope there is a setting in the connection or something I can do to 
 fix this up?  I've never used Postgres before, so I'm groping in the dark..   
 From Googling around, it appears that there is some issue with determining 
 the schema or some such, but it's all assuming I know a lot more about 
 Postgres than I do!
 
 Cheers
 Warwick
 
 
 Hi Warwick,
 
 You are using pg8000 1.08 and PostgreSQL = 9.0.  Upgrade to pg8000 1.09, it 
 fixes this issue (there are new PostgreSQL types introduced in version 9 
 which pg8000 didn't know of in 1.08, and added in 1.09).
 
 regards
 -- 
 David Moore
 Senior Software Engineer
 St. James Software
 Email: dav...@sjsoft.com
 
 -- 
 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] Determining sqa type from dialect type

[Warwick Prince]

Hi All

If I have a Column() object, is there a way of determining the sqlalchemy type 
from the dialect specific type?  e.g. I have a Postgres TIMESTAMP column, and I 
want to be able to map that back the a sqa DateTime type.  Why?  If I want to 
automatically clone tables from one database to another (of different types) I 
can't simply clone the table metadata and use it to table.create in the 
destination engine.  In my case source is MySQL destination is Postgres and it 
fails (correctly) saying that it does not know what a datetime type is. 
(Because it's using the MySQL dialect DATETIME not sqa DateTime as the basis of 
the column.  Effectively, I want to reverse engineer the creation of the column.

Cheers
Warwick

Warwick Prince 
Managing Director 
mobile: +61 411 026 992 
skype: warwickprince  

phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 



-- 
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] Determining sqa type from dialect type

[Warwick Prince]

Hi Michel

Thanks!  I knew it was in there somewhere! :-)

Cheers
Warwick
 Hi All
 
 If I have a Column() object, is there a way of determining the sqlalchemy 
 type from the dialect specific type?  e.g. I have a Postgres TIMESTAMP 
 column, and I want to be able to map that back the a sqa DateTime type.  Why?
 
 
 column.type._type_affinity
 
 
 -- 
 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] Postgres migration issue

[Warwick Prince]

Hi

I usually use MySQL to develop on, however I need to work with Postgres for the 
first time today so I fired it up.  I have a routine which converts a non-SQL 
database into the database of choice, converting its schema into a new table in 
the target database using SQA, and then copies all the data in the source 
database into the new SQL table.   

That all worked fine into the Postgres+pg8000 database.  My problem is when I 
then attempt to open up a table again using auto reflection I get an error I've 
never seen before, and I don't get how this can be, given the table was created 
via sqlalchemy?  The data looks fine in the table, and all columns are created 
as I expected (converting to the correct Postrgres column types etc.

Error when I issue   t = Table('my_table', meta, autoload=True) is; (sorry 
about the screen shot, I'm working in a bad RDP client and can't cut/paste into 
my Mac. :-(



So it appears to be having some problem in the reflection, but I can't see why 
- I hope there is a setting in the connection or something I can do to fix this 
up?  I've never used Postgres before, so I'm groping in the dark..   From 
Googling around, it appears that there is some issue with determining the 
schema or some such, but it's all assuming I know a lot more about Postgres 
than I do!

Cheers
Warwick

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

inline: PastedGraphic-1.png

[sqlalchemy] Re: Postgres migration issue

[Warwick Prince]

 Hi
 
 I usually use MySQL to develop on, however I need to work with Postgres for 
 the first time today so I fired it up.  I have a routine which converts a 
 non-SQL database into the database of choice, converting its schema into a 
 new table in the target database using SQA, and then copies all the data in 
 the source database into the new SQL table.   
 
 That all worked fine into the Postgres+pg8000 database.  My problem is when I 
 then attempt to open up a table again using auto reflection I get an error 
 I've never seen before, and I don't get how this can be, given the table was 
 created via sqlalchemy?  The data looks fine in the table, and all columns 
 are created as I expected (converting to the correct Postrgres column types 
 etc.
 
 Error when I issue   t = Table('my_table', meta, autoload=True) is; (sorry 
 about the screen shot, I'm working in a bad RDP client and can't cut/paste 
 into my Mac. :-(
 
 PastedGraphic-1.png
 
 So it appears to be having some problem in the reflection, but I can't see 
 why - I hope there is a setting in the connection or something I can do to 
 fix this up?  I've never used Postgres before, so I'm groping in the dark..   
 From Googling around, it appears that there is some issue with determining 
 the schema or some such, but it's all assuming I know a lot more about 
 Postgres than I do!
 
 Cheers
 Warwick
 

Further to the above - I looked into my code for the database cloning explained 
above, to see how IT opened the table.  It worked because I was still using the 
same MetaData object, therefore the column defs were cached.  The error happens 
on a virgin MetaData object with autoload.

-- 
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] Performance Mystery

[Warwick Prince]

Hi All

This is addressed to anyone who may be able to shed some light on this strange 
behaviour;

I'm running MySQL on Windows 2003 Server (Sorry) and have a table that has ~2M 
rows of sales data in it.   Knowing the type of BI queries I would want to be 
doing, I have added indexes where I feel they would help.  I tested the queries 
I wanted to perform manually by directly entering the SQL into the MySQL 
console.  Results were fine. (Tested by putting raw SQL into MySQL Workbench on 
the same server as the database)

I then simply moved the logic into my code, and created very simple queries on 
tables using the table.select() syntax, and adding a few basic group_by and 
'where' additions.   All works perfectly, and I get exactly the same results as 
my manual query BUT...  the manual query returns the results in 3 seconds, and 
the programatic query returns the results in 200+ seconds!!  This is the round 
trip time from .execute() 'til the next line of code being hit. i.e. I have 
fetched nothing yet.

This is what I have done;

I added an audit line in my code to show the final SQL that was being emitted, 
then cut and paste that into MySQL Workbench and executed it.   3 seconds was 
the result, with exactly the same actual rows being returned.
The Python code is running on the same server as the MySQL Console.
I have created the same query manually on the Python console, using the 
sqlalchemy constructs like query=table.select() etc and run it there - 180+ 
seconds again - it's I've basically eliminated by code.
I'm using 'mysqlconnector' which I have had no problems in the past, and it's 
not like I'm hammering it with 1000's of queries - just 1 query that will 
return around 12 rows (having processed several 1000 and then GROUPed them).   
I can see the query sitting there in Workbench, taking minutes to complete.
I've tried adding index hints just in case..  Made no difference, as it should 
have been using that index anyway..
When creating the SQLAlchemy test query, (and indeed in my real code) I used 
all defaults for engine, metadata etc with no additional settings at all.
Finally, to add insult to injury, I can execute the identical manual query 
DURING the slow queries execution, and still get it back  3 seconds.. whilst 
the slow one clunks along and spits out the same result minutes later.
In the SQL below, invoice and customer are VARCHAR columns, whilst date is DATE 
and TotalEx is FLOAT
I have restarted the MySQL Service (several times)
It's *like* the sqlalchemy query is not allowed to use any of the indexes, and 
is trawling through all the data..  Are there permissions or something on using 
an index that I don't know about?  (Both queries are being executed by the same 
user - root)
It is EXACTLY the same query in all ways, and in fact is an extremely simple 
query - as below;

SELECT sum(`sales_data`.`TotalEx`) AS totalSales, date, invoice, customer 
FROM sales_data USE INDEX(idx_date) 
WHERE date =2011-12-01 AND date =2012-07-31
GROUP BY customer, invoice

Any clues?  My project just fell in a hole!

Thanks an advance for any pointers.
Warwick

-- 
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] PostgreSQL 9.1 on the horizon, cool new stuff

[Warwick Prince]

Thanks for the 'heads-up' Eric :-)

 ! Nothing to see here, move right along !
 
 Except... Couple of interesting additions coming up in PostgreSQL 9.1
 (still in beta) for anyone who's interested.  Release notes:
 http://developer.postgresql.org/pgdocs/postgres/release-9-1.html   A
 couple of selected items I found of interest:
 
 * New support for CREATE UNLOGGED TABLE -- a new type of table that is
 sort of in between temporary tables and ordinary tables.  They are not
 crash-safe as they are not written to the write-ahead log and are not
 replicated if you have replication set up, but the tradeoff is they
 can be written to a lot faster.  Could use these to speed up testing,
 or in other non-production scenarios where crashproofness is not a
 concern.
 
 * New support for Synchronous replication -- primary master waits for
 a standby to write the transaction information to disk before
 acknowledging the commit.  This behavior can be enabled or disabled on
 a per-transaction basis.  Also a number of new settings related to
 keeping a 'hot standby'.
 
 * They added a true serializable transaction isolation level.
 Previously, asking for serializable isolation guaranteed only that a
 single MVCC snapshot would be used for the entire transaction, which
 allowed certain documented anomalies.  The old snapshot isolation
 behavior will now be accessible by using the repeatable read
 isolation level.
 
 --This one might be particularly interesting for SQLAlchemy--
 * INSERT, UPDATE, and DELETE will now be allowed in WITH clauses;
 these commands can use RETURNING to pass data up to the containing
 query.  While not strictly necesary, this can improve the clarity of
 SQL emitted by eliminating some nested sub-SELECTs.  There is other
 cool stuff you can accomplish with this such as deleting rows from one
 table according to a WHERE clause inside of a WITH...RETURNING, and
 inserting the same rows into another table in the same statement.  The
 recursive abilities of WITH statements can also be used now to perform
 useful maneuvers like recursive DELETEs in tree structures (as long as
 the data-modifying part is outside of the WITH clause).
 
 * New support for per-column collation settings (yawn... but someone
 somewhere needs this while migrating or something)
 
 * New support for foreign tables -- allowing data stored outside the
 database to be used like native postgreSQL-stored data (read-only).
 
 * Enum types can now be added to programmatically (i don't know if
 they can be removed from) via ALTER TYPE
 
 * Added CREATE TABLE IF NOT EXISTS syntax -- seems like SA's DDL
 machinery might want to use that in the checkfirst=True case to
 eliminate the separate check operation?  A minor matter, but nice.
 
 * Added transaction-level advisory locks (non-enforced, application-
 defined) similar to existing session-level advisory locks.
 
 * Lots more (a dump + restore will be required between 9.0 and 9.1)
 
 -- 
 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] Removing a session (Really)

[Warwick Prince]

Hi Michael

I'm having an issue with memory usage that I would appreciate some insight..

I have a fairly straight forward process, that works perfectly as far as it 
delivering the desired updates in the DB etc, however, it accumulates memory 
usage (just like a leak) and I can not find a way to release it.

I have a multi threaded process.  Each thread creates a scopedsession from the 
standard global Session = scoped_session(sessionmaker()) construct.  Each 
thread does some work using mapped objects to update some and add some rows 
into the (MySQL on Windows) DB.  All this works perfectly and as expected.   
Due to various reasons, I flush/commit after each row is updated/inserted.   
After the batch of updates is complete, I come back and session.remove() (In 
an attempt to dump the session) and then wait for a while and do the entire 
thing again.  At the start of each run, I create a new session=Session() and do 
the updates and return and session.remove().

To me, I would assume that the memory would be the session's cache of objects 
that are being managed - which I can understand.  What I can't understand is 
why when I delete *everything* e.g. del engine, del meta, del session and even 
stop the thread, the memory is still consumed.I must stop the entire 
process before the memory is returned to the system.   After around 10 hours of 
running, I've used 2Gb+ of memory and everything crashes.

BTW: I have created a version of my code that does everything EXCEPT the SA 
part(s), and no memory is being used at all. (Just checking that it wasn't my 
own code causing the issue!)  i.e. It loops over the other database (non SQL) 
reading all the data that I WOULD use to update the SQL database using SA.  
When SA is not involved, nothing is happening with the memory.

Any hint on how I can a) see what is being held and b) dump it!

I'm using 0.6.3 and Python 2.6.3 on Windows.   BTW: I tried to update to latest 
7.x and Python 2.7.2 however that broke everything in a spectacular way - I'll 
leave that one for another day..

Cheers
Warwick
 

-- 
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] Removing a session (Really)

[Warwick Prince]

Excellent - thanks  :-)

Warwick

On 27/06/2011, at 2:37 AM, Michael Bayer wrote:

 
 On Jun 26, 2011, at 4:31 AM, Warwick Prince wrote:
 
 Hi Michael
 
 I'm having an issue with memory usage that I would appreciate some insight..
 
 I have a fairly straight forward process, that works perfectly as far as it 
 delivering the desired updates in the DB etc, however, it accumulates memory 
 usage (just like a leak) and I can not find a way to release it.
 
 I have a multi threaded process.  Each thread creates a scopedsession from 
 the standard global Session = scoped_session(sessionmaker()) construct.  
 Each thread does some work using mapped objects to update some and add some 
 rows into the (MySQL on Windows) DB.  All this works perfectly and as 
 expected.   Due to various reasons, I flush/commit after each row is 
 updated/inserted.   After the batch of updates is complete, I come back 
 and session.remove() (In an attempt to dump the session) and then wait for a 
 while and do the entire thing again.  At the start of each run, I create a 
 new session=Session() and do the updates and return and session.remove().
 
 To me, I would assume that the memory would be the session's cache of 
 objects that are being managed - which I can understand.  What I can't 
 understand is why when I delete *everything* e.g. del engine, del meta, del 
 session and even stop the thread, the memory is still consumed.I must 
 stop the entire process before the memory is returned to the system.   After 
 around 10 hours of running, I've used 2Gb+ of memory and everything crashes.
 
 By the memory is still consumed, if you're talking about the memory of your 
 process, that's Python's behavior - once the size of memory usage grows to X, 
 it stays at X no matter what you dereference within the process.  So the key 
 is to manage how large a collection ever gets filled up in the first place.   
  The only true measure of python objects being leaked is the size of 
 gc.get_objects().  If that size is managed, that's as far as Python code can 
 go towards managing memory.
 
 So I'm assuming you just mean the size of the process. If you're dealing 
 with large numbers of rows being loaded into memory, you'd need to cut down 
 on the maximum size of objects loaded at once.
 
 The Session does not strongly reference anything, except for that which is 
 present in the .new and .dirty collections.   If those are empty, it is 
 not strongly referencing anything, and as long as gc is enabled, the number 
 of objects in memory will be managed.Older versions of Session in 0.5, 
 0.4 and such were not as good at this, but in 0.6, 0.7 it's quite solid, 
 there is a whole suite of unit tests that ensure SQLAlchemy components like 
 Engine, Session, schema, etc. do not leak memory under a variety of 
 setup/teardown situations.  But it seems like you're don't yet know if 
 you're experiencing a problem at the Python object level.
 
 
 
 i.e. It loops over the other database (non SQL) reading all the data that I 
 WOULD use to update the SQL database using SA.  When SA is not involved, 
 nothing is happening with the memory.
 
 note that DBAPIs, particularly older versions of MySQLdb, may have memory 
 leaks, and most DBAPIs when asked to fetch a result will load the full set of 
 results into memory before fetchone() is ever called, thus causing a great 
 unconditional increase in the size of memory if you are fetching very large 
 result sets.
 
 
 Any hint on how I can a) see what is being held and b) dump it!
 
 gc.get_objects()
 
 
 -- 
 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] ODBC general question

[Warwick Prince]

Hi All

We need to connect to a Progress database, and we are in the very early 
days of this.  I understand it supports an ODBC interface and therefore should 
be able to be connected to using SA - correct?

Are there any limitations on the ODBC connector or gotcha's that we should 
look out for?   Any advise / direction would be most appreciated when you have 
a moment.

Cheers from very wet Brisbane Australia.

Warwick

-- 
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] ODBC general question

[Warwick Prince]

Hi Michael

Thanks yet again for excellent advice provided in a timely manner!  :-)

Cheers
Warwick

On 19/01/2011, at 10:25 AM, Michael Bayer wrote:

 pyodbc works very well, as does mxodbc which is commercial.   Most issues 
 have to do with using ODBC from unix, where if we're working for free we use 
 FreeTDS, that has a lot of quirks.  There are commercial ODBC drivers for 
 unix which I haven't used but we will be using them soon for a commercial 
 project.   When writing an ODBC dialect for SQLAlchemy you extend 
 sqlalchemy.connectors.pyodbc.PyODBCConnector (or MxODBCConnector) for your 
 dialect, you can look at the several pyodbc clients we have already (mssql, 
 mysql, sybase) for examples.
 
 The other advantage/disadvantage of ODBC is the usage of datasources.   This 
 is basically an extra layer of indirection between connect strings and an 
 actual TCP hostname.  Some setups allow the bypassing of the DSN and a lot of 
 confusion comes from whether or not that is in use, since a lot of 
 homegrowers impatiently try to skip that layer and get all confused.   Its 
 best to use externally configured DSNs when working with ODBC since that's 
 how it was designed to be used.
 
 
 
 On Jan 18, 2011, at 6:51 PM, Warwick Prince wrote:
 
 Hi All
 
 We need to connect to a Progress database, and we are in the very early 
 days of this.  I understand it supports an ODBC interface and therefore 
 should be able to be connected to using SA - correct?
 
 Are there any limitations on the ODBC connector or gotcha's that we should 
 look out for?   Any advise / direction would be most appreciated when you 
 have a moment.
 
 Cheers from very wet Brisbane Australia.
 
 Warwick
 
 -- 
 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.
 

-- 
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] flush() issued, SQL seen, but database not updated?

[Warwick Prince]

Hi Jerry

Looks to me like you will need to swap your .flush for a .commit.commit 
will flush for you, then actually commit the changes to the DB.  :-)

Cheers
Warwick

 Hi,
 
 I have been pulling my hair the whole day today: I have a web
 application that runs fine, however, during unittest I noticed that
 the test (PostgreSQL) database is not updated even after I issue the
 flush() and see the SQL statement, which inserts fine by itself in
 psql --
 
 
 (Pdb) list
 73user = model.User(user_name=user_name, email=email,
 password=password)
 74
 75dbsession = DBSession()
 76  -dbsession.add(user)
 77try:
 78dbsession.flush()
 79except:
 80raise
 (Pdb) user
 myapp.models.User object at 0xa9b8c4c
 (Pdb) user.user_name, user.email, user.user_id
 (u'test', u't...@example.com', u'f24a24217248480d90c1c370c103e07f')
 (Pdb) n
 myapp/views/signup.py(77)signup_view()
 - try:
 (Pdb) n
 myapp/views/signup.py(78)signup_view()
 - dbsession.flush()
 (Pdb) n
 ...INFO sqlalchemy.engine.base.Engine.0x...24ac INSERT INTO users
 (user_id, user_name, email) VALUES (%(user_id)s, %(user_name)s, %
 (email)s)
 ...INFO sqlalchemy.engine.base.Engine.0x...24ac {'user_id':
 u'f24a24217248480d90c1c370c103e07f', 'user_name': u'test', 'email':
 u't...@example.com'}
 
 
 I have dropped all the databases in my computer leaving only the test
 db just to make absolute sure that I'm not connecting to one database
 while looking into another.
 
 What could have gone wrong?
 
 Many thanks in advance!
 
 Jerry
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Char encoding..

[Warwick Prince]

Hi Michael

Thanks for your thoughts and comments to date.

I can replicate the problem with ease, so perhaps this will help;

# -*- coding: utf-8 -*-
e = 
create_engine('mysql+mysqlconnector://user:passw...@127.0.0.1/testdb?use_unicode=0',
 encoding='utf8', echo=False)
m = MetaData(e)
t = Table('test_table', m, autoload=True)
#test_table is;
Table('test_table', 
MetaData(Engine(mysql+mysqlconnector://user:passw...@127.0.0.1/testdb?use_unicode=0)),
 Column(u'ID', INTEGER(display_width=11), table=test_table, primary_key=True, 
nullable=False), Column(u'SourceType', VARCHAR(length=10), table=test_table), 
Column(u'SourceID', VARCHAR(length=128), table=test_table), Column(u'Date', 
DATE(), table=test_table), Column(u'Time', TIME(timezone=False), 
table=test_table), Column(u'UserID', VARCHAR(length=10), table=test_table), 
Column(u'Note', BLOB(length=None), table=test_table), Column(u'Division', 
VARCHAR(length=3), table=test_table), schema=None)

# Set some row  data in a dict
columns = dict(ID=1, SourceType='TEST', SourceID='WAP', Note=u'Aligot\xe9')  # 
The Note column is set to a unicode value for a French word with accents. 
Column type is BLOB

# insert it
t.insert(values=columns).execute()

get this;
Traceback (most recent call last):
  File interactive input, line 1, in module
  File C:\Python26\lib\site-packages\sqlalchemy\sql\expression.py, line 1217, 
in execute
return e._execute_clauseelement(self, multiparams, params)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1722, in 
_execute_clauseelement
return connection._execute_clauseelement(elem, multiparams, params)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1235, in 
_execute_clauseelement
parameters=params
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1343, in 
__create_execution_context
connection=self, **kwargs)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line 384, 
in __init__
self.parameters = self.__convert_compiled_params(self.compiled_parameters)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line 513, 
in __convert_compiled_params
param[key] = processors[key](compiled_params[key])
  File C:\Python26\lib\site-packages\sqlalchemy\types.py, line 1209, in 
process
return DBAPIBinary(value)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 6: 
ordinal not in range(128)

It appears to be in the processing of the Binary type that something is going 
wrong. 

Further testing showed something interesting.  I changed around the data above 
and set the unicode value to the VARCHAR column SourceID.  That worked..  
Therefore, the issue is related to storing a unicode value into a BLOB.  Surely 
I can store anything in a BLOB, or am I missing something?

Cheers
Warwick

Warwick Prince 
Managing Director 
mobile: +61 411 026 992 
skype: warwickprince  
 
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 

On 30/11/2010, at 1:29 AM, Michael Bayer wrote:

 we've got unicode round trips down very well for years now with plenty of 
 tests, so would need a specific series of steps to reproduce what you're 
 doing here.  Note that the recommended connect string for MySQL + Mysqldb 
 looks like mysql://scott:ti...@localhost/test?charset=utf8use_unicode=0 .
 
 On Nov 29, 2010, at 2:37 AM, Warwick Prince wrote:
 
 Hi All
 
 I thought I had Character Encoding licked, but I've hit something I can't 
 work through.  Any help appreciated.
 
 I have a legacy non SQL database that I read legacy data from (using cool 
 Python code that emulates the old ISDB binary comms) and it reads a str 
 which has Foreign language chars in it.  (French for example).  
 
 So, firstly, I have   myStr = ''Aligot\xc3\xa9  which when printed is 
 Aligoté.   So far so good.
 
 I then convert that to unicode by  myUnicode = unicode(myStr, 'utf-8', 
 errors='ignore') and get u'Aligot\xe9'.   This printed is also Aligoté, 
 therefore all is good.
 
 I have a MySQL database, InnoDB table, charset utf-8.
 
 I set up my values in a dict called setValues with all the columns and their 
 respective unicode'd values ready to go
 
 I then do a table.insert(values=setValues).execute() and get this error.
 
 Traceback (most recent call last):
 File C:\Documents and Settings\wprince\Desktop\PY CODE 
 DEVELOPMENT\CESyncSQL\TEST_Sync.py, line 148, in SYNC_IT
   SyncFunction(ceDB, session, meta)
 File C:\Documents and Settings\wprince\Desktop\PY CODE 
 DEVELOPMENT\CESyncSQL\TEST_Sync.py, line 840, in SYNC_VarietiesOUT
   DAPDB_SetColumns(meta, 'varieties', 
 {'DescriptiveText':self.CEUnicode(tVarieties.ceVarietyText.value), 
 'FlavourText':self.CEUnicode(tVarieties.ceFlavourText.value), 
 'ImageURL':imageURL}, Variety=variety)
 File C:\Python26\lib\DAPDBHelpers.py, line 323, in DAPDB_SetColumns
   table.insert(values=setColumns).execute()
 File C:\Python26\lib\site-packages\sqlalchemy\sql\expression.py, line

[sqlalchemy] Char encoding..

[Warwick Prince]

Hi All

I thought I had Character Encoding licked, but I've hit something I can't 
work through.  Any help appreciated.

I have a legacy non SQL database that I read legacy data from (using cool 
Python code that emulates the old ISDB binary comms) and it reads a str which 
has Foreign language chars in it.  (French for example).  

So, firstly, I have   myStr = ''Aligot\xc3\xa9  which when printed is Aligoté. 
  So far so good.

I then convert that to unicode by  myUnicode = unicode(myStr, 'utf-8', 
errors='ignore') and get u'Aligot\xe9'.   This printed is also Aligoté, 
therefore all is good.

I have a MySQL database, InnoDB table, charset utf-8.

I set up my values in a dict called setValues with all the columns and their 
respective unicode'd values ready to go

I then do a table.insert(values=setValues).execute() and get this error.

Traceback (most recent call last):
  File C:\Documents and Settings\wprince\Desktop\PY CODE 
DEVELOPMENT\CESyncSQL\TEST_Sync.py, line 148, in SYNC_IT
SyncFunction(ceDB, session, meta)
  File C:\Documents and Settings\wprince\Desktop\PY CODE 
DEVELOPMENT\CESyncSQL\TEST_Sync.py, line 840, in SYNC_VarietiesOUT
DAPDB_SetColumns(meta, 'varieties', 
{'DescriptiveText':self.CEUnicode(tVarieties.ceVarietyText.value), 
'FlavourText':self.CEUnicode(tVarieties.ceFlavourText.value), 
'ImageURL':imageURL}, Variety=variety)
  File C:\Python26\lib\DAPDBHelpers.py, line 323, in DAPDB_SetColumns
table.insert(values=setColumns).execute()
  File C:\Python26\lib\site-packages\sqlalchemy\sql\expression.py, line 1217, 
in execute
return e._execute_clauseelement(self, multiparams, params)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1722, in 
_execute_clauseelement
return connection._execute_clauseelement(elem, multiparams, params)
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 4: 
ordinal not in range(128)

I know what the error means, I just don't know why I'm getting it.  The 
offending u'\xe9' character is in the DescriptiveText column.  DAPDB_SetColumns 
is a simple wrapper around an update/insert that builds up the 
table.insert(values=setColumns).execute() you see.

This is what setColumns looks like;
{'ImageURL': '', 'DescriptiveText': u'Carm\xe9n\xe8re is a red wine grape 
variety originally from Bordeaux, France. Having lost favor in France, the 
largest area planted with this variety is in now Chile. It only survived, due 
to growers believing it was Merlot. The vines were imported into Chil', 
'FlavourText': u'Carmenere is a full bodied red wine with approachable tannins 
and a combination of sweet berry fruit, savory pepper, smoke, tar, with a 
slight leafy character.\n', 'Variety': u'Carmenere'}

'Variety' is the primary key BTW.

What gives?  It feels like SQLA is encoding/decoding somewhere it shouldn't..

Cheers
Warwick

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Multiple request in the same controller causing problems with SQLAlchemy Session object

[Warwick Prince]

Hi Alan

We're also doing battle with this one..   One thing I did find was if I had 
large(ish) BLOB values, MySQL would go away.  I found that I had to tweak some 
settings on MySQL to allow for larger sizes.  (The default settings appear to 
be ridiculously small).   Let me know what else you find, as it is a very 
annoying issue for us as well.  BTW: What connector are you using?

Cheers
Warwick


Warwick Prince 
Managing Director 
mobile: +61 411 026 992 
skype: warwickprince  
 
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 

On 10/11/2010, at 7:56 AM, Alan Castro wrote:

 Hello,
 
 Latelly I've been running into this issue:
 
 OperationalError: (OperationalError) (2013, 'Lost connection to MySQL
 server during query') SELECT...
 
 To contextualize, I developed a Pylons application using
 scoped_session (default in pylons). And it was working until I created
 some extra processes of my application.
 
 Thanks
 
 Alan
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Connection / disconnect / Pool

[Warwick Prince]

Excellent.  That'll be it for sure.  I have a bunch of result sets that I 
assumed would just go away.. :-)

Cheers
Warwick

Warwick A. Prince
Managing Director
Mushroom Systems International P/L

On 04/11/2010, at 1:11 AM, Michael Bayer mike...@zzzcomputing.com wrote:

 
 On Nov 2, 2010, at 11:46 PM, Warwick Prince wrote:
 
 Hi Michael
 
 I have an issue I can't fathom regarding Pools.I'm doing testing and hit 
 the following error at exatly the same point each time;
 
   File C:\Documents and Settings\wprince\Desktop\PY CODE 
 DEVELOPMENT\pyDAP\DAPForm.py, line 3805, in _processQuery
 self._queryCount = self._query.count()
   File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1888, 
 in count
 should_nest = should_nest[0]
   File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1924, 
 in _col_aggregate
 mapper=self._mapper_zero())
   File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 740, 
 in scalar
 return self.execute(clause, params=params, mapper=mapper, **kw).scalar()
   File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 734, 
 in execute
 return self._connection_for_bind(engine, close_with_result=True).execute(
 TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection 
 timed out, timeout 30
 
 I'm creating a basic create_engine using defaults for the pool settings 
 (as you can tell).   Within each Thread that does the connection, I create a 
 new engine, metadata and scoped session.  That thread then does a basic 
 query (in this case NOT using a session at all, just a direct table query) 
 and then I do everything I can think of to close the connections.   I do 
 this;
 
 engine.dispose()
 session.commit()
 session.close()
 
 and then the class instance that holds all these is removed and the thread 
 terminates.   All works fine (i.e. no errors or exceptions) until I reach 
 the 10 overflow limit and then it dies.   Each destroy of an engine and 
 recreate of an engine is at least a second apart so it's not being thrashed 
 at all.
 
 What am I missing?
 
 The error is local to a single engine.   So engine.dispose() has no impact on 
 the issue.   While a Session will maintain a single checked out connection, 
 result sets that are returned by engine.execute(some select) and some 
 select.execute() also do so, so be sure to fully exhaust and/or close() 
 those result sets as well.
 
 
 
 
 
 Cheers
 Warwick
 
 Warwick Prince 
 Managing Director 
 mobile: +61 411 026 992 
 skype: warwickprince  
  
 phone: +61 7 3102 3730 
 fax:  +61 7 3319 6734 
 web: www.mushroomsys.com 
  
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Connection / disconnect / Pool

[Warwick Prince]

Hi Michael

I have an issue I can't fathom regarding Pools.I'm doing testing and hit 
the following error at exatly the same point each time;

  File C:\Documents and Settings\wprince\Desktop\PY CODE 
DEVELOPMENT\pyDAP\DAPForm.py, line 3805, in _processQuery
self._queryCount = self._query.count()
  File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1888, in 
count
should_nest = should_nest[0]
  File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1924, in 
_col_aggregate
mapper=self._mapper_zero())
  File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 740, in 
scalar
return self.execute(clause, params=params, mapper=mapper, **kw).scalar()
  File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 734, in 
execute
return self._connection_for_bind(engine, close_with_result=True).execute(
TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed 
out, timeout 30

I'm creating a basic create_engine using defaults for the pool settings (as 
you can tell).   Within each Thread that does the connection, I create a new 
engine, metadata and scoped session.  That thread then does a basic query (in 
this case NOT using a session at all, just a direct table query) and then I do 
everything I can think of to close the connections.   I do this;

engine.dispose()
session.commit()
session.close()

and then the class instance that holds all these is removed and the thread 
terminates.   All works fine (i.e. no errors or exceptions) until I reach the 
10 overflow limit and then it dies.   Each destroy of an engine and recreate of 
an engine is at least a second apart so it's not being thrashed at all.

What am I missing?

Cheers
Warwick

Warwick Prince 
Managing Director 
mobile: +61 411 026 992 
skype: warwickprince  
 
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Secialists question: how to do implement stock-management

[Warwick Prince]

Hi Dan

This is a little off topic for this group, however, I consider myself a 
specialist in the area you question, so I'm delighted to be on the helping 
end for a change :-)

We can continue this one-on-one outside the group.  Send your questions to me.  
 I prefer Skype for this type of thing - see my Skype id below.

Cheers
Warwick

Warwick Prince 
CEO
mobile: +61 411 026 992 
skype: warwickprince  
 
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 

On 29/10/2010, at 8:12 PM, Dan @ Austria wrote:

 Hi,
 
 i have a question to database/design specialist. How can (should!) i
 implement a stock management system in sql-alchemy and python? I get
 the following data from another system via files
 
 - movements: bills from a scanner at a cash deck
 - movements: from goods-receipt
 
 Of course i have also master data on an per article basis.
 
 What i want do is keep charge of the stock available in our warehouse.
 Should i built a warehouse table with
 [ article / amount of article available ]
 
 and update the articles with an update statement like UPDATE
 warehouse_table SET amount = amount - (bill amount) where article =
 bill_article ? Would that be a good solution?
 
 Is there any literature or reference implementations around? Any hint
 is much apreciated. Although i have written a couple of database
 applications, i never had the probleme to change a field (amount field
 per article) so often. I guess there is a lot of data comming in ...
 
 Thanks in advance,
 Dan
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Result of a table.update()

[Warwick Prince]

Hi All

I'm using 0.6.4 under Windoze with MySQL,  Python 2.6.4 and I had code that I 
thought worked before (0.6.3)  - which appeared to break due to this issue.. I 
could be wrong on this point so I'll just get to the crux of the matter...

I have a result = table.update(whereClause, values=someValuesDict).execute()

The table is updated correctly, however, the resultproxy object I receive as 
'result' appears to have no members.   If I fetchone() or fetchall() I simply 
get a None result.  Is this correct?

If this IS correct, how is the best way to tell if the update was a success?   
I tried putting bad data in the whereClause and it simply did nothing to the 
database, but my resultproxy was the same.  No Exceptions raised in either case?

Please enlighten me..

Cheers
Warwick


Warwick Prince 
Managing Director 
mobile: +61 411 026 992 
skype: warwickprince  
 
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Result of a table.update()

[Warwick Prince]

HI Michael

Thanks for the info.  .rowcount was the missing link I needed.  I'll also play 
with RETURNING as well as I thought I had tried that at one stage and saw no 
change in the SQL emitted, so moved on.   I'll let you know.

Cheers
Warwick

 an UPDATE statement returns no rows unless RETURNING was used to return 
 columns from those rows that were updated.   When an UPDATE or DELETE is 
 emitted, result.rowcount contains the number of rows that were matched by the 
 statement's criterion.
 
 On Oct 21, 2010, at 2:06 AM, Warwick Prince wrote:
 
 Hi All
 
 I'm using 0.6.4 under Windoze with MySQL,  Python 2.6.4 and I had code that 
 I thought worked before (0.6.3)  - which appeared to break due to this 
 issue.. I could be wrong on this point so I'll just get to the crux of the 
 matter...
 
 I have a result = table.update(whereClause, values=someValuesDict).execute()
 
 The table is updated correctly, however, the resultproxy object I receive as 
 'result' appears to have no members.   If I fetchone() or fetchall() I 
 simply get a None result.  Is this correct?
 
 If this IS correct, how is the best way to tell if the update was a success? 
   I tried putting bad data in the whereClause and it simply did nothing to 
 the database, but my resultproxy was the same.  No Exceptions raised in 
 either case?
 
 Please enlighten me..
 
 Cheers
 Warwick
 
 
 Warwick Prince 
 Managing Director 
 mobile: +61 411 026 992 
 skype: warwickprince  
  
 phone: +61 7 3102 3730 
 fax:  +61 7 3319 6734 
 web: www.mushroomsys.com 
 
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 'MySQL server has gone away')

[Warwick Prince]

HiTimmy

What OS are you running under for each?

Cheers
Warwick

Warwick Prince 
Managing Director 
mobile: +61 411 026 992 
skype: warwickprince  
 
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 


On 15/10/2010, at 7:48 AM, Timmy Chan wrote:

 sorry if this is addressed, but i'm running
 
 apache2
 SQLAlchemy 0.5.8
 Pylons 1.0
 Python 2.5.2
 
 and on a simple page (just retrieve data from DB), I get:
 
 Error - class 'sqlalchemy.exc.OperationalError': (OperationalError) (2006, 
 'MySQL server has gone away')
 
 every few other requests, not after a long time as other posts I've searched 
 for.  I still added
 
 sqlalchemy.pool_recycle = 1800
 
 but that did not fix the issue.  After a fresh apache restart, every 5th or 
 6th requests gets a 500 from the above error.
 
 thanks
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Session problems

[Warwick Prince]

Dear All

I'm having a very strange issue with Sessions that I'm hoping someone can
guide me on;

I have a situation where a large body of code spawns new processes
(multiprocessing.process).  The core design manages 3 (possible) database
connections, and I've used a unique session for each.  The session is create
thus within the new process (i.e. not handed in)

Session = sessionmaker()#  This is global

class myManager(object):
# I use a manager class to manage the engines, MetaData and Sessions of
the 3 possible DB's   I create new sessions in here like this

  self.session1 = Session(bind = engine1)
  self.session2 = Session(bind = engine2)
  self.session3 = Session(bind = engine3)

All this works fine and all testing up to this point has been perfect.  Now,
when I start to load test and create more than one concurrent process, I'm
getting some form of corruption of my connections to the Database
(mysql+mysqlconnector) !?

All NON session based access to the server still work, but after I start the
second process, BOTH processes lost the ability to use the sessions create
(ones that where running and working stop immediately the second process
starts) with this;

(InterfaceError) 2055: Lost connection to MySQL server at '192.168.50.2:3306',
system error: 10054 u'SELECT products..

The connection is NOT lost really, as I can still do NON session based
queries using the same engine that the session is bound to.

As far as I can see, since the code is running in a completely different
process, how can they be interacting with each other and breaking the
connection to mySQL?

I've tried using scoped_session but could not work out how to have the 3
sessions as above all in the same context - so gave up.   As far as I can
see though on my limited understanding of the Session process, I have
isolated everything so there should be no issues.

Any ideas?

Cheers
Warwick

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Session problems

[Warwick Prince]

Hi All

More details on the session issue;

Please note, the test I just did was this;

Restart the mySQL server.

Start Process #1 and create a session and use it (works fine)
Start Process #2 (Identical code to #1, just another instance).

Go back to #1 and attempt to do another query and I get this;

  File Z:\warwickprince On My Mac\Desktop\Code Developement\MSI\Clear
Enterprise\DAP2 Python\trunk\DAPForm.py, line 2556, in _moveToRow
self._formInstance._currentRowProxy = self._query[self._currentPosition]
# Get the one at that position
  File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1446,
in __getitem__
return list(self[item:item+1])[0]
  File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1444,
in __getitem__
return list(res)
  File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1604,
in __iter__
return self._execute_and_instances(context)
  File C:\Python26\lib\site-packages\sqlalchemy\orm\query.py, line 1609,
in _execute_and_instances
mapper=self._mapper_zero_or_none())
  File C:\Python26\lib\site-packages\sqlalchemy\orm\session.py, line 701,
in execute
clause, params or {})
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1194,
in execute
params)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1272,
in _execute_clauseelement
parameters=params
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1380,
in __create_execution_context
connection=self, **kwargs)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line
381, in __init__
self.cursor = self.create_cursor()
  File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line
523, in create_cursor
return self._connection.connection.cursor()
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 920,
in connection
Can't reconnect until invalid 
InvalidRequestError: Can't reconnect until invalid transaction is rolled
back

BTW: self._query is self._Session.Query(Product)

I can't quite get my head around the scope of sessionmaker() yet..  I've
tried putting Session = sessionmaker() as a global to the entire codebase
(works but has same problem)  I've put it inside the process code so that it
can not share any state or memory with the other one(s) (This is the current
config) and yet it's immediately trashing the first session as soon as I
create a second.   I'm only *reading* from either of the sessions at this
stage, so there is actually no pending data or writes being done
what-so-ever, so I don't know what transaction should be rolled back or why
it's invalid.

The connection (created in the same myManager class) used for direct
table.select() operations still works fine even after the above error
happens.

Importantly..  I can completely close all my processes and cold start my
code - and this error continues until I restart mySQL !  i.e. NO session
will work again, but I can do basic queries.  What the?!

Hope someone can shed some light on this one :-S

BTW: It someone says You should be using scoped_session..  Please explain
how to have either more than one session in the same context (I use three
for a possible three different binds) or how to create one session that I
can bind to more than one engine, and not have to know in advance all
possible tables I might want to use on each engine..This is a generic
session that I want to be able to use for all tables in the bound engine -
thus three sessions given that is the maximum possible choices of DB in this
scenario. What if I don't bind a session to any engine..  Does it then
follow the bind on the mapped table class for a given query?

Cheers
Warwick


On 6 October 2010 20:02, Warwick Prince warwi...@mushroomsys.com wrote:

 Hi Chris

 It's simply trapped as a

 except Exception as message:

 I'll see what I can do - Just a mo..

 Cheers
 Warwick


 On 6 October 2010 19:54, Chris Withers ch...@simplistix.co.uk wrote:

 On 06/10/2010 10:46, Warwick Prince wrote:

 (InterfaceError) 2055: Lost connection to MySQL server at
 '192.168.50.2:3306 http://192.168.50.2:3306', system error: 10054
 u'SELECT products..


 ...it would be interesting to see the rest of that error message...

 Chris




-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Session problems

[Warwick Prince]

Hi All

Just incase anyone was wondering..  I found the cause of my session issues.
(Hooray!)

The Application is served by a home grown python HTTP server which works
just fine - however, I found that it had an implementation of threading POOL
to handle requests, rather than creating a new thread for each request.  As
it is a dedicated and task specific HTTP server, I had added some DB work
inside the code that handles POST data (i.e. before it despatched the job to
the waiting processes to deal with).   I had forgotten that the POST was
being handled by a thread that was not torn down at the end - and therefore
the next POST request ran in the same context!   Hence broken transactions
on the MySQL side, and then the subsequent inability to serve further
requests.   Changed one line of code in the HTTP server to change it back to
non pooled and it all worked perfectly :-)

Thanks Michael to your invaluable insights into the workings of SA, as it
was only a few minutes after reading your response that the answer was
obvious. :-)

Cheers
Warwick


On 7 October 2010 00:20, Michael Bayer mike...@zzzcomputing.com wrote:


 On Oct 6, 2010, at 6:46 AM, Warwick Prince wrote:


 I can't quite get my head around the scope of sessionmaker() yet..  I've
 tried putting Session = sessionmaker() as a global to the entire codebase
 (works but has same problem)


 do you mean scoped_session here ?  sessionmaker is just a constructor for a
 new Session.It doesn't hold onto anything.

 scoped_session OTOH is nothing more than a thread local variable.
 Background on this concept is available here:
 http://docs.python.org/library/threading.html#threading.local .The
 remove() call removes the current thread local's context.   If your app were
 single threaded, you could replace it with a single Session object, where
 you just call close() instead of remove() - it wouldn't be very different.

 Within the context of a multiprocess, single-threaded application,
 threading.local doesn't have any effect - there's just one thread.

 Importantly..  I can completely close all my processes and cold start my
 code - and this error continues until I restart mySQL !  i.e. NO session
 will work again, but I can do basic queries.  What the?!


 I've seen this happen with PG when we are testing out two-phase
 transactions.You might want to ask on some MySQL forums what queries you
 might do to look at current lock/transaction state.


 BTW: It someone says You should be using scoped_session..  Please explain
 how to have either more than one session in the same context (I use three
 for a possible three different binds)


 yeah I actually have an app with a couple of scoped sessions, since there
 are two different databases and operations generally proceed with one or the
 other.

 or how to create one session that I can bind to more than one engine, and
 not have to know in advance all possible tables I might want to use on each
 engine..


 If the table metadata is bound to an engine, then the session doesn't need
 to be bound.  I.e. if tables A, B, C on metadata X are bound to engine P,
 tables D, E, F on metadata Y are bound to engine Q, you just use the
 Session, and it will handle the two engines as needed.

 If you really want total control, using some ruleset that's not quite as
 simple as table-metadata-engine, you can subclass Session and override
 get_bind().   I've never recommended that to anyone, but I put it out there
 just to help de-mystify the situation.   Its just one call that takes in a
 mapper, returns an engine.

  --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Session problems

[Warwick Prince]

Hi Michael

I'm still having a couple of issues with the sessions, but I'm now starting to 
suspect mysqlconnector..  

For completeness, could you please let me know if there is anything specific I 
need to do to close down a session / connection / engine etc if I want to 
completely release it.

I see session.close(), but that appears to be more about committing etc, and I 
can't see anything specific I need to do to an engine etc.  Basically, I just 
want to ensure that I'm doing everything correctly when I drop a session and 
kill the thread that it was in.

Also, If I'm NOT creating lots of sessions for short periods of time (i.e. web 
services)  (which I'm not) do I need to consider pools for any specific reason? 
 They appear to be more about scaling that sort of situation.  I'm basically 
creating a session and hanging on to it - doing lots of queries, updates etc 
with lots of commit/roll back, then dropping the session and exiting some time 
later -- Is my interpretation of session use correct?

I'll play with other avenues of investigation before bringing the current 
session issues to the table.  ;-)

Cheers
Warwick


 
 On Oct 6, 2010, at 6:46 AM, Warwick Prince wrote:
 
 
 I can't quite get my head around the scope of sessionmaker() yet..  I've 
 tried putting Session = sessionmaker() as a global to the entire codebase 
 (works but has same problem)
 
 do you mean scoped_session here ?  sessionmaker is just a constructor for a 
 new Session.It doesn't hold onto anything.
 
 scoped_session OTOH is nothing more than a thread local variable. 
 Background on this concept is available here:  
 http://docs.python.org/library/threading.html#threading.local .The 
 remove() call removes the current thread local's context.   If your app were 
 single threaded, you could replace it with a single Session object, where you 
 just call close() instead of remove() - it wouldn't be very different.
 
 Within the context of a multiprocess, single-threaded application, 
 threading.local doesn't have any effect - there's just one thread.
 
 Importantly..  I can completely close all my processes and cold start my 
 code - and this error continues until I restart mySQL !  i.e. NO session 
 will work again, but I can do basic queries.  What the?!
 
 I've seen this happen with PG when we are testing out two-phase transactions. 
You might want to ask on some MySQL forums what queries you might do to 
 look at current lock/transaction state.
 
 
 BTW: It someone says You should be using scoped_session..  Please explain 
 how to have either more than one session in the same context (I use three 
 for a possible three different binds)
 
 yeah I actually have an app with a couple of scoped sessions, since there are 
 two different databases and operations generally proceed with one or the 
 other.
 
 or how to create one session that I can bind to more than one engine, and 
 not have to know in advance all possible tables I might want to use on each 
 engine..  
 
 If the table metadata is bound to an engine, then the session doesn't need to 
 be bound.  I.e. if tables A, B, C on metadata X are bound to engine P, tables 
 D, E, F on metadata Y are bound to engine Q, you just use the Session, and it 
 will handle the two engines as needed.   
 
 If you really want total control, using some ruleset that's not quite as 
 simple as table-metadata-engine, you can subclass Session and override 
 get_bind().   I've never recommended that to anyone, but I put it out there 
 just to help de-mystify the situation.   Its just one call that takes in a 
 mapper, returns an engine.  
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Simple Join failing

[Warwick Prince]

Hi All

I have what I hope is a very simple question;

Just started experimenting with joins, so I tried a very basic test and got
a fail that I don't understand.  It appears that SA is creating bad SQL, but
I'm sure it's something I'm missing..  Here's what I did;

I have two tables.  products and product_prices.  There is a one to many
relationship based on Foreign Keys of Group and Code   Both tables have
columns Group and Code and they are also the primary of each.

I do this;

e = an Engine (MySQL connector)
m = MetaData(e)

prod = Table('products', m, autoload=True)
price = Table('product_prices, m, autoload=True
# These tables are both fine and load correctly

# I want to build up my query generatively, so..

# Note that I'm selecting specific columns, and both sets of Foreign Keys
are in the selected columns (not that I believe I should need to do that)
q = prod.select().with_only_columns(['products.Group', 'products.Code',
'product_prices.Group', 'product_prices.Code', 'product_prices.ListPriceEx',
'product_prices.ListPriceInc'])

q = q.join(price)

# I get this error;
ArgumentError: Can't find any foreign key relationships between 'Select
object' and 'product_prices'.(They do exists BTW)

So, I remove my .*with_only_columns* and try again

q = prod.select()

q = q.join(price)

# OK - no errors so far..  BUT...

print q1 gives me this;

*(*SELECT products.`Group` AS `Group`, products.`Code` AS `Code`,
products.`Description` AS `Description`,
...lots of other columns removed for your viewing pleasure...
FROM products*)* INNER JOIN product_prices ON `Group` =
product_prices.`Group` AND `Code` = product_prices.`Code`

Note the ( ) around the (SELECT . products)

When I execute that query it fails and gives me this;

Traceback (most recent call last):
  File interactive input, line 1, in module
  File string, line 1, in lambda
  File C:\Python26\lib\site-packages\sqlalchemy\util.py, line 1780, in
warned
return fn(*args, **kwargs)
  File C:\Python26\lib\site-packages\sqlalchemy\sql\expression.py, line
1290, in execute
return e._execute_clauseelement(self, multiparams, params)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1798,
in _execute_clauseelement
return connection._execute_clauseelement(elem, multiparams, params)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1272,
in _execute_clauseelement
parameters=params
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1380,
in __create_execution_context
connection=self, **kwargs)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line
342, in __init__
raise exc.*ArgumentError(Not an executable clause: %s % compiled)*

So, I have two basic questions;

1) Why did the first error occur with the FK's simply because I limited the
columns returned?
2) What's wrong with the join I'm doing and why is SA creating SQL that it
can't use?

I'm guessing I'm doing SOMETHING wrong,  so the goal here is simple -This is
what I WANT to do;

Create a query that allows me to dictate the columns returned from a join of
two or more tables giving me a result set with access to all the columns
I've nominated.

I've looked hard in the docs and Google, and I guess everyone thinks this is
too basic to actually SHOW you how to do it!  ;-)I took the ( ) out with
a cut and paste and dropped the SQL into an e.execute('SELECT.) and it
worked fine, so it's really close it just appears to be adding erroneous
brackets.

I'm running 0.6.4 BTW.

Thanking you in advance.

Cheers
Warwick

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Batch Delete with ORM

[Warwick Prince]

Hi All

Just my 2c;

The original question was why is SA doing the select before it does the
delete?  and then the comment was added that he would have done a simple
count instead..  It appears that he was not aware that the DELETE could
return the count as well (indirectly) so in actual fact, NEITHER the SELECT
count OR the SELECT that SA inserts in front of the DELETE appear to be
required.

So, back to the thread of the question - Why is SA doing the query that it
does BEFORE it does the DELETE?  It's purpose is not obvious.

Hope that helps!? :-)


On 5 October 2010 18:48, Chris Withers ch...@simplistix.co.uk wrote:

 On 04/10/2010 13:16, Mark Erbaugh wrote:

 If I were doing this in SQL, I would to the first command as

 SELECT count(*)
 FROM period
 WHERE period.cycle = ?


 Why would you do this first?

 Chris

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Simple Join failing

[Warwick Prince]

Hi Simon

Thanks for that - I knew it was something wrong with the approach but simply
could not pick it!Back to the test bench for another go :-)

Cheers
Warwick

P.S.  OK - I have to ask - when and how (why?) do I do the .join on the
query? ;-)


On 5 October 2010 19:41, King Simon-NFHD78 simon.k...@motorola.com wrote:

 Warwick Prince wrote:
 
  Hi All
 
  I have what I hope is a very simple question;
 
  Just started experimenting with joins, so I tried a very basic test
  and got a fail that I don't understand.  It appears that SA is
  creating bad SQL, but I'm sure it's something I'm missing..  Here's
  what I did;
 
  I have two tables.  products and product_prices.  There is a one to
  many relationship based on Foreign Keys of Group and Code   Both
  tables have columns Group and Code and they are also the primary of
  each.
 
  I do this;
 
  e = an Engine (MySQL connector)
  m = MetaData(e)
 
  prod = Table('products', m, autoload=True)
  price = Table('product_prices, m, autoload=True
  # These tables are both fine and load correctly
 
  # I want to build up my query generatively, so..
 
  # Note that I'm selecting specific columns, and both sets of Foreign
  Keys are in the selected columns (not that I believe I should need to
  do that)
  q = prod.select().with_only_columns(['products.Group',
  'products.Code', 'product_prices.Group', 'product_prices.Code',
  'product_prices.ListPriceEx', 'product_prices.ListPriceInc'])
 
  q = q.join(price)
 
  # I get this error;
  ArgumentError: Can't find any foreign key relationships between
  'Select object' and 'product_prices'.(They do exists BTW)
 
  So, I remove my .with_only_columns and try again
 
  q = prod.select()


 Here you are creating a Select object (ie SELECT all columns FROM
 products)


 
  q = q.join(price)
 

 Now you are joining that Select object with another table

 ie. (SELECT all columns FROM products) JOIN price ON join condition

 The extra parentheses are there because you are joining a SELECT with a
 table.

 Instead, you want to join the tables together:

  prod.join(price)

 To select from that, you can use the standalone select function:

 http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.e
 xpression.select

 eg.

 select([products.c.Group, products.c.Code, price.c.ListPriceEx],
   from_obj=[prod.join(price)])

 Hope that helps,

 Simon



 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Simple Join failing

[Warwick Prince]

Hi Simon

Thanks for your help.  It's amazing what a tiny hint in the right direction
can do..  Between these emails, I've made a proof on concept, and am now
implementing the code in the real app.   So easy when I'm not blocked by a
warped vision of what I'm doing.

Funny, looking back at the docs I can now clearly see TABLE.join T A B L
E.join, not query.join...I'm SURE that wasn't there before  ;-D

Thanks again.

Cheers
Warwick


On 5 October 2010 20:39, King Simon-NFHD78 simon.k...@motorola.com wrote:

 Warwick Prince wrote:
 
  Hi Simon
 
  Thanks for that - I knew it was something wrong with the approach but
  simply could not pick it!Back to the test bench for another go :-)
 
  Cheers
  Warwick
 
  P.S.  OK - I have to ask - when and how (why?) do I do the .join on
  the query? ;-)
 

 In SQL, you can treat a query just like a table, so you can join 2
 queries together, or join a query to another table. For example:

 SELECT *
 FROM
   (SELECT a, b FROM table_1) as q1
 INNER JOIN
   (SELECT c, d FROM table_2) as q2
 ON q1.b = q2.c

 That example is not very helpful - it could easily be rewritten as a
 single SELECT, but I hope you see that the subqueries can be as
 complicated as you like.

 The object that you were originally producing with your 'q.join(price)'
 wasn't a Select object, but a Join - something that you can select from.
 You could write something like this:

 # JOIN the price table with a query on the products table:
 j = prod.select().join(price)

 # SELECT from that JOIN:
 q = select(some_columns, from_obj=[j])

 This almost certainly isn't what you wanted in your situation, but there
 are plenty of cases where subqueries are very useful.

 Hope that helps,

 Simon

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Locking strategy

[Warwick Prince]

Hi All

I would like some advice / best practice on the following basic problem
please.  I'm new to SQL so am groping with some things that used to be
basic. :-(

I've looked around in SA and have only found a few small notes on locking.
 There is a for_update but when I try this it appears to be locked out for
me as well!  Perhaps the answer is here and I'm missing something?

I want a standard ability to have a user who is editing a row on some GUI to
have control of that row through a lock. Pessimistic locking.   I've read
various posts talking of having another table that stores the primary key
and table name which is written to keep the lock table and therefore all
processes must check this first..  but what if they don't?

Is there an SA way to deal with this, or do I start inventing?

This is the desired outcome;

Code can lock a row and leave it locked until unlocked or the session ends.
Other attempts to write to that row will receive a locked exception.
Some way of enforcing access to the DB for writes must use locking -
otherwise it's a strange opt in approach.  i.e. it's only locked if you
bother to check if it's locked!

If what I read is true, and I need to create my own lock table, is there a
nice generic way (i.e. I don't know or care what table schema is being
locked via my handy lock table) of getting the primary key of the row in
question to pass to the lock_row(yourKey).   Should I use the primary key,
or is there some other unique way of identifying it within a table?

In this case, I'm running on MySQL, but I would prefer a DB agnostic
solution for this project.

Cheers
Warwick

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] SQL / SQLAlchemy'centric approach to problem

[Warwick Prince]

Hi All

I'm new to SQLAlchemy (love it) and also new to SQL in general, although I have 
25 years experience in a range of obscure databases.  I have what I hope will 
be a simple question as I believe I'm missing a critical understanding of some 
of the underlying SQL machinery.

This is the situation (dumbed down for the example);

1) I have selected a row from a table somewhere else and therefore have a 
session.query resulted mapped class instance of that row (Or the primary 
key(s), which ever is most useful).   We'll call this row Fred.
2) I have an ordered_by x result of a query on the same table (with many 
rows) that I wish to navigate with first/next/prev/last type controls.  This I 
have implemented using the cool result[position] syntax and that all works 
fine. (Gets a little slow over several million rows, but that's outside the 
scope of the design requirement)

My problem is this;  I want to find out what position my recordFred is within 
the larger result set.. so that I can then locate myself onto fred and move 
to the next or previous row in that query as normal.  To do that, I need the 
position value so I can do the slice.

Sure, I could do an .all() on the query and then loop through until I found 
fred counting as I go, but that's horrible and smacks of you don't know what 
you are doing..

Picture it like this;

table has rows 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.   I query that and order it by 
something else which gives me an ordered result of

1, 3, 5, 7, 9, 2, 4, 6, 8, 0

I know that fred's key is 9, so I want to determine that in the ordered list 
(above), 9 is the 4th (zero based) element.  I can then locate fred by saying 
result[4] and next is simply result[5] etc.

Is there a good way?  I suspect I could do something with a secondary query 
that would produce the results 1, 3, 5, 7 and then I could count that and that 
would be my position, but it's all sounding a little amateurish to me.

Any advice would be most appreciated.  :-)

Cheers
Warwick


Warwick Prince 
Managing Director 
mobile: +61 411 026 992 
skype: warwickprince  
 
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Sudden Connection issues

[Warwick Prince]

Hi All

Just to put closure on this issue, I finally sorted it out as a bug in the 
connector.  This has now been resolved and the latest build has the correction. 
:-)  As a matter of interest, the line db.conn.protocol should have been 
db.protocol..   Anyway - all's well that ends well.. :-)

Cheers
Warwick
 
 On Aug 27, 2010, at 4:39 AM, Warwick Prince wrote:
 
 Hi Michael
 
 OK, I've invested (wasted?) my entire day on this connection issue and have 
 the following to report.  Hopefully, the hints I've managed to find may 
 trigger something with you that will point me in the right direction.
 
 In recap; the issue was I could not get a simple engine to connect to the 
 MySQL database.  This used to work on this server with the current 
 configuration and simply seemed to stop working.
 
 e = create_engine('mysql+mysqlconnector://user:passw...@127.0.0.1/testdb', 
 encoding='utf8', echo=False)
 e.connect()
 
 (Traceback below from previous messages)
 
 So, I followed all the code through and found that it actually failing at 
 the point where in cursor.py it's attempting to create a new cursor.
 
 def set_connection(self, db):
 try:
 if isinstance(db.conn.protocol,protocol.MySQLProtocol):
 self.db = weakref.ref(db)
 if self not in self.db().cursors:
 self.db().cursors.append(self)
 except Exception as message:
 raise errors.InterfaceError(errno=2048)
 
 The db appears to be correct (I looked), protocol.MySQLProtocol appears to 
 be correct BUT db.conn = None !  Therefore it raises 2048
 
 So, after many hours I can not find where db.conn is set or what it is 
 supposed to be in the first place!Note: I have a virtually identical 
 setup on my XP VM, and the same example of engine.connect() works fine.
 
 What I'm looking for is a little info on what db.conn should be, where is it 
 set, how can it be NOT set etc.Your help would be most appreciated.
 
 Incidentally, all was not a waste of time as I traversed nearly ALL of the 
 SA code today and picked up a few nice tips..  Thanks! :-)
 
 Well, that above is not part of SQLAlchemy.   I would assume, since its 
 called cursor.py and is dealing with MySQL internals, that its part of MySQL 
 connector, so you should email on their list (and also you can test things 
 without SQLAlchemy at all, just use a script with MySQL connector directly).
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Sudden Connection issues

[Warwick Prince]

Hi Michael

OK, I've invested (wasted?) my entire day on this connection issue and have the 
following to report.  Hopefully, the hints I've managed to find may trigger 
something with you that will point me in the right direction.

In recap; the issue was I could not get a simple engine to connect to the MySQL 
database.  This used to work on this server with the current configuration and 
simply seemed to stop working.

e = create_engine('mysql+mysqlconnector://user:passw...@127.0.0.1/testdb', 
encoding='utf8', echo=False)
e.connect()

(Traceback below from previous messages)

So, I followed all the code through and found that it actually failing at the 
point where in cursor.py it's attempting to create a new cursor.

def set_connection(self, db):
try:
if isinstance(db.conn.protocol,protocol.MySQLProtocol):
self.db = weakref.ref(db)
if self not in self.db().cursors:
self.db().cursors.append(self)
except Exception as message:
raise errors.InterfaceError(errno=2048)

The db appears to be correct (I looked), protocol.MySQLProtocol appears to be 
correct BUT db.conn = None !  Therefore it raises 2048

So, after many hours I can not find where db.conn is set or what it is supposed 
to be in the first place!Note: I have a virtually identical setup on my XP 
VM, and the same example of engine.connect() works fine.

What I'm looking for is a little info on what db.conn should be, where is it 
set, how can it be NOT set etc.Your help would be most appreciated.

Incidentally, all was not a waste of time as I traversed nearly ALL of the SA 
code today and picked up a few nice tips..  Thanks! :-)

Cheers
Warwick




Warwick Prince 
Managing Director 
mobile: +61 411 026 992 
skype: warwickprince  
 
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 

On 26/08/2010, at 3:37 PM, Michael Bayer wrote:

 
 On Aug 25, 2010, at 11:09 PM, Warwick Prince wrote:
 
 HI Michael
 
 Excellent.  Thanks for the pointers - I'll investigate further and get back 
 to you.  This is really strange as I do not believe that I changed anything 
 from the point it all worked, to the point at which it didn't!   (I'm sure 
 there will be something however - there always is..).  :-)   I'll let you 
 know how I go.   When I get it going again, I'll hit you with the REAL 
 questions I have..  
 
 those connection issues are fixed in tip (not your MySQL problem, though).
 
 
 
 Cheers
 Warwick
 
 On 26/08/2010, at 12:43 PM, Michael Bayer wrote:
 
 
 On Aug 25, 2010, at 10:11 PM, Warwick Prince wrote:
 
 Hi All
 
 This is my first post here, so I wish it were a little more spectacular..  
 :-)
 
 I have been working happily with SA 0.6.x on Windows 2003 server with 
 MySQL and the Sun Python Connector.  I have an identical config running 
 (and working) on XP.
 
 I was testing my code which had been working perfectly, and suddenly 
 started getting this message which I had never seen before;
 
 Traceback (most recent call last):
 File pyshell#3, line 1, in module
 e.connect()
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1731, 
 in connect
 return self.Connection(self, **kwargs)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 821, 
 in __init__
 self.__connection = connection or engine.raw_connection()
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1787, 
 in raw_connection
 return self.pool.unique_connection()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 135, in 
 unique_connection
 return _ConnectionFairy(self).checkout()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 329, in 
 __init__
 rec = self._connection_record = pool.get()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 177, in get
 return self.do_get()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 692, in 
 do_get
 con = self.create_connection()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 138, in 
 create_connection
 return _ConnectionRecord(self)
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 218, in 
 __init__
 l.first_connect(self.connection, self)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\strategies.py, line 
 145, in first_connect
 dialect.initialize(c)
 File C:\Python26\lib\site-packages\sqlalchemy\dialects\mysql\base.py, 
 line 1774, in initialize
 default.DefaultDialect.initialize(self, connection)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line 
 144, in initialize
 self._get_default_schema_name(connection)
 File C:\Python26\lib\site-packages\sqlalchemy\dialects\mysql\base.py, 
 line 1739, in _get_default_schema_name
 return connection.execute('SELECT DATABASE()').scalar()
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1157, 
 in execute
 params)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1252, 
 in _execute_text

Re: [sqlalchemy] Sudden Connection issues

[Warwick Prince]

Hi Michael

Sorry about that - it had been a long day and I didn't realise I was quoting 
code from the connector!   I'll get onto them and see if they can help.

Cheers
Warwick

Warwick Prince 
Managing Director 
mobile: +61 411 026 992 
skype: warwickprince  
 
phone: +61 7 3102 3730 
fax:  +61 7 3319 6734 
web: www.mushroomsys.com 


On 27/08/2010, at 11:40 PM, Michael Bayer wrote:

 
 On Aug 27, 2010, at 4:39 AM, Warwick Prince wrote:
 
 Hi Michael
 
 OK, I've invested (wasted?) my entire day on this connection issue and have 
 the following to report.  Hopefully, the hints I've managed to find may 
 trigger something with you that will point me in the right direction.
 
 In recap; the issue was I could not get a simple engine to connect to the 
 MySQL database.  This used to work on this server with the current 
 configuration and simply seemed to stop working.
 
 e = create_engine('mysql+mysqlconnector://user:passw...@127.0.0.1/testdb', 
 encoding='utf8', echo=False)
 e.connect()
 
 (Traceback below from previous messages)
 
 So, I followed all the code through and found that it actually failing at 
 the point where in cursor.py it's attempting to create a new cursor.
 
 def set_connection(self, db):
 try:
 if isinstance(db.conn.protocol,protocol.MySQLProtocol):
 self.db = weakref.ref(db)
 if self not in self.db().cursors:
 self.db().cursors.append(self)
 except Exception as message:
 raise errors.InterfaceError(errno=2048)
 
 The db appears to be correct (I looked), protocol.MySQLProtocol appears to 
 be correct BUT db.conn = None !  Therefore it raises 2048
 
 So, after many hours I can not find where db.conn is set or what it is 
 supposed to be in the first place!Note: I have a virtually identical 
 setup on my XP VM, and the same example of engine.connect() works fine.
 
 What I'm looking for is a little info on what db.conn should be, where is it 
 set, how can it be NOT set etc.Your help would be most appreciated.
 
 Incidentally, all was not a waste of time as I traversed nearly ALL of the 
 SA code today and picked up a few nice tips..  Thanks! :-)
 
 Well, that above is not part of SQLAlchemy.   I would assume, since its 
 called cursor.py and is dealing with MySQL internals, that its part of MySQL 
 connector, so you should email on their list (and also you can test things 
 without SQLAlchemy at all, just use a script with MySQL connector directly).
 
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Sudden Connection issues

[Warwick Prince]

Hi All

This is my first post here, so I wish it were a little more spectacular..  :-)

I have been working happily with SA 0.6.x on Windows 2003 server with MySQL and 
the Sun Python Connector.  I have an identical config running (and working) on 
XP.

I was testing my code which had been working perfectly, and suddenly started 
getting this message which I had never seen before;

Traceback (most recent call last):
  File pyshell#3, line 1, in module
e.connect()
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1731, in 
connect
return self.Connection(self, **kwargs)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 821, in 
__init__
self.__connection = connection or engine.raw_connection()
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1787, in 
raw_connection
return self.pool.unique_connection()
  File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 135, in 
unique_connection
return _ConnectionFairy(self).checkout()
  File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 329, in __init__
rec = self._connection_record = pool.get()
  File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 177, in get
return self.do_get()
  File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 692, in do_get
con = self.create_connection()
  File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 138, in 
create_connection
return _ConnectionRecord(self)
  File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 218, in __init__
l.first_connect(self.connection, self)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\strategies.py, line 
145, in first_connect
dialect.initialize(c)
  File C:\Python26\lib\site-packages\sqlalchemy\dialects\mysql\base.py, line 
1774, in initialize
default.DefaultDialect.initialize(self, connection)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line 144, 
in initialize
self._get_default_schema_name(connection)
  File C:\Python26\lib\site-packages\sqlalchemy\dialects\mysql\base.py, line 
1739, in _get_default_schema_name
return connection.execute('SELECT DATABASE()').scalar()
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1157, in 
execute
params)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1252, in 
_execute_text
parameters=parameters)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1348, in 
__create_execution_context
None, None)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1311, in 
_handle_dbapi_exception
self.invalidate(e)
  File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 961, in 
invalidate
if self.__connection.is_valid:
AttributeError: 'MySQL' object has no attribute 'is_valid'

I have spent a significant amount of time on this, and nothing makes sense.  
I've run the unittest.py code that comes with the connector, and (connecting to 
the same DB) ran the tests with success.  Firstly, it would appear that there 
is a possible bug in base.py at line 962 where it asks  if 
self.__connection.is_valid: as this attribute has not been created yet, and is 
conditionally created.  However, this is not the root of the issue, merely a 
distraction.  (I forced this to exist and have a value, but the problem just 
fell through to further down the code)

I can get this error by simply doing this (now) when this all has worked for 
days.

e = create_engine('mysql+mysqlconnector://user:passw...@127.0.0.1/testdb', 
encoding='utf8', echo=False)
e.connect()

Any clues - as this is driving me nuts!   Can't do ANYTHING now because I an no 
longer connect to the DB.  DB manager and other tools all show MySQL happily 
ticking along.  I have rebooted the server.

As a possible solution, are there any other stable MySQL connectors out there 
that are easy to install on a Windows platform?   The Python Connector one 
chosen has been good so far, but if that turns out to be  the issue - it's gone!

Cheers
Warwick

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Sudden Connection issues

[Warwick Prince]

HI Michael

Excellent.  Thanks for the pointers - I'll investigate further and get back to 
you.  This is really strange as I do not believe that I changed anything from 
the point it all worked, to the point at which it didn't!   (I'm sure there 
will be something however - there always is..).  :-)   I'll let you know how I 
go.   When I get it going again, I'll hit you with the REAL questions I have..  

Cheers
Warwick

On 26/08/2010, at 12:43 PM, Michael Bayer wrote:

 
 On Aug 25, 2010, at 10:11 PM, Warwick Prince wrote:
 
 Hi All
 
 This is my first post here, so I wish it were a little more spectacular..  
 :-)
 
 I have been working happily with SA 0.6.x on Windows 2003 server with MySQL 
 and the Sun Python Connector.  I have an identical config running (and 
 working) on XP.
 
 I was testing my code which had been working perfectly, and suddenly started 
 getting this message which I had never seen before;
 
 Traceback (most recent call last):
 File pyshell#3, line 1, in module
   e.connect()
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1731, 
 in connect
   return self.Connection(self, **kwargs)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 821, in 
 __init__
   self.__connection = connection or engine.raw_connection()
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1787, 
 in raw_connection
   return self.pool.unique_connection()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 135, in 
 unique_connection
   return _ConnectionFairy(self).checkout()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 329, in 
 __init__
   rec = self._connection_record = pool.get()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 177, in get
   return self.do_get()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 692, in do_get
   con = self.create_connection()
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 138, in 
 create_connection
   return _ConnectionRecord(self)
 File C:\Python26\lib\site-packages\sqlalchemy\pool.py, line 218, in 
 __init__
   l.first_connect(self.connection, self)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\strategies.py, line 
 145, in first_connect
   dialect.initialize(c)
 File C:\Python26\lib\site-packages\sqlalchemy\dialects\mysql\base.py, line 
 1774, in initialize
   default.DefaultDialect.initialize(self, connection)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line 144, 
 in initialize
   self._get_default_schema_name(connection)
 File C:\Python26\lib\site-packages\sqlalchemy\dialects\mysql\base.py, line 
 1739, in _get_default_schema_name
   return connection.execute('SELECT DATABASE()').scalar()
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1157, 
 in execute
   params)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1252, 
 in _execute_text
   parameters=parameters)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1348, 
 in __create_execution_context
   None, None)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1311, 
 in _handle_dbapi_exception
   self.invalidate(e)
 File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 961, in 
 invalidate
   if self.__connection.is_valid:
 AttributeError: 'MySQL' object has no attribute 'is_valid'
 
 I have spent a significant amount of time on this, and nothing makes sense.  
 I've run the unittest.py code that comes with the connector, and (connecting 
 to the same DB) ran the tests with success.  Firstly, it would appear that 
 there is a possible bug in base.py at line 962 where it asks  if 
 self.__connection.is_valid: as this attribute has not been created yet, and 
 is conditionally created.  
 
 __connection is created in the constructor, and it may get del'ed by close or 
 invalidate.   There's a check for closed right there, so its only if 
 invalidate() is called twice that there'd be an issue.  But that's not what's 
 happening here, since __connection is present.
 
 Its still a bug on our part though, partially, there's an edge case where the 
 __connection member is not the usual ConnectionFairy wrapper we use, and is 
 the raw DBAPI connection, and that is during the initilization phase of a new 
 connection.   In your stack trace, a new connection is acquired from the 
 DBAPI, and we're calling SELECT DATABASE() on it.  That is failing.   Very 
 unusually, it is failing with an exception that passes the is_disconnect 
 test, which means the errno is in (2006, 2013, 2014, 2045, 2055, 2048) 
 according to the mysqlconnector dialect, and means the connection has been 
 lost.  Interestingly, that is one more code than we have in base.py.  So that 
 list of codes is suspect (the myconnpy guys provided some of this code) - the 
 base list used by the other MySQL dialects is (2006, 2013, 2014, 2045, 2055).
 
 The exception then passes off to invalidate() which wasn't expecting a raw