![]() |
python pandas sql table with header - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: python pandas sql table with header (/thread-38901.html) |
python pandas sql table with header - mg24 - Dec-08-2022 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() RE: python pandas sql table with header - Larz60+ - Dec-08-2022 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) RE: python pandas sql table with header - mg24 - Dec-08-2022 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) RE: python pandas sql table with header - Larz60+ - Dec-08-2022 mg24 Wrote: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. mg24 Wrote:2) I want to extract sql data in chunk size , Want to avoid memory full issue. SQL Tables are big in Big in sizesee: https://stackoverflow.com/a/29522443 mg24 Wrote: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/stable/reference/api/pandas.DataFrame.to_csv.html change: df.to_csv('output.csv', index=False) to: df.to_csv('output.csv', sep='|', index=False)
|