How to import a CSV file into a sqlite3 database table using Python

How to import CSV files into SQLite3 database tables using Python. This process is explained in several steps.

Step 1: Preparation

First, ensure that your Python environment has the sqlite3 module installed (which is typically included in Python's standard library), and also the pandas library for convenient CSV file handling.

bash
pip install pandas

Step 2: Reading CSV Files

Using the pandas library, you can easily read CSV files. Suppose we have a file named data.csv; we can read it as follows:

python
import pandas as pd

# Read CSV file
data = pd.read_csv('data.csv')

Step 3: Connecting to SQLite Database

Next, we need to connect to the SQLite database. If the database file does not exist, sqlite3 will automatically create it.

python
import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('example.db')

Step 4: Importing Data into the Database Table

Once you have the DataFrame and database connection, you can use the to_sql method to directly import the data into the SQLite table. If the table does not exist, pandas will automatically create it.

python
# Import data into SQLite table
data.to_sql('my_table', conn, if_exists='replace', index=False)

Here, if_exists='replace' means that if the table already exists, it will replace the existing table; index=False indicates that the DataFrame's index will not be stored in the table.

Step 5: Cleanup

Finally, after completing the data import, close the database connection.

python
# Close database connection
conn.close()

Example Complete

This is the complete process of importing CSV files into SQLite3 database tables using Python. This method is not only concise but also powerful, capable of handling large data imports. For example, if data.csv is a large file containing millions of records, the pandas and sqlite3 libraries can effectively collaborate to ensure smooth data import into the SQLite database.