Bulk insert huge data into SQLite using Python

by

Since this is the top result on a Google search I thought it might be nice to update this question.

From the python sqlite docs you can use

import sqlite3

persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
]

con = sqlite3.connect(":memory:")

# Create the table
con.execute("create table person(firstname, lastname)")

# Fill the table
con.executemany("insert into person(firstname, lastname) values (?,?)", persons)

I have used this method to commit over 50k row inserts at a time and it’s lightning fast.

More Related Contents:

  1. Variable table name in sqlite
  2. SQLite parameter substitution problem
  3. Importing a CSV file into a sqlite3 database table using Python
  4. Django: Deploying an application on Heroku with sqlite3 as the database
  5. How do you escape strings for SQLite table/column names in Python?
  6. How to retrieve inserted id after inserting row in SQLite using Python?
  7. python 3.2 UnicodeEncodeError: ‘charmap’ codec can’t encode character ‘\u2013’ in position 9629: character maps to
  8. How to load existing db file to memory in Python sqlite3?
  9. Python insert numpy array into sqlite3 database
  10. Python CSV to SQLite
  11. Python sqlite3 and concurrency
  12. What if I don’t close the database connection in Python SQLite
  13. HDF5 – concurrency, compression & I/O performance [closed]
  14. How to discover table properties from SQLAlchemy mapped object
  15. How to get a single result from a SQL query in python?
  16. Parameter unsupported when inserting int
  17. Executing an SQL query over a pandas dataset
  18. Is this Python code vulnerable to SQL injection? (SQLite3)
  19. SQLite Performance Benchmark — why is :memory: so slow…only 1.5X as fast as disk?
  20. pysqlite: Placeholder substitution for column or table names?
  21. creating a database outside the application context
  22. Python SQLite3 SQL Injection Vulnerable Code
  23. Why do you need to create a cursor when querying a sqlite database?
  24. How to open and convert sqlite database to pandas dataframe
  25. sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings
  26. SQLite, python, unicode, and non-utf data
  27. Python and SQLite: insert into table
  28. Pycharm environment different than command line
  29. Can’t change system default SQLite binary
  30. Django: sudden DB reset after Heroku sleep

Leave a Comment