python - 将大表读入 Pandas，有中间步骤吗？

Question

我正在编写一个数据分析脚本。此脚本连接到 Teradata，从表中Select * 并将其加载到 pandas 数据帧中。

import teradata
import pandas as pd

with udaExec.connect(method="xxx", dsn="xxx", username="xxx", password="xxx") as session:

    query = "Select * from TableA"

    # read in records
    df = pd.read_sql(query, session)

    # misc pandas tests below...

这对于具有 100k 或更少记录的表非常有效，但问题是许多表的记录远多于此(数百万条记录)，而且它往往会无限期地运行。

我可以采取一些中间步骤吗？我一直在研究，我看到一些关于首先将数据库表复制到 .csv 文件或 .txt 文件或其他文件，然后从中加载 pandas 数据框(而不是从表本身加载)，但我不能理解它。

如有任何建议，我们将不胜感激!谢谢。

Answer 1

在评论中，我 promise 提供一些代码，可以将服务器上的表快速读取到本地 CSV 文件中，然后将该 CSV 文件读取到 Pandas 数据框中。请注意，此代码是为 postgresql 编写的，但您可能可以轻松地将其改编为其他数据库。

这是代码:

from cStringIO import StringIO
import psycopg2
import psycopg2.sql as sql
import pandas as pd

database = 'my_db'
pg_host = 'my_postgres_server'
table = 'my_table'
# note: you should also create a ~/.pgpass file with the credentials needed to access
# this server, e.g., a line like "*:*:*:username:password" (if you only access one server)

con = psycopg2.connect(database=database, host=pg_host)
cur = con.cursor()    

# Copy data from the database to a dataframe, using psycopg2 .copy_expert() function.
csv = StringIO()  # or tempfile.SpooledTemporaryFile()
# The next line is the right way to insert a table name into a query, but it requires 
# psycopg2 >= 2.7. See here for more details: https://stackoverflow.com/q/13793399/3830997
copy_query = sql.SQL("COPY {} TO STDOUT WITH CSV HEADER").format(sql.Identifier(table))
cur.copy_expert(copy_query, csv)
csv.seek(0)  # move back to start of csv data
df = pd.read_csv(csv)

这里还有一些通过 CSV 路径将大型数据帧写入数据库的代码:

csv = StringIO()
df.to_csv(csv, index=False, header=False)
csv.seek(0)
try:
    cur.copy_from(csv, table, sep=',', null='\\N', size=8192, columns=list(df.columns))
    con.commit()
except:
    con.rollback()
    raise

我通过 10 Mbps 办公网络(不要问!)使用 70,000 行表(5.3 MB 作为 CSV)测试了此代码。

当从数据库读取表时，我发现上面的代码比 pandas.read_sql() 快了大约 1/3(5.5 秒 vs. 8 秒)。我不确定这是否能证明在大多数情况下额外的复杂性是合理的。这可能是你能得到的最快的速度了——postgresql 的 COPY TO ... 命令非常快，Pandas 的 read_csv 也是如此。

将数据帧写入数据库时​​，我发现使用 CSV 文件(上面的代码)比使用 pandas 的 df.to_sql() 快约 50 倍(5.8 秒 vs 288 秒)。这主要是因为 Pandas 不使用多行插入。这似乎多年来一直是人们积极讨论的话题——参见 https://github.com/pandas-dev/pandas/issues/8953 .

关于chunksize的一些注意事项:这可能达不到大多数用户的预期。如果您在pandas.read_sql()中设置了chunksize，查询仍然作为一个命令运行，但结果会批量返回到您的程序；这是通过一个迭代器来完成的，迭代器依次生成每个 block 。如果您在 pandas.to_sql() 中使用 chunksize，它会导致批量完成插入，从而减少内存需求。然而，至少在我的系统上，每个批处理仍然被分解为每行的单独 insert 语句，并且这些语句需要很长时间来运行。

更新: 在我写完这篇文章后不久，Pandas 在 .to_sql() 中添加了一个 method 参数，这可能正是您想要的。检查documentation对于'multi'和callable选项。