How to write python sql output into CSV using a dataframe

Question

IMPORT MODULES

import pyodbc
import pandas as pd
import csv

CREATE CONNECTION TO MICROSOFT SQL SERVER

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

CREATE VARIABLES THAT HOLD SQL STATEMENTS

SCRIPT = "SELECT * FROM TABLE"

PRINT DATA

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

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

Answer 1

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)

The right tool: odo

From odo docs

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.

And it works the other way round also.

from odo import odo

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

Answer 2

@e4c5's answer is great as it should be faster compared to for loop + cursor - i would extend it with saving result set to CSV:

...
pd.read_sql(sql='SELECT * FROM TABLE', con=msconn) \
  .to_csv('/path/to/file.csv', index=False)

if you want to read all rows (not specifying WHERE clause):

pd.read_sql_table('TABLE', con=msconn).to_csv('/path/to/file.csv', index=False)