Home > Backend Development > Python Tutorial > How to Import a CSV File into a SQLite Database Table Using Python on Windows?

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

Barbara Streisand
Release: 2024-11-09 12:09:02
Original
472 people have browsed it

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()
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template