Connecting SQLite with Python
SQLite is a lightweight, fast, and serverless database engine that’s ideal for small to medium-sized applications. Python’s built-in support for SQLite makes database operations seamless and efficient. This guide walks you through the process of connecting SQLite with Python, creating tables, and performing basic database operations.
Complete Python Course with Advance topics:-Click here
1. Prerequisites
Before we dive in, ensure you have Python and SQLite installed on your system. Follow the steps below for installation.
2. Install Python
To install or update Python on your machine, do the following commands:
sudo apt-get update
sudo apt-get upgrade python
When prompted, press y. The installation will complete within seconds.
3. Install SQLite
To install SQLite, use the following command:
sudo apt-get install sqlite3 libsqlite3-dev
Verify the installation by entering:
sqlite3
You should see a prompt with the SQLite version.
4. Create a Database
Navigate to your desired folder and create a database file:
sqlite3 database.db
This creates a file named database.db in the specified folder. To verify its creation, enter the SQLite prompt and type:
.databases
Note: Starting from Python version 2.5.x, the SQLite connection module (sqlite3) is included by default, so no additional installation is required.
5. Connecting SQLite with Python
Make a file called connect.py and add the following code to link SQLite with Python:
#!/usr/bin/python
import sqlite3
# Connect to the database
conn = sqlite3.connect('example.db')
print("Opened database successfully")
Execute the script:
python connect.py
This establishes a connection with the database and confirms it with a success message.
6. Create a Table
To create a table in the database, write the following code in a file named createtable.py:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('example.db')
print("Opened database successfully")
# Create a table
conn.execute('''CREATE TABLE Employees
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print("Table created successfully")
conn.close()
Run the script:
python createtable.py
This creates a table named Employees in the example.db database.
7. Insert Records
To insert records into the Employees table, create a file named insert.py with the following code:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('example.db')
print("Opened database successfully")
# Insert records
conn.execute("INSERT INTO Employees (ID, NAME, AGE, ADDRESS, SALARY) \
VALUES (1, 'Ajeet', 27, 'Delhi', 20000.00)")
conn.execute("INSERT INTO Employees (ID, NAME, AGE, ADDRESS, SALARY) \
VALUES (2, 'Allen', 22, 'London', 25000.00)")
conn.execute("INSERT INTO Employees (ID, NAME, AGE, ADDRESS, SALARY) \
VALUES (3, 'Mark', 29, 'California', 200000.00)")
conn.execute("INSERT INTO Employees (ID, NAME, AGE, ADDRESS, SALARY) \
VALUES (4, 'Kanchan', 22, 'Ghaziabad', 65000.00)")
conn.commit()
print("Records inserted successfully")
conn.close()
Run the script:
python insert.py
The records are now inserted into the Employees table.
8. Select Records
To fetch and display the records, create a file named select.py with the following code:
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('example.db')
print("Fetching records:")
# Fetch records
data = conn.execute("SELECT * FROM Employees")
for row in data:
print(f"ID = {row[0]}")
print(f"NAME = {row[1]}")
print(f"AGE = {row[2]}")
print(f"ADDRESS = {row[3]}")
print(f"SALARY = {row[4]}\n")
conn.close()
Run the script:
python select.py
This script displays all records in the Employees table.
9. Update and Delete Records
You can also perform update and delete operations. For instance:
Update Example:
conn.execute("UPDATE Employees SET SALARY = 30000.00 WHERE ID = 1")
conn.commit()
Delete Example:
conn.execute("DELETE FROM Employees WHERE ID = 1")
conn.commit()
10. Wrapping Up
By following these steps, you can effortlessly use SQLite with Python for database operations. This guide serves as a foundation for developing more complex database-driven applications.
Download New Real Time Projects :-Click here
PHP PROJECT:- CLICK HERE
sqlite3 python
python sqlite3 w3schools
Connecting SQLite with Python
Connecting SQLite with Python
python sqlite3 tutorial
pip install sqlite3
install sqlite3 python
python: sqlite3 connect to database with password
sqlalchemy sqlite
connect to sqlite database python sqlalchemy
connecting sqlite with python pdf
Connecting SQLite with Python: A Step-by-Step Guide
connecting sqlite with python w3schools
connecting sqlite with python example
