Python Import CSV To Database (Simple Example)

So you are working on a project that needs to import a CSV file into the database? Here’s a quick and simple example using SQLite – Read on!

 

 

TABLE OF CONTENTS

 

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

QUICK SETUP

• Create a virtual environment virtualenv venv.
• Activate it – venv\Scripts\activate (Windows) venv/bin/activate (Linux/Mac)

 

PART 1) DUMMY DATABASE & CSV

1A) USERS DATABASE

CREATE TABLE `users` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `email` TEXT NOT NULL UNIQUE,
  `name` TEXT NOT NULL
);

For this example, we will work with this dummy users table… Don’t think it needs much explanation.

• id User ID, primary key and auto-increment.
• email User’s email, unique to prevent duplicates.
• name User’s name.

 

 

1B) CREATE DATABASE

# (A) LOAD PACKAGES
import sqlite3, os
from sqlite3 import Error
 
# (B) DATABASE + SQL FILE
DBFILE = "users.db"
SQLFILE = "S1A_users.sql"
 
# (C) DELETE OLD DATABASE IF EXIST
if os.path.exists(DBFILE):
  os.remove(DBFILE)
 
# (D) IMPORT SQL
conn = sqlite3.connect(DBFILE)
with open(SQLFILE) as f:
  conn.executescript(f.read())
conn.commit()
conn.close()
print("Database created!")

This will read S1A_users.sql and create the actual users.db.

 

1C) DUMMY CSV

jane@doe.com,Jane Doe
joe@doe.com,Joe Doe
john@doe.com,John Doe
...

Nothing much, just a list of users – The first column is the email and the second is the name.

P.S. For those who are new – CSV is a text file, the columns are separated by a comma. Comma-separated values, get it?

 

 

PART 2) IMPORT CSV TO THE DATABASE

# (A) IMPORT MODULES & SETTINGS
import sqlite3, csv
CSVFILE = "S1C_users.csv"
DBFILE = "users.db"
 
# (B) CONNECT TO DATABASE
conn = sqlite3.connect(DBFILE)
cursor = conn.cursor()
 
# (C) READ & IMPORT CSV FILE
with open(CSVFILE) as demo:
  reader = csv.reader(demo)
  for row in reader:
    cursor.execute("INSERT INTO `users` (`email`, `name`) VALUES (?,?)", row)
 
# (D) COMMIT & CLOSE
conn.commit()
conn.close()
print("OK")

Finally, this should be pretty straightforward too.

1. Load the required modules.
2. Open the database.
3. Read the CSV file and insert it into the database. Take note, we read the CSV file row by row, not all at once.
4. Save and close the database connection.

 

 

 

HOW ABOUT MYSQL, MSSQL, MONGODB, POSTGRESQL?

The process is the same –

• Create your database table.
• Connect to the database, read the CSV, and import row-by-row.

You will have to do some research on your own – It’s impossible to go through every kind of database in this short guide.

 

LINKS & REFERENCES

• Python SQLite
• Python CSV
• CSV To HTML Table – Code Boxx