On Sun, Dec 2, 2018 at 2:35 PM Nirmal .M <[email protected]> wrote: > > engine = create_engine('postgresql+psycopg2://', creator=getconn, > use_native_hstore=False, isolation_level="AUTOCOMMIT") > > I am assuming it's trying to do get version in this settings end up > OperationErrors since version doesnt seem to be supported from postgres > server side. Attached full response. Its consistent with the errors I am > getting when doing direct psql commands command line as well. > > all=> SHOW server_version_num; > ERROR: sessionId: f52a8b98-a551-4d4b-ba98-43b3586ff54d ErrorCode: 42601 > Syntax error encountered. Reason: [Invalid command!] > all=> SELECT version(); > ERROR: ErrorCode: 08P01 sessionId: f52a8b98-a551-4d4b-ba98-43b3586ff54d > queryId: 83ef0b3f-4910-4f2a-9139-6fb2cf4ed858 Unknown error encountered. > Reason: [Undefined function: 'version'. This function is neither a registered > temporary function nor a permanent function registered in the database > 'default'.; line 1 pos 7] > > Irrespective of hstore/version issues, Would I be able to use psycopg2 > connection directly with sqlalchemy, since queries against connection on > driver directly seem to be working fine?
unfortunately, this database is not Postgresql and I know nothing about it. I can't support a database that is an in-house proprietary database that's not publicly available, you should likely have it modified to better support standard Postgresql functions such as version(), or alternatively you can make your own third party SQLAlchemy dialect to support this database. For an example of how to do this for a Postgresql variant, review how sqlalchemy-redshift is organized: https://github.com/sqlalchemy-redshift/sqlalchemy-redshift > > > > On Sun, Dec 2, 2018 at 8:34 AM Mike Bayer <[email protected]> wrote: >> >> 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. > > -- > 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.
