On Sun, Dec 2, 2018 at 11:03 AM Nirmal .M <[email protected]> wrote: > > Hi Mike, > > We use a custom postgres server impl that wraps up data warehouse. The use > case I am trying is to connect it using Jupyter with ipython-sql(which > leverages sqlalchemy).
ah well that is your issue, your PG database is probably not fully compatible. The psycopg2 driver itself is what's failing here when SQLAlchemy is asking it to get the HSTORE oids using a public psycopg2 method. What is slightly concerning is that I've looked at what psycopg2 does for this, and there is no syntactical issue with the SQL it's emitting; the stack trace you have here is instead emitting when psycopg2 is calling the rollback() method. If your custom PG database returns an error when psycopg2 tries to emit a ROLLBACK, then you should set psycopg2 autocommit to prevent this from happening. I notice in your pure psycopg test case, you aren't calling connection.rollback(). Try that out, if it fails, then those have to be disabled. So to just turn off the HSTORE check, you can set use_native_hstore=False: https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#id5 if OTOH the issue is psycopg2's implementation of rollback() is not accepted by your custom database, which is more what this looks like, you should set psycopg2 autocommit: https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#transaction-isolation-level create_engine("postgresql+psycopg2://...", isolation_level="AUTOCOMMIT") > > Here are the versions: > psycopg2 - 2.7.6.1 > sqlalchemy - 1.2.12 > > Direct connection using psycopg2 driver works fine. > > -Nirmal > > > > On Sun, Dec 2, 2018 at 5:00 AM Mike Bayer <[email protected]> wrote: >> >> Hi - >> >> Need to know exact version of postgresql server in use as well as that of >> psycopg2. Problem replicates with most up to date version of psycopg2 as >> well? >> >> >> >> On Sun, Dec 2, 2018, 1:57 AM Nirmal .M <[email protected] wrote: >>> >>> 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. >> >> -- >> 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. > > -- > 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. -- 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.
