Python: Convert SQL to CSV — Export Database Tables Easily

Introduction

Exporting SQL data to CSV is one of the most common tasks for data analysts, ETL engineers, and backend developers. Python makes this task straightforward thanks to libraries like pandas, csv, and SQLAlchemy. Whether you’re working with SQLite, MySQL, or PostgreSQL, you can automate exports, handle large tables, and even schedule daily reports.

In this guide, we’ll cover all approaches to convert SQL to CSV Python with code examples, tips, and production-ready best practices.

Connecting to Your Database in Python

Before exporting, you need to connect Python to your database. SQLAlchemy provides a unified interface:

from sqlalchemy import create_engine

# SQLite (local file, no server)

engine = create_engine(‘sqlite:///mydata.db’)

# MySQL

engine = create_engine(‘mysql+pymysql://user:pass@localhost/dbname’)

# PostgreSQL

engine = create_engine(‘postgresql://user:pass@localhost/dbname’)

This connection allows Python to query any table and fetch results for CSV export.

Method 1 — Using pandas read_sql + to_csv (Easiest)

The simplest and most flexible approach is to use pandas:

import pandas as pd

from sqlalchemy import create_engine

engine = create_engine(‘sqlite:///sales.db’)

df = pd.read_sql(‘SELECT * FROM orders’, engine)

df.to_csv(‘orders.csv’, index=False, encoding=’utf-8′)

print(f”Exported {len(df)} rows successfully”)

Benefits of this method:

• Automatic column names and headers
• Handles NULL values, dates, and numeric types
• Easy to save large tables with chunking
• Supports any SQL database through SQLAlchemy

Method 2 — Using csv.writer (No pandas)

If you prefer no external dependencies, Python’s built-in csv module works perfectly:

import csv

from sqlalchemy import create_engine, text

engine = create_engine(‘sqlite:///sales.db’)

with engine.connect() as conn:

   result = conn.execute(text(“SELECT * FROM orders”))

   columns = result.keys()

   with open(‘orders.csv’, ‘w’, newline=”, encoding=’utf-8′) as f:

       writer = csv.writer(f)

       writer.writerow(columns)  # write header

       writer.writerows(result.fetchall())  # write all rows

This method is lightweight and works well for small to medium datasets.

Handling Large Result Sets

For very large tables, loading everything into memory can crash your script. Use chunked reading with pandas:

import os

import pandas as pd

from sqlalchemy import create_engine

engine = create_engine(‘sqlite:///bigdata.db’)

for chunk in pd.read_sql(‘SELECT * FROM big_table’, engine, chunksize=10000):

   chunk.to_csv(‘output.csv’, mode=’a’, header=not os.path.exists(‘output.csv’), index=False)

Notes:

• chunksize specifies how many rows to fetch at a time
• mode=’a’ appends to the CSV file
• header=not os.path.exists() ensures the header is only written once

Automating SQL to CSV Exports

Python makes scheduling easy with the schedule library. For example, exporting daily sales:

import schedule

import time

import pandas as pd

from sqlalchemy import create_engine

def export_daily():

   engine = create_engine(‘postgresql://user:pass@localhost/db’)

   df = pd.read_sql(“SELECT * FROM daily_sales WHERE date = CURRENT_DATE”, engine)

   df.to_csv(f”sales_{pd.Timestamp.today().date()}.csv”, index=False)

   print(“Daily export complete”)

schedule.every().day.at(“06:00”).do(export_daily)

while True:

   schedule.run_pending()

   time.sleep(60)

This script ensures your CSV reports are generated automatically every day.

Tips for Production-Ready SQL to CSV Exports

1. Always include headers – pandas and csv.writer both handle this automatically.
2. Use UTF-8 encoding – prevents errors with special characters.
3. Escape special characters – especially single quotes or commas in text fields.
4. Use chunked reads – avoids memory issues with large tables.
5. Verify row counts – compare the number of rows in SQL and CSV.

Conclusion

Python provides multiple ways to convert SQL to CSV Python, each suited to different needs:

• pandas – best for simplicity, flexibility, and large datasets
• csv.writer – lightweight, dependency-free solution
• Chunked reading – essential for big tables
• Automation – schedule daily exports with schedule

With these tools, you can export SQL tables to CSV efficiently, reliably, and in a production-ready manner.