SQL查询输出到.csv

问题描述

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

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

这是我到目前为止的代码.

This is the code so far I have.

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:

我想念的是什么?

推荐答案

import pandas as pd

import numpy as np

from sqlalchemy import create_engine

from urllib.parse import quote_plus

params = quote_plus(r'Driver={SQL Server};Server=server_name;                        Database=DB_name;Trusted_Connection=yes;')

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

sql_string = '''SELECT id,author From researches WHERE id < 20 '''

final_data_fetch = pd.read_sql_query(sql_string, engine)

final_data_fetch.to_csv('file_name.csv')

希望这会有所帮助！

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