How to Convert a CSV to SQL INSERT Statements

You have a CSV and need it in a database. Generating INSERT statements is the portable answer: it works with any client, drops into a migration, and you can read it before running it.

It is also the approach with the most ways to go quietly wrong. Two of them corrupt data, and one of them is a security hole.

The shape you're aiming for

CREATE TABLE "people" (
  "id" INTEGER,
  "name" TEXT,
  "email" TEXT
);

INSERT INTO "people" ("id", "name", "email") VALUES
  (1, 'Jane Smith', 'jane@example.com'),
  (2, 'O''Brien', 'ob@example.com');

Note 'O''Brien'. That doubled quote is the whole ballgame.

Escaping: the part that breaks everything

A single apostrophe in your data ends the string literal early and the rest of the row becomes broken SQL:

-- Wrong: syntax error, or worse
INSERT INTO people VALUES ('O'Brien');

-- Right: the quote is doubled
INSERT INTO people VALUES ('O''Brien');

Doubling single quotes is the SQL-standard escape and works across MySQL, PostgreSQL, SQLite, and SQL Server.

This is not only a correctness problem. If you generate SQL by string concatenation from untrusted data, you have written an SQL injection. A name field containing:

'); DROP TABLE users; --

produces a file that, when run, does exactly what it says. Generating a .sql file feels safer than a live query because there's a human in the loop — but people run generated files without reading 40,000 lines.

If the CSV came from anywhere you don't control, prefer parameterized bulk loading over generated SQL:

import csv, sqlite3

conn = sqlite3.connect("app.db")
with open("people.csv", newline="", encoding="utf-8-sig") as f:
    rows = list(csv.reader(f))
    header, body = rows[0], rows[1:]

placeholders = ",".join("?" * len(header))
conn.executemany(f"INSERT INTO people VALUES ({placeholders})", body)
conn.commit()

The values never become part of the SQL text, so escaping is not your problem at all.

Column types: the leading-zero trap

Type inference by scanning the column is convenient and has one dangerous case.

A column of all-digit strings looks like an integer. But if it holds ZIP codes, phone numbers, or SKUs:

zip
02134
90210

Typing that as INTEGER turns 02134 into 2134. Every Boston address is now wrong, and nothing errored.

The rule is the same as for CSV to JSON: numbers are quantities you do arithmetic on. Anything with a leading zero, or that you'd never sum, belongs in TEXT.

The related failure is inconsistent quoting within a column. If quoting is decided per value rather than per column, you get:

"phone" TEXT
...
  ('(415) 555-1234'),   -- quoted
  (4155551234),         -- not quoted

Same column, two types. Some databases coerce, some reject the batch, and 0 prefixes vanish either way. Decide the type once per column and apply it to every value in it.

Identifier quoting differs by database

Column names with spaces, reserved words, or mixed case need quoting, and every database picked a different character:

| Database | Quoting | Example | |---|---|---| | PostgreSQL, SQLite | double quotes | "order" | | MySQL | backticks | `order` | | SQL Server | square brackets | [order] |

order, group, user, table, and select are all reserved words that appear constantly as column names. Quote identifiers, or sanitize them to plain alphanumerics and underscores.

Batch your inserts

One statement per row is slow — each is a separate round trip and, on some configurations, its own transaction:

INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);   -- 100,000 of these takes minutes

Multi-row batches are dramatically faster:

INSERT INTO t VALUES
  (1),
  (2),
  (3);

Batch around 100–1,000 rows. Don't put all 100,000 in one statement: MySQL rejects anything over max_allowed_packet (often 4 MB), and when one row fails you lose the entire batch with little indication which row caused it.

For large files, don't use INSERT at all

Above roughly 100,000 rows, native bulk loaders are an order of magnitude faster because they bypass statement parsing entirely:

PostgreSQL:

COPY people (id, name, email)
FROM '/path/people.csv'
WITH (FORMAT csv, HEADER true);

Or \copy from psql when the file is on your machine rather than the server.

MySQL:

LOAD DATA INFILE '/path/people.csv'
INTO TABLE people
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 ROWS;

SQLite:

.mode csv
.import --skip 1 people.csv people

Generated INSERT statements are for portability and reviewability. Bulk loaders are for volume. Pick based on which you need.

Before you run it

• Run against a copy or in a transaction you can roll back.
• Read the CREATE TABLE and check the inferred types, especially any all-digit column.
• Check a row containing an apostrophe made it through escaped.
• Confirm the row count matches the CSV after loading.

Checklist

• Double single quotes; never concatenate untrusted values into SQL.
• Prefer parameterized executemany for data you don't control.
• Resolve column types once per column, not per value.
• Keep identifier-like numeric columns as TEXT.
• Quote identifiers for your dialect.
• Batch 100–1,000 rows; use COPY or LOAD DATA above ~100k.