Вопрос по python – Python-панды и базы данных, такие как MySQL

94

Документация для Pandas содержит множество примеров лучших практик для работы с данными, хранящимися в различных форматах.

Однако я не могу найти хороших примеров для работы с базами данных, например, MySQL.

Может кто-нибудь указать мне ссылки или дать некоторые фрагменты кода о том, как преобразовать результаты запроса с помощьюmysql-python к кадрам данных в Pandas эффективно?

Также взгляните наBlaze. osa
Если вы готовы тратить деньги, я полагаю, что в книге Уэса МакКинни («Python для анализа данных») есть несколько полезных примеров. MTrenfield
Смотрите также:stackoverflow.com/questions/15231646/… Mechanical snail
Документация:pandas.pydata.org/pandas-docs/stable/io.html#sql-queries Mechanical snail

Ваш Ответ

11   ответов
96

Как говорит Уэс, io / sql 's read_sql сделает это, как только вы получите соединение с базой данных, используя DBI-совместимую библиотеку. Мы можем посмотреть на два коротких примера, используяMySQLdb а такжеcx_Oracle библиотеки для подключения к Oracle и MySQL и запроса их словарей данных. Вот пример для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()
1

import the module

import pandas as pd
import oursql

connect

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

Это прекрасно работает и использует pandas.io.sql frame_works (с предупреждением об устаревании). База данных используется образец базы данных из учебника MySQL.

4

http://python-sybase.sourceforge.net)

import pandas.io.sql as psql
import Sybase

df = psql.frame_query("<Query>", con=Sybase.connect("<dsn>", "<user>", "<pwd>"))
19

SQLAlchemy, а затем сделать из него DataFrame.SQLAlchemy облегчает комбинированиеSQL условия Python, если вы собираетесь смешивать и сопоставлять вещи снова и снова.

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:[email protected]: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 >= 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)
8

Тот же синтаксис работает и для Ms SQL-сервера, использующего podbc.

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

у панд есть следующее предупреждение в ихдокументы для версии 14.0:

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.

А также:

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

Это делает многие ответы здесь устаревшими. Вы должны использоватьsqlalchemy:

from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('dialect://user:[email protected]:port/schema', echo=False)
f = pd.read_sql_query('SELECT * FROM mytable', engine, index_col = 'ID')
Можно ли передать запрос sqlalchemy (session.query, как в моем ответе ниже) непосредственно в метод pandas? Это будет потрошитель!
@idoda [в общем, это не тема вопроса, и лучше задать новый вопрос, чтобы у вас было больше мнений]. Вы уверены, что это не вопрос задержки запроса? Является ли отправка запроса и получение результатов значительно быстрее?
загрузка таблицы с 133 строками и 7 столбцами занимает около 30 секунд. Можете ли вы рассказать, почему это так?
@idoda Я имею в виду сравнение времени, которое требуется, чтобы выполнитьengine.execute("select * FROM mytable") со временем, необходимым для выполненияpd.read_sql_query('SELECT * FROM mytable', engine)
@Korem Я думал об открытии нового, но сначала хотел убедиться, что он не тривиальный. Когда я использую клиент mySql (Sequel pro) и запрашиваю базу данных, результаты поиска происходят намного быстрее. Когда вы говорите «просто отправляете, а затем извлекаете», вы это имеете в виду? (используя клиента)
23

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
Кстати, frame_query - это синоним read_frame ()
Вы можете указать столбец для использования в качестве индекса, указавindex_col='timestamp' вframe_query.
4

pandas.io.sql.frame_query устарела. использованиеpandas.read_sql вместо.

0

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

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)
frame_query сейчас устарела. Сейчас используюpd.read_sql(query, db) вместо.
5

И вот как вы подключаетесь к PostgreSQL с помощью драйвера psycopg2 (установите с помощью «apt-get install python-psycopg2», если вы работаете на производной ОС Debian Linux).

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)

Похожие вопросы