blocks|key|232237|text|正如Wes所说，一旦您使用DBI兼容库获得了数据库连接，io/sql的read_sql就可以做到这一点。我们可以看两个简短的示例，使用MySQLdb和cx_Oracle库连接到Oracle和MySQL并查询它们的数据字典。以下是cx_Oracle的示例|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|232238|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()|code-block|syntax|javascript|232239|下面是MySQLdb的等效示例|232240|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()|232241|entityMap^0|1V|7|23|9|36|9|0|0|3|7|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|P|$]]

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 <code>MySQLdb</code> and <code>cx_Oracle</code> libraries to connect to Oracle and MySQL and query their data dictionaries. Here is the example for <code>cx_Oracle</code>:

<pre><code>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()
</code></pre>

And here is the equivalent example for <code>MySQLdb</code>:

<pre><code>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()
</code></pre>

blocks|key|2892803|text|同样的语法也适用于使用podbc的Ms+SQL+server。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2892804|import+pyodbc
import+pandas.io.sql+as+psql

cnxn+=+pyodbc.connect('DRIVER={SQL+Server};SERVER=servername;DATABASE=mydb;UID=username;PWD=password')+
cursor+=+cnxn.cursor()
sql+=+("""select+*+from+mytable""")

df+=+psql.frame_query(sql,+cnxn)
cnxn.close()|code-block|syntax|javascript|2892805|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The same syntax works for Ms SQL server using podbc also. 

<pre><code>import pyodbc
import pandas.io.sql as psql

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=servername;DATABASE=mydb;UID=username;PWD=password') 
cursor = cnxn.cursor()
sql = ("""select * from mytable""")

df = psql.frame_query(sql, cnxn)
cnxn.close()
</code></pre>

blocks|key|229381|text|对于Sybase，下面的工作(使用http://python-sybase.sourceforge.net)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|229382|import+pandas.io.sql+as+psql
import+Sybase

df+=+psql.frame_query("<Query>",+con=Sybase.connect("<dsn>",+"<user>",+"<pwd>"))|code-block|syntax|javascript|229383|entityMap|0|LINK|mutability|MUTABLE|url|http://python-sybase.sourceforge.net/^0|H|10|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

For Sybase the following works (with <a href="http://python-sybase.sourceforge.net" rel="nofollow">http://python-sybase.sourceforge.net</a>)

<pre><code>import pandas.io.sql as psql
import Sybase

df = psql.frame_query("&lt;Query&gt;", con=Sybase.connect("&lt;dsn&gt;", "&lt;user&gt;", "&lt;pwd&gt;"))
</code></pre>

blocks|key|232263|text|对于记录，下面是一个使用sqlite数据库的示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|232264|import+pandas+as+pd
import+sqlite3

with+sqlite3.connect("whatever.sqlite")+as+con:
++++sql+=+"SELECT+*+FROM+table_name"
++++df+=+pd.read_sql_query(sql,+con)
++++print+df.shape|code-block|syntax|javascript|232265|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For the record, here is an example using a sqlite database:

<pre><code>import pandas as pd
import sqlite3

with sqlite3.connect("whatever.sqlite") as con:
 sql = "SELECT * FROM table_name"
 df = pd.read_sql_query(sql, con)
 print df.shape
</code></pre>

blocks|key|233288|text|这就是如何使用psycopg2驱动程序连接到PostgreSQL+(如果您使用的是Debian+Linux派生操作系统，则使用"apt-get+install+python-install+copg2“进行安装)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|233289|import+pandas.io.sql+as+psql
import+psycopg2

conn+=+psycopg2.connect("dbname='datawarehouse'+user='user1'+host='localhost'+password='uberdba'")

q+=+"""select+month_idx,+sum(payment)+from+bi_some_table"""

df3+=+psql.frame_query(q,+conn)|code-block|syntax|javascript|233290|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

And this is how you connect to PostgreSQL using psycopg2 driver (install with "apt-get install python-psycopg2" if you're on Debian Linux derivative OS).

<pre><code>import pandas.io.sql as psql
import psycopg2

conn = psycopg2.connect("dbname='datawarehouse' user='user1' host='localhost' password='uberdba'")

q = """select month_idx, sum(payment) from bi_some_table"""

df3 = psql.frame_query(q, conn)
</code></pre>

blocks|key|3106756|text|MySQL示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3106757|import+MySQLdb+as+db
from+pandas+import+DataFrame
from+pandas.io.sql+import+frame_query

database+=+db.connect('localhost','username','password','database')
data+++++=+frame_query("SELECT+*+FROM+data",+database)|code-block|syntax|javascript|3106758|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

MySQL example:

<pre><code>import MySQLdb as db
from pandas import DataFrame
from pandas.io.sql import frame_query

database = db.connect('localhost','username','password','database')
data = frame_query("SELECT * FROM data", database)
</code></pre>

blocks|key|2892892|text|我更喜欢用创建查询，然后用它创建一个DataFrame。如果您打算一遍又一遍地混合和匹配，SQLAlchemy使您可以更轻松地组合SQL条件。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2892893|from+sqlalchemy.ext.declarative+import+declarative_base
from+sqlalchemy+import+Table
from+sqlalchemy+import+create_engine
from+sqlalchemy.orm+import+sessionmaker
from+pandas+import+DataFrame
import+datetime

#+We+are+connecting+to+an+existing+service
engine+=+create_engine('dialect://user:pwd@host:port/db',+echo=False)
Session+=+sessionmaker(bind=engine)
session+=+Session()
Base+=+declarative_base()

#+And+we+want+to+query+an+existing+table
tablename+=+Table('tablename',+
++++Base.metadata,+
++++autoload=True,+
++++autoload_with=engine,+
++++schema='ownername')

#+These+are+the+"Where"+parameters,+but+I+could+as+easily+
#+create+joins+and+limit+results
us+=+tablename.c.country_code.in_(['US','MX'])
dc+=+tablename.c.locn_name.like('%25DC%25')
dt+=+tablename.c.arr_date+>=+datetime.date.today()+#+Give+me+convenience+or...

q+=+session.query(tablename).\
++++++++++++filter(us+&+dc+&+dt)+#+That's+where+the+magic+happens!!!

def+querydb(query):
++++"""
++++Function+to+execute+query+and+return+DataFrame.
++++"""
++++df+=+DataFrame(query.all());
++++df.columns+=+[x['name']+for+x+in+query.column_descriptions]
++++return+df

querydb(q)|code-block|syntax|javascript|2892894|entityMap^0|19|A|1T|3|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

I prefer to create queries with <a href="http://www.sqlalchemy.org/">SQLAlchemy</a>, and then make a DataFrame from it. SQLAlchemy makes it easier to combine SQL conditions Pythonically if you intend to mix and match things over and over.

<pre><code>from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from pandas import DataFrame
import datetime

# We are connecting to an existing service
engine = create_engine('dialect://user:pwd@host:port/db', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()

# And we want to query an existing table
tablename = Table('tablename', 
 Base.metadata, 
 autoload=True, 
 autoload_with=engine, 
 schema='ownername')

# These are the "Where" parameters, but I could as easily 
# create joins and limit results
us = tablename.c.country_code.in_(['US','MX'])
dc = tablename.c.locn_name.like('%DC%')
dt = tablename.c.arr_date &gt;= datetime.date.today() # Give me convenience or...

q = session.query(tablename).\
 filter(us &amp; dc &amp; dt) # That's where the magic happens!!!

def querydb(query):
 """
 Function to execute query and return DataFrame.
 """
 df = DataFrame(query.all());
 df.columns = [x['name'] for x in query.column_descriptions]
 return df

querydb(q)
</code></pre>

blocks|key|3106800|text|对于这个问题的最近读者:熊猫在它们的docs+for+version+14.0中有以下警告|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3106801|3106802|警告:某些现有函数或函数别名已弃用，将在未来版本中删除。这包括:+tquery，uquery，read_frame，frame_query，write_frame。|blockquote|3106803|3106804|3106805|和：|3106806|3106807|警告:已弃用使用DBAPI连接对象时对‘mysql’风格的支持。SQLAlchemy+engines+(GH6900)将进一步支持MySQL。|3106808|3106809|3106810|这使得这里的许多答案都过时了。您应该使用sqlalchemy|style|CODE|3106811|from+sqlalchemy+import+create_engine
import+pandas+as+pd
engine+=+create_engine('dialect://user:pass@host:port/schema',+echo=False)
f+=+pd.read_sql_query('SELECT+*+FROM+mytable',+engine,+index_col+=+'ID')|code-block|syntax|javascript|3106812|entityMap|0|LINK|mutability|MUTABLE|url|http://pandas.pydata.org/pandas-docs/stable/whatsnew.html^0|I|L|0|0|0|0|0|0|0|0|0|0|0|K|A|0|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@$A|18|B|19|1|1A]]|C|$]]|$1|D|3|-4|5|6|7|1B|8|@]|9|@]|C|$]]|$1|E|3|F|5|G|7|1C|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|1D|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|1E|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@]|C|$]]|$1|L|3|-4|5|6|7|1G|8|@]|9|@]|C|$]]|$1|M|3|N|5|G|7|1H|8|@]|9|@]|C|$]]|$1|O|3|-4|5|6|7|1I|8|@]|9|@]|C|$]]|$1|P|3|-4|5|6|7|1J|8|@]|9|@]|C|$]]|$1|Q|3|R|5|6|7|1K|8|@$A|1L|B|1M|S|T]]|9|@]|C|$]]|$1|U|3|V|5|W|7|1N|8|@]|9|@]|C|$X|Y]]|$1|Z|3|-4|5|6|7|1O|8|@]|9|@]|C|$]]]|10|$11|$5|12|13|14|C|$15|16]]]]

For recent readers of this question: pandas have the following warning in their <a href="http://pandas.pydata.org/pandas-docs/stable/whatsnew.html" rel="noreferrer">docs for version 14.0</a>:

<blockquote>
 Warning: Some of the existing functions or function aliases have been
 deprecated and will be removed in future versions. This includes:
 tquery, uquery, read_frame, frame_query, write_frame. 
</blockquote>

And:

<blockquote>
 Warning: The support for the ‘mysql’ flavor when using DBAPI connection objects has
 been deprecated. MySQL will be further supported with SQLAlchemy
 engines (GH6900).
</blockquote>

This makes many of the answers here outdated. You should use <code>sqlalchemy</code>:

<pre><code>from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('dialect://user:pass@host:port/schema', echo=False)
f = pd.read_sql_query('SELECT * FROM mytable', engine, index_col = 'ID')
</code></pre>

blocks|key|233312|text|pandas.io.sql.frame_query已弃用。请改用pandas.read_sql。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|233313|entityMap^0|0|P|W|F|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

<code>pandas.io.sql.frame_query</code> is deprecated. Use <code>pandas.read_sql</code> instead.

blocks|key|229428|text|导入模块|type|unstyled|depth|inlineStyleRanges|entityRanges|data|229429|import+pandas+as+pd
import+oursql|code-block|syntax|javascript|229430|连接|229431|conn=oursql.connect(host="localhost",user="me",passwd="mypassword",db="classicmodels")
sql="Select+customerName,+city,country+from+customers+order+by+customerName,country,city"
df_mysql+=+pd.read_sql(sql,conn)
print+df_mysql|229432|这在使用pandas.io.sql+frame_works+(带有弃用警告)的情况下工作得很好。使用的数据库是mysql教程中的示例数据库。|229433|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

<h2>import the module</h2>

<pre><code>import pandas as pd
import oursql
</code></pre>

<h2>connect</h2>

<pre><code>conn=oursql.connect(host="localhost",user="me",passwd="mypassword",db="classicmodels")
sql="Select customerName, city,country from customers order by customerName,country,city"
df_mysql = pd.read_sql(sql,conn)
print df_mysql
</code></pre>

That works just fine and using pandas.io.sql frame_works (with the deprecation warning). Database used is the sample database from mysql tutorial.

blocks|key|3106866|text|这应该可以很好地工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3106867|import+MySQLdb+as+mdb
import+pandas+as+pd
con+=+mdb.connect(‘127.0.0.1’,+‘root’,+‘password’,+‘database_name’);
with+con:
+cur+=+con.cursor()
+cur.execute(“select+random_number_one,+random_number_two,+random_number_three+from+randomness.a_random_table”)
+rows+=+cur.fetchall()
+df+=+pd.DataFrame(+[[ij+for+ij+in+i]+for+i+in+rows]+)
+df.rename(columns={0:+‘Random+Number+One’,+1:+‘Random+Number+Two’,+2:+‘Random+Number+Three’},+inplace=True);
+print(df.head(20))|code-block|syntax|javascript|3106868|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This should work just fine.

<pre><code>import MySQLdb as mdb
import pandas as pd
con = mdb.connect(‘127.0.0.1’, ‘root’, ‘password’, ‘database_name’);
with con:
 cur = con.cursor()
 cur.execute(“select random_number_one, random_number_two, random_number_three from randomness.a_random_table”)
 rows = cur.fetchall()
 df = pd.DataFrame( [[ij for ij in i] for i in rows] )
 df.rename(columns={0: ‘Random Number One’, 1: ‘Random Number Two’, 2: ‘Random Number Three’}, inplace=True);
 print(df.head(20))
</code></pre>

blocks|key|3106892|text|这对我从Python3.x基于的lambda函数连接到AWS+MYSQL(RDS)并加载到pandas+DataFrame中很有帮助。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3106893|import+json
import+boto3
import+pymysql
import+pandas+as+pd
user+=+'username'
password+=+'XXXXXXX'
client+=+boto3.client('rds')
def+lambda_handler(event,+context):
++++conn+=+pymysql.connect(host='xxx.xxxxus-west-2.rds.amazonaws.com',+port=3306,+user=user,+passwd=password,+db='database+name',+connect_timeout=5)
++++df=+pd.read_sql('select+*+from+TableName+limit+10',con=conn)
++++print(df)
++++#+TODO+implement
++++#return+{
++++#++++'statusCode':+200,
++++#++++'df':+df
++++#}|code-block|syntax|javascript|3106894|entityMap^0|4|B|G|8|R|E|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

This helped for me for connecting to AWS MYSQL(RDS) from python 3.x based lambda function and loading into a pandas DataFrame

<pre><code>import json
import boto3
import pymysql
import pandas as pd
user = 'username'
password = 'XXXXXXX'
client = boto3.client('rds')
def lambda_handler(event, context):
 conn = pymysql.connect(host='xxx.xxxxus-west-2.rds.amazonaws.com', port=3306, user=user, passwd=password, db='database name', connect_timeout=5)
 df= pd.read_sql('select * from TableName limit 10',con=conn)
 print(df)
 # TODO implement
 #return {
 # 'statusCode': 200,
 # 'df': df
 #}
</code></pre>

blocks|key|233376|text|适用于Postgres用户|type|unstyled|depth|inlineStyleRanges|entityRanges|data|233377|import+psycopg2
import+pandas+as+pd

conn+=+psycopg2.connect("database='datawarehouse'+user='user1'+host='localhost'+password='uberdba'")

customers+=+'select+*+from+customers'

customers_df+=+pd.read_sql(customers,conn)

customers_df|code-block|syntax|javascript|233378|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For Postgres users

<pre><code>import psycopg2
import pandas as pd

conn = psycopg2.connect("database='datawarehouse' user='user1' host='localhost' password='uberdba'")

customers = 'select * from customers'

customers_df = pd.read_sql(customers,conn)

customers_df
</code></pre>

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 ?

python-pandas and databases like mysql

SQL Server

PostgreSQL

数据库

Debian

MySQL

Linux

Pandas的文档中有许多处理以各种格式存储的数据的最佳实践示例。但是，我找不到任何使用数据库的好例子，比如MySQL。谁能给我一些链接，或者给我一些代码片段，告诉我如何使用mysql-python将查询结果有效地转换为熊猫中的数据帧？

问python-pandas和mysql等数据库
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问python-pandas和mysql等数据库EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问python-pandas和mysql等数据库
EN