1. What is a Database?
A database is an organized system that stores, manages, and retrieves data
efficiently.
It can be relational (SQL) or non-relational (NoSQL).
Relational Databases (SQL): -Store data in tables (rows and columns),
examples: SQLite, MySQL, PostgreSQL, Oracle, SQL Server.
Non-relational Databases (NoSQL): Store data as documents, key-value
pairs, or graphs examples: MongoDB, Firebase.
Python works primarily with SQL (relational) databases using standard libraries.
2. What is Python SQL Database Integration?
It means connecting your Python program to a database so you can:
1. Store data permanently
2. Run SQL queries
3. Analyze and visualize results
You use Python as the interface and SQL as the query language.
4. Python Libraries Used for SQL Databases
Library Description Example Database
sqlite3 Built-in Python library for lightweight SQLite
local databases
[Link] Official MySQL driver for Python MySQL
psycopg2 Popular adapter for PostgreSQL PostgreSQL
SQLAlchemy Universal ORM (Object Relational SQLite, MySQL,
Mapper) supporting many databases PostgreSQL
pandas Imports or exports SQL data directly All
into DataFrames
5. How to Connect Python to SQL Database
Example: SQLite (local file)
import sqlite3
conn = [Link]('[Link]') # Creates/opens database
cursor = [Link]() # Cursor lets you run SQL commands
Example: MySQL (server-based)
import [Link]
conn = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
cursor = [Link]()
6. What is Cursor
A cursor is like a pointer or controller that allows your Python program to interact
with the database. You use it to execute SQL commands, fetch results, and
traverse data stored in tables.
In simple terms:
“A cursor is a control structure that lets you run SQL queries and move through
database records one by one.”
Why We Need a Cursor
When you connect to a database in Python, e.g., using [Link]() or
[Link](), the connection only gives you access to the database, not
to specific queries.
You need a cursor object to:
Execute SQL statements (CREATE, INSERT, SELECT, etc.)
Fetch data from result sets
Manage multiple query executions
7. SQL Queries in Python
Operation SQL Query Purpose
Create CREATE DATABASE school; Makes a new database.
Database
Use USE school; Selects active database.
Database
Create CREATE TABLE students(id INT, Defines a new table.
Table name TEXT, age INT);
Insert Data INSERT INTO students VALUES (1, Adds a row of data.
'Asha', 20);
Select Data SELECT * FROM students; Displays all records.
Where SELECT * FROM students WHERE Filters rows.
Clause age > 18;
Update Data UPDATE students SET age=21 Edits existing records.
WHERE name='Asha';
Delete Data DELETE FROM students WHERE Removes specific
id=1; records.
Drop Table DROP TABLE students; Deletes table structure.
Order SELECT * FROM students ORDER Sorts output.
Results BY age DESC;
Group Data SELECT grade, COUNT(*) FROM Summarizes data.
students GROUP BY grade;
Join Tables SELECT [Link], [Link] FROM Combines related data
students s JOIN marks m ON from two tables.
[Link]=[Link];
Distinct SELECT DISTINCT grade FROM Removes duplicates.
Values students;
Limit Rows SELECT * FROM students LIMIT 5; Shows top 5 records.
Like SELECT * FROM students WHERE Pattern matching search.
Operator name LIKE 'A%';
Count SELECT COUNT(*) FROM students; Counts total rows.
Average SELECT AVG(age) FROM students; Calculates average.
Max/Min SELECT MAX(age), MIN(age) Finds highest/lowest.
FROM students;