SQLite is a lightweight, disk-based database. Since it does not require a separate database server, it is often used for prototyping or for small applications that are often used by a single user or by one user at a given time.

import sqlite3

conn = sqlite3.connect("users.db")
c = conn.cursor()

c.execute("CREATE TABLE user (name text, age integer)")

c.execute("INSERT INTO user VALUES ('User A', 42)")
c.execute("INSERT INTO user VALUES ('User B', 43)")

conn.commit()

c.execute("SELECT * FROM user")
print(c.fetchall())

conn.close()

The code above connects to the database stored in the file named users.db, creating the file first if it doesn’t already exist. You can interact with the database via SQL statements.

The result of this example should be:

[(u'User A', 42), (u'User B', 43)]

The SQLite Syntax: An in-depth analysis

Getting started

  1. Import the sqlite module using
>>> import sqlite3
  1. To use the module, you must first create a Connection object that represents the database. Here the data will be stored in the example.db file:
>>> conn = sqlite3.connect('users.db')

Alternatively, you can also supply the special name `:memory:` to create a temporary database in RAM, as follows:

>>> conn = sqlite3.connect(':memory:')
  1. Once you have a Connection, you can create a Cursor object and call its execute() method to perform SQL commands:
##

   c = conn.cursor()

   # Create table
   c.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')
  
   # Insert a row of data
   c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
  
   # Save (commit) the changes
   conn.commit()
  
   # We can also close the connection if we are done with it.
   # Just be sure any changes have been committed or they will be lost.
   conn.close()

Important Attributes and Functions of Connection

  1. isolation_level

It is an attribute used to get or set the current isolation level. None for autocommit mode or one of DEFERRED, IMMEDIATE or EXCLUSIVE.

  1. cursor

The cursor object is used to execute SQL commands and queries.

  1. commit()

Commits the current transaction.