How can I export to csv using pandas pd.read_sql?

Question

I have a python function that queries a database, how can I write this to csv when I do not convert to dataframe? Or must I import dataframe and use that function?

Return works fine to see results in my consol, but now I would like to export as csv.

any help would be appreciated.

code:

import pandas as pd
import pyodbc, os 
import datetime
from datetime import datetime
from pandas import ExcelWriter
import numpy as np


    def date(businessDate):
        con = pyodbc.connect(r'DSN='+'Stack',autocommit=True)  
        print('working')


        sql = f"""
        SELECT * FROM date 
        where businessDate = {businessDate}

        """

        df_date = pd.read_sql(sql,con)

        con.close()

        return(df_date)

Angel Roman · Accepted Answer · 2019-04-18 17:54:29Z

1

It appears df_date = pd.read_sql(sql,con) produces a dataframe. You can test this by running type(df_date).

See function below write_to_csv() which is essentially the same function as date() with df_date.to_csv() function. Ensure the csv file already exists before running write_to_csv() since this function will only write to it and not create it.

import pandas as pd
import pyodbc, os 
import datetime
from datetime import datetime
from pandas import ExcelWriter
import numpy as np


def date(businessDate):
    con = pyodbc.connect(r'DSN='+'Stack',autocommit=True)  
    print('working')
    sql = f""" 
    SELECT * FROM date 
    where businessDate = {businessDate}
    """
    df_date = pd.read_sql(sql,con)
    con.close()
    return(df_date)

path = "C:\\Users\\path\\to\\your\\folder\\file.csv"

def write_to_csv(businessDate):
    con = pyodbc.connect(r'DSN='+'Stack',autocommit=True)  
    print('working')
    sql = f""" 
    SELECT * FROM date 
    where businessDate = {businessDate}
    """
    df_date = pd.read_sql(sql,con)
    con.close()
    df_date.to_csv(path)
    return('Successfully wrote to csv')

answered Apr 18, 2019 at 17:54

Angel Roman

6381 gold badge4 silver badges14 bronze badges

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

5 Comments

excelguy Over a year ago

Thanks Angel. Can you explain to me how and where in the code it become a dataframe? Also why is path in the first function?

Angel Roman Over a year ago

df_date = pd.read_sql(sql,con) should produce a dataframe. You can test this by running type(df_date) and it should return pandas.core.frame.DataFrame. Path will direct where the csv file is located and allows the function df_date.to_csv() to write to it.

excelguy Over a year ago

thanks, could the path been stored in the write_to_csv function?

Angel Roman Over a year ago

Yes you can store it in the to_csv function. df_date.to_csv(C:\\path\\to\\file.csv)

greendino Over a year ago

so much import for a simple task. why oh why python

Collectives™ on Stack Overflow

How can I export to csv using pandas pd.read_sql?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related