Sometimes, we want to import a CSV file into a sqlite3 database table using Python.
In this article, we’ll look at how to import a CSV file into a sqlite3 database table using Python.
How to import a CSV file into a sqlite3 database table using Python?
To import a CSV file into a sqlite3 database table using Python, w ecan use the execute method to run SQL queries.
And we use the csv module to read the CSV.
For instance, we write
import csv, sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE t (col1, col2);")
with open('data.csv','r') as fin:
dr = csv.DictReader(fin)
to_db = [(i['col1'], i['col2']) for i in dr]
cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)
con.commit()
con.close()
to open the SQLite database with
con = sqlite3.connect(":memory:")
Then we open the cursor with con.cursor.
And then we run the create table query with
cur.execute("CREATE TABLE t (col1, col2);")
Next, we open the csv file with open.
We read the rows with
dr = csv.DictReader(fin)
Then we create the to_db list that has tuples with the values we want to insert into the database from the csv.
Next, we run the insert query to insert all the items with
cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)
con.commit()
Finally, we close the database with con.close.
Conclusion
To import a CSV file into a sqlite3 database table using Python, w ecan use the execute method to run SQL queries.
And we use the csv module to read the CSV.
