提高数据库查询速度与Python
编辑 - 我使用的是Windows 10提高数据库查询速度与Python
是否有快速的替代pd._read_sql_query的MS SQL数据库?
我在使用熊猫来读取数据并在数据上添加一些列和计算。我已经删除了大部分的修改,现在我基本上只是阅读(每天一百到二百万行;我的查询是读取前一天的所有数据)数据并将其保存到本地数据库(Postgres的)。
我连接的服务器遍布世界各地,除了查询数据外,我没有任何特权。如果可能,我希望解决方案保留在Python中。我想加快它,并消除任何开销。另外,您可以看到我正在将文件临时写入磁盘,然后将其打开到STDIN副本。有没有办法跳过文件创建?它有时超过500MB,这似乎是一种浪费。
engine = create_engine(engine_name)
query = 'SELECT * FROM {} WHERE row_date = %s;'
df = pd.read_sql_query(query.format(table_name), engine, params={query_date})
df.to_csv('../raw/temp_table.csv', index=False)
df= open('../raw/temp_table.csv')
process_file(conn=pg_engine, table_name=table_name, file_object=df)
UPDATE:
,你也可以尝试使用bcp utility,这可能会更快被很多比较pd.read_sql()
卸载数据,但你需要的Microsoft Command Line Utilities for SQL Server
本地安装之后,你可以用PostgreSQL的COPY ... FROM ...
...
OLD回答:
你可以尝试直接写您的DF到PostgreSQL(跳过df.to_csv(...)
和df= open('../raw/temp_table.csv')
部分):
from sqlalchemy import create_engine
engine = create_engine(engine_name)
query = 'SELECT * FROM {} WHERE row_date = %s;'
df = pd.read_sql_query(query.format(table_name), engine, params={query_date})
pg_engine = create_engine('postgresql+psycopg2://user:[email protected]:port/dbname')
df.to_sql(table_name, pg_engine, if_exists='append')
只是测试无论是更快相比COPY FROM STDIN
...
我曾经使用to_sql,但实际上它慢很多(即使创建一个文件并再次读取它的时间)。在你的代码中,你不会在任何地方使用df = pd.read_。没有必要,或者你只是把这部分放出去?我想我会需要它,因为我需要指定我查询的日期。 – trench
@trench,在这种情况下,我不认为你可以加快它的速度,因为'COPY ...'PostgreSQL命令专门用于加载/卸载数据,它已经非常优化。您可以尝试在加载前删除PostgreSQL表上的索引,并在加载完成后重新创建它 – MaxU
啊,是的,要清楚,我很满意COPY FROM速度。它工作得很快。我的原始查询(read_sql_query)与熊猫持续使用,这就是我希望取代的。另外,编写temporary.csv文件也需要很长时间。我认为花费大约1500秒左右的时间来查询数据并花费大量时间来编写500MB临时文件。我会看看你的bcp实用程序建议 – trench
什么是你的本地OS(你的PostgreSQL正在运行)? – MaxU
Windows 10,对不起。添加到OP – trench