I have trouble inserting Chinese characters into an Oracle database with
SQLAlchemy (and to some extent with cx_Oracle). I don't specifically need
Chinese characters but I used them in a unit test to see if my code
(hopefully) can handle "any" unicode characters.
The setup:
- SQLAlchemy 1.1.15 and 1.2
- Oracle database 11.2
- cx_Oracle 6.1
- environment variable set from the shell ( export
NLS_LANG="German_Germany.UTF8" ) and in the code
Database table:
CREATE TABLE UTF8_TEST (
TEXT NVARCHAR2(10)
)
1) SQLAlchemy, version 1.1.15 and 1.2
import os
os.environ["NLS_LANG"] = "German_Germany.UTF8"
import sqlalchemy as sa
metadata = sa.MetaData()
test_table = sa.Table("utf8_test", metadata, sa.Column("text",
sa.Unicode(10)))
def test(host, port, service_name, user, password):
connect_string =
"oracle+cx_oracle://{}:{}@{}:{}/?service_name={}".format(user, password,
host, port, service_name)
engine = sa.engine.create_engine(connect_string, encoding="utf8")
insert = test_table.insert().values(text="\u7535\u8111")
with engine.connect() as connection:
connection.execute(insert)
if __name__ == "__main__":
db_config = ...
test(db_config.host, db_config.port, db_config.service_name,
db_config.user, db_config.password)
When this code is executed, it inserts something into the database, but it
shows up as two upside-down question marks when viewed with Oracle SQL
Developer. This doesn't seem to be an encoding/rendering problem in SQL
Developer (see below for code snippet 3).
2) cx_Oracle, without `Cursor.setinputsizes`
import os
os.environ["NLS_LANG"] = "German_Germany.UTF8"
import cx_Oracle
def test(host, port, service_name, user, password):
dsn = cx_Oracle.makedsn(host, port, service_name=service_name)
conn = cx_Oracle.connect(user, password, dsn=dsn, encoding="utf8",
nencoding="utf8")
cur = conn.cursor()
cur.execute("INSERT INTO UTF8_TEST (TEXT) VALUES (:t)", {"t":
"\u7535\u8111"})
cur.close()
conn.commit()
if __name__ == "__main__":
db_config = ...
test(db_config.host, db_config.port, db_config.service_name,
db_config.user, db_config.password)
This code has the same effect as the SQLAlchemy code above.
3) cx_Oracle with `Cursor.setinputsizes`
import os
os.environ["NLS_LANG"] = "German_Germany.UTF8"
import cx_Oracle
def test(host, port, service_name, user, password):
dsn = cx_Oracle.makedsn(host, port, service_name=service_name)
conn = cx_Oracle.connect(user, password, dsn=dsn, encoding="utf8",
nencoding="utf8")
cur = conn.cursor()
* # Using `setinputsizes` makes the difference. cur.execute("SELECT
TEXT FROM UTF8_TEST") description = cur.description # `t` is name of
bind parameter below. cur.setinputsizes(t=description[0][1])*
cur.execute("INSERT INTO UTF8_TEST (TEXT) VALUES (:t)", {"t":
"\u7535\u8111"})
cur.close()
conn.commit()
if __name__ == "__main__":
db_config = ...
test(db_config.host, db_config.port, db_config.service_name,
db_config.user, db_config.password)
With the `setinputsizes` call, the Chinese characters appear in Oracle SQL
Developer as they should.
In case it matters, I tried to use some German special characters (the
string "äß") and this works for all three variants. These two characters
are encoded in UTF-8 to one byte each whereas the Chinese characters are
encoded to several bytes each.
Should the above code for SQLAlchemy work as-is and insert the Chinese
charaters (I expected it)?
Is there a workaround for now to have SQLAlchemy insert the correct
characters? For example, is there a way to tell SQLAlchemy to use
`Cursor.setinputsizes` for inserts? (I assume this would apply to updates,
too.)
Best regards,
Stefan
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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.
