如何使用数据框将python sql输出写入CSV [英] How to write python sql output into CSV using a dataframe

问题描述

导入模块

import pyodbc
import pandas as pd
import csv

创建与MICROSOFT SQL Server的连接

CREATE CONNECTION TO MICROSOFT SQL SERVER

msconn = pyodbc.connect(driver='{SQL Server}',
                        server='SERVER',
                        database='DATABASE', 
                        trusted_msconnection='yes')                                                                                                                
cursor = msconn.cursor()

创建包含SQL语句的变量

CREATE VARIABLES THAT HOLD SQL STATEMENTS

SCRIPT = "SELECT * FROM TABLE"

打印数据

cursor.execute(SCRIPT)
cursor.commit
for row in cursor:
    print (row)

将所有具有列名称的行写入CSV ---需要帮助

WRITE ALL ROWS WITH COLUMN NAME TO CSV --- NEED HELP HERE

推荐答案

熊猫

由于熊猫支持从RDBMS直接导入，名称为 read_sql ，您无需手动编写.

Pandas

Since pandas support direct import from an RDBMS with the name being called read_sql you don't need to write this manually.

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine('mssql+pyodbc://user:pass@mydsn')
df = pd.read_sql(sql='SELECT * FROM ...', con=engine)

正确的工具:odoo

来自 odo文档

将CSV文件加载到数据库中已解决.这是一个问题 已经很好地解决了.而不是每次都滚动自己的装载机 当我们需要这样做并浪费计算资源时，我们应该 在我们选择的数据库中使用本地加载程序.

Loading CSV files into databases is a solved problem. It’s a problem that has been solved well. Instead of rolling our own loader every time we need to do this and wasting computational resources, we should use the native loaders in the database of our choosing.

它反过来也起作用.

from odo import odo

odo('mssql+pyodbc://user:pass@mydsn::tablename','myfile.csv')

这篇关于如何使用数据框将python sql输出写入CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！