9

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'

tschm
  • 2,905
  • 6
  • 33
  • 45
  • Related question [python-pandas and databases like mysql](http://stackoverflow.com/questions/10065051/python-pandas-and-databases-like-mysql) – van Dec 06 '13 at 07:34

3 Answers3

19

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()
van
  • 74,297
  • 13
  • 168
  • 171
  • 11
    Just an update that (I think starting with pandas version 0.14) you can now use an engine directly: `xx = sql.read_sql("SELECT * FROM user", engine)`. Check out the [docs](http://pandas.pydata.org/pandas-docs/dev/generated/pandas.io.sql.read_sql.html). – Midnighter Nov 15 '14 at 12:35
  • 2
    @Midnighter: indeed and this is the way I use it now most of the time – van Jan 12 '15 at 07:25
1

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.

Dan Allan
  • 34,073
  • 6
  • 70
  • 63
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)

Idodo
  • 1,322
  • 11
  • 18