tldr
# example borked
engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=lambda: 'asdf')
# example works
engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=lambda: conn)
我认为这可能是以下之一的错误:
examples
具体来说,可以从以下位置引发错误:
psycopg2.
_ipaddress.py
_json.py
_psycopg.cpython-37m-darwin.so
_range.py
extensions.py
extras.py
就我而言:
_ext.register_type(_ext.UUID, conn_or_curs)
显然这就是它的作用:
- https://github.com/psycopg/psycopg2/blob/master/doc/src/extensions.rst#database-types-casting-functions https://github.com/psycopg/psycopg2/blob/master/doc/src/extensions.rst#database-types-casting-functions
在我的例子中导致问题的代码是create_engine
关键词creator=get_connection
在以下示例中:
from psycopg2.pool import ThreadedConnectionPool
from contextlib import contextmanager
import sqlalchemy
conn_string = "host='127.0.0.1' dbname='postgres' user='someuser' password='somepassword'"
top = ThreadedConnectionPool(1, 250, conn_string)
@contextmanager
def get_connection():
try:
connection = top.getconn()
yield connection
finally:
top.putconn(connection)
@contextmanager
def get_cursor(commit=False):
with get_connection() as connection:
cursor = connection.cursor(
cursor_factory=psycopg2.extras.RealDictCursor)
try:
yield cursor
if commit:
connection.commit()
finally:
cursor.close()
engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=get_connection)
@contextmanager
def get_sqlalchemy_engine():
yield engine.connect()
导致问题的原因:
with get_sqlalchemy_engine() as engine:
pd.DataFrame([1]).to_sql('asdf', engine, if_exists='replace')
以及解决问题的方法:
@contextmanager
def get_sqlalchemy_engine():
with get_connection() as conn:
try:
engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=lambda: conn)
# engine = sqlalchemy.create_engine('postgresql+psycopg2://', creator=lambda: 'asdf')
yield engine
finally:
engine.dispose()
进一步的研究表明:
JSON = new_type((oid, ), name, typecast_json)
if array_oid is not None:
JSONARRAY = new_array_type((array_oid, ), "%sARRAY" % name, JSON)
else:
JSONARRAY = None
return JSON, JSONARRAY
基本上conn_or_curs
不是连接或光标,而是其他东西,
register_type(JSON, not globally and conn_or_curs or None)
register_type(JSONARRAY, not globally and conn_or_curs or None)
- https://github.com/psycopg/psycopg2/blob/f947c0e6be1d2c3ea8d2d8badf683b95bd213444/psycopg/psycopgmodule.c#L189 https://github.com/psycopg/psycopg2/blob/f947c0e6be1d2c3ea8d2d8badf683b95bd213444/psycopg/psycopgmodule.c#L189
- https://github.com/psycopg/psycopg2/blob/f947c0e6be1d2c3ea8d2d8badf683b95bd213444/psycopg/psycopgmodule.c#L260 https://github.com/psycopg/psycopg2/blob/f947c0e6be1d2c3ea8d2d8badf683b95bd213444/psycopg/psycopgmodule.c#L260