[sqlalchemy] Full connection pool close
Hi, I'm not a SQLAchemy expert ( just an average user... ). I have an application that's causing me some problems... It's a monitoring application that connects to a MS Sql Server, so it's always on. Sometimes happens that casualy I have a DBAPIError with pyodbc. The error is something like [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed After the first time I have this error every other DB operation generates this Error. So.. what I would like to do is completely close ( kill ) che active connection pool and recreate it. My code is somethink like this: sa_engine=sa.create_engine(dbUri, echo=False, strategy='threadlocal') sa_Session = sa.orm.scoped_session( sessionmaker(bind=sa_engine, autoflush=False) ) metadata = sa.MetaData(sa_engine) sa_session = sa_Session() and then: sa_session.close() sa_Session.close_all() sa_engine.dispose() del sa_session del sa_Session del sa_engine But after executing this Sql Server Profiler tells me that the connection is still opened. The only way is to kill the application. My sqlalchemy.__version__ is 0.4.8 cheers Fabio --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: passing tuple argument into sqlalchemy.sql.text string
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Faheem Mitha
Sent: 20 January 2009 22:05
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] passing tuple argument into
sqlalchemy.sql.text string
Hi,
I've got a query as follows:
from sqlalchemy.sql import text
gq = text(
SELECT decode_genotype(cell.snpval_id, snp.allelea_id,
snp.alleleb_id) FROM cell JOIN snp ON snp.fid =
cell.snp_id WHERE cell.patient_chipid IN ('DUKE1_plateA_A10.CEL',
'DUKE1_plateA_A11.CEL')
)
I want to pass in the tuple as an argument, and was wondering
how to do
it.
So, I'm looking for something conceptually like
gq = text(
SELECT decode_genotype(cell.snpval_id, snp.allelea_id,
snp.alleleb_id) FROM cell JOIN snp ON snp.fid =
cell.snp_id WHERE cell.patient_chipid IN :plist
)
gq = conn.execute(gq, plist=('DUKE1_plateA_A10.CEL',
'DUKE1_plateA_A11.CEL'))
Note, I want to pass in a tuple of arbitary length, so
changing this to
pass two string arguments would not do. Perhaps I'm supposed
to pass in
some bindparams too, but I don't know what type I should be using.
Regards, Faheem.
I'm not sure you can do that in the general case. I think bind
parameters (in the DBAPI sense) are only really intended for
substituting individual query parameters, not lists.
If you are happy to regenerate your query each time you want to execute
it, you could create a function which generates a string of the form
(:p0, :p1, :p2, :p3) for the given tuple length, and appends that to
the query.
If you use the SQLAlchemy expression language to build that query, it'll
do that for you automatically.
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 sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
On Jan 21, 2009, at 5:22 AM, Smoke wrote: Hi, I'm not a SQLAchemy expert ( just an average user... ). I have an application that's causing me some problems... It's a monitoring application that connects to a MS Sql Server, so it's always on. Sometimes happens that casualy I have a DBAPIError with pyodbc. The error is something like [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed After the first time I have this error every other DB operation generates this Error. So.. what I would like to do is completely close ( kill ) che active connection pool and recreate it. My code is somethink like this: sa_engine=sa.create_engine(dbUri, echo=False, strategy='threadlocal') sa_Session = sa.orm.scoped_session( sessionmaker(bind=sa_engine, autoflush=False) ) metadata = sa.MetaData(sa_engine) sa_session = sa_Session() and then: sa_session.close() sa_Session.close_all() sa_engine.dispose() del sa_session del sa_Session del sa_engine But after executing this Sql Server Profiler tells me that the connection is still opened. The only way is to kill the application. My sqlalchemy.__version__ is 0.4.8 assuming no other connections are checked out, that would close all connections. its possible the DBAPI is just not able to close the connection fully. try with a raw pyodbc application to see if this is the case. Other things to try are to use the NullPool with create_engine() which doesn't pool connections. Its also extremely unlikely that you should be using strategy='threadlocal' unless you are calling begin()/commit() from your Engine directly so you might want to take that out. --~--~-~--~~~---~--~~ 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] INSERT ... ON DUPLICATE KEY UPDATE
hello, i'm trying to use sqlalchemy 0.5.1 with python 2.5.4 on windows. and mysql 5.1. the task is simple: to keep fresh information about our servers. i can get a list of server names from AD, get some info about them and insert them into DB using Session.add(). if i run the script for the first time, it works fine. however, the next run fails: sqlalchemy.exc.IntegrityError: (IntegrityError) (1062, Duplicate entry... the table looks like this: 'id', 'int(11)', 'PRIMARY KEY', 'auto_increment' 'name', 'varchar(16)', 'UNIQUE' 'ip_address', 'varchar(16)' ... (some other columns which are not important) if i want to keep the data up to date, i need to update them if they already exist in DB. if i would use plain (literal) sql, i could execute insert in $SUBJ. but i would like to use sqlalchemy's native solution if it's possible. however, i don't know how to do this. can anyone help me? thanks c. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: INSERT ... ON DUPLICATE KEY UPDATE
camlost wrote: hello, i'm trying to use sqlalchemy 0.5.1 with python 2.5.4 on windows. and mysql 5.1. the task is simple: to keep fresh information about our servers. i can get a list of server names from AD, get some info about them and insert them into DB using Session.add(). if i run the script for the first time, it works fine. however, the next run fails: sqlalchemy.exc.IntegrityError: (IntegrityError) (1062, Duplicate entry... the table looks like this: 'id', 'int(11)', 'PRIMARY KEY', 'auto_increment' 'name', 'varchar(16)', 'UNIQUE' 'ip_address', 'varchar(16)' ... (some other columns which are not important) if i want to keep the data up to date, i need to update them if they already exist in DB. if i would use plain (literal) sql, i could execute insert in $SUBJ. but i would like to use sqlalchemy's native solution if it's possible. however, i don't know how to do this. can anyone help me? What about something like this: # get by primary key, you could also use a where clause item = session.query(db.YourClass).get(131312) if item: # do whatever to update print item else: # create a new one newItem = db.YourClass() print newItem session.add(newItem) # commit session.commit() thanks c. --~--~-~--~~~---~--~~ 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] Losing columns on join ?
Hi, I've been using sqlalchemy with sqlsoup and having some trouble getting my head around joins in here as opposed to sql. I've two tables, device + manufacturer [device] id, device_name, manufacturer [manufacturer] id, manufacturer_name I can't seem to get a result that contains both device_name and manufacturer_name... device.first() MappedBuildinfo_device(id=1,device_name='c701',manufacturer_id=1) manufacturer.first() MappedBuildinfo_manufacturer(id=1,manufacturer_name='alcatel') device.join(manufacturer).first() MappedBuildinfo_device(id=1034,device_name='s920',manufacturer_id=1) Hopefully I'm doing something really dumb :) --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Losing columns on join ?
On Jan 21, 10:59 am, Stuart Axon stua...@yahoo.com wrote: Hi, I've been using sqlalchemy with sqlsoup and having some trouble getting my head around joins in here as opposed to sql. I've two tables, device + manufacturer [device] id, device_name, manufacturer [manufacturer] id, manufacturer_name I can't seem to get a result that contains both device_name and manufacturer_name... device.first() MappedBuildinfo_device(id=1,device_name='c701',manufacturer_id=1) manufacturer.first( MappedBuildinfo_manufacturer(id=1,manufacturer_name='alcatel') device.join(manufacturer).first() MappedBuildinfo_device(id=1034,device_name='s920',manufacturer_id=1) SQLSoup may make this more complicated, but you should probably say x.add_entity(y).join(X.y).first(). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: passing tuple argument into sqlalchemy.sql.text string
On Wed, 21 Jan 2009 10:55:14 -, King Simon-NFHD78
simon.k...@motorola.com wrote:
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Faheem Mitha
Sent: 20 January 2009 22:05
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] passing tuple argument into
sqlalchemy.sql.text string
Hi,
I've got a query as follows:
from sqlalchemy.sql import text
gq = text(
SELECT decode_genotype(cell.snpval_id, snp.allelea_id,
snp.alleleb_id) FROM cell JOIN snp ON snp.fid =
cell.snp_id WHERE cell.patient_chipid IN ('DUKE1_plateA_A10.CEL',
'DUKE1_plateA_A11.CEL')
)
I want to pass in the tuple as an argument, and was wondering
how to do
it.
So, I'm looking for something conceptually like
gq = text(
SELECT decode_genotype(cell.snpval_id, snp.allelea_id,
snp.alleleb_id) FROM cell JOIN snp ON snp.fid =
cell.snp_id WHERE cell.patient_chipid IN :plist
)
gq = conn.execute(gq, plist=('DUKE1_plateA_A10.CEL',
'DUKE1_plateA_A11.CEL'))
Note, I want to pass in a tuple of arbitary length, so
changing this to
pass two string arguments would not do. Perhaps I'm supposed
to pass in
some bindparams too, but I don't know what type I should be using.
Regards, Faheem.
I'm not sure you can do that in the general case. I think bind
parameters (in the DBAPI sense) are only really intended for
substituting individual query parameters, not lists.
If you are happy to regenerate your query each time you want to execute
it, you could create a function which generates a string of the form
(:p0, :p1, :p2, :p3) for the given tuple length, and appends that to
the query.
If you use the SQLAlchemy expression language to build that query, it'll
do that for you automatically.
Hope that helps,
Simon
Hi Simon,
Thanks for your reply.
I've already been using sql expressions to create this query, but it
was not obvious how to do this using copy to, so I switched back to
not using it.
gq = select([func.decode_genotype(cell_table.c.snpval_id,
snp_table.c.allelea_id, snp_table.c.alleleb_id)],
from_obj=[cell_table.join(snp_table)], order_by =
'sort_key(snp.chromosome), snp.location')
patient_sublist = ['DUKE1_plateA_A10.CEL', 'DUKE1_plateA_A11.CEL']
gq = gq.where(cell_table.c.patient_chipid.in_(patient_sublist))
print gq
#gq = conn.execute(gq).fetchall()
The result of this is
SELECT decode_genotype(cell.snpval_id, snp.allelea_id, snp.alleleb_id)
AS decode_genotype_1 FROM cell JOIN snp ON snp.fid = cell.snp_id WHERE
cell.patient_chipid IN (:patient_chipid_1, :patient_chipid_2) ORDER BY
sort_key(snp.chromosome), snp.location
The question is, can I make this into a copy using sql expressions,
ie. can I do something like (the current version of my query)
copy (select array_to_string(array_agg(e.decode_genotype_1), E'\t')
from (SELECT decode_genotype(cell.snpval_id, snp.allelea_id,
snp.alleleb_id) AS decode_genotype_1 FROM cell JOIN snp ON snp.fid =
cell.snp_id WHERE cell.patient_chipid IN :plist ORDER BY
sort_key(snp.chromosome), snp.location) as e) to
'/tmp/btsnpSNP_6-chr.ped' with csv;
The differences between the version above and the version below, are
because I made additions to the query since I switched away from using
sql expressions.
Please CC me on any reply.
Regards, Faheem Mitha.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
I guess I'm kind of confused as to how it's supposed to create a table
that it doesn't even really know about yet. It hasn't successfully
reflected the table yet, so shouldn't know anything about its
structure. It seems like it'd just create an empty table at this
point.
engine = create_engine('CONNECTION STRING HERE')
meta = MetaData()
meta.bind = engine
# SQLAlchemy doesn't know about the remote table structure yet, how
will this do us any good?
meta.create_all()
# This is where I get that exception listed earlier. SQLAlche
artreqs = Table('tb_FSAR_Data_SampArtReq', meta, autoload=True,
autoload_with=engine)
On Jan 21, 1:14 am, Michael Bayer mike...@zzzcomputing.com wrote:
yeah i meant send along the CREATE TABLE to the mailing list here.
or a describe, whatever shows us what column type might be failing.
On Jan 20, 2009, at 9:45 PM, Greg wrote:
How do I go about doing this? Showing the create table?
On Jan 20, 4:54 pm, Rick Morrison rickmorri...@gmail.com wrote:
I'm just trying to introspect an existing production database, not
create any new tables.
The structure of the table is read when reflecting the table: it's
likely
that an unusual column definition would trigger an error like this,
and it
would be helpful to someone diagnosing the problem to get a better
clue as
to what might be happening.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: passing tuple argument into sqlalchemy.sql.text string
On Wed, 21 Jan 2009 08:26:13 -0800, jason kirtland
j...@discorporate.us wrote:
Faheem Mitha wrote:
Hi,
I've got a query as follows:
from sqlalchemy.sql import text
gq = text(
SELECT decode_genotype(cell.snpval_id, snp.allelea_id,
snp.alleleb_id) FROM cell JOIN snp ON snp.fid =
cell.snp_id WHERE cell.patient_chipid IN ('DUKE1_plateA_A10.CEL',
'DUKE1_plateA_A11.CEL')
)
I want to pass in the tuple as an argument, and was wondering how to do
it.
So, I'm looking for something conceptually like
gq = text(
SELECT decode_genotype(cell.snpval_id, snp.allelea_id,
snp.alleleb_id) FROM cell JOIN snp ON snp.fid =
cell.snp_id WHERE cell.patient_chipid IN :plist
)
gq = conn.execute(gq, plist=('DUKE1_plateA_A10.CEL',
'DUKE1_plateA_A11.CEL'))
Note, I want to pass in a tuple of arbitary length, so changing
this to pass two string arguments would not do. Perhaps I'm
supposed to pass in some bindparams too, but I don't know what type
I should be using.
IN takes a list of scalars, each of which requires its own :bind
parameter. On Postgresql you might find it more convenient to use
ANY, which takes a single array argument. WHERE
cell.patient_chipid ANY (:plist)
Thanks for the suggestion. Can such an array argument be passed in
from Python?
Regards, Faheem.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
As a disclaimer, this is a legacy system in which I have no control over, so what you are about to see is pretty disturbing :) http://pastebin.com/m10d49ac1 The formatting is pretty crazy, I'm hoping you can make use of this. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
Hey Greg, please set the output format to text (if you're in mssql 2005, there's a button over the query window with a tooltip that should say Results to text) and re-run the query. The text output will be a lot easier to read. Thanks --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
Greg wrote: As a disclaimer, this is a legacy system in which I have no control over, so what you are about to see is pretty disturbing :) http://pastebin.com/m10d49ac1 The formatting is pretty crazy, I'm hoping you can make use of this. If you have the MSSQL tools handy, try this: - Open Query Analyzer, connect to the server - Press F8 to get the Object Browser (unless it's already open - Go to the database and table in question. - Right click on the table name - Select Script object to new window as; in the submenu choose Create You should have a reasonably formatted table creation script, including indices and FKs. HTH, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: passing tuple argument into sqlalchemy.sql.text string
Faheem Mitha wrote:
On Wed, 21 Jan 2009 08:26:13 -0800, jason kirtland
j...@discorporate.us wrote:
Faheem Mitha wrote:
Hi,
I've got a query as follows:
from sqlalchemy.sql import text
gq = text(
SELECT decode_genotype(cell.snpval_id, snp.allelea_id,
snp.alleleb_id) FROM cell JOIN snp ON snp.fid =
cell.snp_id WHERE cell.patient_chipid IN ('DUKE1_plateA_A10.CEL',
'DUKE1_plateA_A11.CEL')
)
I want to pass in the tuple as an argument, and was wondering how to do
it.
So, I'm looking for something conceptually like
gq = text(
SELECT decode_genotype(cell.snpval_id, snp.allelea_id,
snp.alleleb_id) FROM cell JOIN snp ON snp.fid =
cell.snp_id WHERE cell.patient_chipid IN :plist
)
gq = conn.execute(gq, plist=('DUKE1_plateA_A10.CEL',
'DUKE1_plateA_A11.CEL'))
Note, I want to pass in a tuple of arbitary length, so changing
this to pass two string arguments would not do. Perhaps I'm
supposed to pass in some bindparams too, but I don't know what type
I should be using.
IN takes a list of scalars, each of which requires its own :bind
parameter. On Postgresql you might find it more convenient to use
ANY, which takes a single array argument. WHERE
cell.patient_chipid ANY (:plist)
Thanks for the suggestion. Can such an array argument be passed in
from Python?
Give it a try and let us know how it goes.
Cheers,
Jason
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
On Wed, Jan 21, 2009 at 12:16 PM, Michael Bayer mike...@zzzcomputing.comwrote: I think we might need to just change the *args approach in mssql reflecttable to do everything based on keyword arguments, and add in some isinstance(String) / isinstance(Numeric) to determine what args get sent where. I can see the if a is not None: on line 1138 not being reliable. Yeah, that sounds like a good approach. I'll have a look later today. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
Unfortunately, I'm connecting remotely via iODBC and do not have said tools. I'm running Ubuntu Linux, if you know of any way to get the same thing prettied up, let me know and I will. On Jan 21, 12:22 pm, Don Dwiggins d...@dondwiggins.net wrote: Greg wrote: As a disclaimer, this is a legacy system in which I have no control over, so what you are about to see is pretty disturbing :) http://pastebin.com/m10d49ac1 The formatting is pretty crazy, I'm hoping you can make use of this. If you have the MSSQL tools handy, try this: - Open Query Analyzer, connect to the server - Press F8 to get the Object Browser (unless it's already open - Go to the database and table in question. - Right click on the table name - Select Script object to new window as; in the submenu choose Create You should have a reasonably formatted table creation script, including indices and FKs. HTH, -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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] Slow subqueries in one-to-many relation with Joined Table Inheritance
Hello all,
I have a question about SQLAlchemy when dealing with joining on one-to-
many relationships.
When I try to load data with eager=True for relation with objects,
which subclasses from some base class, SQLAlchemy generates query with
subselect.
Subselects are quite painful for me, as I'm using MySQL so I can't
count on query-optymalization.
Is there some easy way to tell mapper to use outerjoins instead of
subselects?
Here is some simple example, that generates such a query:
let say we have 3 classes: Employee(object), Doctor(Employee),
MedCompany(object)
e_mapper = mapper( Employee,
employees,
polymorphic_on=employees.c.type,
polymorphic_identity='employee' )
d_mapper = mapper( Doctor,
doctors,
inherits = Employee,
polymorphic_identity='doctor' )
c_mapper = mapper( Company,
companies,
properties={
'employees' : relation(Doctor, lazy=False)
}
)
data = session.query(Company).all()
it generates something like:
SELECT companies... , anon_1...
FROM companies
LEFT OUTER JOIN (
SELECT employees... , doctors...
FROM employees INNER JOIN doctors ON employees.employee_id =
doctors.doctor_id
) AS anon_1 ON companies.company_id = anon_1.employees_company_id
One solution could be:
remove : lazy=False from c_mapper
and write query like:
data = session.query(Company).outerjoin(employees
).outerjoin(doctors
).options(
contains_eager('employees')
).all()
But is there any way to avoid writing such ugly queries?
--~--~-~--~~~---~--~~
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] pretend like an object was loaded, not created
I have a web application which is accessed from different sub-domains. Each sub-domain corresponds to one row/object in installation table. I am fetching this one row/object on every request which is unnecessary. My question is: how can I fetch this object only once and somehow stuff it inside a session on each request, from memory. Currently I'm doing this: def web_method(): # fetch every time: subdomain = get_subdomain() installation = session.query(Installation).filter_by (subdomain=subdomain).one() # use installation as filter session.query(SomeOb).filter_by(installation=installation).all() I want this: def web_method2(): # get from cache installation_data = get_subdomain_data() installation = Installation(**installation_data) # somehow add installation to session so that it appears # as if it has just been fetched from db ??? # this must work session.query(SomeOb).filter_by(installation=installation).all() # this must also work # select child/parent objects from corresponding relations len(installation.related_child_object_list) installation.related_parent_object Thanks, Tvrtko --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: pretend like an object was loaded, not created
the session.merge() method is provided with a flag dont_load=True to assist in common caching scenarios. it copies the state of a cached object into a given session so that you get the benefits of globally cached state combined with no data shared between sessions. some examples of moderately-to-completely transparent caches that build into Query are in the examples directory with the distribution under examples/query_caching. they might give you some ideas. On Jan 21, 2009, at 3:51 PM, qvx wrote: I have a web application which is accessed from different sub-domains. Each sub-domain corresponds to one row/object in installation table. I am fetching this one row/object on every request which is unnecessary. My question is: how can I fetch this object only once and somehow stuff it inside a session on each request, from memory. Currently I'm doing this: def web_method(): # fetch every time: subdomain = get_subdomain() installation = session.query(Installation).filter_by (subdomain=subdomain).one() # use installation as filter session.query(SomeOb).filter_by(installation=installation).all() I want this: def web_method2(): # get from cache installation_data = get_subdomain_data() installation = Installation(**installation_data) # somehow add installation to session so that it appears # as if it has just been fetched from db ??? # this must work session.query(SomeOb).filter_by(installation=installation).all() # this must also work # select child/parent objects from corresponding relations len(installation.related_child_object_list) installation.related_parent_object Thanks, Tvrtko --~--~-~--~~~---~--~~ 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] concatentated column property with joins
Perhaps someone could offer some guidance here. I am trying to make a
column_property. The value of this property is supposed to be the
concatenation of two fields from different tables, both joined to the
mapped table.
To make an example I have renamed these tables parent, child, and
grandchild. In this application, a parent can have zero or more
children. A child can have zero or one grandchildren. If there's a
child, one of the parent's children has a column first_child == 1.
The column property is supposed to be the concatenation of the name of
the first child and the name of the child's grandchild, if any. (One
of these names will always be NULL, and the other non-NULL). This
seems to work if there is a grandchild. But if there isn't a
grandchild I seem to be picking up the wrong grandchild. But so far
there's only one grandchild record, so maybe it always picks up the
wrong grandchild.
I realize this example sounds pretty strange -- in reality it involves
molecular biology and makes sense (I say), but I thought the real
names of the tables would be more confusing. If this approach is
wrong, is there a better way to concatenate two fields from a child
and grandchild of the mapped table? Basically I want each parent to
wind up with column set to a name of this first subsidiary thing,
whether it's the child or the grandchild. If anyone had the patience
to read this far, here's the example:
from_obj = self.parentTable.join(self.childTable,
onclause=self.parentTable.c.id == self.childTable.c.parent_id)
from_obj = from_obj.outerjoin(self.grandchildTable,
onclause=self.childTable.c.grandchild_id == self.grandchildTable.c.id)
special_name = select([self.childTable.c.name +
self.grandchildTable.c.name],
from_obj=from_obj,
whereclause=and_(self.parentTable.c.id ==
self.childTable.c.parent_id,
self.childTable.c.grandchild_id == self.grandchildTable.c.id,
self.childTable.c.first_child == 1)).as_scalar().label('special_name')
mapper(self.Parent, self.parentTable,
properties=dict(...
special_name=column_property(special_name),
...))
query = query.filter(Parent.somefield.like('%parent with grandchild
%'))
parents = list(query)
print parents[0].special_name
Name of grandchild as expected
query = query.filter(Parent.somefield.like('%parent with no
grandchild%'))
parents = list(query)
print parents[0].special_name
Name of some else's grandchild
--~--~-~--~~~---~--~~
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] checking if a column is in a list
Hello,
I've got a few SQL interfaces where I'd like to change my query based on the
columns required by the client. For example, in one situation, I only join
against my User table if my client requires a photo id:
# cols is the list of columns the client would like selected
if User.c.photo_id in cols:
table = table.join(User.table)
In another situation, I'd like to include the flags column in the query even
if the client ask for it
if not cols:
cols = [cls]
elif not (cls.c.flags in cols):
# ensure flags are there so we can add is_active
cols.append(cls.c.flags)
However, my tests for membership pass regardless of whether column is
actually in the list cols. That is,
Column('flags', BitField(), table=foo_table) in [Column('foo',
String(length=84, convert_unicode=False, assert_unicode=None),
table=foo_table), Column('bar', Text(length=None, convert_unicode=False,
table=foo_table)]
evaluates to True
Is the == operator not properly implemented for SQLAlchemy Columns? (I'm
using version 0.42)
Thanks,
Jack
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: checking if a column is in a list
On Jan 21, 2009, at 7:46 PM, Jack Stahl wrote:
Hello,
I've got a few SQL interfaces where I'd like to change my query
based on the columns required by the client. For example, in one
situation, I only join against my User table if my client requires a
photo id:
# cols is the list of columns the client would like selected
if User.c.photo_id in cols:
table = table.join(User.table)
In another situation, I'd like to include the flags column in the
query even if the client ask for it
if not cols:
cols = [cls]
elif not (cls.c.flags in cols):
# ensure flags are there so we can add is_active
cols.append(cls.c.flags)
However, my tests for membership pass regardless of whether column
is actually in the list cols. That is,
Column('flags', BitField(), table=foo_table) in [Column('foo',
String(length=84, convert_unicode=False, assert_unicode=None),
table=foo_table), Column('bar', Text(length=None,
convert_unicode=False, table=foo_table)]
evaluates to True
Is the == operator not properly implemented for SQLAlchemy Columns?
(I'm using version 0.42)
it is not properly implemented in the sense that it does not return
True or False, but if you''ve used SQLalchemy, you'd know that we
redefine the == operator to return ClauseElement objects. That's
why you can say somecolumn==5 and get an expression from it.
So you cannot use the in operator to search for a Column object in a
list. The solution however is simple. Use a set() instead, where the
objects will be compared based on the return value of __hash__() which
results in an object identity comparison.
if User.c.photo_id in set(cols):
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: checking if a column is in a list
Thanks Michael!
Properly was a poor choice of words on my part. Yes, of course, == is
overloaded to make where (etc) clauses pretty, I just didn't put two and two
together.
--jack
On Wed, Jan 21, 2009 at 5:38 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Jan 21, 2009, at 7:46 PM, Jack Stahl wrote:
Hello,
I've got a few SQL interfaces where I'd like to change my query
based on the columns required by the client. For example, in one
situation, I only join against my User table if my client requires a
photo id:
# cols is the list of columns the client would like selected
if User.c.photo_id in cols:
table = table.join(User.table)
In another situation, I'd like to include the flags column in the
query even if the client ask for it
if not cols:
cols = [cls]
elif not (cls.c.flags in cols):
# ensure flags are there so we can add is_active
cols.append(cls.c.flags)
However, my tests for membership pass regardless of whether column
is actually in the list cols. That is,
Column('flags', BitField(), table=foo_table) in [Column('foo',
String(length=84, convert_unicode=False, assert_unicode=None),
table=foo_table), Column('bar', Text(length=None,
convert_unicode=False, table=foo_table)]
evaluates to True
Is the == operator not properly implemented for SQLAlchemy Columns?
(I'm using version 0.42)
it is not properly implemented in the sense that it does not return
True or False, but if you''ve used SQLalchemy, you'd know that we
redefine the == operator to return ClauseElement objects. That's
why you can say somecolumn==5 and get an expression from it.
So you cannot use the in operator to search for a Column object in a
list. The solution however is simple. Use a set() instead, where the
objects will be compared based on the return value of __hash__() which
results in an object identity comparison.
if User.c.photo_id in set(cols):
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL Reflection Error
I think we might need to just change the *args approach in mssql reflecttable to do everything based on keyword arguments Yeah, that sounds like a good approach. I'll have a look later today. Attached is an untested patch against trunk that uses only kwargs to build out the tabledef. I won't be able to test this until late tomorrow, but feel free to apply and try it out. Rick --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- relect_via_kwargs.patch Description: Binary data
[sqlalchemy] Re: concatentated column property with joins
On Jan 21, 2009, at 6:15 PM, MHC wrote:
Perhaps someone could offer some guidance here. I am trying to make a
column_property. The value of this property is supposed to be the
concatenation of two fields from different tables, both joined to the
mapped table.
To make an example I have renamed these tables parent, child, and
grandchild. In this application, a parent can have zero or more
children. A child can have zero or one grandchildren. If there's a
child, one of the parent's children has a column first_child == 1.
The column property is supposed to be the concatenation of the name of
the first child and the name of the child's grandchild, if any. (One
of these names will always be NULL, and the other non-NULL). This
seems to work if there is a grandchild. But if there isn't a
grandchild I seem to be picking up the wrong grandchild. But so far
there's only one grandchild record, so maybe it always picks up the
wrong grandchild.
I realize this example sounds pretty strange -- in reality it involves
molecular biology and makes sense (I say), but I thought the real
names of the tables would be more confusing. If this approach is
wrong, is there a better way to concatenate two fields from a child
and grandchild of the mapped table? Basically I want each parent to
wind up with column set to a name of this first subsidiary thing,
whether it's the child or the grandchild. If anyone had the patience
to read this far, here's the example:
from_obj = self.parentTable.join(self.childTable,
onclause=self.parentTable.c.id == self.childTable.c.parent_id)
from_obj = from_obj.outerjoin(self.grandchildTable,
onclause=self.childTable.c.grandchild_id == self.grandchildTable.c.id)
special_name = select([self.childTable.c.name +
self.grandchildTable.c.name],
from_obj=from_obj,
whereclause=and_(self.parentTable.c.id ==
self.childTable.c.parent_id,
self.childTable.c.grandchild_id == self.grandchildTable.c.id,
self.childTable.c.first_child == 1)).as_scalar().label('special_name')
mapper(self.Parent, self.parentTable,
properties=dict(...
special_name=column_property(special_name),
...))
query = query.filter(Parent.somefield.like('%parent with grandchild
%'))
parents = list(query)
print parents[0].special_name
Name of grandchild as expected
query = query.filter(Parent.somefield.like('%parent with no
grandchild%'))
parents = list(query)
print parents[0].special_name
Name of some else's grandchild
one issue might be that you have a comparison to NULL going on, in the
case where you say childtable.grandchild_id==grandchildtable.id.
Although I think that would cause no row to be returned.
otherwise, see what the SQL looks like. you always have the option of
using a python descriptor instead of column_property() if the mapper
is not managing to render the column_property() as expected within the
larger query.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: pretend like an object was loaded, not created
This project moves so fast. I guess it's time to reread the documentation. Again. Knowing how great this project is, it was silly of me not to expect SQLAlchemy ho have such functionality built in into the public interface: I was kinda expecting to play with the object state or who knows what. On Jan 21, 10:35 pm, Michael Bayer mike...@zzzcomputing.com wrote: the session.merge() method is provided with a flag dont_load=True to assist in common caching scenarios. it copies the state of a cached object into a given session so that you get the benefits of globally cached state combined with no data shared between sessions. some examples of moderately-to-completely transparent caches that build into Query are in the examples directory with the distribution under examples/query_caching. they might give you some ideas. On Jan 21, 2009, at 3:51 PM, qvx wrote: I have a web application which is accessed from different sub-domains. Each sub-domain corresponds to one row/object in installation table. I am fetching this one row/object on every request which is unnecessary. My question is: how can I fetch this object only once and somehow stuff it inside a session on each request, from memory. Currently I'm doing this: def web_method(): # fetch every time: subdomain = get_subdomain() installation = session.query(Installation).filter_by (subdomain=subdomain).one() # use installation as filter session.query(SomeOb).filter_by(installation=installation).all() I want this: def web_method2(): # get from cache installation_data = get_subdomain_data() installation = Installation(**installation_data) # somehow add installation to session so that it appears # as if it has just been fetched from db ??? # this must work session.query(SomeOb).filter_by(installation=installation).all() # this must also work # select child/parent objects from corresponding relations len(installation.related_child_object_list) installation.related_parent_object Thanks, Tvrtko --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: does tometadata have to change foreign key schema name if it already specified?
Thank you, Michael, it was very helpful!
Michael Bayer написав:
this can be changed but the function still makes no sense. What if
you also said t3 = users.tometadata(metadata,
schema='SOME_SCHEMA') ? then you would want the system.users.id
FK to be changed.the tometadata() approach doesn't provide an API
that can take the use case of variable schema names into account.
Therefore, feel free to implement tometadata() yourself using
column.copy() and constraint.copy():
def copy_table(table, metadata, schema_map):
args = []
for c in table.columns:
args.append(c.copy())
for c in table.constraints:
if isinstance(c, ForeignKeyConstraint):
elem = list(c.elements)[0]
schema = schema_map[elem.column.table.schema]
else:
schema=None
args.append(c.copy(schema=schema))
return Table(table.name, metadata,
schema=schema_map[table.schema], *args)
usage:
m2 = MetaData()
t2 = copy_table(t, m2, {None:'SOME_SCHEMA', 'system':'system'})
will map tables with no schema to SOME_SCHEMA, tables with system to
the system schema.
On Jan 20, 2009, at 3:02 PM, sector119 wrote:
Hi ALL!
Does tometadata have to change foreign key schema name if it already
specified?
For example if I have column 'user_id' with 'system.users.id' FK -
tometadata change 'system' schema to SOME_SCHEMA. Is it ok, or
tometadata have to set schema to SOME_SCHEMA for street_id,
locality_id columns _only_ ?
transactions_t = Table('transactions', meta.metadata,
Column('id', Integer,
primary_key=True, autoincrement=False),
Column('user_id', Integer,
ForeignKey('system.users.id'), nullable=False),
Column('street_id', Integer,
ForeignKey('streets.id'), nullable=False),
Column('locality_id', Integer,
ForeignKey('locality.id'), nullable=False),
Column('sum', Integer,
nullable=False)
)
transactions_t.tometadata(metadata, schema='SOME_SCHEMA').create()
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
