When you’re ready to work with serious data, you need a proper database. This guide will show you how to get started with SQLite3 and Python. SQLite3 is a fantastic choice for many projects because it’s a self-contained, serverless, single-file database. This means there’s no complex server setup, making it incredibly easy to integrate into your Python applications.
Table of Contents
💻 Connecting to a Database and Executing Queries
Python has a built-in module called sqlite3, so you don’t need to install anything extra. Connecting to a database is as simple as calling one function.
import sqlite3
# This creates or connects to a database file named 'LXF.sqlite'
conn = sqlite3.connect('LXF.sqlite')
# Create a cursor object to execute SQL commands
c = conn.cursor()
# Execute a query
c.execute("SELECT count(*) from sqlite_master")
# Fetch and print the result
for row in c:
print(row)
# Always close the connection when you're done
conn.close()- The
sqlite3.connect()function opens a connection. If the database file doesn’t exist, it will be created automatically. - You must create a cursor object to run queries. This object helps manage the results.
- The
.execute()method runs your SQL command as a string. - To see the results of a
SELECTquery, you simply iterate over the cursor object.
💻 Basic CRUD Operations in Python
CRUD stands for Create, Read, Update, and Delete—the four basic operations of data management. Here’s how you can perform them in Python with SQLite3.
- Create a Table:
conn.execute('''CREATE TABLE ISSUES (ID INTEGER PRIMARY KEY AUTOINCREMENT, NUMBER INT NOT NULL, COMMENTS CHAR(50));''') - Insert Data: To save your changes after inserting or modifying data, you must call
conn.commit().conn.execute("INSERT INTO ISSUES (NUMBER, COMMENTS) VALUES (202, 'Best issue ever!')")conn.commit() - Update Data:
conn.execute("UPDATE ISSUES set YEAR = 2016 where YEAR = 2015")conn.commit() - Delete Data:
conn.execute("DELETE FROM ISSUES where ID = 2")conn.commit()
Using the AUTOINCREMENT keyword on a primary key is very handy, as it lets SQLite3 automatically handle unique IDs for your records.
More Topics
- Redis Guide: The High-Speed In-Memory Data Store
- Riak NoSQL Guide: What’s the Big Deal?
- MongoDB Guide: Build a Blog with Python and Bottle
- MongoDB Guide: An Admin’s Guide to Maximum Power
- MongoDB Guide: Using Native Drivers with Python and Ruby
- MariaDB Guide: The Open Source MySQL Alternative
- A Beginner’s Guide to Go: Explore Functions
