Re: [sqlalchemy] Map to Arbitrary Select Using Raw SQL
Michael Chambliss wrote:
Michael Bayer wrote:
On Jan 26, 2010, at 7:48 PM, Michael Chambliss wrote:
Presently, I'm trying to determine the best way to map a class against an
arbitrary select where the select is constructed from raw SQL. Based on this,
it's possible using the expression builders:
http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects
from_statement() is the primary means of doing this, assuming you're mapped to
some kind of construct already and just need to select the rows from some
particular statement you happen to have as a string. This means, the
configuration of your application would consist of mapping your classes to
table metadata as per the documentation, and then at query time you can load
and persist objects, using all hand-written SQL to load rows.
In a theoretical example, say I have a CAR table that refers to both a
CAR_TYPE table and CAR_ATTRIBUTES table. CAR_TYPE is simply an
enumeration for a static list of types, and CAR_ATTRIBUTES is an
arbitrarily long list of key,value attributes (color, weight, top
speed, etc). So, ultimately, a Car is made up of these three.
I'd want to bake all of these together, passing in a CAR.ID (primary
key) to map to a Car instance.
Michael - this may better illustrate the question. Note the ??query??.
engine = create_engine('oracle://user:passw...@server:1521/database')
Session = sessionmaker(bind=engine)
s = Session()
metadata = MetaData()
vehicle_query =
select
v.vehicle_id as vehicle_id,
v.name as vehicle_name,
v.description as vehicle_description,
vt.name as vehicle_type,
vs.name as vehicle_status,
v.modify_dttm as vehicle_modify_dttm
from
vehicle v,
vehicle_type vt,
vehicle_status vs
where
v.vehicle_id = :vehicle_id
and vs.vehicle_status_id = v.vehicle_status_id
and vt.vehicle_type_id = v.vehicle_type_id
class Vehicle(object):
def __init__(self, vehicle_id, vehicle_name, vehicle_description,
vehicle_type, vehicle_status, vehicle_modify_dttm):
self.vehicle_id = vehicle_id
self.vehicle_name = vehicle_name
self.vehicle_description = vehicle_description
self.vehicle_type = vehicle_type
self.vehicle_status = vehicle_status,
self.vehicle_modify_dttm = vehicle_modify_dttm
def __repr__(self):
return Name('%s') % (self.vehicle_name)
# Here's where I get sideways...
# Obviously this won't work, but I'm not sure how to map the query to
the class
mapper(Vehicle, ??query??)
metadata.bind = engine
q = s.query(Vehicle).from_statement(vehicle_query).params(vehicle_id=123)
for vehicle in q:
print vehicle.vehicle_id, vehicle.vehicle_name
#...
Thanks,
Mike
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Map to Arbitrary Select Using Raw SQL
Michael Chambliss wrote: Hey Michael - thanks for the patient and helpful response. I played around with the from_statement() approach earlier today, but what I was able to derive seemed to follow the standard model of define table, define class, map table to class, execute query. That approach would be great assuming I can map to some composite result (IE, multi-table/function). Perhaps I need to dive further into this to determine how joins are handled and how the mapping should be defined for them. The original example I linked seemed to imply some mapping magic in that the Customer class wasn't defined but was mapped to the complex Selectable. However, my research and attempts to do this mapping with from_statement() proved fruitless. In a theoretical example, say I have a CAR table that refers to both a CAR_TYPE table and CAR_ATTRIBUTES table. CAR_TYPE is simply an enumeration for a static list of types, and CAR_ATTRIBUTES is an arbitrarily long list of key,value attributes (color, weight, top speed, etc). So, ultimately, a Car is made up of these three. I'd want to bake all of these together, passing in a CAR.ID (primary key) to map to a Car instance. I prefer to live in SQL because I'm pretty good at it, and I need to reference, specifically, Oracle Spatial and Workspace functions. I do not, however, need to chain additional filters off of this, handle updates/inserts (at least at this point), etc. I'm literally just looking for a cheap way to map a row to an object and scoop up connection pooling, type handling, and other great things I'll probably learn about as I go. Assuming you've configured Car, CarType and CarAttributes with mappers, and associated them all together using relation(), and the general usage is hypothetically along these lines: sess.query(Car).from_statement(select car.*, car_attributes.*, car_type.* from ) you can route the individual columns into attributes and collections on each Car object using contains_eager(). the naming convention here is a little tedious, but you want to go with raw SQL so here you go, assuming each table had id and name columns, substitute the actual names.. sess.query(Car).from_statement( select car.id as car_id, car.name as car_name, car_attributes.id as car_att_id, car_attributes.name as car_att_name, car_type.id as car_type_id, car_type.name as car_type_name from ).options( contains_eager(Car.type, alias='car_type'), contains_eager(Car.attributes, alias='car_att') ).all() havent tried it though. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Map to Arbitrary Select Using Raw SQL
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: 27 January 2010 16:31 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Map to Arbitrary Select Using Raw SQL Michael Chambliss wrote: Hey Michael - thanks for the patient and helpful response. I played around with the from_statement() approach earlier today, but what I was able to derive seemed to follow the standard model of define table, define class, map table to class, execute query. That approach would be great assuming I can map to some composite result (IE, multi-table/function). Perhaps I need to dive further into this to determine how joins are handled and how the mapping should be defined for them. The original example I linked seemed to imply some mapping magic in that the Customer class wasn't defined but was mapped to the complex Selectable. However, my research and attempts to do this mapping with from_statement() proved fruitless. In a theoretical example, say I have a CAR table that refers to both a CAR_TYPE table and CAR_ATTRIBUTES table. CAR_TYPE is simply an enumeration for a static list of types, and CAR_ATTRIBUTES is an arbitrarily long list of key,value attributes (color, weight, top speed, etc). So, ultimately, a Car is made up of these three. I'd want to bake all of these together, passing in a CAR.ID (primary key) to map to a Car instance. I prefer to live in SQL because I'm pretty good at it, and I need to reference, specifically, Oracle Spatial and Workspace functions. I do not, however, need to chain additional filters off of this, handle updates/inserts (at least at this point), etc. I'm literally just looking for a cheap way to map a row to an object and scoop up connection pooling, type handling, and other great things I'll probably learn about as I go. Assuming you've configured Car, CarType and CarAttributes with mappers, My reading of the original email is that configuring the mapping is the problem that the OP is having. To the OP: As far as I'm aware, you can't configure a mapper directly against a textual SQL statement. However, you might be able to get away with using the declarative syntax to define your Car class: http://www.sqlalchemy.org/docs/reference/ext/declarative.html#synopsis You'll have to give it a table name, which will have the effect of defining a Table object even though no such table exists in the database, but I don't think this matters. Then you could use the query that Mike suggested to actually retrieve rows. Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Map to Arbitrary Select Using Raw SQL
Michael Chambliss wrote:
vehicle_query =
select
v.vehicle_id as vehicle_id,
v.name as vehicle_name,
v.description as vehicle_description,
vt.name as vehicle_type,
vs.name as vehicle_status,
v.modify_dttm as vehicle_modify_dttm
from
vehicle v,
vehicle_type vt,
vehicle_status vs
where
v.vehicle_id = :vehicle_id
and vs.vehicle_status_id = v.vehicle_status_id
and vt.vehicle_type_id = v.vehicle_type_id
class Vehicle(object):
def __init__(self, vehicle_id, vehicle_name, vehicle_description,
vehicle_type, vehicle_status, vehicle_modify_dttm):
self.vehicle_id = vehicle_id
self.vehicle_name = vehicle_name
self.vehicle_description = vehicle_description
self.vehicle_type = vehicle_type
self.vehicle_status = vehicle_status,
self.vehicle_modify_dttm = vehicle_modify_dttm
def __repr__(self):
return Name('%s') % (self.vehicle_name)
# Here's where I get sideways...
# Obviously this won't work, but I'm not sure how to map the query to
the class
mapper(Vehicle, ??query??)
the mapper really requires Table metadata in order to be mapped.
vehicle = Table(vehicle, metadata, autoload=True)
vehicle_type = Table(vehicle_type, metadata, autoload=True)
vehicle_status = Table(vehicle_status, metadata, autoload=True)
j = vehicle.join(vehicle_type,
vehicle.c.foo==vehicle_type.c.bar).join(vehicle_status,
vehicle.c.bat==vehicle_status.c.bat)
mapper(Vehicle, j)
I think you'd find that from_statement() is a lot more work for the
example query you have above. With such a mapping, you'd get the base set
of rows with query(Vehicle).all() and simple filtering with
query.(Vehicle).filter_by(vehicle_status_description='foo') , for example.
But from_statement can be invoked at any point.
from
vehicle v,
vehicle_type vt,
vehicle_status vs
metadata.bind = engine
q = s.query(Vehicle).from_statement(vehicle_query).params(vehicle_id=123)
for vehicle in q:
print vehicle.vehicle_id, vehicle.vehicle_name
#...
Thanks,
Mike
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Map to Arbitrary Select Using Raw SQL
King Simon-NFHD78 wrote:
You'll have to give it a table name, which will have the effect of
defining a Table object even though no such table exists in the
database, but I don't think this matters. Then you could use the query
that Mike suggested to actually retrieve rows
Thanks, Simon - I ended up doing something much like this. I went down
a slightly different path before I realized I was basically doing what
you suggested. I ended up with the following. I'm not sure what sort
of side effects I'd see from this (other than, perhaps, ridicule :), but
it does work the way I want and seems safe enough if used strictly in
this way. Note, 'real_vehicle' is not an actual table in the database.
I think I'll retool this using the declarative_base to see how that
works out.
real_vehicle = Table('real_vehicle', metadata,
Column('vehicle_id', Integer, primary_key=True),
Column('vehicle_name', String),
Column('vehicle_description', String),
Column('vehicle_type', String),
)
class Vehicle(object):
pass
mapper(Vehicle, real_vehicle)
vehicle_query =
select
v.vehicle_id as 'vehicle_id',
v.name as 'vehicle_name',
v.description as 'vehicle_description',
vt.name as 'vehicle_type'
from
vehicle v,
vehicle_type vt
where
vt.vehicle_type_id = v.vehicle_type_id
and v.vehicle_id = :vehicle_id
q = s.query(Vehicle).from_statement(vehicle_query).params(vehicle_id=1)
for vehicle in q:
print vehicle.vehicle_id, vehicle.vehicle_name,
vehicle.vehicle_description, vehicle.vehicle_type
Thanks again, Simon and Michael, for the help!
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Map to Arbitrary Select Using Raw SQL
On Jan 26, 2010, at 7:48 PM, Michael Chambliss wrote: Hello, I'm new to SQLAlchemy (and really Python in general) and admittedly I'm probably not following the best process for learning it. Ultimately, I'd prefer to deal with raw SQL as opposed to working through the expression building methods despite the benefits of the framework I leave on the table. The down side, of course, is that the tutorials aren't written for this wanton approach. Presently, I'm trying to determine the best way to map a class against an arbitrary select where the select is constructed from raw SQL. Based on this, it's possible using the expression builders: http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects so I assume it's possible using SQL. I've researched the text() and Query from_statement() methods, but these don't appear to be applicable in this case. Is there another method to short-cut the mapping of a rowset (generated by raw SQL) to an object? from_statement() is the primary means of doing this, assuming you're mapped to some kind of construct already and just need to select the rows from some particular statement you happen to have as a string. This means, the configuration of your application would consist of mapping your classes to table metadata as per the documentation, and then at query time you can load and persist objects, using all hand-written SQL to load rows. But the literal request to map to an arbitrary select with raw SQL is strange, but this may be semantic - the word map in SQLA parlance means to construct a mapper(), which is a configuration-time, not a query-time, concern. Your mapper would be against the fixed SQL statement, and would be invoked when, for example, you said query.all(). However, that would be all you can do with it - SQLA doesn't parse SQL strings, so its impossible for it to, by itself, alter your string SQL statement to add filtering criterion, ordering, or do anything else with it. Your mapper also wouldn't be able to persist anything - since the requirement that you map to raw SQL means you don't want to tell it which individual tables are referenced in your select. But its all absolutely possible I think we just need more specifics as to what patterns you're looking to achieve. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Map to Arbitrary Select Using Raw SQL
Michael Bayer wrote: On Jan 26, 2010, at 7:48 PM, Michael Chambliss wrote: Presently, I'm trying to determine the best way to map a class against an arbitrary select where the select is constructed from raw SQL. Based on this, it's possible using the expression builders: http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects from_statement() is the primary means of doing this, assuming you're mapped to some kind of construct already and just need to select the rows from some particular statement you happen to have as a string. This means, the configuration of your application would consist of mapping your classes to table metadata as per the documentation, and then at query time you can load and persist objects, using all hand-written SQL to load rows. Hey Michael - thanks for the patient and helpful response. I played around with the from_statement() approach earlier today, but what I was able to derive seemed to follow the standard model of define table, define class, map table to class, execute query. That approach would be great assuming I can map to some composite result (IE, multi-table/function). Perhaps I need to dive further into this to determine how joins are handled and how the mapping should be defined for them. The original example I linked seemed to imply some mapping magic in that the Customer class wasn't defined but was mapped to the complex Selectable. However, my research and attempts to do this mapping with from_statement() proved fruitless. In a theoretical example, say I have a CAR table that refers to both a CAR_TYPE table and CAR_ATTRIBUTES table. CAR_TYPE is simply an enumeration for a static list of types, and CAR_ATTRIBUTES is an arbitrarily long list of key,value attributes (color, weight, top speed, etc). So, ultimately, a Car is made up of these three. I'd want to bake all of these together, passing in a CAR.ID (primary key) to map to a Car instance. I prefer to live in SQL because I'm pretty good at it, and I need to reference, specifically, Oracle Spatial and Workspace functions. I do not, however, need to chain additional filters off of this, handle updates/inserts (at least at this point), etc. I'm literally just looking for a cheap way to map a row to an object and scoop up connection pooling, type handling, and other great things I'll probably learn about as I go. Thanks again for your help! Mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
