How can I import CSV files into SQLite databases using Python?

Importing CSV Files into SQLite Databases with Python

In Python, utilizing the sqlite3 module empowers developers to effortlessly import data from CSV files into sqlite3 database tables. While the ".import" command might not be directly applicable, alternative methods provide a straightforward approach to accomplish this task.

Example Code:

To illustrate the import process, consider the following Python code:

import csv, sqlite3

# Connect to the database (in-memory or file) and create a cursor
con = sqlite3.connect(":memory:") # change to 'sqlite:///your_filename.db'
cur = con.cursor()
cur.execute("CREATE TABLE t (col1, col2);") # use your column names here

# Open the CSV file for reading
with open('data.csv','r') as fin:
    # Create a DictReader object to read data from the CSV file
    dr = csv.DictReader(fin) # comma is default delimiter
    # Convert CSV data into a list of tuples for database insertion
    to_db = [(i['col1'], i['col2']) for i in dr]

# Execute the insert query using executemany to efficiently import data
cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)

# Commit changes to the database
con.commit()

# Close the connection and cursor
con.close()

Explanation:

• Connect to the SQLite database using sqlite3.connect() and create a cursor for executing queries.
• Create the target table in the database using the CREATE TABLE statement.
• Open the CSV file for reading using the with statement.
• Use csv.DictReader to read data from the CSV file. It automatically maps column names to their respective values.
• Convert the data into a list of tuples for efficient insertion into the database.
• Utilize executemany to import multiple rows simultaneously.
• Commit the changes to make the data persistent in the database.
• Close the connection and cursor to release resources.

The above is the detailed content of How can I import CSV files into SQLite databases using Python?. For more information, please follow other related articles on the PHP Chinese website!