Re: [sqlalchemy] any way to pre cook a monster query?
Michael Bayer wrote: Now, is there any way I can pre-cook this (eg: at module-level) such that I can later just plug in self.id and on_date, bind to a session and call .all() on it? It seems a bit wasteful to do all the SQL generation on every query when it's almost all identical all the time... easiest way, call your Query from a def. I'm not 100% what you mean here, but it looks like just put the above lump in a function definition and call it, but that sounds like all the work will still be done when the function is called? most of the work in SQL generation isn't internally cached anyway so you aren't saving much by having the same Query lying around. This is a bit puzzling. Surely all the taking of python Query, select, and_, join, etc objects, running them through dialects, etc and ending up with a string of sql only needs to be done once; then it should just be a case of formatting the values and plugging them into the text string at the bind points, which obviously needs to be done for each call... what am I missing? second way, you can use bindparam() for the binds as others have mentioned, and then params() to set the values as needed, but the missing link is that you want the Query against your own particular session at the moment. I haven't yet gotten the chance to add a with_session() method to Query but you can do this easily enough yourself: from sqlalchemy.orm.query import Query, _generative class MyQuery(Query): @_generative what does @_generative do? def with_session(self, session): self.session = session Session = sessionmaker(query_cls=MyQuery) so have your query lying around: q = Session().query(...).filter(...) Could I instead just do: q = MyQuery(...).filter(...) and then use it with: q.with_session(my_session).params(foo='bar').all() ? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- 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:
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] any way to pre cook a monster query?
Chris Withers wrote: This is a bit puzzling. Surely all the taking of python Query, select, and_, join, etc objects, running them through dialects, etc and ending up with a string of sql only needs to be done once; then it should just be a case of formatting the values and plugging them into the text string at the bind points, which obviously needs to be done for each call... what am I missing? I would consider that to be a minor-to-moderate optimization that would increase complexity and would not usually even be used. Piecing together a select() construct and compiling to a string is a relatively inexpensive operation.The vast majority of time in Query is spent about 1/3rd-half on the database side and the rest in fetching rows and instantiating/populating objects. I would recommend running some profiling to see this in effect (though maybe I wouldn't, since I don't think you're going to like it much). reddit.com uses SQLA expression constructs, and they create and compile them fresh for each usage. They even built on SQLA 0.3 originally where the compilation steps were way more expensive.SQL generation of a SELECT construct nowadays takes from 200-500 function calls. The overhead within Query is higher as it has to traverse mapped classes to generate. But its a fraction of what it takes to fetch rows, and in that area we'd ultimately like to reimplement that part in C. the mechanics of caching the compiled statement would be like this: q = query(...).filter(...).order_by(...) no SQL construct is generated. then: q.all() or iter(q) utimately this calls q.__iter__(), which is the point at which a SQL execution occurs. We have two choices. We can generate the select() construct at this point and store that, but not do the string part. At this level, we have the issue that if any query objects were cached before subsequent attributes were attached to mappers, it will break. Granted, a very edge case. Or, we can generate the compiled() object, which contains the SQL string as well as a lot of important metadata about the statement used when fetching results. But this is not possible without access to a dialect and changes for every dialect - so we can key the string off of the current dialect in a dictionary. But another super edge case, some inexperienced users create new engines on every request of their application - the dictionary would grow and they'd say we have a memory leak (this has actually happened). We can key the string off the dialect's class instead, but then each dialect object has options configured, some user configured and some derived from the server information about the database - in a multi-engine scenario with varied backend characteristics, this could again break. These are all slim edge cases, but they would still be vulnerabilities in such an approach. Other points: - its a lot of work to implement and test. We have a QueryContext that carries along information about the query and onto the result generation - those are per-run so we have to re-generate those for an individual compiled() when pulling from cache, and make sure whatever bits it needs are still there. Testing now means we have a lot of tests that run Query objects twice to ensure results are created in exactly the same way on a second run as the first. - we currently implement LIMIT and OFFSET values as part of the SQL string, i.e. not as bind parameters. There is a ticket for this, but its not clear yet that every backend supports them as binds (i.e. I'm not sure, I may have heard MS-SQL doesn't like it perhaps). So caching wouldn't get us anything for a query that's used in pagination until that is done. Suppose we get LIMIT/OFFSET to work as binds. If we cache at the point of __iter__, but then any subsequent generation blows away the cached statement for the new Query, calling limit() or offset() (or first(), one()) can't use the cached construct anyway (as the limit/offset implies a different SQL statement), so we must add more logic to account for this. limit() and offset() would have to be smart enough to re-use a cached a SQL string (cached on the previous generation, probably) that included the appropriate LIMIT/OFFSET syntax. Suppose LIMIT/OFFSET work as binds but only on some dialects. Now the caching gets even more complicated, as it has to deal with whether or not the dialect supports reusing the same statement with different LIMIT/OFFSET. Nothing there with limit/offest is impossible, just a PITA, and creates more complicated/harder to understand/more fragile code which will ultimately generate more support requests. - Query doesn't have a with_session() method right now - little to no users are actually doing what you want here, i.e. making a module level Query object and keeping it lying around. Its not a pattern most people care to use. So despite added complexity, its questionable how often it would even be worth it. All of that said
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] any way to pre cook a monster query?
Le mercredi 27 janvier 2010 à 12:31 -0500, Michael Bayer a écrit : Or, we can generate the compiled() object, which contains the SQL string as well as a lot of important metadata about the statement used when fetching results. But this is not possible without access to a dialect and changes for every dialect - so we can key the string off of the current dialect in a dictionary. But another super edge case, some inexperienced users create new engines on every request of their application - the dictionary would grow and they'd say we have a memory leak (this has actually happened). You could use a weak key dictionary, which would remove the entry as soon as all strong references to the dialect disappear. -- 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] any way to pre cook a monster query?
Antoine Pitrou wrote: Le mercredi 27 janvier 2010 à 12:31 -0500, Michael Bayer a écrit : Or, we can generate the compiled() object, which contains the SQL string as well as a lot of important metadata about the statement used when fetching results. But this is not possible without access to a dialect and changes for every dialect - so we can key the string off of the current dialect in a dictionary. But another super edge case, some inexperienced users create new engines on every request of their application - the dictionary would grow and they'd say we have a memory leak (this has actually happened). You could use a weak key dictionary, which would remove the entry as soon as all strong references to the dialect disappear. sssh ! -- 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] any way to pre cook a monster query?
Antoine Pitrou wrote: Le mercredi 27 janvier 2010 à 12:31 -0500, Michael Bayer a écrit : Or, we can generate the compiled() object, which contains the SQL string as well as a lot of important metadata about the statement used when fetching results. But this is not possible without access to a dialect and changes for every dialect - so we can key the string off of the current dialect in a dictionary. But another super edge case, some inexperienced users create new engines on every request of their application - the dictionary would grow and they'd say we have a memory leak (this has actually happened). You could use a weak key dictionary, which would remove the entry as soon as all strong references to the dialect disappear. my resistance with WKDs is that they're expensive compared to regular dicts. It means creating WKDs and weakrefs on every query, which will almost never be used. -- 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.
[sqlalchemy] Translating a sql union statement into SA problem
I am trying different solutions to my problem of getting default
languages back.
I have following statement:
Select
T1.id, T1.code_id, T1.lang_code5, T1.name
from
somecode_t T1
where
T1.lang_code5 = 'FR_fr'
UNION
Select
T2.id, T2.code_id, T2.lang_code5, T2.name
from
somecode_t T2
left outer join
somecode_t T3
on T2.code_id = T3.code_id
and
T3.lang_code5 = 'FR_fr'
where
T2.lang_code5 = 'EN_en'
and
T3.id is null;
Which I like to translate into SA and have come up with this:
stAlias1 = db.sao.aliased(db.Somecode_T)
q1 = session.query(stAlias1).filter(stAlias1.lang_code5=='FR_fr')
stAlias2 = db.sao.aliased(db.Somecode_T)
stAlias3 = db.sao.aliased(db.Somecode_T)
q2 = session.query(stAlias2).outerjoin((stAlias2,
stAlias2.code_id==stAlias3.code_id),
(stAlias3,
stAlias3.lang_code5=='FR_fr')
).filter(db.sa.and_(
stAlias2.lang_code5=='EN_en',
stAlias3.id==db.sa.null()))
q3 = q1.union(q2)
print q3
print ''
print q3.all()
I am probably overusing alias but it makes it clearer to me to match
with the original sql.
However I am getting the following exception as somecode_t_1 and
somecode_t_2 use the same column aliases, what am I doing wrong that
this is happening?
Werner
Traceback (most recent call last):
File saTest.py, line 102, in module
print q3.all()
File
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\orm\query.py,
line 1267, in all
return list(self)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\orm\query.py,
line 1361, in __iter__
return self._execute_and_instances(context)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\orm\query.py,
line 1364, in _execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\orm\session.py,
line 753, in execute
clause, params or {})
File
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py,
line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py,
line 874, in _execute_clauseelement
return self.__execute_context(context)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py,
line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py,
line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File
c:\python25\lib\site-packages\sqlalchemy-0.5.8-py2.5.egg\sqlalchemy\engine\base.py,
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (-204,
'isc_dsql_prepare: \n Dynamic SQL Error\n SQL error code = -204\n
alias ANON_1 SOMECODE_T_2 conflicts with an alias in the same
statement') 'SELECT anon_1.id AS anon_1_id, anon_1.code_id AS
anon_1_code_id, anon_1.lang_code5 AS anon_1_lang_code5, anon_1.name AS
anon_1_name, anon_1.short_name AS anon_1_short_name, anon_1.created_at
AS anon_1_created_at, anon_1.updated_at AS anon_1_updated_at \nFROM
(SELECT somecode_t_1.id AS id, somecode_t_1.code_id AS code_id,
somecode_t_1.lang_code5 AS lang_code5, somecode_t_1.name AS name,
somecode_t_1.short_name AS short_name, somecode_t_1.created_at AS
created_at, somecode_t_1.updated_at AS updated_at \nFROM somecode_t
somecode_t_1 \nWHERE somecode_t_1.lang_code5 = ? UNION SELECT
somecode_t_2.id AS id, somecode_t_2.code_id AS code_id,
somecode_t_2.lang_code5 AS lang_code5, somecode_t_2.name AS name,
somecode_t_2.short_name AS short_name, somecode_t_2.created_at AS
created_at, somecode_t_2.updated_at AS updated_at \nFROM somecode_t
somecode_t_2 LEFT OUTER JOIN somecode_t somecode_t_2 ON
somecode_t_2.code_id = somecode_t_3.code_id LEFT OUTER JOIN somecode_t
somecode_t_3 ON somecode_t_3.lang_code5 = ? \nWHERE
somecode_t_2.lang_code5 = ? AND somecode_t_3.id IS NULL) anon_1'
['FR_fr', 'FR_fr', 'EN_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.
[sqlalchemy] Interface to (very old) Sybaase?
Heyho! Has anybody worked with a Sybase Anywhere (ASA 9 -- yes, very old ...) database? I may need to build a simple CRUD (actually onnly R and U ;-) frontend to some legacy application. (I probably will give TurbeGears a try for this.) I do have a JDBC driver, and I *think* ODBC should work (when I tried it some time ago), but I haven't worked (much) with either. (I think I remember having seen a jdbc bridge for either Perl or Python, but I'm not sure anywhere and at least I can't find Debian packages right now.) Thanks in advance -- vbi -- Je n'ai pas souvent assisté à des course de spermatozoïdes, mais j'ai donné beaucoup de départs. -+- Olivier de Kersauson -+- signature.asc Description: This is a digitally signed message part.
