Re: [sqlalchemy] Bulk insertion with null values
Thanks for your fast response! On Wednesday, January 6, 2016 at 10:42:59 PM UTC+1, Michael Bayer wrote: > > I don't have a solution for that at the moment other than to use a > straight Core insert, if there aren't other complicating factors. > In my case I am having a model with inheritance and the bulk functions are nicely taking care of inserting the entities in the parent and child tables. With a core insert, I would have to do that manually. > bulk_insert_mappings() tries to keep the same contract as that of a > regular Session.flush(), where None means to "omit" the value, which has > affects whether or not column-level defaults fire off. > > Perhaps supporting a constant such as BIND_NULL, or adding support for > the bulk insert to interpret null() as a bound value, might be a way to > support this use case. or just a flag on bulk_insert_mappings, which > would be easier to develop and test and less confusing. > Such flag would be great! For the standard Session.flush it makes sense to skip None values. But for the bulk functions it would be very useful to include None fields, so that the number of insert statements is reduced. > > On 01/06/2016 03:45 PM, Tobias wrote: > > Hi, > > > > I am using Session.bulk_insert_mappings() > > < > http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.bulk_insert_mappings> > > > > to insert a number of entities. This works great except when there are > > entities with None values. For example the following call issues three > > insert statements instead of a single one: > > > > session.bulk_insert_mappings(Doc, [ > > dict(id=1, col_a='A', col_b='B'), > > dict(id=2, col_a='A', col_b=None), > > dict(id=3, col_a='A', col_b='B') > > ]) > > > > > > Log: > > > > 2016-01-06 21:35:58,033 INFO sqlalchemy.engine.base.Engine INSERT > > INTO doc (id, col_a, col_b) VALUES (?, ?, ?) > > 2016-01-06 21:35:58,033 INFO sqlalchemy.engine.base.Engine (1, 'A', > 'B') > > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine INSERT > > INTO doc (id, col_a) VALUES (?, ?) > > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine (2, 'A') > > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine INSERT > > INTO doc (id, col_a, col_b) VALUES (?, ?, ?) > > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine (3, 'A', > 'B') > > > > > > I would expect that a single insert is made and that `null` is used as > > value for `col_b`. I tried to use `sqlalchemy.sql.expression.null()` but > > that does not seem to work with the bulk functions. I also know that I > > could use evaluates_none() > > < > http://docs.sqlalchemy.org/en/latest/core/type_api.html#sqlalchemy.types.TypeEngine.evaluates_none>, > > > > but I don't want to change the model because the bulk insertion is only > > used in the migration script. > > > > Here is an example: > https://gist.github.com/tsauerwein/d9630336731fff0547ba > > > > Thanks, > > Tobias > > > > -- > > You received this message because you are subscribed to the Google > > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > > an email to sqlalchemy+...@googlegroups.com > > <mailto:sqlalchemy+unsubscr...@googlegroups.com >. > > To post to this group, send email to sqlal...@googlegroups.com > > > <mailto:sqlal...@googlegroups.com >. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Bulk insertion with null values
Hi, I am using Session.bulk_insert_mappings() <http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.bulk_insert_mappings> to insert a number of entities. This works great except when there are entities with None values. For example the following call issues three insert statements instead of a single one: session.bulk_insert_mappings(Doc, [ > dict(id=1, col_a='A', col_b='B'), > dict(id=2, col_a='A', col_b=None), > dict(id=3, col_a='A', col_b='B') > ]) > Log: > 2016-01-06 21:35:58,033 INFO sqlalchemy.engine.base.Engine INSERT INTO doc > (id, col_a, col_b) VALUES (?, ?, ?) > 2016-01-06 21:35:58,033 INFO sqlalchemy.engine.base.Engine (1, 'A', 'B') > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine INSERT INTO doc > (id, col_a) VALUES (?, ?) > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine (2, 'A') > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine INSERT INTO doc > (id, col_a, col_b) VALUES (?, ?, ?) > 2016-01-06 21:35:58,034 INFO sqlalchemy.engine.base.Engine (3, 'A', 'B') > I would expect that a single insert is made and that `null` is used as value for `col_b`. I tried to use `sqlalchemy.sql.expression.null()` but that does not seem to work with the bulk functions. I also know that I could use evaluates_none() <http://docs.sqlalchemy.org/en/latest/core/type_api.html#sqlalchemy.types.TypeEngine.evaluates_none>, but I don't want to change the model because the bulk insertion is only used in the migration script. Here is an example: https://gist.github.com/tsauerwein/d9630336731fff0547ba Thanks, Tobias -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Force SQLAlchemy to not use Unicode
Am Dienstag, 1. Oktober 2013 17:19:11 UTC+2 schrieb Michael Bayer:
here's a full proof-of-concept. see if it works for you and if you can
figure out where your real app is diverging from the basic idea:
from sqlalchemy import MetaData, Table, Column, String, Integer,
create_engine
e = create_engine(sqlite://, echo=True)
m1 = MetaData()
t1 = Table('t1', m1, Column('data', String(50)))
t1.create(e)
e.execute(t1.insert(), data='d1')
import sqlalchemy.types as types
from sqlalchemy.schema import Table
from sqlalchemy import event
class MyStringType(types.TypeDecorator):
impl = types.String
def process_bind_param(self, value, dialect):
if value is not None:
value = value.encode('utf-8', 'replace')
return value
@event.listens_for(Table, 'column_reflect')
def listen_for_reflect(inspector, table, column_info):
if isinstance(column_info['type'], types.String):
column_info['type'] = MyStringType(column_info['type'].length)
m2 = MetaData()
t2 = Table('t1', m2, autoload=True, autoload_with=e)
e.execute(t2.select().where(t2.c.data == u'd1')).fetchall()
On Oct 1, 2013, at 4:12 AM, Tobias Bell tobia...@gmail.com javascript:
wrote:
Am Montag, 30. September 2013 19:18:01 UTC+2 schrieb Michael Bayer:
On Sep 30, 2013, at 9:30 AM, Tobias Bell tobia...@gmail.com wrote:
Hello
I'm currently migrating a rather old application built with SQLAlchemy
0.6 and also an older cx_oracle to use SQLAlchemy 0.8 and cx_oracle 5.1.2.
The problem is, all queries with strings send unicode data to cx_oracle
an cx_oracle uses NVARCHAR2 to talk to the database. The table itself has
VARCHAR2 columns
and no SYS_OP_C2C() indexes. So full table scans are the result. What can
I do to enforce that cx_oracle doesn't bind with NVARCHAR2?
What I tried till now was
- Forcing unicode conversion on engine leve -
sa.create_engine(connect_url, convert_unicode=True)
- encoding parameter = param = param.encode('utf8')
I found
thishttps://groups.google.com/forum/#!msg/sqlalchemy/8Xn31vBfGKU/bAGLNKapvSMJ
post,
but couldn't use it because all my table definitions are autoloaded.
What would you suggest?
I've just gone through that thread for review, seems like nothing is
really going to change with cx_oracle. It would be great if they could
send a bytestring through on their end, as any application in Python 3 that
naively uses cx_oracle directly with VARCHAR columns is going to perform
terribly otherwise.The issue here is essentially using SQLAlchemy to
fix a problem that cx_oracle isn't.
Anyway, the same recommendations about custom types and/or
convert_unicode='force' still apply here. For reflection, use the column
reflect event to upgrade types on each column as it is reflected.
http://docs.sqlalchemy.org/en/rel_0_8/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect
from sqlalchemy.schema import Table
from sqlalchemy import event
@event.listens_for(Table, 'column_reflect')
def listen_for_reflect(inspector, table, column_info):
if isinstance(column_info['type'], String):
column_info['type'] = MyStringType(column_info['type'].length)
Thanks for the quick answer Michael. I just tried it now
import sqlalchemy.types as types
from sqlalchemy.schema import Table
from sqlalchemy import event
class MyStringType(types.TypeDecorator):
impl = types.String
def process_bind_param(self, value, dialect):
if value is not None:
value = value.encode('utf-8', 'replace')
event.listens_for(Table, 'column_reflect')
def listen_for_reflect(inspector, table, column_info):
if isinstance(column_info['type'], types.String):
column_info['type'] = MyStringType(column_info['type'].length)
But it is not working. Is there something wrong with my code? Do you need
to see some more code? Here is an example query
2013-10-01 10:04:22,171 INFO:SELECT POSTEINGANG_ADM.pe_process.status AS
POSTEINGANG_ADM_pe_proce_1, POSTEINGANG_ADM.pe_process.stack_id AS
POSTEINGANG_ADM_pe_proce_2, POSTEINGANG_ADM.pe_process.process_id AS
POSTEINGANG_ADM_pe_proce_3, POSTEINGANG_ADM.pe_process.rout_client AS
POSTEINGANG_ADM_pe_proce_4, POSTEINGANG_ADM.pe_process.rout_category AS
POSTEINGANG_ADM_pe_proce_5, POSTEINGANG_ADM.pe_process.rout_message AS
POSTEINGANG_ADM_pe_proce_6, POSTEINGANG_ADM.pe_process.archive_path AS
POSTEINGANG_ADM_pe_proce_7, POSTEINGANG_ADM.pe_process.fndoc_id AS
POSTEINGANG_ADM_pe_proce_8, POSTEINGANG_ADM.pe_process.fndoc_class AS
POSTEINGANG_ADM_pe_proce_9, POSTEINGANG_ADM.pe_process.fndoc_date AS
POSTEINGANG_ADM_pe_proce_a, POSTEINGANG_ADM.pe_process.scart AS
POSTEINGANG_ADM_pe_proce_b, POSTEINGANG_ADM.pe_process.host_stack_id AS
POSTEINGANG_ADM_pe_proce_c, POSTEINGANG_ADM.pe_process.altered_by AS
POSTEINGANG_ADM_pe_proce_d, POSTEINGANG_ADM.pe_process.retry_at
Re: [sqlalchemy] Force SQLAlchemy to not use Unicode
Am Montag, 30. September 2013 19:18:01 UTC+2 schrieb Michael Bayer:
On Sep 30, 2013, at 9:30 AM, Tobias Bell tobia...@gmail.com javascript:
wrote:
Hello
I'm currently migrating a rather old application built with SQLAlchemy 0.6
and also an older cx_oracle to use SQLAlchemy 0.8 and cx_oracle 5.1.2.
The problem is, all queries with strings send unicode data to cx_oracle an
cx_oracle uses NVARCHAR2 to talk to the database. The table itself has
VARCHAR2 columns
and no SYS_OP_C2C() indexes. So full table scans are the result. What can
I do to enforce that cx_oracle doesn't bind with NVARCHAR2?
What I tried till now was
- Forcing unicode conversion on engine leve -
sa.create_engine(connect_url, convert_unicode=True)
- encoding parameter = param = param.encode('utf8')
I found
thishttps://groups.google.com/forum/#!msg/sqlalchemy/8Xn31vBfGKU/bAGLNKapvSMJ
post,
but couldn't use it because all my table definitions are autoloaded.
What would you suggest?
I've just gone through that thread for review, seems like nothing is
really going to change with cx_oracle. It would be great if they could
send a bytestring through on their end, as any application in Python 3 that
naively uses cx_oracle directly with VARCHAR columns is going to perform
terribly otherwise.The issue here is essentially using SQLAlchemy to
fix a problem that cx_oracle isn't.
Anyway, the same recommendations about custom types and/or
convert_unicode='force' still apply here. For reflection, use the column
reflect event to upgrade types on each column as it is reflected.
http://docs.sqlalchemy.org/en/rel_0_8/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect
from sqlalchemy.schema import Table
from sqlalchemy import event
@event.listens_for(Table, 'column_reflect')
def listen_for_reflect(inspector, table, column_info):
if isinstance(column_info['type'], String):
column_info['type'] = MyStringType(column_info['type'].length)
Thanks for the quick answer Michael. I just tried it now
import sqlalchemy.types as types
from sqlalchemy.schema import Table
from sqlalchemy import event
class MyStringType(types.TypeDecorator):
impl = types.String
def process_bind_param(self, value, dialect):
if value is not None:
value = value.encode('utf-8', 'replace')
event.listens_for(Table, 'column_reflect')
def listen_for_reflect(inspector, table, column_info):
if isinstance(column_info['type'], types.String):
column_info['type'] = MyStringType(column_info['type'].length)
But it is not working. Is there something wrong with my code? Do you need
to see some more code? Here is an example query
2013-10-01 10:04:22,171 INFO:SELECT POSTEINGANG_ADM.pe_process.status AS
POSTEINGANG_ADM_pe_proce_1, POSTEINGANG_ADM.pe_process.stack_id AS
POSTEINGANG_ADM_pe_proce_2, POSTEINGANG_ADM.pe_process.process_id AS
POSTEINGANG_ADM_pe_proce_3, POSTEINGANG_ADM.pe_process.rout_client AS
POSTEINGANG_ADM_pe_proce_4, POSTEINGANG_ADM.pe_process.rout_category AS
POSTEINGANG_ADM_pe_proce_5, POSTEINGANG_ADM.pe_process.rout_message AS
POSTEINGANG_ADM_pe_proce_6, POSTEINGANG_ADM.pe_process.archive_path AS
POSTEINGANG_ADM_pe_proce_7, POSTEINGANG_ADM.pe_process.fndoc_id AS
POSTEINGANG_ADM_pe_proce_8, POSTEINGANG_ADM.pe_process.fndoc_class AS
POSTEINGANG_ADM_pe_proce_9, POSTEINGANG_ADM.pe_process.fndoc_date AS
POSTEINGANG_ADM_pe_proce_a, POSTEINGANG_ADM.pe_process.scart AS
POSTEINGANG_ADM_pe_proce_b, POSTEINGANG_ADM.pe_process.host_stack_id AS
POSTEINGANG_ADM_pe_proce_c, POSTEINGANG_ADM.pe_process.altered_by AS
POSTEINGANG_ADM_pe_proce_d, POSTEINGANG_ADM.pe_process.retry_at AS
POSTEINGANG_ADM_pe_proce_e, POSTEINGANG_ADM.pe_process.discard_at AS
POSTEINGANG_ADM_pe_proce_f,
POSTEINGANG_ADM.pe_process.rout_specification AS
POSTEINGANG_ADM_pe_proce_10
FROM POSTEINGANG_ADM.pe_process
WHERE :param_1 = POSTEINGANG_ADM.pe_process.stack_id
2013-10-01 10:04:22,171 INFO:{'param_1': u'skcla0005982302000'}
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Force SQLAlchemy to not use Unicode
Hello
I'm currently migrating a rather old application built with SQLAlchemy 0.6
and also an older cx_oracle to use SQLAlchemy 0.8 and cx_oracle 5.1.2.
The problem is, all queries with strings send unicode data to cx_oracle an
cx_oracle uses NVARCHAR2 to talk to the database. The table itself has
VARCHAR2 columns
and no SYS_OP_C2C() indexes. So full table scans are the result. What can I
do to enforce that cx_oracle doesn't bind with NVARCHAR2?
What I tried till now was
- Forcing unicode conversion on engine leve -
sa.create_engine(connect_url, convert_unicode=True)
- encoding parameter = param = param.encode('utf8')
I found
thishttps://groups.google.com/forum/#!msg/sqlalchemy/8Xn31vBfGKU/bAGLNKapvSMJ
post,
but couldn't use it because all my table definitions are autoloaded.
What would you suggest?
Best regards
Tobias Bell
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Query.update() and joins
Hi guys, I have stripped down my problematic code to the following: http://pastebin.com/AvvEr103 I am trying to call the Query.update() method on a query that is using an innerjoin to load some more information that I use in a filter. If I use the same expression to query data everything works fine, but once I call update() the join expression (Event.id == Notification.event_id) is no longer taken into account and the update() call updates all the rows in the table. I'm having the feeling that this might actually be a bug in SQLAlchemy. As you can see there is a workaround by simply adding the expression explicitly as a filter, but it would obviously be better to have this be the default behaviour. Turbo -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Postgres custom composite types
GeoAlchemy is for SQLALchemy =0.7.x GeoAlchemy2 if for SQLALchemy =0.8 Kamil is mostly right. GeoAlchemy2 v0.1 is published on PyPI now, and GeoAlchemy(1) is also still available. The latter is still compatible with SQLAlchemy 0.8 though, but GeoAlchemy2 has a much easier code base and API. I'm already running it on a production server and it worked very well so far. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Postgres custom composite types
Hi Michael, thanks for the explanation on the key attribute. I ended up misunderstanding it at first, but it seems to work now. I had a few problems when I tried to use a UserDefinedType (e.g. geoalchemys Geometry) in the typemap, because it complained about a missing key attribute. I've fixed that by adding key = None to the comparator class though ( https://github.com/Turbo87/geoalchemy2/commit/264c7cd578da78c5c5f964ecd44c35a307923643) and it seems to work as expected now :) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Postgres custom composite types
*Sorry, just noticed that I replied directly, instead of to the list. Here are the contents for everyone:* Thanks for you help. Using the FunctionElement worked quite well, but I had to tweak the constructor and assign the base parameter to self.key to make it work properly (failing stack trace at https://gist.github.com/Turbo87/b5f49ffeedb3fa288b4f). Now I've tried the same approach using the declarative method (https://gist.github.com/Turbo87/e7fb19b8e532d3e3b6f3) but I get the next error: TypeError: Boolean value of this clause is not defined (stack trace for that is at https://gist.github.com/Turbo87/ab5f0ef5159dc85dd0b3 and what I got out of the debugger is at https://gist.github.com/Turbo87/bef75287f67c0f1f948a) I hope I'm not stealing too much of your time. Thanks again for the great help already! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Postgres custom composite types
I've tried to extract the basic structure from this sample code and merge it into the GeoAlchemy2 project, but somehow it fails to work for my specific use-case: https://github.com/geoalchemy/geoalchemy2/pull/18 I'm trying something like: select([func.SomeFunction(table.c.some_column, type_=mytype).attr1]) Unfortunately the resulting query string is missing the FROM clause and won't execute. I'd be glad if anyone has an idea how to fix this. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Postgres custom composite types
thanks for the quick reply! if you can package up a script with full context that would help, that pullreq appears to add a new type to a file and I don't see how that results in anything happening. you are right, https://github.com/Turbo87/geoalchemy2/commit/9d09a94c97faab902ca29000c6e985a7b51f7eb1#L1R154is adding the PGCompositeType class, which is similar to the one in your recipe but with a few simplifications. https://github.com/Turbo87/geoalchemy2/commit/9d09a94c97faab902ca29000c6e985a7b51f7eb1#L0R220 is adding the corresponding unit test for that class, which is unfortunately failing at the moment, due to the missing FROM clause. you should be able to run the test case through python setup.py nosetests. there will be a few failing functional tests due to the missing postgres DB, but the relevant test in test_types.py should be visible too. In general if you are using my recipe earlier that features ColumnElement, that object won't report on its parent table for usage in the FROM clause, if you are producing a custom column class you need to set it up correctly for the way it will be used. could you point me in the right direction how to set it up correctly? I'm currently a bit lost in the documentation and code. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Postgres custom composite types
I've added a small standalone script at https://gist.github.com/Turbo87/5233888 The output is SELECT (SomeFunction(table.foo)).attr1 AS anon_1, without any FROM clause. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Joined Table Inheritance question
However in my case I don't want to be able to persist some random Employee.
All my employees should be engineers or managers. Ideally some exception
should be thrown if I try to add an Employee object to my session.
Maybe something like this. But I'm not sure if it's the right way. It fails on
flush.
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.schema import Column, MetaData, ForeignKey
from sqlalchemy.types import Integer
engine = create_engine('sqlite://')
meta = MetaData(bind=engine)
Session = sessionmaker(bind=engine)
Base = declarative_base(metadata=meta)
class EmployeeChecker(MapperExtension):
def before_insert(self, mapper, connection, target):
if type(target) != Employee:
raise Exception('Inserting of Employee is not supported')
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String)
discriminator = Column('type', String(50))
__mapper_args__ = {'polymorphic_on': discriminator, 'extension' :
EmployeeChecker()}
class Manager(Employee):
__tablename__ = 'manager'
__mapper_args__ = {'polymorphic_identity' : 'manager'}
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
meta.drop_all()
meta.create_all()
session = Session()
session.add(Employee())
session.add(Manager())
session.commit()
e = session.query(Employee).one()
e.name='test'
session.add(e)
session.commit()
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Do not bind None values in insert/update statements?
Actually I am not manually constructing the insert statement,
SQLAlchemy is doing that for me. I am just committing my session:
spot_null = Spot(spot_height=None, spot_location=None)
session.add(spot_null)
session.commit();
Note that spot_location is a GeoAlchemy geometry column. And if the
value of this attribute is None, GeoAlchemy just returns None in its
bind_processor.process() method.
How is the type information sent to cx_Oracle. Using
Cursor.inputtypehandler (http://cx-oracle.sourceforge.net/html/
cursor.html#Cursor.inputtypehandler)?
On May 20, 7:04 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On May 20, 2010, at 5:25 AM, Tobias wrote:
Hi,
I am working on Oracle support for GeoAlchemy and having problems when
trying to insert NULL values into geometry columns.
spot_null = Spot(spot_height=None, spot_location=None)
session.add(spot_null)
session.commit();
DatabaseError: (DatabaseError) ORA-00932: inconsistent datatypes:
expected MDSYS.SDO_GEOMETRY got CHAR
'INSERT INTO spots (spot_id, spot_height, spot_location) VALUES
(spots_id_seq.nextval, :spot_height, :spot_location) RETURNING
spots.spot_id INTO :ret_0' {'spot_location': None, 'spot_height':
None, 'ret_0': cx_Oracle.NUMBER with value None}
The problem is that Oracle requires a type for bind parameters, and
cx_Oracle -when using None- choses CHAR, because 'CHAR seems the most
likely to cause the least difficulties', see [1]. I hope cx_Oracle
will provide a proper way to use NULL for Oracle objects in near
future.
But right now I am wondering if there is still a way to insert NULL
values into geometry columns. Does SQLAlchemy maybe have a column
flag, so that the column is not used as bind parameter? Or something
else?
At the moment as workaround I set the attribute of my object to a
select that queries NULL. It works but that is not a solution:
spot_null = Spot(spot_height=None,
spot_location=select([text('NULL')], from_obj=['dual']))
bindparam() supports a type_ attribute, and we send cx_oracle typing
information for all binds except for strings (which for some reason seem to
make things work more poorly). Not sure what the context here is though,
i.e. at what point are the bindparams being generated etc.
if you really didn't want to bind them, then the insert() statement has to be
constructed that way. I.e. it can't be execute(stmt, {'foo':None}), that's
too late in the process.
need more context here.
--
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
athttp://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] Do not bind None values in insert/update statements?
Hi,
I am working on Oracle support for GeoAlchemy and having problems when
trying to insert NULL values into geometry columns.
spot_null = Spot(spot_height=None, spot_location=None)
session.add(spot_null)
session.commit();
DatabaseError: (DatabaseError) ORA-00932: inconsistent datatypes:
expected MDSYS.SDO_GEOMETRY got CHAR
'INSERT INTO spots (spot_id, spot_height, spot_location) VALUES
(spots_id_seq.nextval, :spot_height, :spot_location) RETURNING
spots.spot_id INTO :ret_0' {'spot_location': None, 'spot_height':
None, 'ret_0': cx_Oracle.NUMBER with value None}
The problem is that Oracle requires a type for bind parameters, and
cx_Oracle -when using None- choses CHAR, because 'CHAR seems the most
likely to cause the least difficulties', see [1]. I hope cx_Oracle
will provide a proper way to use NULL for Oracle objects in near
future.
But right now I am wondering if there is still a way to insert NULL
values into geometry columns. Does SQLAlchemy maybe have a column
flag, so that the column is not used as bind parameter? Or something
else?
At the moment as workaround I set the attribute of my object to a
select that queries NULL. It works but that is not a solution:
spot_null = Spot(spot_height=None,
spot_location=select([text('NULL')], from_obj=['dual']))
..
Thanks,
Tobias
[1]:
http://sourceforge.net/mailarchive/message.php?msg_name=49F050EC.30205%40gmail.com
--
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] Re: (NotSupportedError) Variable_TypeByValue(): unhandled data type cx_Oracle.OBJECT
Ok, nothing wrong with SQLAlchemy, cx_Oracle does not support Oracle
objects (yet):
http://sourceforge.net/mailarchive/message.php?msg_name=AANLkTilkBwWsIy0yEFQpPOvQiF-k9RxvlYlKf2KyaOfw%40mail.gmail.com
On May 10, 5:45 pm, Michael Bayer mike...@zzzcomputing.com wrote:
the first step here would be to create a cx_oracle -only application that
issues your query and gets the right result back. then we can make sure
sqlalchemy is passing that along in the same way. The error you are
seeing is generated by cx_oracle (SQLA just wraps the NotSupportedError).
What may be the issue here is that cx_oracle.OBJECT is being used at all -
SQLAlchemy is not aware of this type so it appears to be some guess that
cx_oracle is making about input or output parameters.
On May 10, 2010, at 10:39 AM, Tobias wrote:
Hi!
Using cx_oracle and SQLAlchemy 0.6 I am having troubles with Oracle
objects (cx_Oracle.OBJECT) as function parameters. For example I have
a function that returns an object of type cx_Oracle.OBJECT, and now I
want to use that object as argument for a new function call:
obj = session.scalar(func.SDO_GEOMETRY('POINT(0 0)', 4326))
2010-05-10 16:23:57,337 INFO sqlalchemy.engine.base.Engine.0x...b82c
SELECT SDO_GEOMETRY(:SDO_GEOMETRY_2, :SDO_GEOMETRY_3) AS
SDO_GEOMETRY_1 FROM DUAL
2010-05-10 16:23:57,337 INFO sqlalchemy.engine.base.Engine.0x...b82c
{'SDO_GEOMETRY_2': 'POINT(0 0)', 'SDO_GEOMETRY_3': 4326}
session.scalar(func.SDO_UTIL.TO_WKTGEOMETRY(obj))
[..]
File /../env/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/
sqlalchemy/engine/default.py, line 277, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.NotSupportedError: (NotSupportedError)
Variable_TypeByValue(): unhandled data type cx_Oracle.OBJECT 'SELECT
SDO_UTIL.TO_WKTGEOMETRY(:TO_WKTGEOMETRY_2) AS TO_WKTGEOMETRY_1 FROM
DUAL' {'TO_WKTGEOMETRY_2': cx_Oracle.OBJECT object at 0x8e2f560}
I tried to set a type using bindparam, but that did not help:
session.scalar(func.SDO_UTIL.TO_WKTGEOMETRY(bindparam('', wkt,
type_=LargeBinary)))
What can I do?
Thanks,
Tobias
--
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
athttp://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
athttp://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] Re: Custom compiler for column is ignored when using aliases
Hi Michael,
checking for proxies did not work for aliases when using a Query
object:
q = Query(mappedclass_alias).filter(mappedclass_alias.id ==
2).limit(1)
print q.statement
SELECT table_1.id, table_1.text, table_1.geom
FROM table AS table_1
WHERE table_1.id = :id_1
LIMIT 1
But your 2nd suggestion works fine, my compiles method now looks like
this:
@compiles(GeometryExtensionColumn)
def compile_column(element, compiler, **kw):
if isinstance(element.table, (Table, Alias)):
if kw.has_key(within_columns_clause) and
kw[within_columns_clause] == True:
return compiler.process(functions.wkb(element))
return compiler.visit_column(element)
Thanks a lot, you are doing an awesome job!
Tobias
On May 11, 5:21 pm, Michael Bayer mike...@zzzcomputing.com wrote:
now you're on the big stage where your column is properly recognzied, so you
have to write the compiles method correctly:
@compiles(GeometryExtensionColumn)
def compile_column(element, compiler, **kw):
if not getattr(element, 'proxies', None):
if kw.has_key(within_columns_clause) and
kw[within_columns_clause] == True:
return AsBinary(%s) % element
return compiler.visit_column(element)
I cannot say for sure if checking for proxies is always the correct way to
tell if you're derived or not. Another way is to check the parent table of
the column and seeing if it is an instance of Table or Select.
On May 11, 2010, at 10:49 AM, Tobias wrote:
Ok, I made a basic example. Thanks for taking a look at it!
from sqlalchemy import MetaData, Table, Column, create_engine
from sqlalchemy.types import Integer, Unicode
from sqlalchemy.orm import mapper
from sqlalchemy.orm.query import Query
from sqlalchemy.sql import func, and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import select
engine = create_engine('oracle://dummy:du...@dummy:1521/dummy',
echo=True)
Base = declarative_base(metadata=MetaData())
class GeometryExtensionColumn(Column):
pass
@compiles(GeometryExtensionColumn)
def compile_column(element, compiler, **kw):
if kw.has_key(within_columns_clause) and
kw[within_columns_clause] == True:
return AsBinary(%s) % element
return element.__str__()
class MappedClass(Base):
__tablename__ = table
id = Column(Integer, primary_key=True)
text = Column(Unicode)
geom = GeometryExtensionColumn(Unicode)
print select([MappedClass.__table__]).where(MappedClass.id ==
1).limit(1).compile(engine)
# Output for release 0.6.0
#
#SELECT id, text, geom
#FROM (SELECT table.id AS id, table.text AS text,
AsBinary(table.geom) AS geom
#FROM table
#WHERE table.id = :id_1)
#WHERE ROWNUM = :ROWNUM_1
# Output for current tip (b03613c840a4)
#
#SELECT id, text, AsBinary(geom)
#FROM (SELECT table.id AS id, table.text AS text,
AsBinary(table.geom) AS geom
#FROM table
#WHERE table.id = :id_1)
#WHERE ROWNUM = :ROWNUM_1
On May 11, 2:41 pm, Michael Bayer mike...@zzzcomputing.com wrote:
I would need fully working code to test. just one python file with the
minimal reproducing situation.
On May 11, 2010, at 2:41 AM, Tobias wrote:
Hi Michael,
thanks for your reply. The current tip seems to fix that problem
without having to override '_make_proxy()'. But there is another
problem I have with the current tip even without aliases.
The following query worked fine in release 0.6.0, but in the current
tip the outer queries are not compiled correctly:
s1 = session.query(Spot).filter(Spot.spot_id == 1).first()
Query sent in release 0.6.0:
2010-05-11 08:25:01,938 INFO sqlalchemy.engine.base.Engine.0x...112c
SELECT spots_spot_location, spots_spot_id, spots_spot_height
FROM (SELECT spots_spot_location, spots_spot_id, spots_spot_height,
ROWNUM AS ora_rn
FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
AS spots_spot_height
FROM spots
WHERE spots.spot_id = :spot_id_1)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-11 08:25:01,939 INFO sqlalchemy.engine.base.Engine.0x...112c
{'ROWNUM_1': 1, 'spot_id_1': 1, 'ora_rn_1': 0}
Query sent in current tip:
2010-05-11 08:28:33,856 INFO sqlalchemy.engine.base.Engine.0x...838c
SELECT SDO_UTIL.TO_WKBGEOMETRY(spots_spot_location), spots_spot_id,
spots_spot_height
FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots_spot_location),
spots_spot_id, spots_spot_height, ROWNUM AS ora_rn
FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
AS spots_spot_height
FROM spots
WHERE spots.spot_id = :spot_id_1)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-11 08:28:33,857 INFO sqlalchemy.engine.base.Engine.0x...838c
{'ROWNUM_1': 1, 'spot_id_1': 1, 'ora_rn_1
[sqlalchemy] Re: Custom compiler for column is ignored when using aliases
Hi Michael,
thanks for your reply. The current tip seems to fix that problem
without having to override '_make_proxy()'. But there is another
problem I have with the current tip even without aliases.
The following query worked fine in release 0.6.0, but in the current
tip the outer queries are not compiled correctly:
s1 = session.query(Spot).filter(Spot.spot_id == 1).first()
Query sent in release 0.6.0:
2010-05-11 08:25:01,938 INFO sqlalchemy.engine.base.Engine.0x...112c
SELECT spots_spot_location, spots_spot_id, spots_spot_height
FROM (SELECT spots_spot_location, spots_spot_id, spots_spot_height,
ROWNUM AS ora_rn
FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
AS spots_spot_height
FROM spots
WHERE spots.spot_id = :spot_id_1)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-11 08:25:01,939 INFO sqlalchemy.engine.base.Engine.0x...112c
{'ROWNUM_1': 1, 'spot_id_1': 1, 'ora_rn_1': 0}
Query sent in current tip:
2010-05-11 08:28:33,856 INFO sqlalchemy.engine.base.Engine.0x...838c
SELECT SDO_UTIL.TO_WKBGEOMETRY(spots_spot_location), spots_spot_id,
spots_spot_height
FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots_spot_location),
spots_spot_id, spots_spot_height, ROWNUM AS ora_rn
FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
AS spots_spot_height
FROM spots
WHERE spots.spot_id = :spot_id_1)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-11 08:28:33,857 INFO sqlalchemy.engine.base.Engine.0x...838c
{'ROWNUM_1': 1, 'spot_id_1': 1, 'ora_rn_1': 0}
Only the innermost query should contain SELECT
SDO_UTIL.TO_WKBGEOMETRY(..).
Tobias
On May 10, 5:40 pm, Michael Bayer mike...@zzzcomputing.com wrote:
creating an alias() or otherwise using the .c. collection of any selectable
that's derived from another selectable (as when you say
select([sometable]).c.somecolumn) means that the Column objects are actually
copies of the original column objects. This copying procedure is performed
by column._make_proxy(). in rb03613c840a4 I have modified this so that
it uses the effective class of the object, typically self.__class__, when it
constructs the new Column object. However in 0.6 it is harcdoded to
ColumnClause and/or Column. So for the current release, you'd have to
ensure your GeometryExtensionColumn is overriding _make_proxy() as well. I
would use the _make_proxy() of Column to get its result, then change the
class of the returned Column to GeometryExtensionColumn on the way out.
you might want to first check that the problem goes away when using the
current tip.
On May 10, 2010, at 6:07 AM, Tobias wrote:
Hi,
I am currently working on adding support for Oracle to GeoAlchemy and
Oracle has some methods [1] that (somehow) are only recognized when a
table alias is used. The function aliased [2] seemed to work
perfectly, but then I realized that the compiler extension for my
custom column is not executed anymore.
The compiler extension looks like this [3]:
[..]
class GeometryExtensionColumn(Column):
pass
@compiles(GeometryExtensionColumn)
def compile_column(element, compiler, **kw):
if kw.has_key(within_columns_clause) and
kw[within_columns_clause] == True:
return compiler.process(functions.wkb(element))
return element.__str__()
[..]
And if I make a query using the original mapped class, it works as
expected:
s = session.query(Spot).get(1)
2010-05-10 11:49:19,957 INFO sqlalchemy.engine.base.Engine.0x...408c
SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
AS spots_spot_height
FROM spots
WHERE spots.spot_id = :param_1
2010-05-10 11:49:19,958 INFO sqlalchemy.engine.base.Engine.0x...408c
{'param_1': 1}
But when I create an alias and use this alias in a query,
compile_column is not called anymore and in this case
SDO_UTIL.TO_WKBGEOMETRY is not added to the query:
spot_alias = aliased(Spot)
s_alias = session.query(spot_alias).filter(spot_alias.spot_id ==
1).first()
2010-05-10 11:49:36,481 INFO sqlalchemy.engine.base.Engine.0x...408c
SELECT spots_1_spot_location, spots_1_spot_id, spots_1_spot_height
FROM (SELECT spots_1_spot_location, spots_1_spot_id,
spots_1_spot_height, ROWNUM AS ora_rn
FROM (SELECT spots_1.spot_location AS spots_1_spot_location,
spots_1.spot_id AS spots_1_spot_id, spots_1.spot_height AS
spots_1_spot_height
FROM spots spots_1
WHERE spots_1.spot_id = :spot_id_1)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
What is going wrong?
Thanks,
Tobias
[1]:http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_ob...
[2]:http://www.sqlalchemy.org/docs/ormtutorial.html#using-aliases
[3]:http://bitbucket.org/geoalchemy/geoalchemy/src/c0bfcd46cb3a/geoalchem...
--
You
[sqlalchemy] Re: Custom compiler for column is ignored when using aliases
Ok, I made a basic example. Thanks for taking a look at it!
from sqlalchemy import MetaData, Table, Column, create_engine
from sqlalchemy.types import Integer, Unicode
from sqlalchemy.orm import mapper
from sqlalchemy.orm.query import Query
from sqlalchemy.sql import func, and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import select
engine = create_engine('oracle://dummy:du...@dummy:1521/dummy',
echo=True)
Base = declarative_base(metadata=MetaData())
class GeometryExtensionColumn(Column):
pass
@compiles(GeometryExtensionColumn)
def compile_column(element, compiler, **kw):
if kw.has_key(within_columns_clause) and
kw[within_columns_clause] == True:
return AsBinary(%s) % element
return element.__str__()
class MappedClass(Base):
__tablename__ = table
id = Column(Integer, primary_key=True)
text = Column(Unicode)
geom = GeometryExtensionColumn(Unicode)
print select([MappedClass.__table__]).where(MappedClass.id ==
1).limit(1).compile(engine)
# Output for release 0.6.0
#
#SELECT id, text, geom
#FROM (SELECT table.id AS id, table.text AS text,
AsBinary(table.geom) AS geom
#FROM table
#WHERE table.id = :id_1)
#WHERE ROWNUM = :ROWNUM_1
# Output for current tip (b03613c840a4)
#
#SELECT id, text, AsBinary(geom)
#FROM (SELECT table.id AS id, table.text AS text,
AsBinary(table.geom) AS geom
#FROM table
#WHERE table.id = :id_1)
#WHERE ROWNUM = :ROWNUM_1
On May 11, 2:41 pm, Michael Bayer mike...@zzzcomputing.com wrote:
I would need fully working code to test. just one python file with the
minimal reproducing situation.
On May 11, 2010, at 2:41 AM, Tobias wrote:
Hi Michael,
thanks for your reply. The current tip seems to fix that problem
without having to override '_make_proxy()'. But there is another
problem I have with the current tip even without aliases.
The following query worked fine in release 0.6.0, but in the current
tip the outer queries are not compiled correctly:
s1 = session.query(Spot).filter(Spot.spot_id == 1).first()
Query sent in release 0.6.0:
2010-05-11 08:25:01,938 INFO sqlalchemy.engine.base.Engine.0x...112c
SELECT spots_spot_location, spots_spot_id, spots_spot_height
FROM (SELECT spots_spot_location, spots_spot_id, spots_spot_height,
ROWNUM AS ora_rn
FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
AS spots_spot_height
FROM spots
WHERE spots.spot_id = :spot_id_1)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-11 08:25:01,939 INFO sqlalchemy.engine.base.Engine.0x...112c
{'ROWNUM_1': 1, 'spot_id_1': 1, 'ora_rn_1': 0}
Query sent in current tip:
2010-05-11 08:28:33,856 INFO sqlalchemy.engine.base.Engine.0x...838c
SELECT SDO_UTIL.TO_WKBGEOMETRY(spots_spot_location), spots_spot_id,
spots_spot_height
FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots_spot_location),
spots_spot_id, spots_spot_height, ROWNUM AS ora_rn
FROM (SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
AS spots_spot_height
FROM spots
WHERE spots.spot_id = :spot_id_1)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-11 08:28:33,857 INFO sqlalchemy.engine.base.Engine.0x...838c
{'ROWNUM_1': 1, 'spot_id_1': 1, 'ora_rn_1': 0}
Only the innermost query should contain SELECT
SDO_UTIL.TO_WKBGEOMETRY(..).
Tobias
On May 10, 5:40 pm, Michael Bayer mike...@zzzcomputing.com wrote:
creating an alias() or otherwise using the .c. collection of any
selectable that's derived from another selectable (as when you say
select([sometable]).c.somecolumn) means that the Column objects are
actually copies of the original column objects. This copying procedure is
performed by column._make_proxy(). in rb03613c840a4 I have modified
this so that it uses the effective class of the object, typically
self.__class__, when it constructs the new Column object. However in 0.6
it is harcdoded to ColumnClause and/or Column. So for the current
release, you'd have to ensure your GeometryExtensionColumn is overriding
_make_proxy() as well. I would use the _make_proxy() of Column to get its
result, then change the class of the returned Column to
GeometryExtensionColumn on the way out. you might want to first check
that the problem goes away when using the current tip.
On May 10, 2010, at 6:07 AM, Tobias wrote:
Hi,
I am currently working on adding support for Oracle to GeoAlchemy and
Oracle has some methods [1] that (somehow) are only recognized when a
table alias is used. The function aliased [2] seemed to work
perfectly, but then I realized that the compiler extension for my
custom column is not executed anymore.
The compiler extension looks like this [3]:
[..]
class GeometryExtensionColumn(Column):
pass
[sqlalchemy] Custom compiler for column is ignored when using aliases
Hi,
I am currently working on adding support for Oracle to GeoAlchemy and
Oracle has some methods [1] that (somehow) are only recognized when a
table alias is used. The function aliased [2] seemed to work
perfectly, but then I realized that the compiler extension for my
custom column is not executed anymore.
The compiler extension looks like this [3]:
[..]
class GeometryExtensionColumn(Column):
pass
@compiles(GeometryExtensionColumn)
def compile_column(element, compiler, **kw):
if kw.has_key(within_columns_clause) and
kw[within_columns_clause] == True:
return compiler.process(functions.wkb(element))
return element.__str__()
[..]
And if I make a query using the original mapped class, it works as
expected:
s = session.query(Spot).get(1)
2010-05-10 11:49:19,957 INFO sqlalchemy.engine.base.Engine.0x...408c
SELECT SDO_UTIL.TO_WKBGEOMETRY(spots.spot_location) AS
spots_spot_location, spots.spot_id AS spots_spot_id, spots.spot_height
AS spots_spot_height
FROM spots
WHERE spots.spot_id = :param_1
2010-05-10 11:49:19,958 INFO sqlalchemy.engine.base.Engine.0x...408c
{'param_1': 1}
But when I create an alias and use this alias in a query,
compile_column is not called anymore and in this case
SDO_UTIL.TO_WKBGEOMETRY is not added to the query:
spot_alias = aliased(Spot)
s_alias = session.query(spot_alias).filter(spot_alias.spot_id ==
1).first()
2010-05-10 11:49:36,481 INFO sqlalchemy.engine.base.Engine.0x...408c
SELECT spots_1_spot_location, spots_1_spot_id, spots_1_spot_height
FROM (SELECT spots_1_spot_location, spots_1_spot_id,
spots_1_spot_height, ROWNUM AS ora_rn
FROM (SELECT spots_1.spot_location AS spots_1_spot_location,
spots_1.spot_id AS spots_1_spot_id, spots_1.spot_height AS
spots_1_spot_height
FROM spots spots_1
WHERE spots_1.spot_id = :spot_id_1)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
What is going wrong?
Thanks,
Tobias
[1]:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_objrelschema.htm#insertedID3
[2]: http://www.sqlalchemy.org/docs/ormtutorial.html#using-aliases
[3]:
http://bitbucket.org/geoalchemy/geoalchemy/src/c0bfcd46cb3a/geoalchemy/geometry.py#cl-121
--
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] (NotSupportedError) Variable_TypeByValue(): unhandled data type cx_Oracle.OBJECT
Hi!
Using cx_oracle and SQLAlchemy 0.6 I am having troubles with Oracle
objects (cx_Oracle.OBJECT) as function parameters. For example I have
a function that returns an object of type cx_Oracle.OBJECT, and now I
want to use that object as argument for a new function call:
obj = session.scalar(func.SDO_GEOMETRY('POINT(0 0)', 4326))
2010-05-10 16:23:57,337 INFO sqlalchemy.engine.base.Engine.0x...b82c
SELECT SDO_GEOMETRY(:SDO_GEOMETRY_2, :SDO_GEOMETRY_3) AS
SDO_GEOMETRY_1 FROM DUAL
2010-05-10 16:23:57,337 INFO sqlalchemy.engine.base.Engine.0x...b82c
{'SDO_GEOMETRY_2': 'POINT(0 0)', 'SDO_GEOMETRY_3': 4326}
session.scalar(func.SDO_UTIL.TO_WKTGEOMETRY(obj))
[..]
File /../env/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/
sqlalchemy/engine/default.py, line 277, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.NotSupportedError: (NotSupportedError)
Variable_TypeByValue(): unhandled data type cx_Oracle.OBJECT 'SELECT
SDO_UTIL.TO_WKTGEOMETRY(:TO_WKTGEOMETRY_2) AS TO_WKTGEOMETRY_1 FROM
DUAL' {'TO_WKTGEOMETRY_2': cx_Oracle.OBJECT object at 0x8e2f560}
I tried to set a type using bindparam, but that did not help:
session.scalar(func.SDO_UTIL.TO_WKTGEOMETRY(bindparam('', wkt,
type_=LargeBinary)))
What can I do?
Thanks,
Tobias
--
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] Oracle binding problem: (DatabaseError) ORA-01036: illegal variable name/number
Hi,
I am using SQLAlchemy 0.6 together with cx_Oracle and I am receiving
an error message when a database function is inside a package and has
to be called like package.functionname.
For example the following query can reproduce the error (beside that
this query makes not much sense):
session.query(table('spots',
column('spot_location'))).filter(getattr(func, 'MDSYS.SDO_GEOMETRY')
('POINT(0 0)', 4326) == text('spot_location')).first()
The output is:
2010-05-06 10:29:45,530 INFO sqlalchemy.engine.base.Engine.0x...a8ac
SELECT spots_spot_location
FROM (SELECT spots_spot_location, ROWNUM AS ora_rn
FROM (SELECT spots.spot_location AS spots_spot_location
FROM spots
WHERE
MDSYS.SDO_GEOMETRY(:MDSYS.SDO_GEOMETRY_1, :MDSYS.SDO_GEOMETRY_2) =
spot_location)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-06 10:29:45,530 INFO sqlalchemy.engine.base.Engine.0x...a8ac
{'ora_rn_1': 0, 'ROWNUM_1': 1, u'MDSYS.SDO_GEOMETRY_2': 4326,
u'MDSYS.SDO_GEOMETRY_1': 'POINT(0 0)'}
Traceback (most recent call last):
File stdin, line 1, in module
[..]
File .../env/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/
sqlalchemy/engine/default.py, line 277, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01036: illegal
variable name/number
'SELECT spots_spot_location \nFROM (SELECT spots_spot_location,
ROWNUM AS ora_rn \nFROM (SELECT spots.spot_location AS
spots_spot_location \nFROM spots \nWHERE
MDSYS.SDO_GEOMETRY(:MDSYS.SDO_GEOMETRY_1, :MDSYS.SDO_GEOMETRY_2) =
spot_location) \nWHERE ROWNUM = :ROWNUM_1) \nWHERE ora_rn
:ora_rn_1' {'ora_rn_1': 0, 'ROWNUM_1': 1, u'MDSYS.SDO_GEOMETRY_2':
4326, u'MDSYS.SDO_GEOMETRY_1': 'POINT(0 0)'}
Something is wrong about the escaping.
Thanks,
Tobias
--
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] Re: Oracle binding problem: (DatabaseError) ORA-01036: illegal variable name/number
By accident I found out that the SQLAlchemy Function class has an
attribute 'packagenames'. If you set this attribute manually, the
query is executed properly.
It would be great if this attribute is filled automatically by
splitting the function name on every dot.
session.query(Function('SDO_GEOMETRY', 'POINT(0 0)', 4326,
packagenames=['MDSYS'])).select_from(table('dual')).first()
2010-05-06 13:35:31,546 INFO sqlalchemy.engine.base.Engine.0x...a8ac
SELECT SDO_GEOMETRY_1
FROM (SELECT SDO_GEOMETRY_1, ROWNUM AS ora_rn
FROM (SELECT MDSYS.SDO_GEOMETRY(:SDO_GEOMETRY_2, :SDO_GEOMETRY_3) AS
SDO_GEOMETRY_1
FROM dual)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-06 13:35:31,547 INFO sqlalchemy.engine.base.Engine.0x...a8ac
{'ROWNUM_1': 1, 'SDO_GEOMETRY_3': 4326, 'SDO_GEOMETRY_2': 'POINT(0
0)', 'ora_rn_1': 0}
(cx_Oracle.OBJECT object at 0x8c11da0,)
On May 6, 10:37 am, Tobias tobias.sauerw...@camptocamp.com wrote:
Hi,
I am using SQLAlchemy 0.6 together with cx_Oracle and I am receiving
an error message when a database function is inside a package and has
to be called like package.functionname.
For example the following query can reproduce the error (beside that
this query makes not much sense):
session.query(table('spots',
column('spot_location'))).filter(getattr(func, 'MDSYS.SDO_GEOMETRY')
('POINT(0 0)', 4326) == text('spot_location')).first()
The output is:
2010-05-06 10:29:45,530 INFO sqlalchemy.engine.base.Engine.0x...a8ac
SELECT spots_spot_location
FROM (SELECT spots_spot_location, ROWNUM AS ora_rn
FROM (SELECT spots.spot_location AS spots_spot_location
FROM spots
WHERE
MDSYS.SDO_GEOMETRY(:MDSYS.SDO_GEOMETRY_1, :MDSYS.SDO_GEOMETRY_2) =
spot_location)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-06 10:29:45,530 INFO sqlalchemy.engine.base.Engine.0x...a8ac
{'ora_rn_1': 0, 'ROWNUM_1': 1, u'MDSYS.SDO_GEOMETRY_2': 4326,
u'MDSYS.SDO_GEOMETRY_1': 'POINT(0 0)'}
Traceback (most recent call last):
File stdin, line 1, in module
[..]
File .../env/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/
sqlalchemy/engine/default.py, line 277, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01036: illegal
variable name/number
'SELECT spots_spot_location \nFROM (SELECT spots_spot_location,
ROWNUM AS ora_rn \nFROM (SELECT spots.spot_location AS
spots_spot_location \nFROM spots \nWHERE
MDSYS.SDO_GEOMETRY(:MDSYS.SDO_GEOMETRY_1, :MDSYS.SDO_GEOMETRY_2) =
spot_location) \nWHERE ROWNUM = :ROWNUM_1) \nWHERE ora_rn :ora_rn_1'
{'ora_rn_1': 0, 'ROWNUM_1': 1, u'MDSYS.SDO_GEOMETRY_2':
4326, u'MDSYS.SDO_GEOMETRY_1': 'POINT(0 0)'}
Something is wrong about the escaping.
Thanks,
Tobias
--
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
athttp://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] Re: Oracle binding problem: (DatabaseError) ORA-01036: illegal variable name/number
Thanks for your reply!
I always thought that func.package.name(..) would be the same as calling
getattr(func, 'package.name')(..), but now I understand how the class
_FunctionGenerator works. :)
Because I only have the function name as string, I can't use the func.xy
construct. I am now manually constructing Function objects, which works
fine.
On Thu, May 6, 2010 at 4:52 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On May 6, 2010, at 7:47 AM, Tobias wrote:
By accident I found out that the SQLAlchemy Function class has an
attribute 'packagenames'. If you set this attribute manually, the
query is executed properly.
It would be great if this attribute is filled automatically by
splitting the function name on every dot.
it is, if you say func.MDSYS.SDO_GEOMETRY('POINT(0 0)'). The tokens
before the final one are sent into packagenames. see the third example at
http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.func.
session.query(Function('SDO_GEOMETRY', 'POINT(0 0)', 4326,
packagenames=['MDSYS'])).select_from(table('dual')).first()
2010-05-06 13:35:31,546 INFO sqlalchemy.engine.base.Engine.0x...a8ac
SELECT SDO_GEOMETRY_1
FROM (SELECT SDO_GEOMETRY_1, ROWNUM AS ora_rn
FROM (SELECT MDSYS.SDO_GEOMETRY(:SDO_GEOMETRY_2, :SDO_GEOMETRY_3) AS
SDO_GEOMETRY_1
FROM dual)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-06 13:35:31,547 INFO sqlalchemy.engine.base.Engine.0x...a8ac
{'ROWNUM_1': 1, 'SDO_GEOMETRY_3': 4326, 'SDO_GEOMETRY_2': 'POINT(0
0)', 'ora_rn_1': 0}
(cx_Oracle.OBJECT object at 0x8c11da0,)
On May 6, 10:37 am, Tobias tobias.sauerw...@camptocamp.com wrote:
Hi,
I am using SQLAlchemy 0.6 together with cx_Oracle and I am receiving
an error message when a database function is inside a package and has
to be called like package.functionname.
For example the following query can reproduce the error (beside that
this query makes not much sense):
session.query(table('spots',
column('spot_location'))).filter(getattr(func, 'MDSYS.SDO_GEOMETRY')
('POINT(0 0)', 4326) == text('spot_location')).first()
The output is:
2010-05-06 10:29:45,530 INFO sqlalchemy.engine.base.Engine.0x...a8ac
SELECT spots_spot_location
FROM (SELECT spots_spot_location, ROWNUM AS ora_rn
FROM (SELECT spots.spot_location AS spots_spot_location
FROM spots
WHERE
MDSYS.SDO_GEOMETRY(:MDSYS.SDO_GEOMETRY_1, :MDSYS.SDO_GEOMETRY_2) =
spot_location)
WHERE ROWNUM = :ROWNUM_1)
WHERE ora_rn :ora_rn_1
2010-05-06 10:29:45,530 INFO sqlalchemy.engine.base.Engine.0x...a8ac
{'ora_rn_1': 0, 'ROWNUM_1': 1, u'MDSYS.SDO_GEOMETRY_2': 4326,
u'MDSYS.SDO_GEOMETRY_1': 'POINT(0 0)'}
Traceback (most recent call last):
File stdin, line 1, in module
[..]
File .../env/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/
sqlalchemy/engine/default.py, line 277, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01036: illegal
variable name/number
'SELECT spots_spot_location \nFROM (SELECT spots_spot_location,
ROWNUM AS ora_rn \nFROM (SELECT spots.spot_location AS
spots_spot_location \nFROM spots \nWHERE
MDSYS.SDO_GEOMETRY(:MDSYS.SDO_GEOMETRY_1, :MDSYS.SDO_GEOMETRY_2) =
spot_location) \nWHERE ROWNUM = :ROWNUM_1) \nWHERE ora_rn :ora_rn_1'
{'ora_rn_1': 0, 'ROWNUM_1': 1, u'MDSYS.SDO_GEOMETRY_2':
4326, u'MDSYS.SDO_GEOMETRY_1': 'POINT(0 0)'}
Something is wrong about the escaping.
Thanks,
Tobias
--
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.comsqlalchemy%2bunsubscr...@googlegroups.com
.
For more options, visit this group athttp://
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.comsqlalchemy%2bunsubscr...@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] Re: @compiles ignores inheritance
Great! Thanks a lot, Michael. On Mar 30, 4:42 pm, Michael Bayer mike...@zzzcomputing.com wrote: Tobias wrote: Hi, So I thought I could write just one method, that is annotated with @compiles(__base_function), but this does not work. I have to write a method for each class that inherits from __base_function: @compiles(wkt) def compile_wkt(element, compiler, **kw): return __call_function(element, compiler) @compiles(wkb) def compile_wkb(element, compiler, **kw): return __call_function(element, compiler) none of that was really working (including, can't even have @compiles on the base and subclass at the same time) so that all works in rea184f5ba747. latest tip. -- 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] @compiles ignores inheritance
Hi, I am having a bunch of classes that inherit from Function and all of them should be compiled by a method annotated with @compiles. class __base_function(Function): def __init__(self, clause, *clauses, **kw): self.clause = clause Function.__init__(self, self.__class__.__name__, *clauses, **kw) class wkt(__base_function): pass class wkb(__base_function): pass [..] So I thought I could write just one method, that is annotated with @compiles(__base_function), but this does not work. I have to write a method for each class that inherits from __base_function: @compiles(wkt) def compile_wkt(element, compiler, **kw): return __call_function(element, compiler) @compiles(wkb) def compile_wkb(element, compiler, **kw): return __call_function(element, compiler) [..] Is there a more elegant way that I do not have to write a method for each class? Thank you, Tobias -- 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] Re: @compiles: only apply in select clause, but not in where clause
take a look in **kw. there should be a within_columns_clause flag. if not, try 0.6beta2. if not there, let me know. Hi Michael, thanks for your reply! In 0.5.8 there was no within_columns_clause, but it is available in 0.6beta2. And it seems to do exactly what I need! Tobias -- 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] 0.5 - 0.6: Default parameters of Numeric changed?
Hi, I just found out that the default parameters for sqlalchemy.types.Numeric changed in 0.6. 0.6beta2: http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#sqlalchemy.types.Numeric 0.5: http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#sqlalchemy.types.Numeric Is this change intended? Tobias -- 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] Re: 0.5 - 0.6: Default parameters of Numeric changed?
This was fast! Thanks for the information. On Mar 23, 11:36 am, Wichert Akkerman wich...@wiggy.net wrote: On 3/23/10 11:33 , Tobias wrote: Hi, I just found out that the default parameters for sqlalchemy.types.Numeric changed in 0.6. 0.6beta2: http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#sqlalc... 0.5: http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#sql... Is this change intended? Quoting fromhttp://www.sqlalchemy.org/trac/wiki/06Migration: The default precision and scale arguments of Numeric and Float have been removed and now default to None. NUMERIC and FLOAT will be rendered with no numeric arguments by default unless these values are provided. Wichert. -- 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] @compiles: only apply in select clause, but not in where clause
Hi, I am working on the extension GeoAlchemy [1]. Currently GeoAlchemy always fetches the data for the mapped geometry attributes in the database internal format. I am trying to force GeoAlchemy to use the format WKB for the communication with the database. @compiles(MyColumn) def compile_mycolumn(element, compiler, **kw): return AsBinary(%s) % element.name I am using the compiler extension to query for the geometry in WKB. So far this works fine for loading and inserting/updating mapped objects, but it is getting a bit ugly for queries. Please consider to following query: session.query(MyPoint).filter(MyPoint.the_geom.within(polygon.the_geom)).first() After modifying the SpatialComparator, the query send to the database looks like this: SELECT AsBinary(the_geom) AS tests_the_geom, tests.id AS tests_id, tests.name AS tests_name FROM tests WHERE Within(GeomFromWKB(AsBinary(the_geom)), GeomFromWKB(%s)) LIMIT 0, 1 For this query the method compile_mycolumn(..) is called two times: one time for the select clause and one time for the where clause. The first time it is intended, but the second time I have to do this round- trip GeomFromWKB(AsBinary(the_geom)) to make it work. My question is: Is there a way to determine if the attribute is used in the select clause, so that only then the name is surrounded by the function call? Or is there another possibility to use a different format than the database internal? Thank you, Tobias [1]: http://geoalchemy.org/ -- 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] ORM-Query generates correct SQL but returns no result
Hello after falling in love with SQLAlchemy I'm trying to transform most of my raw SQL-Statements into ORM-Queries. I'm using cx_Oracle 4.3.3, SQLAlchemy 0.5.4p2 and Oracle 10.2.0.4.0 under Windows XP with Python 2.5.2. At the moment I'm stuck with this statement (most columns are replaced with `*') -- D) stack_id select SELECT * FROM pe_statushistory WHERE stack_id = :stack_id UNION ALL -- C) IN-Clause with subselect SELECT * FROM pe_statushistory WHERE msg_detail2 IN ( -- A) Document subselect SELECT fndoc_id FROM pe_process p WHERE p.stack_id = :stack_id AND fndoc_id IS NOT NULL UNION ALL -- B) Process subselect SELECT fndoc_id FROM pe_document d WHERE d.stack_id = :stack_id AND fndoc_id IS NOT NULL) ORDER BY dbtime DESC, logtime DESC The transformation looks like this # A) fdoc_id_from_doc = self.session.query (pe_model.Document.fndoc_id)\ .filter(pe_model.Document.stack_id == stack_id)\ .filter(pe_model.Document.fndoc_id != None) # B) fdoc_id_from_proc = self.session.query (pe_model.Process.fndoc_id)\ .filter(pe_model.Process.stack_id == stack_id)\ .filter(pe_model.Process.fndoc_id != None) doc_id_sub = fdoc_id_from_doc.union_all(fdoc_id_from_proc) # C) history_from_fdoc_id = self.session.query (pe_model.StatusHistory)\ .filter(pe_model.StatusHistory.msg_detail2.in_ (doc_id_sub)) # D) history = self.session.query(pe_model.StatusHistory)\ .filter(pe_model.StatusHistory.stack_id == stack_id)\ .union_all(history_from_fdoc_id) The generated SQL-Statement looks good print history SELECT anon1.* FROM (SELECT * FROM POSTEINGANG_ADM.pe_statushistory WHERE POSTEINGANG_ADM.pe_statushistory.stack_id = :stack_id_1 UNION ALL SELECT * FROM POSTEINGANG_ADM.pe_statushistory WHERE POSTEINGANG_ADM.pe_statushistory.msg_detail2 IN ( SELECT anon_2.fndoc_id FROM (SELECT POSTEINGANG_ADM.pe_document.fndoc_id AS fndoc_id FROM POSTEINGANG_ADM.pe_document WHERE POSTEINGANG_ADM.pe_document.stack_id = :stack_id_2 AND POSTEINGANG_ADM.pe_document.fndoc_id IS NOT NULL UNION ALL SELECT POSTEINGANG_ADM.pe_process.fndoc_id AS fndoc_id FROM POSTEINGANG_ADM.pe_process WHERE POSTEINGANG_ADM.pe_process.stack_id = :stack_id_3 AND POSTEINGANG_ADM.pe_process.fndoc_id IS NOT NULL) anon_2)) anon_1 ORDER BY anon_1.dbtime, anon_1.logtime When pasted into Toad or SQLPlus the result are expected and match. But history.all() [None] How can I help SQLAlchemy to map the results into instances of the mapped class pe_model.StatusHistory? Regards Tobias Bell --~--~-~--~~~---~--~~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
