How to Import a CSV File into a SQLite Database Table Using Python?

Bulk Importing CSV Data into SQLite with Python

Importing a CSV file into a SQLite database table can be a straightforward process using Python's sqlite3 module. However, directly using the ".import" command may not yield desired results, as suggested in a recent query.

To effectively import a CSV file, consider the following approach:

Example:

Assuming you have a CSV file named "data.csv" located in your current working directory and a database connection established using sqlite3.connect(), you can perform the import using the following steps:

import csv, sqlite3

# Connect to the database
con = sqlite3.connect(":memory:") # or 'sqlite:///your_filename.db'

# Create a cursor
cur = con.cursor()

# Create the target table in advance
cur.execute("CREATE TABLE t (col1, col2);") # Adjust column names as needed

# Open the CSV file for reading
with open('data.csv','r') as fin:

    # Create a DictReader to read the header and rows as dictionaries
    # In case of a headerless file, skip the header argument
    dr = csv.DictReader(fin, delimiter=',')

    # Convert the rows into a list of tuples
    to_db = [(i['col1'], i['col2']) for i in dr]

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

# Commit the changes
con.commit()

# Close the connection
con.close()

This script assumes your CSV file has two columns named "col1" and "col2." If your file has different column names, adjust them accordingly in the code. Upon successful execution, the CSV data will be imported into the newly created "t" table within the specified database.

The above is the detailed content of How to Import a CSV File into a SQLite Database Table Using Python?. For more information, please follow other related articles on the PHP Chinese website!