pythonpandas数据库_python-pandas和类似mysql的数据库

The documentation for Pandas has numerous examples of best practices for working with data stored in various formats.

However, I am unable to find any good examples for working with databases like MySQL for example.

Can anyone point me to links or give some code snippets of how to convert query results using mysql-python to data frames in Pandas efficiently ?

解决方案

As Wes says, io/sql's read_sql will do it, once you've gotten a database connection using a DBI compatible library. We can look at two short examples using the MySQLdb and cx_Oracle libraries to connect to Oracle and MySQL and query their data dictionaries. Here is the example for cx_Oracle:

import pandas as pd

import cx_Oracle

ora_conn = cx_Oracle.connect('your_connection_string')

df_ora = pd.read_sql('select * from user_objects', con=ora_conn)

print 'loaded dataframe from Oracle. # Records: ', len(df_ora)

ora_conn.close()

And here is the equivalent example for MySQLdb:

import MySQLdb

mysql_cn= MySQLdb.connect(host='myhost',

port=3306,user='myusername', passwd='mypassword',

db='information_schema')

df_mysql = pd.read_sql('select * from VIEWS;', con=mysql_cn)

print 'loaded dataframe from MySQL. records:', len(df_mysql)

mysql_cn.close()