python - 如何对多个 .db 文件执行 SQL 查询并将结果存储在 .csv 中?
问题描述
我的 Google Drive 上存储了大约 100 个 .db 文件,我想在这些文件上运行相同的 SQL 查询。我想将这些查询结果存储在一个 .csv 文件中。
我设法使用以下代码将单个 SQL 查询的结果写入 .csv 文件,但我无法使其适用于多个文件。
conn = sqlite3.connect('/content/drive/My Drive/Data/month_2014_01.db')
df = pd.read_sql_query("SELECT * FROM messages INNER JOIN users ON messages.id = users.id WHERE text LIKE '%house%'", conn)
df.to_csv('/content/drive/My Drive/Data/Query_Results.csv')
根据这篇文章,这是我迄今为止用来尝试使其适用于所有文件的代码。
databases = []
directory = '/content/drive/My Drive/Data/'
for filename in os.listdir(directory):
flname = os.path.join(directory, filename)
databases.append(flname)
for database in databases:
try:
with sqlite3.connect(database) as conn:
conn.text_factory = str
cur = conn.cursor()
cur.execute(row["SELECT * FROM messages INNER JOIN users ON messages.id = users.id WHERE text LIKE '%house%'"])
df.loc[index,'Results'] = cur.fetchall()
except sqlite3.Error as err:
print ("[INFO] %s" % err)
但这会给我一个错误:TypeError: tuple indices must be integers or slices, not str。我显然做错了什么,我将非常感谢任何指向答案的提示。
解决方案
考虑构建一个数据帧列表,然后将它们连接到一个数据帧中pandas.concat
:
gdrive = "/content/drive/My Drive/Data/"
sql = """SELECT * FROM messages
INNER JOIN users ON messages.id = users.id
WHERE text LIKE '%house%'
"""
def build_df(db)
with sqlite3.connect(os.path.join(gdrive, db)) as conn:
df = pd.read_sql_query(sql, conn)
return df
# BUILD LIST OF DFs WITH LIST COMPREHENSION
df_list = [build_df(db) for db in os.listdir(gdrive) if db.endswith('.db')]
# CONCATENATE ALL DFs INTO SINGLE DF FOR EXPORT
final_df = pd.concat(df_list, ignore_index = True)
final_df.to_csv(os.path.join(gdrive, 'Query_Results.csv'), index = False)
更好的是,考虑 SQLiteATTACH DATABASE
并将查询结果附加到主表中。这也避免了使用繁重的数据科学第三方库pandas
来满足简单的数据迁移需求。此外,您可以将所有数据库数据保存在 SQLite 中,而无需担心数据类型转换和 i/o 传输问题。
import csv
import sqlite3
with sqlite3.connect(os.path.join(gdrive, 'month_2014_01')) as conn:
# CREATE MASTER TABLE
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS master_query")
cur.execute("""CREATE TABLE master_query AS
SELECT * FROM tmp.messages
INNER JOIN tmp.users
ON tmp.messages.id = tmp.users.id
WHERE text LIKE '%house%'
""")
conn.commit()
# ITERATIVELY ATTACH AND APPEND RESULTS
for db in os.listdir(gdrive):
if db.endswith('.db'):
cur.execute("ATTACH DATABASE ? AS tmp", [db])
cur.execute("""INSERT INTO master_query
SELECT * FROM tmp.messages
INNER JOIN tmp.users
ON tmp.messages.id = tmp.users.id
WHERE text LIKE '%house%'
""")
cur.execute("DETACH DATABASE tmp")
conn.commit()
# WRITE TUPLE OF ROWS TO CSV
data = cur.execute("SELECT * FROM master_query")
with open(os.path.join(gdrive, 'Query_Results.csv'), 'wb') as f:
writer = csv.writer(f)
writer.writerow([i[0] for i in cur.description]) # HEADERS
writer.writerows(data) # DATA
cur.close()
推荐阅读
- python - 在一级减去不匹配索引器的 pd 系列
- r - 替换R中整列中的撇号和空格
- python - Python Pandas 和 Seaborn 日期格式问题:strftime 对不同 seaborn 图表的工作方式不同
- flutter - 我如何在颤振中播放网络视频
- php - 如何在php中将具有并行属性的数组列表转换为类似对象的数组列表
- unit-testing - rust 中的单元测试、模拟和特征
- amazon-web-services - apiserver pod 无法加载基于 configmap 的 request-header-client-ca-file
- javascript - “GL_INVALID_OPERATION:缓冲区大小不足。” 在可变数量的渲染调用之后
- c# - 正则表达式查找范围内的 GUID 和数字
- php - 我想在 PHP 中只为每个循环执行一次 if else 语句