Skip to content

sqlite3

SQLite3 is a python library that provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249.

Usage⚑

To use the module, you must first create a Connection object that represents the database, and a Cursor one to interact with it. Here the data will be stored in the example.db file:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

Once we have a cursor we can execute the different SQL statements and the save them with the commit method of the Connection object. Finally we can close the connection with close.

# Create table
cursor.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
cursor.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()

Get columns of a query ⚑

cursor = connection.execute('select * from bar')
names = [description[0] for description in cursor.description]

Get a list of the tables ⚑

sql_query = """SELECT name FROM sqlite_master
  WHERE type='table';"""
cursor = sqliteConnection.cursor()
cursor.execute(sql_query)
print(cursor.fetchall())

Regexp ⚑

SQLite needs the user to define a regexp function to be able to use the filter.

import sqlite3
import re

def regexp(expr, item):
    reg = re.compile(expr)
    return reg.search(item) is not None

conn = sqlite3.connect(':memory:')
conn.create_function("REGEXP", 2, regexp)
cursor = conn.cursor()

References⚑

Docs

Last update: 2022-05-13