How to Import CSV Data into SQLite3 Using Python?

Bulk Importing CSV Data into SQLite3 Using Python

Importing a CSV file into a SQLite3 database table is a common task when dealing with data analysis and storage. While the ".import" command may not work as expected in certain cases, Python provides several methods to efficiently import CSV data into sqlite3.

One effective approach is using the sqlite3.connect() function to establish a connection to the database and create a cursor using the cursor() method. Subsequently, you can execute a SQL statement to create the target table with appropriate column names.

To import the CSV data, open the file using the open() function and create a DictReader object using csv.DictReader(). This object allows you to access the data as a dictionary, where the column names correspond to the values. Convert the data into a list of tuples suitable for insertion into the table using a comprehension.

Finally, use the executemany() method to execute multiple INSERT statements efficiently, passing the tuple list as the second argument. This method helps avoid multiple cursor executions and enhances performance. Don't forget to commit the changes to the database using the commit() method and close the connection using the close() method.

The code sample provided in the response does exactly this, demonstrating how to connect to a database, create a table, import CSV data, and close the connection appropriately. By following these steps, you can seamlessly import your CSV data into a sqlite3 database table using Python.

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