[sqlalchemy] Query for an empty to_many relationship?
Hello all, I'm puzzled as I cannot seem to do a pretty simple query: I have a many to many relationship (say from Vendor to Item) and want to get a list of all vendors which have no item. The obvious: Vendor.query.filter(Vendor.items == []).all() does not work (it produce a query without any where clause). Also, Vendor.items.count doesn't exist. It would be nice if we could express things like: Vendor.query.filter(Vendor.items.count() == 0).all() Maybe I'm just not awake yet, but can someone enlighten me how to do that? On a related note, I've seen the following block in the documentation (in the Relation Operators section): # locate an address sql address = session.query(Address).\ ...filter(Address.email_address=='[EMAIL PROTECTED]').one() ['[EMAIL PROTECTED]'] # use the address in a filter_by expression sql session.query(User).filter_by(addresses=address).all() Is it a simple mistake in the docs or is it really valid? In the later case, shouldn't [collection attribute == single instance] be an invalid case since the new contains, has and any operators appeared? Or is filter_by(x=y) not always equal to CurrentJoinPoint.x == y ? -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] _CompileOnAttr when executed under Pylons
In Pylons webapp, module model I have three tables, orm-mapped with
autoload=True, and join them as the following:
result_select = select([
merk_table.c.nMerkID,
merk_table.c.Merknaam,
model_table.c.nModelID,
model_table.c.Modelnaam,
model_table.c.Deuren,
model_table.c.nModelNo,
model_table.c.Jaar,
type_table.c.nTypeIDHist,
type_table.c['100'].label('prijs'),
type_table.c['724'].label('zitplaatsen'),
type_table.c.Typenaam_2],
from_obj=[
merk_table.join(
model_table,
merk_table.c.nMerkID==model_table.c.nMerkID).join(
type_table,
type_table.c.nModelID==model_table.c.nModelID)
]
).alias('auto')
Now, the first strange thing:
after retrieving rows, the field model_table.c.nModelNo (of type
INT(11), no keys, no indices, in underlying MySQL db) has as value an
object of type _CompileOnAttr.
And other fields of the same table and type (eg. Jaar) have proper
values (of type 'long').
The second strange thing:
when I import the module and run in from command line, the field
(nModelNo) gets filled properly (long), and _CompileOnAttr objects are
only assigned, when it is run as Pylons app.
Is this a bug, known issue?
I'm looking for it, but maybe someone knows the problem...
Cheers,
Artur
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Reflecting tables with foreign keys
Michael Bayer wrote: hi don - heres a script using SQLite which illustrates how the foreign key reflection works. This same sort of thing should be working on MS-SQL as well but I dont have access to an MS-SQL server here to test. If the example below is not working for MS-SQL, please file a trac ticket - we have some MS-SQL developers who can take a look. OK, thanks. I tried it with MySQL, and it worked fine, so it does seem to be mssql-specific. I've filed Ticket #979. (I can work around this without too much trouble.) # reflect an entire DB meta3 = MetaData(engine) meta3.reflect() This worked on a large mssql DB -- took several minutes to autoload 760 tables and views. I don't think I'll be doing that very often. 8^) -- 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Avoiding reflection (was: Re: Reflecting tables with foreign keys)
Rick Morrison wrote: Ugg, I am not a big table reflection fan:-( I am in the middle of readying a presentation, so unless someone else wants to jump on this, I'll take a look at this later on this week, along with integrating a reflection speed-up patch I remember from a while back. Thanks. I'm willing to use something other than reflection, if there's a good way I can avoid having to duplicate my schema (or significant chunks of it) in SA declarations. I'd like to stick to the DRY principle if at all possible. (I'm dealing with a legacy DB, and can't really take SA declarations as ground truth.) OTOH, I have the schema scripted out in some SQL files (for configuration management purposes). If it turns out to be worth the effort, I can imagine writing a script to parse them to construct the equivalent SA Table declarations and keep them up to date with schema changes. Any ideas along these lines? -- 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Avoiding reflection (was: Re: Reflecting tables with foreign keys)
I actually keep most of my DB schemas in SA syntax these days: Python is everywhere and SA does a great job of issuing DDL creates in the correct sequence based on a dependancy sort that I would otherwise have to myself. But I think a pretty useful tool, and fairly easy to create would be to have SA generate the DDL to a file instead of issuing it to the server -- there's your create script, and as a bonus, in the correct sequence. Given this, then SA could then also act as a kind of reverse-engineering tool. Turn it loose on a database with table reflection, and then after it has sucked in all the table definitions, have it output an SQL DDL script. Most databases can do that anyway today, but not always in the right dependancy order. And with a bit of work making things like sequences and defaults more generic, you would able to slurp in, say a Postgresql schema, and output MSSQL or DB2, and so on. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLError: (OperationalError) (2013, 'Lost connection to MySQL server during query')
Ross Vandegrift wrote: Hello everyone, I've got a particularly perplexing case of SQLAlchemy losing SQL connections to a MySQL 4.1 database. Before you roll your eyes, I am familiar with both wait_timeout and pool_recycle! On the MySQL server, this is set to eight hours: mysql show variables like 'wait_timeout'; +---+---+ | Variable_name | Value | +---+---+ | wait_timeout | 28800 | +---+---+ In my production.ini on the web server, I have tried a few settings: sqlalchemy.default.pool_recycle = 3600 and even: sqlalchemy.default.pool_recycle = 600 Neither seems to help though. Anyone have any ideas? Perhaps I'm doing something wrong in configuring production.ini? MySQL can throw a 2013 and hang up if the resources needed by a SELECT exceed the server's configuration. I'd check the server logs for clues, then adjust the key_buffer, sort_buffer, etc. as required. The ORM can generate some pretty intense SQL with extreme ease. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] weird SA + PostgreSQL with two schemas issue
Ran across a weird issue this morning, not sure if its even a SA issue,
may just be PostgreSQL (8.3) being weird.
I recently merged two pgsql databases into one database with two
schemas, and a foreign key connecting two of the tables.
he first schema is in the search path, the second is not. The problem
occurred when I specified the schema='schamaA' in my Table() calls then
SA wasn't able to see any foreign keys referencing it, but things worked
just fine when I removed them.
I suspect the problem is that even though I am specifying the schema
name in my add constraint ... foreign key... references
schema_name.table.column, PostgreSQL only seems to store the table
name, I believe this is because that schema is in the search path.
As I said, I have a work-around, just wanted to mention this in case
someone else runs into this issue.
And of course SQLAlchemy is great and has made my life easier, and in
general works like a dream.
The error message:
Can't locate any foreign key columns in primary join condition
'job.outdir_assetuid = nodehierarchy.uid' for relationship 'BObj.A
(AObj)'. Specify 'foreign_keys' argument to indicate which columns in
the join condition are foreign.
Test case:
from sqlalchemy import *
from sqlalchemy.orm import *
import sys
db_host = 'server'
db_name = 'db'
db_user = 'sqluser'
db_pass = 'notmypassword'
db_uri = 'postgres://'+db_user+':'+db_pass+'@'+db_host+'/'+db_name
metadata = MetaData(db_uri)
tbl_a = Table('nodehierarchy', metadata, autoload=True) # works
#tbl_a = Table('nodehierarchy', metadata, schema='asset', autoload=True)
# doesn't
tbl_b = Table('job', metadata, schema='farm', autoload=True)
class AObj(object):
def __repr__(self):
return self.name
class BObj(object):
def __repr__(self):
return self.name
a_mapper = mapper(AObj, tbl_a, properties = {})
b_mapper = mapper(BObj, tbl_b, properties = {
'A' : relation(AObj, primaryjoin=(tbl_b.c.outdir_assetuid==tbl_a.c.uid))
})
def testy(session):
bs= session.query(BObj).all()
for b in bs:
s = b.name
if b.A:
s += , + b.A.name
print s
try:
session = create_session()
testy(session)
except Exception, e:
print e
sys.exit (1)
sys.exit (0)
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: weird SA + PostgreSQL with two schemas issue
David Gardner wrote:
Ran across a weird issue this morning, not sure if its even a SA issue,
may just be PostgreSQL (8.3) being weird.
I recently merged two pgsql databases into one database with two
schemas, and a foreign key connecting two of the tables.
he first schema is in the search path, the second is not. The problem
occurred when I specified the schema='schamaA' in my Table() calls then
SA wasn't able to see any foreign keys referencing it, but things worked
just fine when I removed them.
I suspect the problem is that even though I am specifying the schema
name in my add constraint ... foreign key... references
schema_name.table.column, PostgreSQL only seems to store the table
name, I believe this is because that schema is in the search path.
Yeah, that seems to be the case. The allegedly fully qualified paths
we're reading during reflection are sensitive to the search path. There
are a couple other options:
1) don't use a schema= for the Tables on the search_path. SQLAlchemy
interprets 'schema=None' to mean 'anything that can be referenced
without a schema qualifier'- could be 'public', could be anything in the
path.
2) remove the other schemas from the search path prior to reflection,
and restore them after:
con = engine.connect()
con.execute('set search_path to public')
tbl_a = Table('nodehierarchy', metadata,
autoload=True, autoload_with=con)
tbl_b = Table(...)
con.execute('set search_path to public,asset')
3) provide a column override for that foreign key:
tbl_b = Table('job', metadata,
Column('outdir_assetuid', Integer,
ForeignKey('alt_schema.nodehierarchy.uid')),
schema='alt_schema_2',
autoload=True)
It may be that there's an improvement that can be made to the
cross-schema reflection, but I think the methodology of #1 above usually
works out pretty well.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Reflection + override error with SA 0.3.10 and MySQL
I'm using SQLAlchemy, reflecting from an existing MySQL database. I
want to override two DateTime columns to provide proper created and
updated timestamps (since MySQL can't handle auto-updating two
TIMESTAMP columns in the same row).
According to the SA docs, this should work; however, when I autoload
my Table objects, I get the error:
class 'sqlalchemy.exceptions.ArgumentError': Table 'tablename' is
already defined for this MetaData instance.
This short example illustrates the issue; the test_users table fails
to load. The error goes away if I either remove the foreign key
constraints in the 'test_pets' table, or remove the Column overrides
from the 'test_users' table.
It seems as if SA is instantiating the users mapper first (because the
pets table refers to it), but not paying attention to the override; it
then tries to instantiate the users mapper to effect the override, but
fails.
Thanks in advance...
##
from sqlalchemy import Table, Column, MetaData, create_engine, func,
ForeignKey
from sqlalchemy.orm import mapper
from sqlalchemy.types import DateTime, Integer, String
__engine = create_engine('mysql://user:[EMAIL PROTECTED]/test')
metadata = MetaData()
metadata.bind = __engine
# Create the tables for example's sake; in production, they
# already exist.
users = Table('test_users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40), nullable=False),
Column('created_at', DateTime, nullable=False),
Column('updated_at', DateTime, nullable=False),
mysql_engine='InnoDB')
pets = Table('test_pets', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40), nullable=False),
Column('user_id', Integer, ForeignKey('test_users.id'),
nullable=False),
Column('created_at', DateTime, nullable=False),
Column('updated_at', DateTime, nullable=False),
mysql_engine='InnoDB')
metadata.drop_all()
metadata.create_all()
metadata.clear()
pets = Table('test_pets', metadata,
Column('created_at', DateTime, default=func.now()),
Column('updated_at', DateTime, default=func.now(),
onupdate=func.now()),
autoload=True)
users = Table('test_users', metadata,
Column('created_at', DateTime, default=func.now()),
Column('updated_at', DateTime, default=func.now(),
onupdate=func.now()),
autoload=True)
class Pet(object):
pass
class User(object):
pass
mapper(Pet, pets)
mapper(User, users)
##
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Reflection + override error with SA 0.3.10 and MySQL
Tim Lesher wrote: I'm using SQLAlchemy, reflecting from an existing MySQL database. I want to override two DateTime columns to provide proper created and updated timestamps (since MySQL can't handle auto-updating two TIMESTAMP columns in the same row). According to the SA docs, this should work; however, when I autoload my Table objects, I get the error: class 'sqlalchemy.exceptions.ArgumentError': Table 'tablename' is already defined for this MetaData instance. This short example illustrates the issue; the test_users table fails to load. The error goes away if I either remove the foreign key constraints in the 'test_pets' table, or remove the Column overrides from the 'test_users' table. It seems as if SA is instantiating the users mapper first (because the pets table refers to it), but not paying attention to the override; it then tries to instantiate the users mapper to effect the override, but fails. You just need to swap the order of the two autoloads here. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Reflection + override error with SA 0.3.10 and MySQL
On Feb 25, 2008, at 9:03 PM, Tim Lesher wrote:
I'm using SQLAlchemy, reflecting from an existing MySQL database. I
want to override two DateTime columns to provide proper created and
updated timestamps (since MySQL can't handle auto-updating two
TIMESTAMP columns in the same row).
According to the SA docs, this should work; however, when I autoload
my Table objects, I get the error:
class 'sqlalchemy.exceptions.ArgumentError': Table 'tablename' is
already defined for this MetaData instance.
This short example illustrates the issue; the test_users table fails
to load. The error goes away if I either remove the foreign key
constraints in the 'test_pets' table, or remove the Column overrides
from the 'test_users' table.
It seems as if SA is instantiating the users mapper first (because the
pets table refers to it), but not paying attention to the override; it
then tries to instantiate the users mapper to effect the override, but
fails.
Thanks in advance...
upgrade to 0.4.3. The error message is now Table '%s' is already
defined for this MetaData instance. Specify 'useexisting=True' to
redefine options and columns on an existing Table object.. This new
behavior allows the useexisting=True flag, which has been around for a
long time, to use an existing table and overrride the columns, i.e.:
t1 = Table('users', meta, autoload=True)
t2 = Table('addresses', meta, Column('id', Integer, primary_key=True),
autoload=True, useexisting=True)
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
