[sqlalchemy] Where to use and vs and_()

[Krishnakant]

Hello all,
The subject might have made my problem already clear.
So I am unclear about when I should use the normal Python "and " vs the 
sqlalchemy "and_" while writing where, having or similar queries 
including joins.

I have tryed understanding this but may be I have overlooked some thing.
Kindly give some pointers.
Happy hacking.
Krishnakant.

--
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] Re: reflection taking a very long time?

[Mike Bayer]

On 02/16/2016 04:02 PM, Alex Hall wrote:

Great; I was hoping you wouldn't say that. :) I've been through them
many, many times, trying to get the connection working. I've gone from
error to error, and thought I had it all working when I finally got
the create_engine line to run with no problem. Apparently I'm not as
far along as I thought I was. Back to the drawing board.


big iron databases like DB2 are seriously painful to work with.  Oracle 
is similar and MS SQL Server is barely much better.  These DBs have 
complicated, highly proprietary and arcane connectivity models so this 
is kind of par for the course using a database like that.





To keep things on topic for this thread, let me pose a general
question. This database contains hundreds of tables, maybe thousands.
Some are small, a few have thousands or millions of rows. Would
automap choke on all that, or could it handle it? Will mapping all
that fill up my ram, or have any other impact I should consider?


The rows inside the tables don't matter.   Reflecting thousands of 
tables is definitely not a quick process, and the speed of the operation 
can be hindered further by the responsiveness of the target database's 
information schema views.Reflecting tables on a platform like Oracle 
for example incurs a half dozen queries per table for example which 
don't run too quickly, and for hundreds of tables you could be looking 
at startup times at least in the tens of seconds.   You'd want to do 
some benching against DB2 to see how well the reflection queries 
perform; note these queries are part of the DB2 driver itself and were 
written by the IBM folks in this case.


Additionally, reflecting tables means we're building up Table / Column 
structures in memory, which in most cases is not such a large memory 
investment; however if you truly have thousands of tables, and these are 
big legacy-style tables that themselves have hundreds of columns in some 
cases, this will produce a significant memory footprint.  Not 
necessarily unworkable, but for the Python process to build itself up to 
a very large size itself adds latency.


Depending on what you are trying to do, you'd probably want to look into 
using automap and/or reflection for only the subset of tables that you 
actually need; look at the "only" param 
http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html?highlight=reflect#sqlalchemy.schema.MetaData.reflect.params.only 
for that.  The columns reflected and/or mapped within each Table can be 
limited also but you need a little more code for that.   Work on getting 
connected first :).







On 2/16/16, Mike Bayer  wrote:

well then you're just not making any database connection.   you'd need
to check your database connectivity and your connection parameters.



On 02/16/2016 03:37 PM, Alex Hall wrote:

I tried that, hoping for a bit more insight into the problem. However,
unless I'm doing something wrong, I don't even get any queries. I get
my own print statements, then the script tries to connect and hangs.
I've added
dbEngine.connect()
just to be sure the problem is that first connection, and sure enough,
it hangs on that line.

On 2/16/16, Mike Bayer  wrote:

turning on echo=True inside create_engine() will show you what queries
are emitted as they occur so you can see which ones are taking long
and/or hanging.


On 02/16/2016 02:59 PM, Alex Hall wrote:

Upon re-reading some of the docs, I realized that my problem may still
be that initial connection. The create-engine doesn't actually
*connect* to the database, it just sets things up. That means that my
actual connection happens later, when I try to reflect or use automap.
When that happens, the connection starts up and the script hangs. I'm
no closer to solving this, and would love to hear anyone's thoughts,
but at least I know that my thought of blaming reflect/automap is
likely incorrect.

On 2/16/16, Alex Hall  wrote:

Hi list,
Sorry for all the emails. I've determined that my script is actually
connecting to the 400's test database. At least, a print statement
placed just after the create_engine call is printing, so I guess we're
good there.

What I'm running into now is unresponsiveness when I try to reflect or
automap the database so I can do some basic queries. As soon as I call
either
automap.prepare(dbEngine, reflect=True)
or
metadata = MetaData()
metadata.reflect(dbEngine, only=['tableName'])

the script stops, hanging there with no response at all. The same
thing happened when I was trying to use an inspector on the engine.
It's an AS400, so taking a few seconds is a very long time for it.
This is being left to run for minutes and isn't doing anything. What,
if anything did I do wrong syntactically? Is there a better way to
check that my engine is actually ready to go, or some other check I
should be making? The full script, minus anything sensitive, is below.

import globals
import 

Re: [sqlalchemy] Re: reflection taking a very long time?

[Alex Hall]

Great; I was hoping you wouldn't say that. :) I've been through them
many, many times, trying to get the connection working. I've gone from
error to error, and thought I had it all working when I finally got
the create_engine line to run with no problem. Apparently I'm not as
far along as I thought I was. Back to the drawing board.

To keep things on topic for this thread, let me pose a general
question. This database contains hundreds of tables, maybe thousands.
Some are small, a few have thousands or millions of rows. Would
automap choke on all that, or could it handle it? Will mapping all
that fill up my ram, or have any other impact I should consider?

On 2/16/16, Mike Bayer  wrote:
> well then you're just not making any database connection.   you'd need
> to check your database connectivity and your connection parameters.
>
>
>
> On 02/16/2016 03:37 PM, Alex Hall wrote:
>> I tried that, hoping for a bit more insight into the problem. However,
>> unless I'm doing something wrong, I don't even get any queries. I get
>> my own print statements, then the script tries to connect and hangs.
>> I've added
>> dbEngine.connect()
>> just to be sure the problem is that first connection, and sure enough,
>> it hangs on that line.
>>
>> On 2/16/16, Mike Bayer  wrote:
>>> turning on echo=True inside create_engine() will show you what queries
>>> are emitted as they occur so you can see which ones are taking long
>>> and/or hanging.
>>>
>>>
>>> On 02/16/2016 02:59 PM, Alex Hall wrote:
 Upon re-reading some of the docs, I realized that my problem may still
 be that initial connection. The create-engine doesn't actually
 *connect* to the database, it just sets things up. That means that my
 actual connection happens later, when I try to reflect or use automap.
 When that happens, the connection starts up and the script hangs. I'm
 no closer to solving this, and would love to hear anyone's thoughts,
 but at least I know that my thought of blaming reflect/automap is
 likely incorrect.

 On 2/16/16, Alex Hall  wrote:
> Hi list,
> Sorry for all the emails. I've determined that my script is actually
> connecting to the 400's test database. At least, a print statement
> placed just after the create_engine call is printing, so I guess we're
> good there.
>
> What I'm running into now is unresponsiveness when I try to reflect or
> automap the database so I can do some basic queries. As soon as I call
> either
> automap.prepare(dbEngine, reflect=True)
> or
> metadata = MetaData()
> metadata.reflect(dbEngine, only=['tableName'])
>
> the script stops, hanging there with no response at all. The same
> thing happened when I was trying to use an inspector on the engine.
> It's an AS400, so taking a few seconds is a very long time for it.
> This is being left to run for minutes and isn't doing anything. What,
> if anything did I do wrong syntactically? Is there a better way to
> check that my engine is actually ready to go, or some other check I
> should be making? The full script, minus anything sensitive, is below.
>
> import globals
> import logging
> from sqlalchemy import *
> from sqlalchemy.engine import reflection
> from sqlalchemy.ext.automap import automap_base
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker
>
> logger = logging.getLogger(globals.appName+"."+__name__)
>
> #set up the sqlalchemy objects
> logger.debug("Creating database engine, base, and session.")
> dbEngine =
> create_engine("ibm_db_sa://"+user+":"+pwd+"@"+server+":"+port+"/"+dbName)
> print "connected"
> Session = sessionmaker(bind = dbEngine) #note that's a capital s on
> Session
> session = Session() #lowercase s
> metadata = MetaData()
> logger.debug("Creating session.")
> print "Creating automap base"
> base = automap_base()
> print "setting up automapping"
> #base.prepare(dbEngine, reflect=True)
> metadata.reflect(dbEngine, only=['tableName'])
>
> def getOrderByNumber(orderID):
>orders = base.classes.ORHED
>order =
> session.query(orders).filter(orders.OAORNO==orderID).first()
>print order.OAORNO
> #end def getOrderByNumber
>
> getOrderByNumber("AA111")
>

>>>
>>> --
>>> 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.
>>>
>>
>
> --
> You received this message 

Re: [sqlalchemy] Re: reflection taking a very long time?

[Mike Bayer]

well then you're just not making any database connection.   you'd need 
to check your database connectivity and your connection parameters.




On 02/16/2016 03:37 PM, Alex Hall wrote:

I tried that, hoping for a bit more insight into the problem. However,
unless I'm doing something wrong, I don't even get any queries. I get
my own print statements, then the script tries to connect and hangs.
I've added
dbEngine.connect()
just to be sure the problem is that first connection, and sure enough,
it hangs on that line.

On 2/16/16, Mike Bayer  wrote:

turning on echo=True inside create_engine() will show you what queries
are emitted as they occur so you can see which ones are taking long
and/or hanging.


On 02/16/2016 02:59 PM, Alex Hall wrote:

Upon re-reading some of the docs, I realized that my problem may still
be that initial connection. The create-engine doesn't actually
*connect* to the database, it just sets things up. That means that my
actual connection happens later, when I try to reflect or use automap.
When that happens, the connection starts up and the script hangs. I'm
no closer to solving this, and would love to hear anyone's thoughts,
but at least I know that my thought of blaming reflect/automap is
likely incorrect.

On 2/16/16, Alex Hall  wrote:

Hi list,
Sorry for all the emails. I've determined that my script is actually
connecting to the 400's test database. At least, a print statement
placed just after the create_engine call is printing, so I guess we're
good there.

What I'm running into now is unresponsiveness when I try to reflect or
automap the database so I can do some basic queries. As soon as I call
either
automap.prepare(dbEngine, reflect=True)
or
metadata = MetaData()
metadata.reflect(dbEngine, only=['tableName'])

the script stops, hanging there with no response at all. The same
thing happened when I was trying to use an inspector on the engine.
It's an AS400, so taking a few seconds is a very long time for it.
This is being left to run for minutes and isn't doing anything. What,
if anything did I do wrong syntactically? Is there a better way to
check that my engine is actually ready to go, or some other check I
should be making? The full script, minus anything sensitive, is below.

import globals
import logging
from sqlalchemy import *
from sqlalchemy.engine import reflection
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

logger = logging.getLogger(globals.appName+"."+__name__)

#set up the sqlalchemy objects
logger.debug("Creating database engine, base, and session.")
dbEngine =
create_engine("ibm_db_sa://"+user+":"+pwd+"@"+server+":"+port+"/"+dbName)
print "connected"
Session = sessionmaker(bind = dbEngine) #note that's a capital s on
Session
session = Session() #lowercase s
metadata = MetaData()
logger.debug("Creating session.")
print "Creating automap base"
base = automap_base()
print "setting up automapping"
#base.prepare(dbEngine, reflect=True)
metadata.reflect(dbEngine, only=['tableName'])

def getOrderByNumber(orderID):
   orders = base.classes.ORHED
   order = session.query(orders).filter(orders.OAORNO==orderID).first()
   print order.OAORNO
#end def getOrderByNumber

getOrderByNumber("AA111")





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





--
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] Re: reflection taking a very long time?

[Alex Hall]

I tried that, hoping for a bit more insight into the problem. However,
unless I'm doing something wrong, I don't even get any queries. I get
my own print statements, then the script tries to connect and hangs.
I've added
dbEngine.connect()
just to be sure the problem is that first connection, and sure enough,
it hangs on that line.

On 2/16/16, Mike Bayer  wrote:
> turning on echo=True inside create_engine() will show you what queries
> are emitted as they occur so you can see which ones are taking long
> and/or hanging.
>
>
> On 02/16/2016 02:59 PM, Alex Hall wrote:
>> Upon re-reading some of the docs, I realized that my problem may still
>> be that initial connection. The create-engine doesn't actually
>> *connect* to the database, it just sets things up. That means that my
>> actual connection happens later, when I try to reflect or use automap.
>> When that happens, the connection starts up and the script hangs. I'm
>> no closer to solving this, and would love to hear anyone's thoughts,
>> but at least I know that my thought of blaming reflect/automap is
>> likely incorrect.
>>
>> On 2/16/16, Alex Hall  wrote:
>>> Hi list,
>>> Sorry for all the emails. I've determined that my script is actually
>>> connecting to the 400's test database. At least, a print statement
>>> placed just after the create_engine call is printing, so I guess we're
>>> good there.
>>>
>>> What I'm running into now is unresponsiveness when I try to reflect or
>>> automap the database so I can do some basic queries. As soon as I call
>>> either
>>> automap.prepare(dbEngine, reflect=True)
>>> or
>>> metadata = MetaData()
>>> metadata.reflect(dbEngine, only=['tableName'])
>>>
>>> the script stops, hanging there with no response at all. The same
>>> thing happened when I was trying to use an inspector on the engine.
>>> It's an AS400, so taking a few seconds is a very long time for it.
>>> This is being left to run for minutes and isn't doing anything. What,
>>> if anything did I do wrong syntactically? Is there a better way to
>>> check that my engine is actually ready to go, or some other check I
>>> should be making? The full script, minus anything sensitive, is below.
>>>
>>> import globals
>>> import logging
>>> from sqlalchemy import *
>>> from sqlalchemy.engine import reflection
>>> from sqlalchemy.ext.automap import automap_base
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy.orm import sessionmaker
>>>
>>> logger = logging.getLogger(globals.appName+"."+__name__)
>>>
>>> #set up the sqlalchemy objects
>>> logger.debug("Creating database engine, base, and session.")
>>> dbEngine =
>>> create_engine("ibm_db_sa://"+user+":"+pwd+"@"+server+":"+port+"/"+dbName)
>>> print "connected"
>>> Session = sessionmaker(bind = dbEngine) #note that's a capital s on
>>> Session
>>> session = Session() #lowercase s
>>> metadata = MetaData()
>>> logger.debug("Creating session.")
>>> print "Creating automap base"
>>> base = automap_base()
>>> print "setting up automapping"
>>> #base.prepare(dbEngine, reflect=True)
>>> metadata.reflect(dbEngine, only=['tableName'])
>>>
>>> def getOrderByNumber(orderID):
>>>   orders = base.classes.ORHED
>>>   order = session.query(orders).filter(orders.OAORNO==orderID).first()
>>>   print order.OAORNO
>>> #end def getOrderByNumber
>>>
>>> getOrderByNumber("AA111")
>>>
>>
>
> --
> 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.
>

-- 
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] Re: reflection taking a very long time?

[Mike Bayer]

turning on echo=True inside create_engine() will show you what queries 
are emitted as they occur so you can see which ones are taking long 
and/or hanging.



On 02/16/2016 02:59 PM, Alex Hall wrote:

Upon re-reading some of the docs, I realized that my problem may still
be that initial connection. The create-engine doesn't actually
*connect* to the database, it just sets things up. That means that my
actual connection happens later, when I try to reflect or use automap.
When that happens, the connection starts up and the script hangs. I'm
no closer to solving this, and would love to hear anyone's thoughts,
but at least I know that my thought of blaming reflect/automap is
likely incorrect.

On 2/16/16, Alex Hall  wrote:

Hi list,
Sorry for all the emails. I've determined that my script is actually
connecting to the 400's test database. At least, a print statement
placed just after the create_engine call is printing, so I guess we're
good there.

What I'm running into now is unresponsiveness when I try to reflect or
automap the database so I can do some basic queries. As soon as I call
either
automap.prepare(dbEngine, reflect=True)
or
metadata = MetaData()
metadata.reflect(dbEngine, only=['tableName'])

the script stops, hanging there with no response at all. The same
thing happened when I was trying to use an inspector on the engine.
It's an AS400, so taking a few seconds is a very long time for it.
This is being left to run for minutes and isn't doing anything. What,
if anything did I do wrong syntactically? Is there a better way to
check that my engine is actually ready to go, or some other check I
should be making? The full script, minus anything sensitive, is below.

import globals
import logging
from sqlalchemy import *
from sqlalchemy.engine import reflection
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

logger = logging.getLogger(globals.appName+"."+__name__)

#set up the sqlalchemy objects
logger.debug("Creating database engine, base, and session.")
dbEngine =
create_engine("ibm_db_sa://"+user+":"+pwd+"@"+server+":"+port+"/"+dbName)
print "connected"
Session = sessionmaker(bind = dbEngine) #note that's a capital s on Session
session = Session() #lowercase s
metadata = MetaData()
logger.debug("Creating session.")
print "Creating automap base"
base = automap_base()
print "setting up automapping"
#base.prepare(dbEngine, reflect=True)
metadata.reflect(dbEngine, only=['tableName'])

def getOrderByNumber(orderID):
  orders = base.classes.ORHED
  order = session.query(orders).filter(orders.OAORNO==orderID).first()
  print order.OAORNO
#end def getOrderByNumber

getOrderByNumber("AA111")





--
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] How to correctly merge objects of type X or any subclass of type X into the session?

[Mike Bayer]

On 02/16/2016 11:42 AM, Michael Naber wrote:

That's interesting. So, if I'm trying to move instances between threads,
is it recommended that I pass between threads the instance id, and then
in the receiving thread use session.query(Person).get(instance_id),
or... should I pass the instance itself (not by ID), and then use
session.merge(instance)?

My objective is that I would want to have full access to access and
modify the instance in the session of the receiving thread.


you can pass the full instance from one thread to another, and have the 
receiving thread merge() it into the receiving session.   the only thing 
you need to watch for in this case is that the receiving thread is now 
going to be handling the object you give it in a read-only sense, but if 
that object is still associated with the originating thread's Session 
then you're going to have thread-concurrent access to that session, 
which is dangerous.  So you can either synchronize the threads on the 
point at which the receiving thread is calling merge() or you can detach 
the object from the Session before giving it to the receiving thread.







Thank you,
Michael

On Tue, Feb 16, 2016 at 11:26 AM, Mike Bayer > wrote:

answered



On 02/16/2016 09:08 AM, Michael Naber wrote:

I would like to write code which can correctly merge objects of
type X
or any subclass of type X into the session. I have been doing
session.merge(X(id=??)), which works fine for merging type X,
but if the
object ID references an instance of any subclass of X, the merge
results
in the discriminator being set incorrectly. Code example here:
http://stackoverflow.com/questions/35414057

Any help much appreciated.

Regards,
Michael

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


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


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


--
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] Re: reflection taking a very long time?

[Alex Hall]

Upon re-reading some of the docs, I realized that my problem may still
be that initial connection. The create-engine doesn't actually
*connect* to the database, it just sets things up. That means that my
actual connection happens later, when I try to reflect or use automap.
When that happens, the connection starts up and the script hangs. I'm
no closer to solving this, and would love to hear anyone's thoughts,
but at least I know that my thought of blaming reflect/automap is
likely incorrect.

On 2/16/16, Alex Hall  wrote:
> Hi list,
> Sorry for all the emails. I've determined that my script is actually
> connecting to the 400's test database. At least, a print statement
> placed just after the create_engine call is printing, so I guess we're
> good there.
>
> What I'm running into now is unresponsiveness when I try to reflect or
> automap the database so I can do some basic queries. As soon as I call
> either
> automap.prepare(dbEngine, reflect=True)
> or
> metadata = MetaData()
> metadata.reflect(dbEngine, only=['tableName'])
>
> the script stops, hanging there with no response at all. The same
> thing happened when I was trying to use an inspector on the engine.
> It's an AS400, so taking a few seconds is a very long time for it.
> This is being left to run for minutes and isn't doing anything. What,
> if anything did I do wrong syntactically? Is there a better way to
> check that my engine is actually ready to go, or some other check I
> should be making? The full script, minus anything sensitive, is below.
>
> import globals
> import logging
> from sqlalchemy import *
> from sqlalchemy.engine import reflection
> from sqlalchemy.ext.automap import automap_base
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import sessionmaker
>
> logger = logging.getLogger(globals.appName+"."+__name__)
>
> #set up the sqlalchemy objects
> logger.debug("Creating database engine, base, and session.")
> dbEngine =
> create_engine("ibm_db_sa://"+user+":"+pwd+"@"+server+":"+port+"/"+dbName)
> print "connected"
> Session = sessionmaker(bind = dbEngine) #note that's a capital s on Session
> session = Session() #lowercase s
> metadata = MetaData()
> logger.debug("Creating session.")
> print "Creating automap base"
> base = automap_base()
> print "setting up automapping"
> #base.prepare(dbEngine, reflect=True)
> metadata.reflect(dbEngine, only=['tableName'])
>
> def getOrderByNumber(orderID):
>  orders = base.classes.ORHED
>  order = session.query(orders).filter(orders.OAORNO==orderID).first()
>  print order.OAORNO
> #end def getOrderByNumber
>
> getOrderByNumber("AA111")
>

-- 
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] reflection taking a very long time?

[Alex Hall]

Hi list,
Sorry for all the emails. I've determined that my script is actually
connecting to the 400's test database. At least, a print statement
placed just after the create_engine call is printing, so I guess we're
good there.

What I'm running into now is unresponsiveness when I try to reflect or
automap the database so I can do some basic queries. As soon as I call
either
automap.prepare(dbEngine, reflect=True)
or
metadata = MetaData()
metadata.reflect(dbEngine, only=['tableName'])

the script stops, hanging there with no response at all. The same
thing happened when I was trying to use an inspector on the engine.
It's an AS400, so taking a few seconds is a very long time for it.
This is being left to run for minutes and isn't doing anything. What,
if anything did I do wrong syntactically? Is there a better way to
check that my engine is actually ready to go, or some other check I
should be making? The full script, minus anything sensitive, is below.

import globals
import logging
from sqlalchemy import *
from sqlalchemy.engine import reflection
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

logger = logging.getLogger(globals.appName+"."+__name__)

#set up the sqlalchemy objects
logger.debug("Creating database engine, base, and session.")
dbEngine = 
create_engine("ibm_db_sa://"+user+":"+pwd+"@"+server+":"+port+"/"+dbName)
print "connected"
Session = sessionmaker(bind = dbEngine) #note that's a capital s on Session
session = Session() #lowercase s
metadata = MetaData()
logger.debug("Creating session.")
print "Creating automap base"
base = automap_base()
print "setting up automapping"
#base.prepare(dbEngine, reflect=True)
metadata.reflect(dbEngine, only=['tableName'])

def getOrderByNumber(orderID):
 orders = base.classes.ORHED
 order = session.query(orders).filter(orders.OAORNO==orderID).first()
 print order.OAORNO
#end def getOrderByNumber

getOrderByNumber("AA111")

-- 
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] Pyodbc.Connection has no attribute 'dbms_ver'?

[Alex Hall]

You're onto something, I think. When I use a connection string of
"ibm_db_sa://user:pwd@AS400IP:DBAccessPort/DBName"
I get no errors. I don't actually get anything, though; my command
prompt is unresponsive, as though waiting for a script to finish, but
that's all. This seems to be the 400, though, because if I try to
telnet into it with the same address and port, I get the same (lack
of) response. This is progress, at least. Thanks for the help, and
hopefully I'll have it from here! Here's to my next question being
about doing things once I'm IN the database, rather than still
knocking on its door.

On 2/16/16, Michal Petrucha  wrote:
> On Tue, Feb 16, 2016 at 10:27:40AM -0500, Alex Hall wrote:
>> I have pyodbc 3.0.10, ibm_db_sa 0.3.2, and ibm_db 2.0.6. I'm also
>> talking to people on the ibm_db list, and they suggested I re-install
>> ibm_db and ibm_db_sa according to the official tutorial, which uses
>> easy_install. I did so, but there was no change.
>>
>> As to pyodbc, I'm fine with not using it. Thus far, from the two lists
>> I'm on and more research, I thought I *had to* use it to get things
>> working right. Indeed, when I remove "+pyodbc" from my SA connection
>> string, the dbms_ver error goes away. However, it's replaced by an
>> error that the driver can't find the DSN name I give it, even though I
>> can see that DSN right in the IBM ODBC manager on this computer.
>> Someone mentioned 64-bit versus 32-bit; I'm using the 64-bit version
>> of the ODBC manager, and 64-bit Python. I'm not sure how else to tell
>> if the name of the DSN itself is in the correct format.
>>
>> The traceback is very long, but here it is in full:
>>
>> c:\python27\python.exe DBInterface2.py
>> Traceback (most recent call last):
> [...]
>>   File
>> "c:\python27\lib\site-packages\ibm_db_sa-0.3.2-py2.7.egg\ibm_db_sa\base.p
>> y", line 666, in initialize
>> self.dbms_ver = connection.connection.dbms_ver
>> AttributeError: 'pyodbc.Connection' object has no attribute 'dbms_ver'
>
> This traceback is still the dbms_ver thing, did you mean to post the
> other one?
>
> In any case, when you're using a URI in the form of
> "ibm_db_sa://user:pass@host/db_name", at least based on the example in
> the IBM docs [1], I'm guessing that you shouldn't use the ODBC DSN you
> have defined, but rather the server hostname or IP address directly.
> In this case it should be using the IBM DBAPI driver directly, without
> going through ODBC.
>
> Cheers,
>
> Michal
>
>
> [1]:
> https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/t0060891.html
>
> --
> 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.
>

-- 
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] How to correctly merge objects of type X or any subclass of type X into the session?

[Michael Naber]

That's interesting. So, if I'm trying to move instances between threads, is
it recommended that I pass between threads the instance id, and then in the
receiving thread use session.query(Person).get(instance_id), or... should I
pass the instance itself (not by ID), and then use session.merge(instance)?

My objective is that I would want to have full access to access and modify
the instance in the session of the receiving thread.

Thank you,
Michael

On Tue, Feb 16, 2016 at 11:26 AM, Mike Bayer 
wrote:

> answered
>
>
>
> On 02/16/2016 09:08 AM, Michael Naber wrote:
>
>> I would like to write code which can correctly merge objects of type X
>> or any subclass of type X into the session. I have been doing
>> session.merge(X(id=??)), which works fine for merging type X, but if the
>> object ID references an instance of any subclass of X, the merge results
>> in the discriminator being set incorrectly. Code example here:
>> http://stackoverflow.com/questions/35414057
>>
>> Any help much appreciated.
>>
>> Regards,
>> Michael
>>
>> --
>> 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.
>>
>
> --
> 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.
>

-- 
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] How to correctly merge objects of type X or any subclass of type X into the session?

[Mike Bayer]

answered


On 02/16/2016 09:08 AM, Michael Naber wrote:

I would like to write code which can correctly merge objects of type X
or any subclass of type X into the session. I have been doing
session.merge(X(id=??)), which works fine for merging type X, but if the
object ID references an instance of any subclass of X, the merge results
in the discriminator being set incorrectly. Code example here:
http://stackoverflow.com/questions/35414057

Any help much appreciated.

Regards,
Michael

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


--
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] Pyodbc.Connection has no attribute 'dbms_ver'?

[Michal Petrucha]

On Tue, Feb 16, 2016 at 10:27:40AM -0500, Alex Hall wrote:
> I have pyodbc 3.0.10, ibm_db_sa 0.3.2, and ibm_db 2.0.6. I'm also
> talking to people on the ibm_db list, and they suggested I re-install
> ibm_db and ibm_db_sa according to the official tutorial, which uses
> easy_install. I did so, but there was no change.
> 
> As to pyodbc, I'm fine with not using it. Thus far, from the two lists
> I'm on and more research, I thought I *had to* use it to get things
> working right. Indeed, when I remove "+pyodbc" from my SA connection
> string, the dbms_ver error goes away. However, it's replaced by an
> error that the driver can't find the DSN name I give it, even though I
> can see that DSN right in the IBM ODBC manager on this computer.
> Someone mentioned 64-bit versus 32-bit; I'm using the 64-bit version
> of the ODBC manager, and 64-bit Python. I'm not sure how else to tell
> if the name of the DSN itself is in the correct format.
> 
> The traceback is very long, but here it is in full:
> 
> c:\python27\python.exe DBInterface2.py
> Traceback (most recent call last):
[...]
>   File 
> "c:\python27\lib\site-packages\ibm_db_sa-0.3.2-py2.7.egg\ibm_db_sa\base.p
> y", line 666, in initialize
> self.dbms_ver = connection.connection.dbms_ver
> AttributeError: 'pyodbc.Connection' object has no attribute 'dbms_ver'

This traceback is still the dbms_ver thing, did you mean to post the
other one?

In any case, when you're using a URI in the form of
"ibm_db_sa://user:pass@host/db_name", at least based on the example in
the IBM docs [1], I'm guessing that you shouldn't use the ODBC DSN you
have defined, but rather the server hostname or IP address directly.
In this case it should be using the IBM DBAPI driver directly, without
going through ODBC.

Cheers,

Michal


[1]: 
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/t0060891.html

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] Pyodbc.Connection has no attribute 'dbms_ver'?

[Alex Hall]

I have pyodbc 3.0.10, ibm_db_sa 0.3.2, and ibm_db 2.0.6. I'm also
talking to people on the ibm_db list, and they suggested I re-install
ibm_db and ibm_db_sa according to the official tutorial, which uses
easy_install. I did so, but there was no change.

As to pyodbc, I'm fine with not using it. Thus far, from the two lists
I'm on and more research, I thought I *had to* use it to get things
working right. Indeed, when I remove "+pyodbc" from my SA connection
string, the dbms_ver error goes away. However, it's replaced by an
error that the driver can't find the DSN name I give it, even though I
can see that DSN right in the IBM ODBC manager on this computer.
Someone mentioned 64-bit versus 32-bit; I'm using the 64-bit version
of the ODBC manager, and 64-bit Python. I'm not sure how else to tell
if the name of the DSN itself is in the correct format.

The traceback is very long, but here it is in full:

c:\python27\python.exe DBInterface2.py
Traceback (most recent call last):
  File "DBInterface2.py", line 28, in 
getAllTables()
  File "DBInterface2.py", line 22, in getAllTables
dbInspector = reflection.Inspector.from_engine(dbEngine)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 135
, in from_engine
return Inspector(bind)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 109
, in __init__
bind.connect().close()
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2018, in
connect
return self._connection_cls(self, **kwargs)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 72, in __
init__
if connection is not None else engine.raw_connection()
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2104, in
raw_connection
self.pool.unique_connection, _connection)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\base.py", line 2074, in
_wrap_pool_connect
return fn()
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 318, in unique_c
onnection
return _ConnectionFairy._checkout(self)
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 713, in _checkou
t
fairy = _ConnectionRecord.checkout(pool)
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 480, in checkout

rec = pool._do_get()
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1060, in _do_get

self._dec_overflow()
  File "c:\python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60,
in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 1057, in _do_get

return self._create_connection()
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 323, in _create_
connection
return _ConnectionRecord(self)
  File "c:\python27\lib\site-packages\sqlalchemy\pool.py", line 454, in __init__

exec_once(self.connection, self)
  File "c:\python27\lib\site-packages\sqlalchemy\event\attr.py", line 246, in ex
ec_once
self(*args, **kw)
  File "c:\python27\lib\site-packages\sqlalchemy\event\attr.py", line 256, in __
call__
fn(*args, **kw)
  File "c:\python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line 1312
, in go
return once_fn(*arg, **kw)
  File "c:\python27\lib\site-packages\sqlalchemy\engine\strategies.py", line 165
, in first_connect
dialect.initialize(c)
  File "c:\python27\lib\site-packages\sqlalchemy\connectors\pyodbc.py", line 154
, in initialize
super(PyODBCConnector, self).initialize(connection)
  File "c:\python27\lib\site-packages\ibm_db_sa-0.3.2-py2.7.egg\ibm_db_sa\base.p
y", line 666, in initialize
self.dbms_ver = connection.connection.dbms_ver
AttributeError: 'pyodbc.Connection' object has no attribute 'dbms_ver'


On 2/15/16, Simon King  wrote:
> What does the traceback say? That exact line would trigger an error much
> like the one you are seeing, if the object in “connection.connection” is a
> pyodbc.Connection and doesn’t have a “dbms_ver” attribute.
>
> Note that there are at least 3 packages that could be involved here:
>
> pyodbc (https://pypi.python.org/pypi/pyodbc)
>
> ibm_db (https://pypi.python.org/pypi/ibm_db/)
>
> ibm_db_sa (https://pypi.python.org/pypi/ibm_db_sa)
>
> What versions do you have of each of them? Note that
> https://github.com/ibmdb/python-ibmdbsa/tree/master/ibm_db_sa says that
> pyodbc support is experimental.
>
> Simon
>
>> On 15 Feb 2016, at 21:07, Alex Hall  wrote:
>>
>> I just downloaded a fresh copy of 0.3.2, just in case I had somehow
>> gotten an old version from Pip. I looked in base.py, and found:
>>
>> def initialize(self, connection):
>>   super(DB2Dialect, self).initialize(connection)
>>self.dbms_ver = connection.connection.dbms_ver
>>
>> While I'm not sure what I can do about it, it looks like this dbms_ver
>> property is definitely in the latest ibm_db_sa version. Am I getting
>> this from the wrong place, or 

[sqlalchemy] Re: proper attribute names for many-to-many relationships using automap

[Brian Cherinka]

Yeah I realized I have somewhat nuanced relationships for automap to 
handle, and that in all the time I spent trying to figure out how to get 
automap working for me, I could have written my classes in explicit 
declarative base.  So that's what I ended up doing.  I was hoping to bypass 
that a bit since I have a large number of tables to declare.  Thanks for 
your explanations and help though.  I appreciate it.  

On Sunday, February 14, 2016 at 5:01:19 PM UTC-5, Brian Cherinka wrote:
>
> What is the proper way to get pluralized shortened names for many-to-many 
> tables when using automap?  I currently have it set to generate pluralized 
> lowercase names for collections instead of the default "_collection".  This 
> is what I want for one-to-many or many-to-one relationships, but not 
> many-to-many.  For example, I have two tables, hdu, and extcol, joined 
> together through a many-to-many table, hdu_to_extcol
>
> create table hdu (pk serial primary key not null, extname_pk integer, 
> exttype_pk integer, extno integer, file_pk integer);
> create table hdu_to_extcol (pk serial primary key not null, hdu_pk integer
> , extcol_pk integer);
> create table extcol (pk serial primary key not null, name text);
>
> ALTER TABLE ONLY mangadapdb.hdu_to_extcol
> ADD CONSTRAINT hdu_fk
> FOREIGN KEY (hdu_pk) REFERENCES mangadapdb.hdu(pk)
> ON UPDATE CASCADE ON DELETE CASCADE;
>
> ALTER TABLE ONLY mangadapdb.hdu_to_extcol
> ADD CONSTRAINT extcol_fk
> FOREIGN KEY (extcol_pk) REFERENCES mangadapdb.extcol(pk)
> ON UPDATE CASCADE ON DELETE CASCADE;
>
> When I use SQLalchemy to automap the Base classes, the relationship this 
> generates on the Hdu and Extcol classes are *Hdu.hdu_to_extcol, and 
> Extcol.hdu_to_extcols*, using the below pluralize, and relationship, 
> code.  However, ideally what I'd like the names to be are *Hdu.extcols, 
> and Extcol.hdus*, respectively.  What's the best to generate this for 
> these many-to-many tables?   I'm not sure if automap is recognizing these 
> as many-to-many tables.  The direction indicated when I print during the 
> relationship stage don't indicate as such.
>
> symbol('ONETOMANY')  extcol
> symbol('MANYTOONE')  
> hdu_to_extcol
> symbol('ONETOMANY')  hdu
> symbol('MANYTOONE')  
> hdu_to_extcol
>
> Here is my Base class generation code. 
>
> def _gen_relationship(base, direction, return_fn, attrname, local_cls, 
> referred_cls, **kw):
> if local_cls.__table__.name in onetoones:
> kw['uselist'] = False
> # make use of the built-in function to actually return the result.
>
> return generate_relationship(base, direction, return_fn, attrname, 
> local_cls, referred_cls, **kw)
>
> _pluralizer = inflect.engine()
> def pluralize_collection(base, local_cls, referred_cls, constraint):
> referred_name = referred_cls.__name__
> uncamelized = re.sub(r'[A-Z]', lambda m: "_%s" % m.group(0).lower(),
> referred_name)[1:]
> pluralized = _pluralizer.plural(uncamelized)
> return pluralized
>
> # Grabs engine
> db = DatabaseConnection()
> engine = db.engine
>
> # Selects schema and automaps it.
> metadata = MetaData(schema='mangadapdb')
> Base = automap_base(bind=engine, metadata=metadata)
> Base.prepare(engine, reflect=True, classname_for_table=camelizeClassName, 
> name_for_collection_relationship=pluralize_collection, 
> generate_relationship=_gen_relationship)
>
> # Explicitly declare classes
> for cl in Base.classes.keys():
> exec('{0} = Base.classes.{0}'.format(cl))
>
>
>
>
>

-- 
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] How to correctly merge objects of type X or any subclass of type X into the session?

[Michael Naber]

I would like to write code which can correctly merge objects of type X or
any subclass of type X into the session. I have been doing
session.merge(X(id=??)), which works fine for merging type X, but if the
object ID references an instance of any subclass of X, the merge results in
the discriminator being set incorrectly. Code example here:
http://stackoverflow.com/questions/35414057

Any help much appreciated.

Regards,
Michael

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