Python SQLite (sqlite3)
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. Python comes with a built-in module sqlite3 to interact with SQLite databases.
Importing the Module
import sqlite3Connecting to a Database
To use SQLite, you must first create a connection object that represents the database.
import sqlite3
# Connect to a database (or create one if it doesn't exist)
conn = sqlite3.connect('example.db')
# Create a cursor object
c = conn.cursor()Creating a Table
# Create table
c.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')
# Save (commit) the changes
conn.commit()Inserting Data
# 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()Using Variables
To insert variables safely (preventing SQL injection), use the ? placeholder.
t = ('2006-01-06', 'BUY', 'IBM', 1000, 45.00)
c.execute('INSERT INTO stocks VALUES (?,?,?,?,?)', t)
conn.commit()Querying Data
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print(c.fetchone())
# Select all
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
print(row)Closing the Connection
# 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()Using Context Managers
It is best practice to use context managers to handle the connection and transactions automatically.
import sqlite3
# Using 'with' automatically closes the connection
# However, for transactions (commit/rollback), you might need to handle them explicitly or use the connection as a context manager for transactions specifically.
conn = sqlite3.connect('example.db')
with conn:
# Automatically commits if no exception, otherwise rolls back
conn.execute("INSERT INTO stocks VALUES ('2006-01-07', 'BUY', 'MSFT', 100, 65.10)")
conn.close()