python pandas sql table with header

[mg24]

Hi Team,

please help me first time using pandas for extracting sql table with headers into csv file.

how to write below header line to csv.
writer.writerow(col[0] for col in cursor.description)

or any other best way of extracting sql table data with headers. Sep="|" via pandas.

below is my attempted code

import csv
import pyodbc
import pandas as pd

cursor = connection.cursor()
f = open('output.csv', 'w')

cursor.execute('select * from mydata')
with open("output.csv", "w", newline="") as outfile:
    writer = csv.writer(outfile, delimiter="|", quoting=csv.QUOTE_NONNUMERIC)
    writer.writerow(col[0] for col in cursor.description)    
# Get data in batches
    while True:
        df = pd.DataFrame(cursor.fetchmany(1000))
        if len(df) == 0:
            break
        else:
            df.to_csv(f, index=False, header=False, mode='a',sep="|")

    # Clean up
f.close()
cursor.close()
connection.close()

[Larz60+]

I can't test this as I don't have a copy of your database, but should be very close if not entirely correct:
It's a lot simpler than you realise.

import pandas as pd
import sqlite3

conn = sqlite3.connect(, isolation_level=None,
    detect_types=sqlite3.PARSE_COLNAMES)
df = pd.read_sql_query("SELECT * FROM mydata", conn)
df.to_csv('output.csv', index=False)

[mg24]

Hi Larz60,

thanks for help need still more help, I am using Microsoft SQL server


1) is sqlite3 Support for extracting data from Microsoft SQL Server.[/b]
2) I want to extract sql data in chunk size , Want to avoid memory full issue. SQL Tables are big in Big in size
3) I want data to be written in CSV with delimiter "|"

import pandas as pd
import sqlite3

conn = sqlite3.connect(, isolation_level=None,
    detect_types=sqlite3.PARSE_COLNAMES)
df = pd.read_sql_query("SELECT * FROM mydata", conn)
df.to_csv('output.csv', index=False)

[Larz60+]

1) is sqlite3 Support for extracting data from Microsoft SQL Server.[/b]

No, sqlite3 is only for sqlite database.
I don't use SQL Server, but connection should be similar.

2) I want to extract sql data in chunk size , Want to avoid memory full issue. SQL Tables are big in Big in size

see: https://stackoverflow.com/a/29522443

3) I want data to be written in CSV with delimiter "|"

pandas calls the delimiter 'sep' so look for this in the docs.
https://pandas.pydata.org/pandas-docs/st...o_csv.html

change: df.to_csv('output.csv', index=False)
to: df.to_csv('output.csv', sep='|', index=False)