read frame with sqlalchemy, mysql and pandas

Question

I am trying to connect to a mysql database, works fine with Option 1:

from sqlalchemy import create_engine
engine = create_engine('mysql://root:root@localhost/lend', echo=True)
cnx = engine.connect()
x = cnx.execute("SELECT * FROM user")

but breaks down here:

from pandas.io import sql
xx = sql.read_frame("SELECT * FROM user", cnx)
cnx.close()

with

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

Answer 1

This is an old question but still relevant apparently. So past 2018 the way to solve this is simply use the engine directly:

xx = sql.read_sql("SELECT * FROM user", engine)

(originally posted by Midnighter in a comment)

Answer 2

You need to have a raw database connection, and not an instance of Connection. In order to get it call either engine.raw_connection() or engine.connect().connection:

from pandas.io import sql
#cnx = engine.connect().connection # option-1
cnx = engine.raw_connection() # option-2
xx = sql.read_frame("SELECT * FROM user", cnx)
cnx.close()

Answer 3

Use the MySQLdb module to create the connection. There is ongoing progress toward better SQL support, including sqlalchemy, but it's not ready yet.

If you are comfortable installing the development version of pandas, you might want to keep an eye on that linked issue and switch to using the development version of pandas as soon as it is merged. While pandas' SQL support is usable, there are some bugs around data types, missing values, etc., that are likely to come up if you use Pandas + SQL extensively.