python-pandas和类似mysql的数据库 [英] python-pandas and databases like mysql

问题描述

Pandas的文档提供了许多使用各种格式存储的最佳实践示例.

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

但是，我找不到用于处理像MySQL这样的数据库的良好示例.

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

任何人都可以指向我指向链接或提供一些代码片段，以介绍如何使用 mysql-python 将查询结果有效地转换为Pandas中的数据帧吗?

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 ?

推荐答案

正如Wes所说，一旦使用DBI兼容库建立了数据库连接，io/sql的read_sql就可以做到.我们可以看两个使用MySQLdb和cx_Oracle库连接到Oracle和MySQL并查询其数据字典的简短示例.这是cx_Oracle的示例:

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()

这是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()

这篇关于python-pandas和类似mysql的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！