Python_MySQL_Lecture_Databases with MySQL.pptx

PYTHON
DATABASES WITH
MYSQL
Connecting, Querying, and Managing Databases

What is MySQL?
■ - Open-source relational database
■ - Stores data in structured tables
■ - Uses SQL for data operations
■ - Commonly used for web applications and data management

Why Use MySQL with Python?
■ - Scalable and reliable database system
■ - Supports multiple connections
■ - Works well with Python via `mysql-connector-python`
■ - Used in web development, data analysis, and automation

Installing MySQL Connector
■ Install the MySQL connector package:
■ `pip install mysql-connector-python`

Connecting to MySQL
Database
import mysql.connector
conn = mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="test_db"
)
cursor = conn.cursor()
print("Connected to MySQL successfully!")
conn.close()

Creating a Database
conn = mysql.connector.connect(host="localhost", user="root", password="your_password")
cursor = conn.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS test_db")
print("Database created successfully!")
conn.close()

Creating a Table
cursor.execute('''CREATE TABLE IF NOT EXISTS employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
age INT,
salary DECIMAL(10,2)
)''')
print("Table created successfully!")
conn.close()

Inserting Data into MySQL
rsor.execute("INSERT INTO employees (name, age, salary)
VALUES (%s, %s, %s)", ("Alice", 25, 5000
nn.commit()
nt("Data inserted successfully!")

Fetching Data from MySQL
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
for row in rows:
print(row)

Updating and Deleting
Records
# Updating a Record
cursor.execute("UPDATE employees SET salary = %s
WHERE name = %s", (55000
conn.commit()
# Deleting a Record
cursor.execute("DELETE FROM employees
WHERE name = %s", ("Alice",
conn.commit()

Best Practices
■ ✅ Use `%s` placeholders to prevent SQL injection
■ ✅ Always commit transactions
■ ✅ Use connection pooling
■ ✅ Use try-except blocks to handle database errors

1. Importing Required
Module
■ import mysql.connector
- Imports the MySQL connector library to enable database
interactions.

2. Connecting to MySQL
Database
mydb = mysql.connector.connect(
host="localhost",
username="springstudent",
password="springstudent",
database="studentdb"
)
■ Establishes a connection to the 'studentdb' database using
specified credentials.

3. Creating a Cursor
mycursor = mydb.cursor()
■ - Creates a cursor object to execute SQL queries.

4. SQL Query for Data
Insertion
sql = "INSERT INTO students (id, name, surname, spec, gpa)
VALUES (%s, %s, %s, %s, %s)"
■ - Defines an SQL statement with placeholders for inserting
data.

5. Data to be Inserted
val = [
(1, "Alan", "Karimov", "Data Science", 3.6),
(2, "Diyaz", "Ziyashev", "Airkick Science", 4.0),
]
■ - List of tuples representing students' information.

6. Executing SQL Query
(Insert)
mycursor.executemany(sql, val)
mydb.commit()
■ - Inserts multiple records into the database.

7. Fetching Data from the
Database
mycursor.execute("SELECT * FROM students")
res = mycursor.fetchall()
■ - Retrieves all records from the 'students' table.

8. Displaying Retrieved Data
for x in res:
print(x)
■ - Iterates through the retrieved records and prints them.

Summary
1. Connected to MySQL database.
2. Inserted student records.
3. Retrieved and displayed records.
The script demonstrates basic MySQL operations in Python.

Python_MySQL_Lecture_Databases with MySQL.pptx

More Related Content

Similar to Python_MySQL_Lecture_Databases with MySQL.pptx

Recently uploaded

Python_MySQL_Lecture_Databases with MySQL.pptx