Hi, I have a CSV file and I want to bulk-import this file into my sqlite3 database using Python. the command is ".import .....". but it seems that it cannot work like this. Can anyone give me an example of how to do it in sqlite3? I am using windows just in case. Thanks
+2
A:
The .import command is a feature of the sqlite3 command-line tool. To do it in Python, you should simply load the data using whatever facilities Python has, such as the csv module, and inserting the data as per usual.
This way, you also have control over what types are inserted, rather than relying on sqlite3's seemingly undocumented behaviour.
Marcelo Cantos
2010-05-22 11:34:41
There is no need to prepare the insert. The source of SQL statements and compiled results are kept in a cache.
John Machin
2010-06-17 04:13:37
@John Machin: Is there a link to how SQLite does this?
Marcelo Cantos
2010-06-17 07:34:35
@Marcelo: If you are interested in HOW it's done (why?), look in the sqlite source or ask on the sqlite mailing list.
John Machin
2010-06-17 07:54:23
@John Machin: I'm interested because in all the SQLite documentation that I've come across, there is not a single word about automatic caching of unprepared statements. I don't think it is reasonable to have to read source code or probe mailing lists to discover something as basic as whether I should prepare my SQL statements or not. What is your source of information on this?
Marcelo Cantos
2010-06-17 08:40:01
@Marcelo: Actually it's done in the Python sqlite3 wrapper module. http://docs.python.org/library/sqlite3.html#module-functions-and-constants says """The sqlite3 module internally uses a statement cache to avoid SQL parsing overhead. If you want to explicitly set the number of statements that are cached for the connection, you can set the cached_statements parameter. The currently implemented default is to cache 100 statements."""
John Machin
2010-06-17 10:25:01
@John Machin: Thank you; that's very useful to know. In fact, after a peruse through the module docs, it seems that there is no way to explicitly prepare a SQLite statement, so the cache is the only way statements can be prepared.
Marcelo Cantos
2010-06-17 11:56:45
+7
A:
import csv
import sqlite3
conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute("create table t (col1 text, col2 float);")
# csv.DictReader uses the first line in the file as column headings by default
dr = csv.DictReader(open('data.csv', delimiter=','))
to_db = [(i['col1'], i['col2']) for i in dr]
c.executemany("insert into t (col1, col2) values (?, ?);", to_db)
Adam Bernier
2010-05-22 12:20:04