Introduction to Python’s sqlite3 Module
Python’s sqlite3 module provides a lightweight disk-based database that doesn’t require a separate server process. This makes it an excellent choice for applications that need to handle low to moderate traffic, as well as for prototyping. In this post, we’ll explore how to use this module to interact with SQLite databases.
Connecting to a Database
To connect to a SQLite database using the sqlite3 module, you can use the connect() function. This function takes the name of the database you want to connect to as its argument. If the database does not exist, it will be created.
import sqlite3
conn = sqlite3.connect('example.db')Creating a Table
Once you’re connected to a database, you can create a table using the execute() method of a cursor object. Here’s an example:
c = conn.cursor()
c.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')Inserting Data
To insert data into a table, you can use the execute() method again. This time, you’ll need to use a SQL INSERT statement:
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")Retrieving Data
Finally, to retrieve data from a table, you can use a SQL SELECT statement with the execute() method:
c.execute('SELECT * FROM stocks WHERE symbol=?', ('RHAT',))Conclusion
The sqlite3 module in Python provides a simple and efficient way to work with SQLite databases. Its lightweight nature makes it ideal for prototyping and for applications that can handle low to moderate traffic. By learning how to connect to a database, create a table, insert data, and retrieve data, you can start leveraging the power of databases in your Python applications.