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!