使用Python或R将非常大的SQL文件导出为CSV

Question

我有一个很大的sql文件（20 GB），想将其转换为csv格式。我计划将文件加载到Stata进行分析。我有足够的内存来加载整个文件（我的电脑内存为32GB）。

问题是：我目前在Python中找到的解决方案（sqlite3）似乎需要比我当前系统拥有的更多的内存：

• 读取SQL
• 写入csv

以下是代码：

import sqlite3
import pandas as pd

con=sqlite3.connect('mydata.sql')
query='select * from mydata'
data=pd.read_sql(query,con)
data.to_csv('export.csv')
con.close()

这个 SQL 文件包含大约 15 个变量，这些变量可以是时间戳、字符串或数字值。没有什么很复杂的。

我认为一个可能的解决方案是逐行读取 SQL 文件并将 CSV 文件写入其中。然而，我不知道如何实现这一点（无论是在 R 还是 Python 中）。

非常感谢任何帮助！

- ℕʘʘḆḽḘ

---

你能否发布一个非常小的 SQL 文件子集？如果可能的话，也请提供你的 Python 或 R 代码。 - dickoa

很遗憾，我无法发布任何子集。但是该文件包含各种变量（时间戳、带有任何字符的字符串（可能包括一些中文）、数字等）。 - ℕʘʘḆḽḘ

@dickoa，请查看我上面的编辑。谢谢！ - ℕʘʘḆḽḘ

您可以使用“编辑”按钮将代码粘贴到问题上，我认为这样更好。如果您无法发布文件的任何子集，则 SQL 文件示例将有助于回答问题并帮助您。 - dickoa

@dickoa，我正在尝试你的方法，但是出现了以下错误UnicodeEncodeError: 'ascii'编解码器无法在位置1处编码字符u'\xee'：该值不在128的范围内... 你知道我应该怎么做吗？ - ℕʘʘḆḽḘ

Answer 1

您可以分批读取SQL数据库并将其写入文件，而不是一次性读取整个数据库。感谢如何将Pandas数据添加到现有CSV文件？的作者。

import sqlite3
import pandas as pd

# Open the file
f = open('output.csv', 'w')
# Create a connection and get a cursor
connection = sqlite3.connect('mydata.sql')
cursor = connection.cursor()
# Execute the query
cursor.execute('select * from mydata')
# Get data in batches
while True:
    # Read the data
    df = pd.DataFrame(cursor.fetchmany(1000))
    # We are done if there are no data
    if len(df) == 0:
        break
    # Let's write to the file
    else:
        df.to_csv(f, header=False)

# Clean up
f.close()
cursor.close()
connection.close()

- Till Hoffmann

---

我还没有测试过这段代码，如果有任何问题，请告诉我。 - Till Hoffmann

没问题，我会告诉你的。再次感谢！ - ℕʘʘḆḽḘ

我正在尝试您的方法，但是出现了以下错误UnicodeEncodeError: 'ascii'编解码器无法在位置1处编码字符u'\xee'：该值不在128的范围内... 您知道我应该怎么做吗？ - ℕʘʘḆḽḘ

请尝试使用df.to_csv(f, header=False, encoding='utf-8')。这条语句的作用是将数据框以CSV格式写入到文件中，其中header=False表示不写入列名，encoding='utf-8'表示使用UTF-8编码来保存文件。 - Till Hoffmann

Answer 2

在Windows命令行或UNIX shell中，可以像这样使用sqlite3命令行程序：

sqlite3 -csv "mydata.sql" "select * from mydata;" > mydata.csv

如果mydata.sql不在当前目录中，请使用路径。在Windows上，请使用正斜杠而不是反斜杠。

或者运行sqlite3。

sqlite3

在sqlite提示符下输入以下命令：

.open "mydata.sql"
.ouptut mydata.csv
.mode csv
select * from mydata;
.quit

（或将它们放在一个名为run的文件中，并使用sqlite3 < run命令）。

- G. Grothendieck

---

谢谢Grothendieck，但是你的解决方案对我来说稍微有点复杂。如何在Windows上创建文件？我需要管理员权限吗（我没有）？为什么这个解决方案会克服我上面解释的问题？谢谢！ - ℕʘʘḆḽḘ

你可以使用记事本创建文件，或者干脆手动在sqlite3中输入这些行。你不需要管理员权限。 - G. Grothendieck

Answer 3

将 .sql 文件加载到 MySQL 数据库中，并将其导出为 CSV。

加载 MySQL 转储文件到 MySQL 数据库的命令。

创建一个 MySQL 数据库。

create database <database_name>

mysqldump -u root -p <database_name> < dumpfilename.sql

将MySQL表导出为CSV的命令

mysql -u root -p
use <database_name>

SELECT * INTO OUTFILE 'file.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM <table_name>;

- Vivek