我在MySQL中有一张表,包含以下列:
id int(11)
contract_id int(11)
datetime datetime
open decimal(18, 10)
high decimal(18, 10)
low decimal(18, 10)
close decimal(18, 10)
表格相当大(> 3亿行),但在数据库内执行的查询即使返回300,000行也仅需半秒钟。然而,当我从Python检索数据时速度非常慢(同一请求在MySQL Workbench中为0.5秒,在Python中为34秒):
import pandas as pd
import mysql.connector
con = mysql.connector.connect(**CONFIG)
cur = con.cursor()
def get_data1():
df = pd.read_sql(
"""
SELECT datetime, open, high, low, close
FROM prices
WHERE contract_id = 1
AND datetime >= '2015-01-01 09:00:00'
AND datetime <= '2015-10-15 16:00:00';
""", con)
return df
我发现将数据从MySQL导出到平面文件,然后在Python中读取,比直接查询数据库快23倍:
def get_data2():
cur.execute(
"""
SELECT datetime, open, high, low, close
FROM prices
WHERE contract_id = 1
AND datetime >= '2015-01-01 09:00:00'
AND datetime <= '2015-10-15 16:00:00'
INTO OUTFILE 'C:/Data/Temp.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY "\n";
""")
return pd.read_csv('C:/Data/Temp.csv')
怎么会这样?我怀疑这与数据类型转换有关。你有什么办法可以使函数get_data1
更快,而不必先导出为CSV文件吗?谢谢。
pd.read_sql
...你尝试过使用SQLAlchemy和新的函数吗?http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html - measureallthethings