[sqlalchemy] Problems with typeadapter on HSTORE
I’ve been looking to make a simple typeadapter based on the HSTORE data type in postgres to do some value coercion and I have been having some problems. I want to coerce the individual values inside my dictionary before they are put into the postgres HSTORE column and when I select keys, I want to coerce them back. What methods do I need to subclass to intercept subkey/indexing operations as well as full assignment operations? Normally for a simpler datatype I’d just implement process_bind_param, process_result_value but that hasn't been working the way I expect it to with HSTORE. If it makes the problem simpler I don’t need the MutableExtension to work so I only need to coerce data on the first assignment to the column. I'll primarily be selecting data like Query(MyObjectClass.MyHSTOREType['somekey']) but occasionally, I may be doing Query(MyObjectClass.MyHSTOREType) and would like my postgres-python coercion to work in both scenarios. Thanks in advance -- 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/d/optout.
[sqlalchemy] UnicodeDecode error on sqlalchemy select query
We are using sqlalchemy version 0.7, python 2.7 and oracle Database. We have ASCII as default python encoding and DB have ISO-8052 encoding. Our DB tables contains some of characters which are out of ASCII range. So when we are running query on those tables we are getting Unicode Decode error saying ASCII codec can not decode. This error we are getting without accessing model attributes. How i can handle these errors without changing python default encoding. -- 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/d/optout.
Re: [sqlalchemy] Problems with typeadapter on HSTORE
On 7/29/15 11:30 AM, Morgan McClure wrote:
I’ve been looking to make a simple typeadapter based on the HSTORE
data type in postgres to do some value coercion and I have been having
some problems.
I want to coerce the individual values inside my dictionary before
they are put into the postgres HSTORE column and when I select keys, I
want to coerce them back.
What methods do I need to subclass to intercept subkey/indexing
operations as well as full assignment operations?
Normally for a simpler datatype I’d just implement process_bind_param,
process_result_value but that hasn't been working the way I expect it
to with HSTORE.
If it makes the problem simpler I don’t need the MutableExtension to
work so I only need to coerce data on the first assignment to the column.
I'll primarily be selecting data like
Query(MyObjectClass.MyHSTOREType['somekey'])
but occasionally, I may be doing
Query(MyObjectClass.MyHSTOREType)
and would like my postgres-python coercion to work in both scenarios.
the issue here is that for PG's special types like HSTORE, JSON, ARRAY,
JSONB, those objects are of those types sure, but when we use the
special index operators, we *dont* get that type back; e.g. for an
HSTORE, myobject['foo'] returns an expression that is of type Text:
from sqlalchemy.dialects import postgresql
from sqlalchemy import column
print column('x', postgresql.HSTORE())['foo'].type
TEXT
The issue of these return types for the PG indexing types has been
coming up a lot
(https://bitbucket.org/zzzeek/sqlalchemy/issues/3499/flip-off-the-hashable-flag-for-pg,
https://bitbucket.org/zzzeek/sqlalchemy/issues/3487/support-direct-mult-dimensional-array).
Something will have to improve in general for this kind of issue.
For now you can apply your type using either cast() or coerce_type:
from sqlalchemy import coerce_type
Query(coerce_type(MyObjectClass.MyHSTOREType['somekey'], MySpecialType))
https://bitbucket.org/zzzeek/sqlalchemy/issues/3503/add-full-control-for-return-type-under-pg
is added as a catchall to look into this.
Thanks in advance
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] UnicodeDecode error on sqlalchemy select query
On 7/29/15 2:23 PM, Abhishek Sharma wrote: We are using sqlalchemy version 0.7, python 2.7 and oracle Database. We have ASCII as default python encoding and DB have ISO-8052 encoding. Our DB tables contains some of characters which are out of ASCII range. So when we are running query on those tables we are getting Unicode Decode error saying ASCII codec can not decode. This error we are getting without accessing model attributes. How i can handle these errors without changing python default encoding. Oracle's client encoding is controlled by the NLS_LANG environment variable.That has to be set correctly first off (see http://www.oracle.com/technetwork/products/globalization/nls-lang-099431.html). If you have non-ASCII strings encoded in datatypes that are explicitly not of type NVARCHAR or NCLOB , or you're relying on a lot of raw SQL, and you are still getting errors, I would set the coerce_to_unicode=True flag on create_engine(), which allows cx_Oracle's unicode facilities to take place fully for all string data being returned, at the expense of some performance. See http://docs.sqlalchemy.org/en/rel_1_0/dialects/oracle.html#unicode for background. -- 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 mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Bizzarre insert behavior: NULL constraint violation with non-null value, column changes every run.
On 7/29/15 4:27 AM, Ladislav Lenart wrote:
Hello.
I don't know why but the problematic version uses bytes as keys in the params
dictionary (e.g. b'batch_id') whereas the working version uses strings (e.g.
'batch_id'). I am not a Python 3 expert but I think that the two types are
distinct and thus the search for a string fails. This would also explain why the
column in the error changes - because dictionaries are nondeterministic.
Whatever key is searched for first will become the culprit.
Note however that these are just my assumptions...
I was about to say that cx_Oracle 5.2 probably has some issue with the
fact that we're encoding strings first, where as 5.1.3 did not. But I
looked at the source, and saw that we are not *supposed* to be encoding
to bytes on Py3k. So this might identify the bug on our end, that we
aren't detecting the cx_oracle version properly (we look for version
(5, 0) and set accept unicode across the board .. why isnt this
happening here?)
https://bitbucket.org/zzzeek/sqlalchemy/issues/3491/queryfirst-sometimes-returns-none
is reopened to look into this.
HTH,
L.
On 28.7.2015 18:08, Bob Ternosky wrote:
That did it.
2015-07-28 12:06:16,283 INFO sqlalchemy.engine.base.Engine INSERT INTO
corp.test_table (batch_id, batch_type, scheduled_date, status, emp_id, bill_per,
label, log_file, debug_file, completed_date) VALUES (:batch_id, :batch_type,
:scheduled_date, :status, :emp_id, :bill_per, :label, :log_file, :debug_file,
:completed_date)
2015-07-28 12:06:16,283 INFO sqlalchemy.engine.base.Engine {'log_file': None,
'batch_id': 99, 'debug_file': None, 'scheduled_date':
datetime.datetime(2015, 7, 28, 12, 6, 16, 282779), 'label': 'Testing insert',
'batch_type': 1, 'bill_per': 201501, 'status': 0, 'completed_date': None,
'emp_id': 8329}
2015-07-28 12:06:16,311 INFO sqlalchemy.engine.base.Engine COMMIT
You just saved my sanity.
Thanks a million!
And thanks to Jonathan Vanasco, your suggestions didn't work, but I learned a
few new settings.
On Tuesday, July 28, 2015 at 11:54:14 AM UTC-4, Michael Bayer wrote:
Just curious, can you try out cx_Oracle 5.1.3? I've seen some problems
reported with Py3K and cx_Oracle 5.2.
On 7/28/15 11:17 AM, Bob Ternosky wrote:
I'm new to SQLAlchemy and have been playing with it for a week. I've got
many SELECT based pieces working and exercising properly, but have hit a
huge wall when trying to test inserts. Worse, what's happening makes no
sense at all.
This will hopefully contain the full set of information needed. Any help
would be greatly appreciated.
The summary: When running a simple test insert it will fail with the error:
cx_Oracle.IntegrityError: ORA-01400: cannot insert NULL into
(CORP.TEST_TABLE.XXX)
where XXX changes just about every time I run the insert statement - with
identical data.
My machine is running Linux Mint 17.2 64-bit
Software:
* Python 3.4.0 (with virtualenv)
* SQLAlchemy 1.0.7
* cx_Oracle 5.2
Connecting to an Oracle 9i (9.2.0.6.0) database (joys of legacy systems).
Using Oracle Instant Client 11.2.0 libraries
An Oracle description of the table (the table is empty):
desc test_table
BATCH_ID NOT NULL NUMBER(10,0)
BATCH_TYPE NOT NULL NUMBER(2,0)
SCHEDULED_DATE NOT NULL DATE
STATUS NOT NULL NUMBER(1,0)
EMP_ID NOT NULL NUMBER(10,0)
BILL_PER NOT NULL NUMBER(6,0)
LABEL NOT NULL VARCHAR2(128)
LOG_FILE NULL VARCHAR2(256)
DEBUG_FILE NULL VARCHAR2(256)
COMPLETED_DATE NULL DATE
The table resides in the CORP schema.
Test script named: isolated.py
# isolated.py
import argparse
import datetime
from sqlalchemy import Column, create_engine, DateTime, insert, MetaData,
Numeric, String, Table
# Declare insert test table
metadata = MetaData()
t_test_table = Table(
'test_table', metadata,
Column('batch_id', Numeric(9, 0, asdecimal=False), primary_key=True),
Column('batch_type', Numeric(2, 0, asdecimal=False), nullable=False),
Column('scheduled_date', DateTime, nullable=False),
Column('status', Numeric(1, 0, asdecimal=False), nullable=False),
Column('emp_id', Numeric(10, 0, asdecimal=False), nullable=False),
Column('bill_per', Numeric(6, 0, asdecimal=False), nullable=False),
Column('label', String(128), nullable=False),
Column('log_file', String(256)),
Column('debug_file', String(256)),
Column('completed_date', DateTime),
schema='corp'
)
# Oracle Credentials
USER = 'REDACTED'
PASSWD = 'REDACTED'
SID= 'REDACTED'
###
# Main
###
parser = argparse.ArgumentParser(description = 'Test SQLAlchemy Insert')
Re: [sqlalchemy] Bizzarre insert behavior: NULL constraint violation with non-null value, column changes every run.
On 7/28/15 11:17 AM, Bob Ternosky wrote:
I'm new to SQLAlchemy and have been playing with it for a week. I've
got many SELECT based pieces working and exercising properly, but have
hit a huge wall when trying to test inserts. Worse, what's happening
makes no sense at all.
This will hopefully contain the full set of information needed. Any
help would be greatly appreciated.
Please also test cx_Oracle 5.2 again with the latest master and/or
rel_1_0 branch of SQLAlchemy - I've repaired an issue with cx_Oracle
version detection for py3K that could fix this, will be in 1.0.9.
The summary: When running a simple test insert it will fail with the
error:
cx_Oracle.IntegrityError: ORA-01400: cannot insert NULL into
(CORP.TEST_TABLE.XXX)
where XXX changes just about every time I run the insert statement -
with identical data.
My machine is running Linux Mint 17.2 64-bit
Software:
* Python 3.4.0 (with virtualenv)
* SQLAlchemy 1.0.7
* cx_Oracle 5.2
Connecting to an Oracle 9i (9.2.0.6.0) database (joys of legacy systems).
Using Oracle Instant Client 11.2.0 libraries
An Oracle description of the table (the table is empty):
desc test_table
BATCH_ID NOT NULL NUMBER(10,0)
BATCH_TYPE NOT NULL NUMBER(2,0)
SCHEDULED_DATE NOT NULL DATE
STATUS NOT NULL NUMBER(1,0)
EMP_ID NOT NULL NUMBER(10,0)
BILL_PER NOT NULL NUMBER(6,0)
LABEL NOT NULL VARCHAR2(128)
LOG_FILE NULL VARCHAR2(256)
DEBUG_FILE NULL VARCHAR2(256)
COMPLETED_DATE NULL DATE
The table resides in the CORP schema.
Test script named: isolated.py
# isolated.py
import argparse
import datetime
from sqlalchemy import Column, create_engine, DateTime, insert,
MetaData, Numeric, String, Table
# Declare insert test table
metadata = MetaData()
t_test_table = Table(
'test_table', metadata,
Column('batch_id', Numeric(9, 0, asdecimal=False), primary_key=True),
Column('batch_type', Numeric(2, 0, asdecimal=False), nullable=False),
Column('scheduled_date', DateTime, nullable=False),
Column('status', Numeric(1, 0, asdecimal=False), nullable=False),
Column('emp_id', Numeric(10, 0, asdecimal=False), nullable=False),
Column('bill_per', Numeric(6, 0, asdecimal=False), nullable=False),
Column('label', String(128), nullable=False),
Column('log_file', String(256)),
Column('debug_file', String(256)),
Column('completed_date', DateTime),
schema='corp'
)
# Oracle Credentials
USER = 'REDACTED'
PASSWD = 'REDACTED'
SID= 'REDACTED'
###
# Main
###
parser = argparse.ArgumentParser(description = 'Test SQLAlchemy Insert')
parser.add_argument('-c', '--cxoracle', dest = 'cxoracle', action =
'store_true',
required = False, default = False, help = 'Use
oracle+cx_oracle engine')
parser.add_argument('-o', '--oracle', dest = 'oracle', action =
'store_true',
required = False, default = False, help = 'Use
oracle only engine')
args = parser.parse_args()
if not args.cxoracle and not args.oracle:
parser.error(You must provide one of: [-c] [-o])
# Pick an Oracle connection method
if args.cxoracle:
LIBRARY = 'oracle+cx_oracle'
else:
LIBRARY = 'oracle'
engine = create_engine('{}://{}:{}@{}'.format(LIBRARY, USER, PASSWD,
SID), echo = True)
conn = engine.connect()
values = dict(batch_id = 99,
batch_type = 1,
scheduled_date = datetime.datetime.now(),
status = 0,
emp_id = 8329,
bill_per = 201501,
label = Testing insert,
log_file = None,
debug_file = None,
completed_date = None)
tbl = t_test_table
ins = tbl.insert().values(values)
result = conn.execute(ins)
print(result)
# Cleanup
conn.close()
engine.dispose()
-
I tried to isolate cx_Oracle vs basic Oracle, but no luck. If I run
the script (with either -c or -o) I get the error message about the
NULL violation. If I run it twice in a row, I get the same message,
but the column changes. I've gotten batch_id, batch_type, status,
emp_id, scheduled_date.
I've added some debug output to SQLAlchemy's do_execute method in
python3.4/site-packages/sqlalchemy/engine/default.py:
def do_execute(self, cursor, statement, parameters, context=None):
+print(* *70)
+ print(cursor)
+print(statement)
+ print(parameters)
+print(context)
+print(* *70)
cursor.execute(statement, parameters)
Here is the output of 2 consecutive runs against an empty table:
(venv)[bash]$ python isolated.py -c
2015-07-28 11:12:40,599 INFO sqlalchemy.engine.base.Engine b'SELECT
USER FROM DUAL'
2015-07-28 11:12:40,599 INFO sqlalchemy.engine.base.Engine {}
**
cx_Oracle.Cursor on cx_Oracle.Connection to USER@DB
b'SELECT USER FROM DUAL'
{}
Re: [sqlalchemy] Bizzarre insert behavior: NULL constraint violation with non-null value, column changes every run.
Hello.
I don't know why but the problematic version uses bytes as keys in the params
dictionary (e.g. b'batch_id') whereas the working version uses strings (e.g.
'batch_id'). I am not a Python 3 expert but I think that the two types are
distinct and thus the search for a string fails. This would also explain why the
column in the error changes - because dictionaries are nondeterministic.
Whatever key is searched for first will become the culprit.
Note however that these are just my assumptions...
HTH,
L.
On 28.7.2015 18:08, Bob Ternosky wrote:
That did it.
2015-07-28 12:06:16,283 INFO sqlalchemy.engine.base.Engine INSERT INTO
corp.test_table (batch_id, batch_type, scheduled_date, status, emp_id,
bill_per,
label, log_file, debug_file, completed_date) VALUES (:batch_id, :batch_type,
:scheduled_date, :status, :emp_id, :bill_per, :label, :log_file, :debug_file,
:completed_date)
2015-07-28 12:06:16,283 INFO sqlalchemy.engine.base.Engine {'log_file': None,
'batch_id': 99, 'debug_file': None, 'scheduled_date':
datetime.datetime(2015, 7, 28, 12, 6, 16, 282779), 'label': 'Testing insert',
'batch_type': 1, 'bill_per': 201501, 'status': 0, 'completed_date': None,
'emp_id': 8329}
2015-07-28 12:06:16,311 INFO sqlalchemy.engine.base.Engine COMMIT
You just saved my sanity.
Thanks a million!
And thanks to Jonathan Vanasco, your suggestions didn't work, but I learned a
few new settings.
On Tuesday, July 28, 2015 at 11:54:14 AM UTC-4, Michael Bayer wrote:
Just curious, can you try out cx_Oracle 5.1.3? I've seen some problems
reported with Py3K and cx_Oracle 5.2.
On 7/28/15 11:17 AM, Bob Ternosky wrote:
I'm new to SQLAlchemy and have been playing with it for a week. I've got
many SELECT based pieces working and exercising properly, but have hit a
huge wall when trying to test inserts. Worse, what's happening makes no
sense at all.
This will hopefully contain the full set of information needed. Any help
would be greatly appreciated.
The summary: When running a simple test insert it will fail with the
error:
cx_Oracle.IntegrityError: ORA-01400: cannot insert NULL into
(CORP.TEST_TABLE.XXX)
where XXX changes just about every time I run the insert statement - with
identical data.
My machine is running Linux Mint 17.2 64-bit
Software:
* Python 3.4.0 (with virtualenv)
* SQLAlchemy 1.0.7
* cx_Oracle 5.2
Connecting to an Oracle 9i (9.2.0.6.0) database (joys of legacy systems).
Using Oracle Instant Client 11.2.0 libraries
An Oracle description of the table (the table is empty):
desc test_table
BATCH_ID NOT NULL NUMBER(10,0)
BATCH_TYPE NOT NULL NUMBER(2,0)
SCHEDULED_DATE NOT NULL DATE
STATUS NOT NULL NUMBER(1,0)
EMP_ID NOT NULL NUMBER(10,0)
BILL_PER NOT NULL NUMBER(6,0)
LABEL NOT NULL VARCHAR2(128)
LOG_FILE NULL VARCHAR2(256)
DEBUG_FILE NULL VARCHAR2(256)
COMPLETED_DATE NULL DATE
The table resides in the CORP schema.
Test script named: isolated.py
# isolated.py
import argparse
import datetime
from sqlalchemy import Column, create_engine, DateTime, insert, MetaData,
Numeric, String, Table
# Declare insert test table
metadata = MetaData()
t_test_table = Table(
'test_table', metadata,
Column('batch_id', Numeric(9, 0, asdecimal=False), primary_key=True),
Column('batch_type', Numeric(2, 0, asdecimal=False), nullable=False),
Column('scheduled_date', DateTime, nullable=False),
Column('status', Numeric(1, 0, asdecimal=False), nullable=False),
Column('emp_id', Numeric(10, 0, asdecimal=False), nullable=False),
Column('bill_per', Numeric(6, 0, asdecimal=False), nullable=False),
Column('label', String(128), nullable=False),
Column('log_file', String(256)),
Column('debug_file', String(256)),
Column('completed_date', DateTime),
schema='corp'
)
# Oracle Credentials
USER = 'REDACTED'
PASSWD = 'REDACTED'
SID= 'REDACTED'
###
# Main
###
parser = argparse.ArgumentParser(description = 'Test SQLAlchemy Insert')
parser.add_argument('-c', '--cxoracle', dest = 'cxoracle', action =
'store_true',
required = False, default = False, help = 'Use
oracle+cx_oracle engine')
parser.add_argument('-o', '--oracle', dest = 'oracle', action =
'store_true',
required = False, default = False, help = 'Use oracle
only engine')
args = parser.parse_args()
if not args.cxoracle and not args.oracle:
parser.error(You must provide one of: [-c] [-o])
# Pick an Oracle connection method
if args.cxoracle:
LIBRARY =
