read_sql fails with psycopg2 connection

[amelio-vazquez-reina]

I am unable to run read_sql against a PostgreSQL database with the current master branch. This error only happens with master. If I switch back to pandas 0.13.1 (stable) everything works again.

For reference, I am using psycopg2 2.5.2 from pip install.

Below is some example code:

# Connect to the database
host       = 'some.remote.host.net'
database   = 'some_remote_database'
port       = '54321'
username   = 'josh'
my_query   = 'select item_uid from db.items as item\nwhere item.end_date > (current_date + 1);'

connection   = pg.connect(host=host, dbname=database, port=port, user=username)
my_dataframe = psql.read_sql(my_query, connection)

The error that I get is:

DatabaseError: Execution failed on sql: SELECT name FROM sqlite_master WHERE type='table' 
AND name='select item_uid from db.items as item\nwhere item.end_date > (current_date + 1);'

I get the same error with read_frame

[jorisvandenbossche]

This is partly deliberate, and partly a bug:

• postgresql was never 'officially' supported (but it worked for reading), but in master the sql submodule is being refactored to use sqlalchemy to support in this way all database flavors that sqlalchemy supports (mysql, postgresql, oracle, ...). To use this, you can feed a sqlalchemy engine instead of a connection to the function and it will work:

  engine = sqlalchemy.create_engine('postgresql://josh@some.remote.host.net:54321/some_remote_database')
  pd.read_sql(my_query, engine)
  

  It is recommended to use sqlalchemy if you want to work with postgresql databases, as other functions (to_sql/read_sql_table) will only work using sqlalchemy engines.

• This is also partly a bug in remaining the backwards compatibility for read_sql. If you use pd.read_sql_query(my_query, connection) this will still work. As I said, postgresql was not officially supported, but did work because read_sql had nothing database flavor specific in its implementation. In the new implementation, there is a small part flavor specific (to see if you have a query or a table), and I think this should be adapted to make this flavor-agnostic to remain backwards compatibility (although it is not recommended to use postgresql without sqlachemy).

[amelio-vazquez-reina]

Thanks @jorisvandenbossche. Regarding your first point, shouldn't the code be:

engine = sqlalchemy.create_engine('postgresql://josh@some.remote.host.net:54321/some_remote_database')
connection = engine.raw_connection()
pd.read_sql(my_query, connection)

?

Otherwise I get the error:

AttributeError: 'Engine' object has no attribute 'rollback'

This answer on SO seems to suggest precisely that.

[jorisvandenbossche]

No that should not be the case, so that is a bug (you should feed it the engine).
You are sure you are using master?

Can you give a more detailed error message?

The answer of stack overflow is correct for current stable version (0.13.1 and below), but not anymore for master.

[amelio-vazquez-reina]

@jorisvandenbossche You are right. I just confirmed this. Passing an engine object works perfectly well on master.

[jorisvandenbossche]

OK, nevertheless, thanks for reporting!
If there are other issues you encounter, let us know!

[jorisvandenbossche]

I am going to leave this open for now, as the part bug should be solved (backwards incompatibility). Normally will be solved when I get all old tests running.

[jorisvandenbossche]

Will be fixed in #6987