CODEWITHSUNDEEP
python-3.x
excelguy
excelguy

Reputation: 1624

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

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)

Upvotes: 0

Views: 7815

Answers (1)

Angel Roman
Angel Roman

Reputation: 638

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

Upvotes: 1

Related Questions