How to import a CSV into SQLite with Python

Python ships with both csv and sqlite3, so loading a CSV into a database needs no installs. The two details that usually go wrong are column types and empty cells; this script handles both.

1. The script

Save as csv2sqlite.py:

import csv, re, sqlite3, sys

csv_path, db_path, table = sys.argv[1], sys.argv[2], sys.argv[3]
with open(csv_path, newline='', encoding='utf-8-sig') as f:
    rows = list(csv.reader(f))
header, data = rows[0], [r for r in rows[1:] if any(c.strip() for c in r)]

def col_type(i):
    vals = [r[i].strip() for r in data if i < len(r) and r[i].strip()]
    if vals and all(re.fullmatch(r'-?(0|[1-9]\d*)', v) for v in vals): return 'INTEGER'
    if vals and all(re.fullmatch(r'-?(0|[1-9]\d*)(\.\d+)?([eE][-+]?\d+)?', v) for v in vals): return 'REAL'
    return 'TEXT'  # includes ids with leading zeros such as 02134

q = lambda name: '"' + name.replace('"', '""') + '"'
types = [col_type(i) for i in range(len(header))]
db = sqlite3.connect(db_path)
db.execute(f'CREATE TABLE {q(table)} ({", ".join(f"{q(h)} {t}" for h, t in zip(header, types))})')
db.executemany(f'INSERT INTO {q(table)} VALUES ({", ".join("?" * len(header))})',
               [[None if (v := (r[i] if i < len(r) else '')).strip() == '' else v for i in range(len(header))] for r in data])
db.commit()
print(f'{len(data)} rows -> {db_path}:{table}', dict(zip(header, types)) if len(header) <= 8 else f'{len(header)} columns')

2. Run it

python3 csv2sqlite.py products.csv shop.db products
sqlite3 shop.db "SELECT * FROM products"

On our test file it printed 3 rows -> shop.db:products {'id': 'INTEGER', 'name': 'TEXT', 'zip': 'TEXT', 'price': 'REAL', 'in_stock': 'TEXT', 'notes': 'TEXT'}. The zip column stays TEXT because one value is 02134, and O'Brien or "Pro" need no escaping because the values are passed as parameters (?), never pasted into the SQL.

3. What it does

Types: a column is INTEGER or REAL only if every non-empty value is a plain number; otherwise TEXT. In the country-codes file, Dial is TEXT because Anguilla's code is 1-264, while ISO3166-1-numeric is INTEGER.
NULLs: empty cells are stored as NULL, not as empty strings, so WHERE zip IS NULL works.
Excel files saved as CSV: utf-8-sig removes the BOM that would otherwise end up in the first column name.

4. Limits

The table must not exist yet; add DROP TABLE IF EXISTS before CREATE TABLE if you reload.
Comma-separated only; pass delimiter=';' to csv.reader for European Excel files.
All rows are read into memory; for files of millions of rows, insert in batches while reading, or use the sqlite3 shell: .import --csv products.csv products (everything becomes TEXT there).

5. Other databases, no code

For PostgreSQL, MySQL or SQL Server, paste the CSV into the free CSV to SQL converter: it writes CREATE TABLE and INSERT statements with the same typing rules and the right quoting for each database.

Frequently asked questions

How do I import CSV into SQLite without Python?

In the sqlite3 shell (version 3.32 or later): .import --csv file.csv tablename. It creates the table from the header if it does not exist, but every column is TEXT.

Can I do this with pandas?

Yes: pd.read_csv('file.csv').to_sql('table', sqlite3.connect('db.sqlite'), index=False). Note that pandas reads 02134 as the number 2134 unless you set dtype=str for that column.

Why use ? placeholders instead of building the SQL string?

Placeholders let sqlite3 handle quotes, commas and newlines in values correctly and prevent SQL injection from the file's content.

Written with AI assistance. The script was run on 2026-10-01 with Python 3.13's standard library on the country-codes dataset (249 rows, 56 columns), the JSONPlaceholder users CSV (10 rows, 15 columns) and a file with quotes, commas, a leading-zero zip code and empty cells; every value read back from SQLite matched the CSV. PlainTables is our extension; the other methods are free and built into Excel, Google Sheets and pandas.