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, we can use the csv and sqlite3 modules.
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 read data from data.csv, which has:
col1,col2
1,2
3,4
5,6
We connect to the SQLite database with sqlite3.connect.
Then we call con.cursor to get a cursor.
Next we use the cursor to create a table by calling cur.execute.
Then we open the CSV with open.
We read the file by using the csv.DictReader constructor with the file fin.
And then we get the items that we want to write to the DB into a list of tuples with to_db = [(i['col1'], i['col2']) for i in dr].
Next we call cur.executemany with the insert SQL statement and to_db to insert the entries.
Finally, we call commit to commit the insertion and close to close the db connection.
Conclusion
To import a CSV file into a sqlite3 database table using Python, we can use the csv and sqlite3 modules.
