Python Sqlite3 Module
The sqlite3 module provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249. It allows you to interact with SQLite databases, which are lightweight, disk-based databases that do not require a separate server process.
Importing the Module
import sqlite3Basic Usage
Connecting to a Database
To use the module, you must first create a connection object that represents the database.
import sqlite3
# Connect to a database file (creates it if it doesn't exist)
con = sqlite3.connect('example.db')
# You can also create an in-memory database
# con = sqlite3.connect(':memory:')Creating a Cursor
Once you have a connection, you can create a cursor object and call its execute() method to perform SQL commands.
cur = con.cursor()Creating Tables
# Create table
cur.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')Inserting Data
# Insert a row of data
cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
# Save (commit) the changes
con.commit()Closing the Connection
We can also close the connection if we are done with it.
con.close()Retrieving Data
To retrieve data after executing a SELECT statement, you can treat the cursor as an iterator, call the cursor's fetchone() method to retrieve a single matching row, or call fetchall() to get a list of the matching rows.
for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
print(row)Parameter Substitution
Use ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor's execute() method. Never use string operations to assemble your SQL statements, as it makes your program vulnerable to SQL injection attacks.
# Incorrect (Vulnerable to SQL Injection)
symbol = 'RHAT'
# cur.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
# Correct
cur.execute("SELECT * FROM stocks WHERE symbol=?", (symbol,))Row Objects
By default, sqlite3 returns rows as tuples. You can use sqlite3.Row to access columns by name.
con.row_factory = sqlite3.Row