[sqlalchemy] Opinion on correct use of Sqlalchemy
Hi,
I was after some opinions on the following use of SA.
1. Is there any problems using SA in this way ?
2. Is there better ways of achieving this ?
My Mapper
db.mapper(Asset, db.asset_table,
properties = {
'location': relation(Location, lazy=False),
'type': relation(AssetType, lazy=False)
}
)
My SA query:
ast = db.asset_table.c
loc = db.location_table.c
criteria_list = (
(ast.branch_id, branch_id),
(loc.code, location_code),
(ast.serial_no, serial_no),
(ast.asset_no, asset_no),
(ast.model_no, model_no),
)
criteria = and_(*[col == value for col, value in criteria_list if
value is not None])
s = outerjoin(db.asset_table, db.location_table)
s = s.outerjoin(db.asset_type_table)
count = select([func.count(ast.id)], criteria).execute().fetchone()[0]
if order_by is None:
order_by = [text('''
COALESCE(CAST(SUBSTRING(%s FROM '([0-9]{1,10})') AS
INTEGER), 0),
lower(%s)
''' % (loc.code, loc.code))]
s = s.select(criteria, use_labels=True, limit=limit, offset=offset,
order_by=order_by)
query = db.query(model.Asset).options(contains_eager('location'),
contains_eager('type'))
r = query.instances(s.execute())
return r, count
The SQL generated from this is exactly what I want.
I have been trying to achieve the same thing using the query() interface
but I can't seem to control the order by clause properly (it is
generated inside an inner query when I have eagerload relations, and
therefore doesn't sort the resulting resultset).
Thanks
Huy
--~--~-~--~~~---~--~~
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] SQLite and decimal.Decimal
Hi, I'm using SQLite in tests and there is a problem when using decimal.Decimal with sqlalchemy's Numeric type: SQLError: (InterfaceError) Error binding parameter 5 - probably unsupported type. This is not a new issue, a similar one was posted in http://groups.google.com/group/sqlalchemy/browse_thread/thread/300b757014c7d375/ad024f5365ab2eea It looks like a bug in sqlalchemy, but I'd rather discuss it here before creating a ticket. What I'd really like is that the Numeric field could work with decimal.Decimal in SQLite as it does with postgres, without any other external hack. Regards, André --~--~-~--~~~---~--~~ 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: SQLite and decimal.Decimal
the thing is, we have support for 6 different databases and postgres is the *only* one where its DBAPI implementation decides to use Decimal for numeric types. the rest return just floats. that means, people who have worked with databases other than postgres will be totally surprised to plug in SQLAlchemy one day and all the sudden they arent getting their expected float types back. So i dont think one DBAPI should dictate the behavior for all DBAPIs, and its definitely not a bug. its a feature request, asking for a generic numeric type that is guaranteed to return decimal.Decimal objects regardless of underlying DBAPI. So, I would rather add a new type called DecimalType that creates columns using NUMERIC semantics but explicitly returns decimal.Decimal objects. On Apr 7, 2007, at 9:16 AM, [EMAIL PROTECTED] wrote: Hi, I'm using SQLite in tests and there is a problem when using decimal.Decimal with sqlalchemy's Numeric type: SQLError: (InterfaceError) Error binding parameter 5 - probably unsupported type. This is not a new issue, a similar one was posted in http://groups.google.com/group/sqlalchemy/browse_thread/thread/ 300b757014c7d375/ad024f5365ab2eea It looks like a bug in sqlalchemy, but I'd rather discuss it here before creating a ticket. What I'd really like is that the Numeric field could work with decimal.Decimal in SQLite as it does with postgres, without any other external hack. Regards, André --~--~-~--~~~---~--~~ 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] Converting queries to mappings
Hi all -
I want to convert a handful of complicated queries into properties,
for simplicity and ease of maintaince.
I currently have a function in my class called 'dependencies':
def dependencies(self):
parent = DataRun.CMDS.alias('PARENT')
return object_session(self).query(Command).select(
and_(parent.c.target_id == self.target_id),
from_obj=[
join(parent, DataRun.DEPS,
parent.c.target_id == DataRun.DEPS.c.target_id).join(
DataRun.CMDS, DataRun.DEPS.c.dep ==
DataRun.CMDS.c.name)])
This works correctly. I'd like to reduce this to an entry in my
mapper() statement.
mapper(Command, DataRun.CMDS)
Alternately, if that's not possible, I'd like to determine how to get
to the table object for CMDS, DEPS objects (which are just table's)
without using the global DataRun. Are there variables associated with
mapped classes that I can use to build references to other tables?
Cheers,
--Benn
--~--~-~--~~~---~--~~
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: simple DateTime select
Hi Ryan,
On Apr 7, 6:10 pm, rkennedy [EMAIL PROTECTED] wrote:
I'm new to SQLAlchemy and am trying to select objects from the
following table that occurred before a specified date.
I'm pretty new myself, but I've been reading the docs a bunch today
and I may be able to help.
event_table = Table('event', meta,
Column('event_id', Integer, primary_key=True),
Column('title', String()),
Column('start_time', DateTime,default=func.now()),
Column('stop_time', DateTime,default=func.now()),
)
[snip]
event = self.sess.query(model.Event).get_by(start_time '2007-10-19
10:23:54')
I think that when you use get_by this way you are supplying a keyword
argument and not referencing a column name. Try something like
(untested):
event = self.sess.query(model.Event).get_by(event_table.c.start_time
'2007-10-19 10:23:54')
That builds a ClauseElement that should do what you want (if I
understand correctly).
I hope I'm correct and this is helpful.
Regards,
Matthew
--~--~-~--~~~---~--~~
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: Converting queries to mappings
Ah ok.
Basically, yeah, I'm trying to put all the 'mapper' type behaviors in
one place, instead of having some of the properties specified where I
declare the mapper, and others declared as functions in the class
itself. There might be a better python-esque way of doing this, I
admit, but I just thought that there should be a way of doing it as a
mapper property instead of as a seperate clause.
How about getting a different existing Table object for a table that's
not mapped to a class? I don't want to call Table(...,autoload=True)
again because that sounds like it'd load the entire metadata for the
table each time the function was called. Can I walk my way through
the metadata object or something?
Cheers,
--Benn
On Apr 7, 4:15 pm, Michael Bayer [EMAIL PROTECTED] wrote:
there is a blue sky kind of idea to add a lazyloader()
MapperProperty construct which could accept any callable, such as the
one you have below, and it would be called in the same manner as any
other lazy loader. But youd still be writing out more or less that
function. im not sure what need youre trying to fill, strictly that
the function should be defined in the same place as the mapper? you
could just attach your dependencies function onto your Command
class at any point in your application.
anyway, to get the mapped table for a class:
class_mapper(Command).mapped_table
On Apr 7, 2007, at 6:43 PM, Benn Bollay wrote:
Hi all -
I want to convert a handful of complicated queries into properties,
for simplicity and ease of maintaince.
I currently have a function in my class called 'dependencies':
def dependencies(self):
parent = DataRun.CMDS.alias('PARENT')
return object_session(self).query(Command).select(
and_(parent.c.target_id == self.target_id),
from_obj=[
join(parent, DataRun.DEPS,
parent.c.target_id == DataRun.DEPS.c.target_id).join(
DataRun.CMDS, DataRun.DEPS.c.dep ==
DataRun.CMDS.c.name)])
This works correctly. I'd like to reduce this to an entry in my
mapper() statement.
mapper(Command, DataRun.CMDS)
Alternately, if that's not possible, I'd like to determine how to get
to the table object for CMDS, DEPS objects (which are just table's)
without using the global DataRun. Are there variables associated with
mapped classes that I can use to build references to other tables?
Cheers,
--Benn
--~--~-~--~~~---~--~~
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: simple DateTime select
Thanks, Matthew. Looks like SA is still complaining about the global name not being defined. event = self.sess.query(model.Event).get_by(event_table.c.start_time '2007-10-19 10:23:54') The above code produces a similar error... NameError: global name 'event_table' is not defined When I try retrieving objects that occurred on a specific date, e.g event = self.sess.query(model.Event).get_by(start_time='2006-10-19 10:23:54') ..everything checks out ok. Perhaps it has something to do with the way the operator is being used? --~--~-~--~~~---~--~~ 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: simple DateTime select
Hi Ryan, On Apr 7, 8:44 pm, rkennedy [EMAIL PROTECTED] wrote: Thanks, Matthew. Looks like SA is still complaining about the global name not being defined. event = self.sess.query(model.Event).get_by(event_table.c.start_time '2007-10-19 10:23:54') The above code produces a similar error... NameError: global name 'event_table' is not defined Does the above code have access to your event table definition? That looks to me like Python can't find event table. I don't think SA has even gotten to it yet. Make sure that you've made the necessary imports to get event_table into the local namspace. When I try retrieving objects that occurred on a specific date, e.g event = self.sess.query(model.Event).get_by(start_time='2006-10-19 10:23:54') ..everything checks out ok. Perhaps it has something to do with the way the operator is being used? When you use the start_time=... form above, you are passing a keyword argument to the get_by method. The start_time name is used inside get_by and so it doesn't need to exist outside. When you use the start_time==... form, I think that you'll find that you will get the same NameError that you got when you tried start_time Regards, Matthew --~--~-~--~~~---~--~~ 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: simple DateTime select
Hi Matthew, Your assessment was spot on. Apparently the start_time field also needed an explicit reference to the database table import. The following query now does the trick... events = self.sess.query(model.Event).select_by(model.Event.c.start_time '2007-10-19 10:23:54') Thanks again for your help...much appreciated! Ryan --~--~-~--~~~---~--~~ 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: Converting queries to mappings
On Apr 7, 2007, at 8:19 PM, Benn Bollay wrote: How about getting a different existing Table object for a table that's not mapped to a class? I don't want to call Table(...,autoload=True) again because that sounds like it'd load the entire metadata for the table each time the function was called. Can I walk my way through the metadata object or something? if you did say Table(..., autoload=True), it pulls the existing table from the metadata - doesnt re-reflect. but also, you can just look in metadata.tables to get at it too. --~--~-~--~~~---~--~~ 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: simple DateTime select
FYI the comparisons to a DateTime column are better done against a similar datetime() object, rather than a string - select(table.c.date_col datetime.datetime(2007, 10, 19, 10, 23, 54)) On Apr 7, 2007, at 11:44 PM, rkennedy wrote: Hi Matthew, Your assessment was spot on. Apparently the start_time field also needed an explicit reference to the database table import. The following query now does the trick... events = self.sess.query(model.Event).select_by(model.Event.c.start_time '2007-10-19 10:23:54') Thanks again for your help...much appreciated! Ryan --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
