SQLite3 is a lightweight, serverless, and self-contained database engine that is widely used for small to medium-sized applications. It is especially popular in embedded systems, mobile applications, and desktop software. In this guide, we will explore how to connect Python to SQLite using the built-in sqlite3 module.
Before getting started, ensure that you have Python installed on your system. Python typically comes with the sqlite3 module, which simplifies the process of working with SQLite databases.
Step 1: Import the sqlite3 Module
pip install sqlite3
import sqlite3The sqlite3 module is part of the Python standard library and provides an interface to interact with SQLite databases.
Step 2: Connect to the SQLite Database
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('USER.db')The connect function is used to establish a connection to the SQLite database. If the specified database file (USER.db in this case) doesn’t exist, it will be created.
Step 3: Create a Cursor Object
# Create a cursor object to execute SQL commands
cursor = conn.cursor()The cursor is a virtual pointer that allows you to execute SQL commands and retrieve results.
Step 4: Create a Table (if it doesn’t exist)
cursor.execute(''' CREATE TABLE IF NOT EXISTS
users( id INTEGER PRIMARY KEY, name TEXT, age INTEGER ) ''')Use the execute method to run SQL commands. In this example, we create a table named users with columns id, name, and age. The IF NOT EXISTS clause ensures that the table is only created if it doesn’t already exist.
Step 5: Insert Data into the Table
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('GK', 25)) cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('AK', 30))Parameterized queries are used to insert data into the users table. This approach helps prevent SQL injection.
Step 6: Commit the Changes
conn.commit()After executing SQL commands, you need to commit the changes to persist them in the database.
Step 7: Query and Print Data
cursor.execute("SELECT * FROM users") rows = cursor.fetchall() for row in rows: print(row)Retrieve data from the users table and print it to the console. The fetchall method fetches all the rows returned by the query.
Step 8: Close the Cursor and Connection
cursor.close() conn.close()Always close the cursor and the connection to free up resources and ensure data integrity.
Must Read..
Python Database Connection :Complete Guide on Python Integration with Oracle and SQL Databases
Full code
import sqlite3
# Specify the database file
database_file = 'your_database.db'
# Try to close any existing connection
try:
if conn:
conn.close()
except NameError:
pass # If the connection variable doesn't exist, ignore the exception
# Create a new connection
conn = sqlite3.connect(database_file)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
# Your SQL operations go here
cursor.execute('''
CREATE TABLE IF NOT EXISTS user (
"No" NUMERIC,
"Name" TEXT,
PRIMARY KEY("No")
)
''')
# Insert data into the table
cursor.execute("INSERT INTO user (No, Name) VALUES (?, ?, ?)", (1, 'Gk'))
# Commit the changes
conn.commit()
# Close the cursor and connection
cursor.close()
conn.close()