Re: [sqlalchemy] SQLAlchemy lock-up when logging to a database
On Wednesday, February 21, 2018 at 10:31:30 PM UTC+1, Mike Bayer wrote: > > or have your logger look for that "log_only" token in the logger name > and skip on that, that's more future-proof. > Yes, using a specific logger for the engine seems to work. :-) I had looked for something like this, but didn't find it. I found http://docs.sqlalchemy.org/en/latest/core/engines.html#configuring-logging , but didn't see anything there on engine-specific logging names, so I thought it wasn't possible. (The `logging_name` argument for `create_engine` _is_ documented though.) 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.
Re: [sqlalchemy] SQLAlchemy lock-up when logging to a database
Thanks for the quick reply! On Wednesday, February 21, 2018 at 6:44:33 PM UTC+1, Mike Bayer wrote: > > I can come up with other things that can break based on the idea that > you're doing a full connection + execute sequence within the log > handler deep within that engine's own processes, such as, it can cause > connection pool exhaustion when you're working with the last > connection available, then your logger has no connection, it goes to > get one and...hang. I can reproduce this with your script (set > pool_size=1, max_overflow=0, set up logging and run an execute() so it > actually logs, and it hangs for 30 seconds until the timeout and > crashes, see stack trace below), so your approach is not safe in any > case. > Thanks for the clarification. > It would be a lot simpler and robust if your log handler just used its > own Engine (make a new one from the engine.url you already have). Good idea. > That way you can lose all that re-entrant mutex stuff too. > The mutex stuff is there because in the actual code SQLAlchemy logging is switched on and I want to prevent that SQLAlchemy tries to log the insert statement in `_emit`. I didn't think of this when I posted the original question, so I could have left out the mutex stuff for the sake of the example code. 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.
[sqlalchemy] SQLAlchemy lock-up when logging to a database
In our project we recently ran into the problem that SQLAlchemy would
block when creating a connection for logging to a database.
Set-up:
Oracle server 11.2
cx_Oracle 6.1
SQLAlchemy 1.2.3
The example code for `connection_lockup.py` is:
==
"""
Example script to demonstrate a lock-up when trying to create a second
connection _while_ creating a first connection.
Do _not_ modify the program to create the table and attempt to block
in the same run. In this case, the lockup won't happen. Instead run
the program first with `connection_lockup.py make_table` (unless the
table already exists), then with `connection_lockup.py block`.
"""
import datetime
import logging
import os
import sys
import threading
import sqlalchemy
# Set connect string (starting with `oracle+cx_oracle://`) in environment.
# I don't know if the problem also occurs with other dialects.
CONNECT_STRING = os.getenv("CONNECT_STRING")
# Schema and engine set-up.
metadata = sqlalchemy.MetaData()
log_table = sqlalchemy.Table(
"log2", metadata,
sqlalchemy.Column("id", sqlalchemy.Integer,
sqlalchemy.schema.Sequence("log_id_seq"),
primary_key=True),
sqlalchemy.Column("timestamp_utc",
sqlalchemy.TIMESTAMP(timezone=False),
nullable=False),
sqlalchemy.Column("level_name", sqlalchemy.Unicode(1000)),
sqlalchemy.Column("message", sqlalchemy.Unicode(1000),
nullable=False))
engine = sqlalchemy.create_engine(CONNECT_STRING)
# --
def make_table():
log_table.create(engine, checkfirst=True)
class DatabaseHandler(logging.Handler):
"""
Handler for logging to Oracle database
"""
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
# Keep a reference to the engine for the log database.
self._engine = engine
# Make sure that at least one connection has been retrieved from the
# engine before database logging is set up.
# with self._engine.connect() as _connection:
# pass
# For preventing that the logging `INSERT` statement is also logged.
thread_local = threading.local()
thread_local.emit_lock = threading.Lock()
self._emit_lock = thread_local.emit_lock
def _emit(self, record):
"""
Emit the log record.
"""
# Some code causes a log message being an empty string. This wouldn't be
# a problem, but Oracle implicitly converts this to `NULL`, which in
# turn causes an `IntegrityError` when the log record is about to be
# written to the database. Therefore, in this case set the message to a
# sensible (non-empty) string.
message = record.msg
if not message:
message = "(no log message)"
else:
message = message % record.args
insert = log_table.insert()
insert = insert.values(
# According to the documentation
#
https://docs.python.org/3/library/logging.html#logrecord-attributes
# the `created` attribute is filled from `time.time()`, which
# uses local time. Therefore, use `utcfromtimestamp` to get
# an UTC timestamp.
timestamp_utc=datetime.datetime.utcfromtimestamp(record.created),
level_name=record.levelname,
message=message)
# Run `INSERT` in a transaction. See
#
http://docs.sqlalchemy.org/en/latest/core/connections.html#using-transactions
with self._engine.begin() as connection:
connection.execute(insert)
def emit(self, record):
"""
Emit the log record.
Make sure that emitting the log record isn't invoked recursively, i. e.
when SQLAlchemy itself would log something while inserting the log
record into the database.
"""
is_acquired = self._emit_lock.acquire(blocking=False)
if not is_acquired:
# An "outer" call of `emit` acquired the lock before. Since we don't
# want any recursive calls, return immediately.
return
# Don't use a `with` statement here because this would try to acquire
# the lock. See
#
https://docs.python.org/3/library/threading.html#using-locks-conditions-and-semaphores-in-the-with-statement
try:
self._emit(record)
finally:
self._emit_lock.release()
def block():
logger = logging.getLogger("sqlalchemy")
logger.addHandler(DatabaseHandler())
logger.setLevel(logging.INFO)
# Blocks.
#
# For this to "work", this must be the first connection created for th
Re: [sqlalchemy] Inserting Chinese characters in Oracle database
Hi Mike,
thanks again!
On 2018-01-15 23:17, Mike Bayer wrote:
> On Mon, Jan 15, 2018 at 3:18 PM, Stefan Schwarzer
> wrote:
>> On 2018-01-12 16:33, Mike Bayer wrote:> On Fri, Jan 12, 2018 at 7:14 AM,
>> Stefan Schwarzer
>>> wrote:
> the issue you refer to with Anthony refers to a new use case for the
> cx_Oracle DBAPI, where we use setinputsizes() again and use a
> different datatype for strings. So that would need to be added to
> SQLAlchemy's cx_Oracle dialect as an option, which can either be on a
> per-datatype basis or engine-wide (engine-wide is easier to implement
> and use however I'm not sure which is more appropriate).
>
> You can probably get this to work right now doing this:
>
> from sqlalchemy.dialects.oracle import _OracleString
> class NCHAR(_OracleNVarChar):
> def get_dbapi_type(self, dbapi):
> return cx_Oracle.NCHAR
>
> and then add cx_Oracle.NCHAR to the dialect._include_setinputsizes list.
The following works for me:
import sqlalchemy.types
import sqlalchemy.dialects.oracle.cx_oracle as cx_oracle_dialect
class OracleNVarChar(cx_oracle_dialect._OracleNVarChar):
def get_dbapi_type(self, dbapi):
return cx_Oracle.NCHAR
cx_oracle_dialect.OracleDialect_cx_oracle.colspecs[sqlalchemy.types.Unicode] =
OracleNVarChar
metadata = sa.MetaData()
test_table = sa.Table("utf8_test", metadata, sa.Column("text", sa.Unicode(20)))
connect_string = "oracle+cx_oracle://..."
engine = sa.create_engine(connect_string, echo='debug')
engine.dialect._include_setinputsizes.add(cx_Oracle.NCHAR)
Changes to your version:
- Changed import. I wasn't sure whether your import or
the base class was wrong.
- Added assignment to `colspecs` dictionary
> I've proposed a few ways we might be able to add this API here:
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4163/cx_oracle-requires-method-to-force-use-of
I added a comment to this ticket.
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.
Re: [sqlalchemy] Inserting Chinese characters in Oracle database
On 2018-01-12 16:33, Mike Bayer wrote:> On Fri, Jan 12, 2018 at 7:14 AM, Stefan Schwarzer > wrote: > In SQLAlchemy 1.1 series and earlier, you can specify > exclude_setinputsizes=() to have STRING be part of the automatic > setinputsizes call. In SQLAlchemy 1.2 these features were all removed > as there was never any reason to pass most datatypes to setinputsizes. > in 1.2 you can still say > engine.dialect._include_setinputsizes.add(cx_Oracle.STRING) to re-add > it, but this should not be necessary (my test works with or without > it). > > Also which version of Python you're using matters, however I get a > round trip under both pythons. > > To round trip it, do this - note I had to expand the size of the > VARCHAR to fit your string, it was giving me a "data too large" error > before, so that might be a clue: > [...] I tried your example and got the same mismatch between original and actual value as before. Also your workaround that I tried for SQLAlchemy 1.2 didn't work for me. I haven't tried the workaround for SQLAlchemy 1.1.15. As described below, I (partially) found out why the workaround for version 1.2 didn't work. Since your code worked for you, but not for me, I concluded that probably the setup of your database is different from "mine" (I don't control the server). I searched for information on encodings in Oracle and found this document: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/supporting-multilingual-databases-with-unicode.html#GUID-AA09A60E-123E-457C-ACE1-89E4634E492C So far I had assumed that if I use `NVARCHAR2`, the column data would be "unicode" and the Oracle driver would do all the necessary conversions. However, with `SELECT * FROM V$NLS_PARAMETERS` I got NLS_CHARACTERSETWE8MSWIN1252 NLS_NCHAR_CHARACTERSET AL16UTF16 What are the values for your database? As far as I understand the above Oracle document, the first encoding, `NLS_CHARACTERSET`, is used for the names of SQL entity names like table and column names, and also for columns defined with `VARCHAR2`, `CLOB` etc. (character data types without the `N` prefix). The second encoding, `NLS_NCHAR_CHARACTERSET` is used for `NVARCHAR2`, `NCLOB` etc. (character data types with the `N` prefix). According to the document, Oracle nowadays recommends `AL32UTF8` for the database encoding and not using `NVARCHAR2` etc. for columns because `AL32UTF8` for `VARCHAR2` can generally encode unicode. I assume that I won't be able to have the above database encodings changed. In this case, what do you recommend when using the database with SQLAlchemy? As far as I understand, if I use `VARCHAR` or `VARCHAR2` (as other tables in the database so far), I won't be able to store anything that doesn't belong in CP1252. If I use `NVARCHAR2`, I _should_ be able to use Chinese characters. Why not? I changed your suggested code for SQLAlchemy 1.2 to include e.dialect._include_setinputsizes.add(cx_Oracle.STRING) e.dialect._include_setinputsizes.add(cx_Oracle.NCHAR) e.dialect._include_setinputsizes.add(cx_Oracle.NCLOB) but still inserting the Chinese characters failed. With some debugging, I noticed that in `engine.default.DefaultExecutionContext.set_input_sizes` before the call to `cursor.setinputsizes`, the `text` column has a corresponding `cx_Oracle.STRING` in the `inputsizes` dictionary. However, the type of `text` (an `NVARCHAR2` column) should be `cx_Oracle.NCHAR`. This is also what I get in the cursor description after the `SELECT` invocation before the `cursor.setinputsizes` in my working pure-cx_Oracle example in my original posting. If I change `cx_Oracle.STRING` to `cx_Oracle.NCHAR` in `DefaultExecutionContext.set_input_sizes` in the debugger, the Chinese characters appear in the database as expected. See also https://github.com/oracle/python-cx_Oracle/issues/119 If you don't have a suspicion why the wrong type for the `NVARCHAR2` column is used, I could try to do more debugging and hopefully find out why. What do you think? 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.
Re: [sqlalchemy] Inserting Chinese characters in Oracle database
Mike, many thanks for your feedback! On 2018-01-12 16:33, Mike Bayer wrote: > On Fri, Jan 12, 2018 at 7:14 AM, Stefan Schwarzer > wrote: >> 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. > > I can't reproduce your result, I'm not using any SQL tool I am instead > just getting the same data back and asserting it matches. I'm doing the same in the unit test. I saw different characters from what I had supposedly inserted and checked with the SQL tool to get a clue whether the insert or the select was the problem. > Per the > author of cx_Oracle the setinputsizes call is no longer needed in > most cases in the 6.x series, but also in the 1.x series, the > autosetinputsizes call for UNICODE and STRING was omitted because I > still got poorer results. Round-tripping of unicode characters is > widely tested with cx_Oracle / SQLAlchemy. > > In SQLAlchemy 1.1 series and earlier, you can specify > exclude_setinputsizes=() to have STRING be part of the automatic > setinputsizes call. At the moment, I'm using SQLAlchemy 1.1.15 at the moment due to the fractional-second problem (described in my other mail), but would like to switch to SQLAlchemy 1.2.x when the fractional-second problem is fixed there. > In SQLAlchemy 1.2 these features were all removed > as there was never any reason to pass most datatypes to setinputsizes. > in 1.2 you can still say > engine.dialect._include_setinputsizes.add(cx_Oracle.STRING) to re-add > it, but this should not be necessary (my test works with or without > it). > > Also which version of Python you're using matters, however I get a > round trip under both pythons. Sorry, I forgot to mention this. The Python version was 3.6.3, if I remember correctly. > To round trip it, do this - note I had to expand the size of the > VARCHAR to fit your string, it was giving me a "data too large" error > before, so that might be a clue: I'll try your script next week when I have access to the database system. 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.
[sqlalchemy] Inserting Chinese characters in Oracle database
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.
[sqlalchemy] Regression for sub-second precision when inserting timestamps into Oracle database
Hello,
I've run into a problem that's present in SQLAlchemy 1.2, but not 1.1.15
when run against an Oracle 11.2 database using cx_Oracle 6.1.
When creating the table `TIMESTAMP_TEST` with
CREATE TABLE TIMESTAMP_TEST (
TS TIMESTAMP(6)
)
and running the following code
import datetime
import sys
import sqlalchemy as sa
def test(user, password, host, port, database_name):
# Setup
metadata = sa.MetaData()
connect_string = "oracle+cx_oracle://{}:{}@{}:{}/{}".format(user,
password, host, port, database_name)
engine = sa.create_engine(connect_string)
timestamp_table = sa.Table("timestamp_test", metadata, sa.Column("ts",
sa.TIMESTAMP(timezone=False)))
# Insert
insert = timestamp_table.insert().values(ts=datetime.datetime.now())
print("insert:", insert)
print("insert params:", insert.compile().params)
with engine.connect() as connection:
connection.execute(insert)
# Get database connection details.
...
test(user, password, host, port, database_name)
fractional seconds *aren't* present in the inserted timestamp when the code
runs with SQLAlchemy 1.2. The fractional seconds *are* present when the
code runs against SQLAlchemy 1.1.15. In both cases the version of cx_Oracle
is 6.1. The behavior is the same when using `sa.dialects.oracle.TIMESTAMP`
instead of `sa.TIMESTAMP`.
Possibly this is to do with the changes described here:
https://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#dialect-improvements-and-changes-oracle
The documentation says that the `cursor.setinputsizes` calls were removed.
http://www.oracle.com/technetwork/articles/dsl/prez-python-timesanddates-093014.html
states that `cursor.setinputsizes` must be used to be able to insert
timestamps with sub-second precision into an Oracle database database with
cx_Oracle. Now, this article is pretty old, so I don't know how relevant
this still is.
Related tickets are possibly
https://bitbucket.org/zzzeek/sqlalchemy/issues/304/oracle-timestamp-with-sub-second
https://bitbucket.org/zzzeek/sqlalchemy/issues/604/oracle-sub-second-timestamp-handling
"Related" in the sense that they describe or refer to what presumably must
be done to get the sub-second precision upon insertion. Then again, these
tickets are old, but I wanted to mention them in case they help.
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.
