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
>> this<https://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+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com<javascript:>
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
> Thank you very much! The problem was the missing return in
process_bind_param(). It works now like a charm.
--
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.
