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

Importing a CSV File into a SQLite Database Table Using Python

Many individuals often encounter a challenge when importing CSV files into their SQLite database tables using Python. The ".import" command may not function as expected, leading to confusion. This article aims to provide a clear and concise solution for importing CSV files into SQLite database tables using Python, particularly for those working on Windows systems.

To begin, establish a connection to your SQLite database using the sqlite3 module. Create a table in your database using the SQL "CREATE TABLE" statement, specifying the appropriate column names.

Next, open your CSV file using the "open" function with the "r" flag. Create a CSV reader object using the csv.DictReader class, which will automatically assign the first line of the CSV file as the column headings.

To prepare the data for insertion into the database, create a list of tuples from the DictReader object, where each tuple represents a row of data from the CSV file. Use the "executemany" method of the cursor object to execute a single SQL INSERT statement for all the data in the list.

Finally, commit the changes to the database using the "commit" method and close the connection using the "close" method.

Here is an example code snippet that demonstrates the entire process:

import csv, sqlite3

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

# Create a table
cur.execute("CREATE TABLE t (col1, col2);")

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

    # Create a CSV reader object
    dr = csv.DictReader(fin)

    # Prepare the data for insertion
    to_db = [(i['col1'], i['col2']) for i in dr]

# Insert the data into the database
cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)

# Commit the changes
con.commit()

# Close the connection
con.close()

By following the steps outlined in this article, you should be able to successfully import CSV files into SQLite database tables using Python, even on Windows systems.

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