Python Database Connectivity (SQLite &
MySQL) – Complete Study Notes
Introduction
Database connectivity is a fundamental concept in Python that enables programs to interact
with structured data stored in databases. Instead of storing data in simple files, modern
applications rely on databases to manage large volumes of data efficiently.
In real-world applications such as banking systems, e-commerce platforms, and student
management systems, data must be stored securely and accessed quickly. Python provides
tools to connect with databases and perform operations on stored data.
Understanding database connectivity is especially important for data analysts and backend
developers because most real-world data is stored in relational databases.
Definition
Database connectivity refers to the process of connecting a Python program to a database
system in order to perform operations such as inserting, retrieving, updating, and deleting
data.
This connection is established using special libraries or connectors that act as an interface
between Python and the database system.
Once connected, Python can execute SQL queries to interact with the database and manage
data effectively.
Types of Databases
Databases are broadly classified into relational and non-relational types. Relational
databases store data in tables with rows and columns and use SQL for operations.
Examples of relational databases include SQLite, MySQL, and PostgreSQL. These are widely
used in structured applications.
Non-relational databases store data in flexible formats such as JSON documents or key-
value pairs, but Python beginners typically start with relational databases.
SQLite Overview
SQLite is a lightweight, file-based relational database that comes built into Python. It does
not require a separate server, making it ideal for beginners and small applications.
, It stores the entire database in a single file, which makes it portable and easy to manage.
SQLite is commonly used in local applications, testing environments, and small-scale
systems.
Connecting to SQLite
To connect to an SQLite database, Python uses the sqlite3 module. This module provides
functions to establish a connection and execute SQL commands.
The connection object represents the database, while the cursor object is used to execute
queries.
Example:
import sqlite3
conn = sqlite3.connect('students.db')
cursor = conn.cursor()
Creating Tables
Tables are used to organize data in a structured format. Each table contains rows and
columns.
A CREATE TABLE query is used to define the structure of a table.
Example:
cursor.execute('CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, marks
INTEGER)')
conn.commit()
Inserting Data
Data is inserted into tables using the INSERT INTO statement.
Each record is stored as a row in the table.
Example:
cursor.execute('INSERT INTO students (name, marks) VALUES ("Rahul", 85)')
conn.commit()
Fetching Data
Data can be retrieved using the SELECT statement.
The fetchall() method retrieves all rows from the query result.
MySQL) – Complete Study Notes
Introduction
Database connectivity is a fundamental concept in Python that enables programs to interact
with structured data stored in databases. Instead of storing data in simple files, modern
applications rely on databases to manage large volumes of data efficiently.
In real-world applications such as banking systems, e-commerce platforms, and student
management systems, data must be stored securely and accessed quickly. Python provides
tools to connect with databases and perform operations on stored data.
Understanding database connectivity is especially important for data analysts and backend
developers because most real-world data is stored in relational databases.
Definition
Database connectivity refers to the process of connecting a Python program to a database
system in order to perform operations such as inserting, retrieving, updating, and deleting
data.
This connection is established using special libraries or connectors that act as an interface
between Python and the database system.
Once connected, Python can execute SQL queries to interact with the database and manage
data effectively.
Types of Databases
Databases are broadly classified into relational and non-relational types. Relational
databases store data in tables with rows and columns and use SQL for operations.
Examples of relational databases include SQLite, MySQL, and PostgreSQL. These are widely
used in structured applications.
Non-relational databases store data in flexible formats such as JSON documents or key-
value pairs, but Python beginners typically start with relational databases.
SQLite Overview
SQLite is a lightweight, file-based relational database that comes built into Python. It does
not require a separate server, making it ideal for beginners and small applications.
, It stores the entire database in a single file, which makes it portable and easy to manage.
SQLite is commonly used in local applications, testing environments, and small-scale
systems.
Connecting to SQLite
To connect to an SQLite database, Python uses the sqlite3 module. This module provides
functions to establish a connection and execute SQL commands.
The connection object represents the database, while the cursor object is used to execute
queries.
Example:
import sqlite3
conn = sqlite3.connect('students.db')
cursor = conn.cursor()
Creating Tables
Tables are used to organize data in a structured format. Each table contains rows and
columns.
A CREATE TABLE query is used to define the structure of a table.
Example:
cursor.execute('CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, marks
INTEGER)')
conn.commit()
Inserting Data
Data is inserted into tables using the INSERT INTO statement.
Each record is stored as a row in the table.
Example:
cursor.execute('INSERT INTO students (name, marks) VALUES ("Rahul", 85)')
conn.commit()
Fetching Data
Data can be retrieved using the SELECT statement.
The fetchall() method retrieves all rows from the query result.
