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 <tobias.b...@gmail.com> 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 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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
