SQL 查询输出到 .csv [英] SQL query output to .csv

问题描述

我正在从 python API 运行 SQL 查询，并希望以结构化(标题下的列数据)收集数据.CSV 格式.

I am running SQL query from python API and want to collect data in Structured(column-wise data under their header).CSV format.

这是我目前的代码.

sql = "SELECT id,author From researches WHERE id < 20 " 
cursor.execute(sql)
data = cursor.fetchall()
print (data)
with open('metadata.csv', 'w', newline='') as f_handle:
    writer = csv.writer(f_handle)
    header = ['id', 'author']
    writer.writerow(header)
    for row in data:
        writer.writerow(row)

现在数据正在控制台上打印，但没有进入 .CSV 文件，这就是我得到的 输出:

Now the data is being printed on the console but not getting in .CSV file this is what I am getting as output:

我缺少什么?

推荐答案

以下是您尝试执行的操作的简单示例:

Here is a simple example of what you are trying to do:

import sqlite3 as db
import csv

# Run your query, the result is stored as `data`
with db.connect('vehicles.db') as conn:
    cur = conn.cursor()
    sql = "SELECT make, style, color, plate FROM vehicle_vehicle"
    cur.execute(sql)
    data = cur.fetchall()

# Create the csv file
with open('vehicle.csv', 'w', newline='') as f_handle:
    writer = csv.writer(f_handle)
    # Add the header/column names
    header = ['make', 'style', 'color', 'plate']
    writer.writerow(header)
    # Iterate over `data`  and  write to the csv file
    for row in data:
        writer.writerow(row)

这篇关于SQL 查询输出到 .csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！