Map to Arbitrary Select Using Raw SQL

[Michael Chambliss]

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?

Thanks!
Mike

[Michael Bayer]

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.

[Michael Chambliss]

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

[Michael Chambliss]

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:password@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

[Michael Bayer]

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.

[King Simon-NFHD78]

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

[Michael Bayer]

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 sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>

[Michael Chambliss]

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!