This article is part 1 of 2 in the series Python SQLite Tutorial

Last Updated: Thursday 12th December 2013

SQLite3 is a very easy to use database engine. It is self-contained, serverless, zero-configuration and transactional. It is very fast and lightweight, and the entire database is stored in a single disk file. It is used in a lot of applications as internal data storage. The Python Standard Library includes a module called "sqlite3" intended for working with this database. This module is a SQL interface compliant with the DB-API 2.0 specification.

Using Python's SQLite Module

To use the SQLite3 module we need to add an import statement to our python script:

Connecting SQLite to the Database

We use the function sqlite3.connect to connect to the database. We can use the argument ":memory:" to create a temporary DB in the RAM or pass the name of a file to open or create it.

When we are done working with the DB we need to close the connection:

Creating (CREATE) and Deleting (DROP) Tables

In order to make any operation with the database we need to get a cursor object and pass the SQL statements to the cursor object to execute them. Finally it is necessary to commit the changes. We are going to create a users table with name, phone, email and password columns.

To drop a table:

Please note that the commit function is invoked on the db object, not the cursor object. If we type cursor.commit we will get AttributeError: 'sqlite3.Cursor' object has no attribute 'commit'

Inserting (INSERT) Data into the Database

To insert data we use the cursor to execute the query. If you need values from Python variables it is recommended to use the "?" placeholder. Never use string operations or concatenation to make your queries because is very insecure. In this example we are going to insert two users in the database, their information is stored in python variables.

The values of the Python variables are passed inside a tuple. Another way to do this is passing a dictionary using the ":keyname" placeholder:

If you need to insert several users use executemany and a list with the tuples:

If you need to get the id of the row you just inserted use lastrowid:

Retrieving Data (SELECT) with SQLite

To retrieve data, execute the query against the cursor object and then use fetchone() to retrieve a single row or fetchall() to retrieve all the rows.

The cursor object works as an iterator, invoking fetchall() automatically:

To retrive data with conditions, use again the "?" placeholder:

Updating (UPDATE) and Deleting (DELETE) Data

The procedure to update or delete data is the same as inserting data:

Using SQLite Transactions

Transactions are an useful property of database systems. It ensures the atomicity of the Database. Use commit to save the changes:

Or rollback to roll back any change to the database since the last call to commit:

Please remember to always call commit to save the changes. If you close the connection using close or the connection to the file is lost (maybe the program finishes unexpectedly), not committed changes will be lost.

SQLite Database Exceptions

For best practices always surround the database operations with a try clause or a context manager:

In this example we used a try/except/finally clause to catch any exception in the code. The finally keyword is very important because it always closes the database connection correctly. Please refer to this article to find more about exceptions. Please take a look to:

This is called a catch-all clause, This is used here only as an example, in a real application you should catch a specific exception such as IntegrityError or DatabaseError, for more information please refer to DB-API 2.0 Exceptions.

We can use the Connection object as context manager to automatically commit or rollback transactions:

In the example above if the insert statement raises an exception, the transaction will be rolled back and the message gets printed; otherwise the transaction will be committed. Please note that we call execute on the db object, not the cursor object.

SQLite Row Factory and Data Types

The following table shows the relation between SQLite datatypes and Python datatypes:

  • None type is converted to NULL
  • int type is converted to INTEGER
  • float type is converted to REAL
  • str type is converted to TEXT
  • bytes type is converted to BLOB

The row factory class sqlite3.Row is used to access the columns of a query by name instead of by index:

To Practice: Try this interactive course on the basics of Lists, Functions, Packages and NumPy in Python.

  • Eze – intermediate programmer

    Thank you very much for this super, duper, ultra, mega, omega, extremely, supremely, asdfasdfly EASY to understand tutorial! I can’t find any better beginner’s tutorial than this πŸ™‚

  • EOE

    Well-written article! It is worth noticing that lastrowid returns ‘None’ when used after an executemany(). Cheers.

  • Excellent, well written and easy to understand, thank you! I wasn’t aware of some of these, such as rollback.

  • K. Chris C.

    Thank you.

  • Thanks for the tutorial. Very informative

  • Edward John Moore

    How have you made this so easy to understand and put into practice, a truly great starting point for anybody,

  • Slavik Timoschenko

    Awesome :). Very powerful! Love it;

  • SE Schoen

    I spent two days reading every tutorial I could find. This one is far and away the best. Short, sweet, to the point, and complete without going overboard. This is the one that goes in my reference book.

  • sergio

    When I write into the gui db=sqlite3.connect(‘data/mydb’)

    I get:
    Traceback (most recent call last):
    File “”, line 1, in
    sqlite3.OperationalError: unable to open database file

    • Hi Sergio, the sqlite3.connect() function takes the parameter as the path to the database file you want to create. In the case of sqlite3.connect('data/mydb'), this means create the database file “mydb” in the directory “data”. I’m guessing there’s no “data” directory present. Try changing “data/mydb” to just “mydb”, or create a “data” directory.

  • Suman K.C

    Thank you !!

  • jean francois Giron

    thanks, its the best python / sqlite tutorial i found

  • Aldo

    Very clear, thanks.

  • Alex

    Simple and at the point tutorial, Thank you!

  • Umar Yusuf

    Good sqlite database engine tutorial. Thanks

  • Marcos Tolcachir

    Sorry , i must be stupid, i created a table with the values(id INTEGER PRIMARY KEY, nombre text, apellido text, numero text) and when i only add the values for every except id columns it tells me that(sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 3 supplied.) i dont know if i’m doing something wrong

  • ssa Driac

    But how to open *.sqlite-wal file?

  • Chaz Clark

    What do you do if you want to save a tuple of URL text strings or a OS file locations in a Sqlite table? I am having a hard time figuring out how to deal with all the / in the string so it keeps the whole string as one value.


    files = (‘home/documents/myfiles/myfile.doc’,’home/documents/myfiles/myfile2.doc’)

    placeholders = ‘, ‘.join(‘?’ * len(files)
    sql = ‘INSERT INTO {tn} ({nf} VALUES ({vs})’

    .format(tn=tableName, nf=column, vs=placeholders))

    c.executemany(sql, files)

  • This article is useful for newbee.After I read and practice it,I am not afraid about sqlite3.It is awesome.

  • arun

    realy amazing tutorial man love it cheerss !!! πŸ™‚

  • arun

    but i have one doubt man cur.execute(“select * from table_name where aa=:aa and date=:date” ,{“aa”: value1, “date”:date}), here in date i want to pass another parameter like ‘<=' for SELECT * FROM 'table_name' where aa='val1' and date <= '2016-06-15 23:59:59'. I need tis type of python query ….