How to Import a CSV File into an SQLite3 Database Table with Python?

Converting a CSV File into an SQLite3 Database Table with Python

Importing a CSV file directly into an SQLite3 database table is not possible using the ".import" command. However, this can be achieved using the following steps:

Establish a Database Connection

Use the sqlite3 module to establish a connection to a database (either an in-memory database or a file-based database):

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()

Create the Target Table

Create the table that will hold the imported data using the "CREATE TABLE" statement, specifying the column names and data types:

cur.execute("CREATE TABLE t (col1, col2);")

Read the CSV File

Open the CSV file and read its contents using a CSV reader. If the column names are specified in the first line of the file, you can use csv.DictReader to map them to a dictionary:

with open('data.csv', 'r') as fin:
    dr = csv.DictReader(fin)

Convert CSV Data to Lists

Convert the dictionary rows from the CSV reader into a list of tuples:

to_db = [(i['col1'], i['col2']) for i in dr]

Insert Data into the Table

Use the executemany() method on the cursor object to insert the list of tuples into the target table:

cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)

Commit Changes

Commit the changes to the database to make them persistent:

con.commit()

Close the Connection

Finally, close the database connection:

con.close()

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