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

e4c5 · Accepted Answer · 2017-01-14 14:42:56Z

3

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')

edited Jan 14, 2017 at 14:42

answered Jan 14, 2017 at 13:02

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

Not exactly what I am looking for. I am trying to write the sql output into a cvs file. Any idea how to to this?

Community · Accepted Answer · 2017-05-23 11:52:53Z

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)

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Jan 14, 2017 at 13:39

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

4 Comments

John Over a year ago

Much appreciated @MaxU .... but I am getting this error message: AttributeError: module 'pandas' has no attribute 'from_sql'

e4c5 Over a year ago

that's my bad @claude, I linked to the correct documentation page by distractedly typed from_sql

MaxU - stand with Ukraine Over a year ago

@Claude, sorry, i just copied that part from e4c5's answer - it's fixed now

HereHere Over a year ago

@Claude Where is the final solution?

Collectives™ on Stack Overflow

How to write python sql output into CSV using a dataframe

2 Answers 2

Pandas

The right tool: odo

1 Comment

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Pandas

The right tool: odo

1 Comment

4 Comments

Linked

Related