If I connect to my database directly using psycopg2 it works, However, if I
try with sqlalchemy it throws Programming error
Direct connection using psycopg2 works:
connection = psycopg2.connect(
sslmode='require',
host='query-prod-va7',
port='80',
dbname='all',
user='262850A15A7B8AB',
password='XXXX')
print(connection.encoding)
cursor = connection.cursor()
cursor.execute("SELECT * FROM segmentation_demo_profiles LIMIT 10;")
rows = cursor.fetchall()
cursor.close()
pprint.pprint(rows)
Over SQLAlchemy that fails
def getconn():
return psycopg2.connect(
sslmode='require',
host='query-prod-va7',
port='80',
dbname='all',
user='262850A15A7B8AB',
password='XXXXX')
engine = create_engine('postgresql+psycopg2://', creator=getconn)
engine.connect()
ProgrammingError: (psycopg2.ProgrammingError) sessionId:
adf707f6-096f-4dd2-be71-883ca7a71a23 ErrorCode: 42601 Syntax error
encountered. Reason: [Invalid command!]
(Background on this error at: http://sqlalche.me/e/f405)
Same issue if using a connection string. Attached stack trace.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--------------------------------------------------------------------------
ProgrammingError Traceback (most recent call last)
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in
_wrap_pool_connect(self, fn, connection)
2157 try:
-> 2158 return fn()
2159 except dialect.dbapi.Error as e:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in
unique_connection(self)
341 """
--> 342 return _ConnectionFairy._checkout(self)
343
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in _checkout(cls,
pool, threadconns, fairy)
787 if not fairy:
--> 788 fairy = _ConnectionRecord.checkout(pool)
789
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in checkout(cls, pool)
528 def checkout(cls, pool):
--> 529 rec = pool._do_get()
530 try:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in _do_get(self)
1192 with util.safe_reraise():
-> 1193 self._dec_overflow()
1194 else:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in
__exit__(self, type_, value, traceback)
65 if not self.warn_only:
---> 66 compat.reraise(exc_type, exc_value, exc_tb)
67 else:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp,
value, tb, cause)
248 raise value.with_traceback(tb)
--> 249 raise value
250
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in _do_get(self)
1189 try:
-> 1190 return self._create_connection()
1191 except:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in
_create_connection(self)
346
--> 347 return _ConnectionRecord(self)
348
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in __init__(self,
pool, connect)
473 if connect:
--> 474 self.__connect(first_connect_check=True)
475 self.finalize_callback = deque()
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in __connect(self,
first_connect_check)
680 for_modify(pool.dispatch).\
--> 681 exec_once(self.connection, self)
682 if pool.dispatch.connect:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/event/attr.py in
exec_once(self, *args, **kw)
273 try:
--> 274 self(*args, **kw)
275 finally:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/event/attr.py in
__call__(self, *args, **kw)
283 for fn in self.listeners:
--> 284 fn(*args, **kw)
285
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py in
on_connect(dbapi_connection, connection_record)
173 return
--> 174 do_on_connect(conn)
175
/opt/conda/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py
in on_connect(conn)
672 for fn in fns:
--> 673 fn(conn)
674 return on_connect
/opt/conda/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py
in on_connect(conn)
647 def on_connect(conn):
--> 648 hstore_oids = self._hstore_oids(conn)
649 if hstore_oids is not None:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in
oneshot(self, *args, **kw)
787 def oneshot(self, *args, **kw):
--> 788 result = fn(self, *args, **kw)
789 memo = lambda *a, **kw: result
/opt/conda/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py
in _hstore_oids(self, conn)
688 extras = self._psycopg2_extras()
--> 689 oids = extras.HstoreAdapter.get_oids(conn)
690 if oids is not None and oids[0]:
/opt/conda/lib/python3.6/site-packages/psycopg2/extras.py in get_oids(self,
conn_or_curs)
924 and not conn.autocommit):
--> 925 conn.rollback()
926
ProgrammingError: sessionId: adf707f6-096f-4dd2-be71-883ca7a71a23 ErrorCode:
42601 Syntax error encountered. Reason: [Invalid command!]
The above exception was the direct cause of the following exception:
ProgrammingError Traceback (most recent call last)
<ipython-input-34-d3a9428eff99> in <module>()
9
10 engine = create_engine('postgresql+psycopg2://', creator=getconn)
---> 11 engine.connect()
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in
connect(self, **kwargs)
2100 """
2101
-> 2102 return self._connection_cls(self, **kwargs)
2103
2104 def contextual_connect(self, close_with_result=False, **kwargs):
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in
__init__(self, engine, connection, close_with_result, _branch_from,
_execution_options, _dispatch, _has_events)
88 else:
89 self.__connection = connection \
---> 90 if connection is not None else engine.raw_connection()
91 self.__transaction = None
92 self.__savepoint_seq = 0
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in
raw_connection(self, _connection)
2186 """
2187 return self._wrap_pool_connect(
-> 2188 self.pool.unique_connection, _connection)
2189
2190
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in
_wrap_pool_connect(self, fn, connection)
2160 if connection is None:
2161 Connection._handle_dbapi_exception_noconnection(
-> 2162 e, dialect, self)
2163 else:
2164 util.reraise(*sys.exc_info())
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in
_handle_dbapi_exception_noconnection(cls, e, dialect, engine)
1474 util.raise_from_cause(
1475 sqlalchemy_exception,
-> 1476 exc_info
1477 )
1478 else:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/util/compat.py in
raise_from_cause(exception, exc_info)
263 exc_type, exc_value, exc_tb = exc_info
264 cause = exc_value if exc_value is not exception else None
--> 265 reraise(type(exception), exception, tb=exc_tb, cause=cause)
266
267 if py3k:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp,
value, tb, cause)
246 value.__cause__ = cause
247 if value.__traceback__ is not tb:
--> 248 raise value.with_traceback(tb)
249 raise value
250
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/base.py in
_wrap_pool_connect(self, fn, connection)
2156 dialect = self.dialect
2157 try:
-> 2158 return fn()
2159 except dialect.dbapi.Error as e:
2160 if connection is None:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in
unique_connection(self)
340
341 """
--> 342 return _ConnectionFairy._checkout(self)
343
344 def _create_connection(self):
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in _checkout(cls,
pool, threadconns, fairy)
786 def _checkout(cls, pool, threadconns=None, fairy=None):
787 if not fairy:
--> 788 fairy = _ConnectionRecord.checkout(pool)
789
790 fairy._pool = pool
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in checkout(cls, pool)
527 @classmethod
528 def checkout(cls, pool):
--> 529 rec = pool._do_get()
530 try:
531 dbapi_connection = rec.get_connection()
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in _do_get(self)
1191 except:
1192 with util.safe_reraise():
-> 1193 self._dec_overflow()
1194 else:
1195 return self._do_get()
/opt/conda/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in
__exit__(self, type_, value, traceback)
64 self._exc_info = None # remove potential circular
references
65 if not self.warn_only:
---> 66 compat.reraise(exc_type, exc_value, exc_tb)
67 else:
68 if not compat.py3k and self._exc_info and self._exc_info[1]:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp,
value, tb, cause)
247 if value.__traceback__ is not tb:
248 raise value.with_traceback(tb)
--> 249 raise value
250
251 else:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in _do_get(self)
1188 if self._inc_overflow():
1189 try:
-> 1190 return self._create_connection()
1191 except:
1192 with util.safe_reraise():
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in
_create_connection(self)
345 """Called by subclasses to create a new ConnectionRecord."""
346
--> 347 return _ConnectionRecord(self)
348
349 def _invalidate(self, connection, exception=None, _checkin=True):
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in __init__(self,
pool, connect)
472 self.__pool = pool
473 if connect:
--> 474 self.__connect(first_connect_check=True)
475 self.finalize_callback = deque()
476
/opt/conda/lib/python3.6/site-packages/sqlalchemy/pool.py in __connect(self,
first_connect_check)
679 pool.dispatch.first_connect.\
680 for_modify(pool.dispatch).\
--> 681 exec_once(self.connection, self)
682 if pool.dispatch.connect:
683 pool.dispatch.connect(self.connection, self)
/opt/conda/lib/python3.6/site-packages/sqlalchemy/event/attr.py in
exec_once(self, *args, **kw)
272 if not self._exec_once:
273 try:
--> 274 self(*args, **kw)
275 finally:
276 self._exec_once = True
/opt/conda/lib/python3.6/site-packages/sqlalchemy/event/attr.py in
__call__(self, *args, **kw)
282 fn(*args, **kw)
283 for fn in self.listeners:
--> 284 fn(*args, **kw)
285
286 def __len__(self):
/opt/conda/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py in
on_connect(dbapi_connection, connection_record)
172 if conn is None:
173 return
--> 174 do_on_connect(conn)
175
176 event.listen(pool, 'first_connect', on_connect)
/opt/conda/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py
in on_connect(conn)
671 def on_connect(conn):
672 for fn in fns:
--> 673 fn(conn)
674 return on_connect
675 else:
/opt/conda/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py
in on_connect(conn)
646 if self.dbapi and self.use_native_hstore:
647 def on_connect(conn):
--> 648 hstore_oids = self._hstore_oids(conn)
649 if hstore_oids is not None:
650 oid, array_oid = hstore_oids
/opt/conda/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py in
oneshot(self, *args, **kw)
786
787 def oneshot(self, *args, **kw):
--> 788 result = fn(self, *args, **kw)
789 memo = lambda *a, **kw: result
790 memo.__name__ = fn.__name__
/opt/conda/lib/python3.6/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py
in _hstore_oids(self, conn)
687 if self.psycopg2_version >=
self.FEATURE_VERSION_MAP['hstore_adapter']:
688 extras = self._psycopg2_extras()
--> 689 oids = extras.HstoreAdapter.get_oids(conn)
690 if oids is not None and oids[0]:
691 return oids[0:2]
/opt/conda/lib/python3.6/site-packages/psycopg2/extras.py in get_oids(self,
conn_or_curs)
923 if (conn_status != _ext.STATUS_IN_TRANSACTION
924 and not conn.autocommit):
--> 925 conn.rollback()
926
927 return tuple(rv0), tuple(rv1)
ProgrammingError: (psycopg2.ProgrammingError) sessionId:
adf707f6-096f-4dd2-be71-883ca7a71a23 ErrorCode: 42601 Syntax error encountered.
Reason: [Invalid command!]
(Background on this error at: http://sqlalche.me/e/f405)
