How to Convert CSV to SQL File Using Python (Generate .sql Scripts)

Introduction

Sometimes, connecting Python directly to a database is not the best option. Instead, you may want to generate a .sql file containing all the CREATE TABLE and INSERT statements. This file can then be executed by a database administrator, shared with clients, or committed to version control.

This guide shows you how to convert csv to sql script in Python, including automatic type inference, batch insertion, and portable SQL generation suitable for production workflows.

Why Generate a SQL Script Instead of Direct Database Insertion?

Creating a .sql file has several advantages:

• No database credentials required locally – safe for restricted environments
• Review SQL before execution – reduces risk of mistakes
• Shareable and portable – can be sent to clients, collaborators, or administrators
• Version control friendly – commit .sql files to git for reproducibility
• Remote server support – works even if direct database access is restricted

This makes convert csv to sql script ideal for auditing, collaboration, and production deployment.

Step 1: Reading CSV Headers for Column Names

First, we need to read the CSV headers to get the table’s column names:

import csv

with open(‘products.csv’, ‘r’, encoding=’utf-8′) as f:

   reader = csv.DictReader(f)

   columns = reader.fieldnames

   rows = list(reader)

print(columns)  # Example output: [‘id’, ‘name’, ‘price’, ‘category’]

This ensures that the SQL table structure matches the CSV headers exactly.

Step 2: Inferring Data Types Automatically

To generate accurate SQL, we need to detect the proper column types based on CSV values:

def infer_type(values):

   non_empty = [v for v in values if v.strip()]

   if not non_empty:

       return ‘TEXT’

   try:

       [int(v) for v in non_empty]

       return ‘INTEGER’

   except ValueError:

       pass

   try:

       [float(v) for v in non_empty]

       return ‘REAL’

   except ValueError:

       pass

   return ‘TEXT’

How it works:

• Empty columns default to TEXT
• Columns with only integers → INTEGER
• Columns with decimal numbers → REAL
• All other types → TEXT

This allows your generated script to be accurate and compatible with most SQL engines.

Step 3: Writing the CREATE TABLE Statement

Once column types are inferred, generate the SQL statement to create the table:

def write_create_table(columns, rows, table_name):

   col_types = {}

   for col in columns:

       values = [row[col] for row in rows]

       col_types[col] = infer_type(values)

   col_defs = ‘, ‘.join([f”{col} {col_types[col]}” for col in columns])

   return f”CREATE TABLE IF NOT EXISTS {table_name} ({col_defs});\n”

Example Output:

CREATE TABLE IF NOT EXISTS products (

   id INTEGER,

   name TEXT,

   price REAL,

   category TEXT

);

Step 4: Writing INSERT Statements in Batches

For performance and readability, generate INSERT statements in batches:

def write_inserts(columns, rows, table_name, batch_size=500):

   lines = []

   for i in range(0, len(rows), batch_size):

       batch = rows[i:i+batch_size]

       vals = []

       for row in batch:

           escaped = [v.replace(“‘”, “””) for v in row.values()]

           vals.append(“(” + “, “.join([f”‘{v}'” for v in escaped]) + “)”)

       lines.append(f”INSERT INTO {table_name} VALUES\n” + “,\n”.join(vals) + “;”)

   return “\n”.join(lines)

Why batching is important:

• Avoids extremely long single INSERT statements
• Improves execution speed in SQL engines
• Keeps the script readable and maintainable

Step 5: Saving and Testing the SQL File

Finally, write the CREATE TABLE and INSERT statements to a .sql file:

with open(‘output.sql’, ‘w’, encoding=’utf-8′) as f:

   f.write(write_create_table(columns, rows, ‘products’))

   f.write(write_inserts(columns, rows, ‘products’))

Test the script with SQLite or any SQL engine:

sqlite3 test.db < output.sql

sqlite3 test.db “SELECT COUNT(*) FROM products;”

This ensures your CSV has been fully converted into a working SQL script.

Tips for Production-Ready SQL Scripts

1. Normalize Column Names: Remove spaces, lowercase, replace special characters.
2. Escape Single Quotes: Prevent SQL injection or syntax errors.
3. Batch Inserts for Large Files: Avoid memory or engine issues.
4. Include DROP TABLE if Needed: For full reload scripts:

DROP TABLE IF EXISTS products;

5. Use UTF-8 Encoding: Prevents errors with special characters in text.

Conclusion

Generating a SQL script from CSV using Python provides a portable, shareable, and reviewable artifact. This method is perfect for situations where direct database access isn’t possible or desired.

With the above Python functions, you can convert csv to sql script for any dataset, handle large files, and ensure accurate data types. This workflow is ideal for developers, data engineers, and analysts working with production databases.