Python read mysql to csv

Question

我想在块中读取mysql数据库并将其内容写入一堆csv文件。

使用下面的命令可以很容易地完成:

df_chunks = pd.read_sql_table(table_name, con, chunksize=CHUNK_SIZE)
for i, df in enumerate(chunks):
df.to_csv("file_{}.csv".format(i)

假设我不能使用pandas，我可以使用其他什么替代方法?我试着用

import sqlalchemy as sqldb
import csv
CHUNK_SIZE = 100000
table_name = "XXXXX"

host = "XXXXXX"
user = "XXXX"
password = "XXXXX"
database = "XXXXX"
port = "XXXX"
engine = sqldb.create_engine('mysql+pymysql://{}:{}@{}:{}/{}'.format(user,password,host,port,database))
con = engine.connect()
metadata = sqldb.MetaData()
table = sqldb.Table(table_name, metadata, autoload=True, autoload_with=engine)
query = table.select()
proxy = con.execution_options(stream_results=True).execute(query)
cols = [""] + [column.name for column in table.c]
file_num = 0
while True:
batch = proxy.fetchmany(CHUNK_SIZE)
if not batch:
break
csv_writer = csv.writer("file_{}.csv".format(file_num), delimiter=',')
csv_writer.writerow(cols)
#csv_writer.writerows(batch) # while this work, it does not have the index similar to df.to_csv()
for i, row in enumerate(batch):
csv_writer.writerow(i + row) # will error here
file_num += 1
proxy.close()

虽然使用.writerows(batch)工作得很好，但它没有像df.to_csv()那样的索引。我想添加行号等效，但似乎不能添加到行，这是一个sqlalchemy.engine.result.RowProxy。我该怎么做呢?或者我可以使用其他更快的替代方案吗?

Answer 1

查找SELECT ... INTO OUTFILE ...

它将在1个SQL语句中完成任务;0行Python(除了调用SQL)。