Advanced Python
Chapter 05:Python with Database
• What is Database and DBMS
• Why need of database
• Different libraries and modules to connect to a database
• Setup Environment
• Setting up the connection to a database
• Create Database and Table
• CRUD operations (Insert, select, update delete)
• Where clause
• Limit
Advanced Python
Chapter 05:Python with Database
What is database?
A database is a collection of organized data that can be easily accessed, managed and updated.
Examples of databases include Oracle, MySQL, SQL Server, MongoDB, and PostgreSQL.
What is DBMS?
DBMS (Database Management System) is a software system that manages and organizes data
stored in a database. Examples of DBMS include Oracle Database, MySQL, Microsoft SQL
Server, MongoDB, and PostgreSQL.
Advanced Python
Chapter 05:Python with Database
Why need of database?
Databases provide a centralized and organized way to store and manage data, making it easier to
access and manipulate large amounts of data efficiently.
Advanced Python
Chapter 05:Python with Database
Different libraries and modules to connect to a database
Here are the databases that these Python database libraries are commonly used to connect to:
1. SQLAlchemy - MySQL, PostgreSQL, Oracle, SQLite, and others
2. Psycopg2 - PostgreSQL
3. PyMySQL – MySQL
4. mysql-connector-python - MySQL
5. mysqlclient - MySQL
6. PyODBC - SQL Server, Oracle, Access, and others
7. cx_Oracle - Oracle
8. mysql-connector-python - MySQL
9. sqlite3 - SQLite
10. peewee - SQLite, MySQL, PostgreSQL, and others
11. pydal - MySQL, PostgreSQL, SQLite, and others
12. dataset - MySQL, PostgreSQL, SQLite, and others
Advanced Python
Chapter 05:Python with Database
Steps to Set up PyMySQL
1) Install PyMySQL: You can install PyMySQL using pip, the Python package manager. Open a terminal or
command prompt and run the following command:
pip install PyMySQL
2) Import PyMySQL: Once PyMySQL is installed, you can import it in your Python code using the following
statement:
import pymysql
Advanced Python
Chapter 05:Python with Database
3) Connect to the database (Setting up the connection to a database )
Connect to a MySQL database: To connect to a MySQL database using PyMySQL, you need to create a
connection object by providing the database host, username, password, and database name.
import pymysql
connection = [Link](
host='localhost',
user='username',
password='password',
db='database_name‘ )
Advanced Python
Chapter 05:Python with Database
4) Create a cursor object:
Once you have a connection object, you can execute queries using a cursor object.
cursor = [Link]()
Advanced Python
Chapter 05:Python with Database
5) Execute queries:
sql = 'SELECT * FROM my_table'
[Link](sql)
# Fetch the results
results = [Link]()
# Print the results
for row in results:
print(row)
# Close the connection
[Link]()
Advanced Python
Chapter 05:Python with Database
Create Database and Table
Steps to create a MySQL database and table using PyMySQL:
Step1: Importing PyMySQL
Step2: Connect to the MySQL Server
Step3: Cursor object
Step4: Create a Database: Use the execute() method of the connection object to execute SQL commands to
create a database.
sql = "CREATE DATABASE mydatabase"
[Link](sql)
Advanced Python
Chapter 05:Python with Database
Committing: Committing refers to making permanent changes to the database after executing
one or more SQL statements.
[Link]()
Close the Connection: Once you are done with creating the database and table, use the close()
method of the connection object to close the connection to the MySQL server.
[Link]()
Advanced Python
Chapter 05:Python with Database
Create a Table: Use the execute() method of the cursor object to execute SQL commands to create a table in the
selected database. The following code creates a table named customers with three columns:
sql = "CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
address VARCHAR(255) )"
[Link](sql)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Example 01:
sql = "
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255) ) "
[Link](sql)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Example 02: Create a table with two columns - id and description.
sql = ''CREATE TABLE tasks (
id INT AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255) ) ''
[Link](sql)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Example 03:
sql = ''
CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
author VARCHAR(255),
published DATE ) ''
[Link](sql)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
CRUD operations (Insert, select, update delete)
CRUD is an acronym for Create, Read, Update, and Delete, which are the four basic operations performed on
data in a database.
▪ Create (Inserting record into database)
▪ Read ( Selecting or fetching record from database)
▪ Update ( Make modification in record in a database table)
▪ Delete ( Removing record from database table record)
Advanced Python
Chapter 05:Python with Database
Create (Insert):
Definition: Inserting new data into a table in a database.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Example:
Inserting a new customer record into a customers table in a MySQL database:
INSERT INTO customers (name, email, phone) VALUES ('John Ali', 'john@[Link]',
'123-456-7890');
Advanced Python
Chapter 05:Python with Database
Example 1: Insert a single record into a users
# Create a cursor object
table.
cursor = [Link]()
import pymysql
# Insert a record
# Connect to the database
sql = ''
connection = [Link](
INSERT INTO users (name, email, password)
host='localhost',
VALUES (‘Faisal Zamir',
user='username',
‘faisalzamir@[Link]', 'password123') ''
password='password',
[Link](sql)
db='database_name‘ )
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Example 2: Insert multiple records into a tasks table.
import pymysql [Link](sql)
# Create a cursor object [Link]()
cursor = [Link]() [Link]()
# Insert multiple records
sql = " INSERT INTO tasks (description, priority)
VALUES ('Task 1', 'High'),
('Task 2', 'Medium'),
('Task 3', 'Low') ''
Advanced Python
Chapter 05:Python with Database
Example 3: Insert data into a books table with a SELECT query.
# Create a cursor object
cursor = [Link]()
# Insert data with a SELECT query
sql = '' INSERT INTO books (title, author, published)
SELECT title, author, published_date FROM book_details ''
[Link](sql)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Inserting a single row of data:
import pymysql
# Create a cursor object
cursor = [Link]()
# Insert a new customer
sql = "INSERT INTO customers (name, email, phone) VALUES (%s, %s, %s)"
values = ('John Doe', 'john@[Link]', '123-456')
[Link](sql, values)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Inserting multiple rows of data: values = [
import pymysql ('Product 1', 9.99),
cursor = [Link]() ('Product 2', 19.99),
# Insert multiple products ('Product 3', 29.99) ]
sql = "INSERT INTO products (name, price) [Link](sql, values)
VALUES (%s, %s)"
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Read (Select):
Definition: Retrieving data from a table in a database.
Syntax:
SELECT column1, column2, ... FROM table_name WHERE condition;
Example:
Retrieving all customer records from a customers table in a MySQL database:
SELECT * FROM customers;
Advanced Python
Chapter 05:Python with Database
Example 1: Select all columns and rows from a table
import pymysql
cursor = [Link]()
sql = 'SELECT * FROM users'
[Link](sql)
# Fetch all rows
rows = [Link]()
for row in rows:
print(row)
[Link]()
Advanced Python
Chapter 05:Python with Database
Parameterized Query or Prepared Statement is a technique used in SQL queries to pass parameters to the query
instead of including them directly in the query string.
This technique helps to prevent SQL injection attacks and improves query performance.
sql = 'SELECT * FROM users WHERE name = %s AND email = %s'
data = ('John Doe', 'johndoe@[Link]')
[Link](sql, data)
# Fetch the results of the query
results = [Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Example 2: Select specific columns and rows with a condition
import pymysql
cursor = [Link]()
# Execute a select query with a condition and LIMIT
sql = 'SELECT name, email FROM users WHERE id > %s'
[Link](sql, (2,))
# Fetch all rows
rows = [Link]()
# Print the results
for row in rows:
print(row)
[Link]()
Advanced Python
Chapter 05:Python with Database
Update:
Definition: Modifying existing data in a table in a database.
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Example:
Updating the email address of a customer with id = 1 in a customers table in a MySQL database:
UPDATE customers SET email = 'new_email@[Link]' WHERE id = 1;
Advanced Python
Chapter 05:Python with Database
Example 1: Update a single column in a table for a specific row
# Update a single column in a table for a specific row
sql = 'UPDATE users SET email = %s WHERE id = %s'
data = ('newemail@[Link]', 1)
[Link](sql, data)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Example 2: Update multiple columns in a table for a specific row
sql = 'UPDATE users SET name = %s, email = %s WHERE id = %s'
data = ('New Name', 'newemail@[Link]', 1)
[Link](sql, data)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Delete:
Definition: Removing data from a table in a database.
Syntax:
DELETE FROM table_name WHERE condition;
Example:
Deleting a customer record with id = 1 from a customers table in a MySQL database:
DELETE FROM customers WHERE id = 1;
Advanced Python
Chapter 05:Python with Database
Example 1: Delete a specific row from a table
import pymysql
cursor = [Link]()
sql = 'DELETE FROM users WHERE id = %s‘ # Delete a specific row from a table
data = (1,)
[Link](sql, data)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
Example 2: Delete all rows from a table
import pymysql
cursor = [Link]()
sql = 'DELETE FROM users‘ # Delete all rows from a table
[Link](sql)
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
LIMIT clause
The LIMIT clause is a SQL statement that is used to limit the number of records returned from a SELECT
statement.
It allows you to specify the maximum number of records that should be returned by a query.
The syntax for the LIMIT clause is as follows:
SELECT column1, column2, ... FROM table_name
LIMIT [offset,] ;
Advanced Python
Chapter 05:Python with Database
Basic Limit
query = "SELECT * FROM customer LIMIT 5;"
[Link](query)
# Fetch and print the results
results = [Link]()
for row in results:
print(row)
# Close the cursor and connection
[Link]()
[Link]()
Advanced Python
Chapter 05:Python with Database
LIMIT with Offset:
query = "SELECT * FROM customer LIMIT 5 OFFSET 5;"
[Link](query)
LIMIT with Order By:
query = "SELECT * FROM customer ORDER BY purchase_amount DESC LIMIT 5;"
[Link](query)
Advanced Python
Chapter 05:Python with Database
WHERE clause
It is used to filter rows from a database table based on specified conditions.
The WHERE clause allows you to select only the rows that meet specific criteria, and it is typically used in
conjunction with the SELECT, UPDATE, DELETE, and INSERT INTO SQL statements.
Advanced Python
Chapter 05:Python with Database
Insert with Where: Update with Where
INSERT INTO table_name (column1, column2, UPDATE table_name
...)
SET column1 = value1, column2 = value2, ...
SELECT value1, value2, ...
WHERE condition;
FROM source_table
WHERE condition;
Delete with Where
Select with Where
DELETE FROM table_name
SELECT column1, column2, ...
FROM table_name WHERE condition;
WHERE condition;
Advanced Python
Chapter 05:Python with Database
Filtering by a Specific Value:
SELECT * FROM employees WHERE first_name = 'John';
Comparing Numeric Values:
SELECT * FROM employees WHERE age > 30;
Combining Conditions with AND:
SELECT * FROM employees WHERE first_name = 'Mary' AND department = 'HR';
Advanced Python
Chapter 05:Python with Database
Using OR for Multiple Conditions:
SELECT * FROM employees WHERE department = 'Sales' OR age < 25;
Filtering by a Range of Values:
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;