How to Connect Python to SqlLite3 With Example

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 sqlite3

The 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


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
    if conn:
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
        "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

# Close the cursor and connection

The Technology Behind ChatGPT 3.5

Leave a Comment