Database Handling in Python: SQLite3, MySQL and CRUD Operations

A practical guide to database management in Python, covering SQLite3, MySQL, CRUD operations, SQL queries, transactions, and applied practice problems, useful for students developing database applications.

Transcript

1. File I/O, Databases & APIs Dr. Madhuri Mahawar Department of Mechatonics Engineering Sanjivani College of Engineering, Kopargaon
2. MySQL / sqlite CRUD • What if the amount of data becomes large and we need to search, modify, and manage it efficiently? • For this, we use databases.
3. Database • A database is an organized collection of data that allows us to store, retrieve, modify, and manage information efficiently. • Consider a college with 10,000 students. • If student records are stored in a CSV file, finding, modifying, or deleting a particular record can become inconvenient. • A database allows us to: • Store large amounts of data • Search records efficiently • Add new records • Modify existing records • Delete unwanted records • Maintain structured data
4. CRUD • CRUD represents the four basic operations performed on data. • C — Create → Add new data • R — Read → Retrieve existing data • U — Update → Modify existing data • D — Delete → Remove data • These four operations are at the heart of most database-driven applications. • As a developer we can create data, read or retrieve data, update data, and delete data. • Whether we access a database or interact with a REST API, only when all four operations are present are we are able to make a complete data roundtrip in our app.
5. SQLite3 and MySQL • SQLite3 • Lightweight database • Database is stored in a file • Does not require a separate database server • Python provides the built-in sqlite3 module • import sqlite3 • MySQL • Client-server relational database system • Requires a MySQL server • Python can connect using a MySQL connector • import mysql.connector
6. SQLite3 and MySQL • SQLite3 • Lightweight database • Database is stored in a file • Does not require a separate database server • Python provides the built-in sqlite3 module • import sqlite3 • MySQL • Client-server relational database system • Requires a MySQL server • Python can connect using a MySQL connector • import mysql.connector
7. Why Start with SQLite3? • SQLite3 is convenient because Python includes the sqlite3 module • We can create a database simply with: import sqlite3 conn = sqlite3.connect("students.db") • If students.db does not exist, SQLite creates it. • So you do not need to install a separate database server just to practice basic SQLite CRUD.
8. Connecting to SQLite Database • Use function sqlite3.connect() • The function takes one paarameter as argument, database name. • Example: sqlite3.connect("students.db") • Connects to the SQLite database. • If the database file does not exist, SQLite creates it. • Use function cursor() • A cursor is used to execute SQL commands and retrieve results.
9. Python Code import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor()
10. Connecting to MySQL Database • Use function mysql.connector.connect() • The function takes four paarameter as argument. • They are: host, user, password and database. • Example: mysql.connector.connect(host="localhost",user="root",password="your_pas sword",database="college") • Connects to the MySQL database. • If the database file does not exist, MySQL will give an error. • Use function cursor() • A cursor is used to execute SQL commands and retrieve results.
11. Python Code: If database does not exist import mysql.connector conn = mysql.connector.connect( host="localhost", user="root", password="your_password") cursor = conn.cursor() cursor.execute("CREATE DATABASE IF NOT EXISTS college") print("Created MySQL Database") conn.close()
12. Python Code import mysql.connector conn = mysql.connector.connect( host="localhost", user="root", password="your_password", database="college" ) cursor = conn.cursor() print("Connected to MySQL") conn.close()
13. CRUD • To execute a CRUD operation, we use execute() • It is used to execute an SQL statement on the database. • Syntax: cursor.execute(sql_query) • Example: import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor() cursor.execute("SELECT * FROM student")
14. CREATE: Creating a Table • To create a table, we use the following SQL statetemnt: CREATE TABLE IF NOT EXISTS table_name( col_name1 datatype, col_name2 datatype, ...) • Example: CREATE TABLE IF NOT EXISTS student ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, branch TEXT )
15. CREATE: Creating a Table • CREATE TABLE → Creates a new table • IF NOT EXISTS → Creates it only if it doesn't already exist • student → Name of the table • id → Student identifier • INTEGER → Data type of id • PRIMARY KEY → Uniquely identifies each record • AUTOINCREMENT → Automatically generates the ID • name TEXT → Student name stored as text • age INTEGER → Student age stored as an integer • branch TEXT → Student branch stored as text
16. Python Code import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor() cursor.execute(""" CREATE TABLE IF NOT EXISTS student ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, branch TEXT ) """) conn.commit() conn.close()
17. What does the table look like? id name age • At this point, the table exists but contains no student records. branch
18. commit() and close() • commit(): • When we perform operations that change the database, such as INSERT, UPDATE, or DELETE, the changes need to be committed. • conn.commit() • “Save the changes permanently to the database.” • Without committing a change, it may not be persisted when the connection is closed. • close(): • When we have finished working with the database, we should close the connection: • conn.close() • This releases the resources associated with the database connection.
19. INSERT: Inserting a Record in a Table • To create a table, we use the following SQL statetemnt: INSERT INTO table_name( col_name1, col_name2, ...) values (?,?,...) • Example: INSERT INTO student (name, age, branch) VALUES (?, ?, ?)
20. INSERT: Inserting a Record in a Table • INSERT INTO → Adds a new record • student → Name of the table • (name, age, branch) → Columns receiving the data • VALUES → Specifies the values to insert • ?, ?, ? → Placeholders for the values
21. Python Code import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor() cursor.execute( "INSERT INTO student (name, age, branch) VALUES (?, ?, ?)", ("Rahul", 21, "MECH") ) conn.commit() conn.close()
22. Python Code: Inserting Multiple Records import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor() students = [ ("Rahul", 21, "MECH"), ("Priya", 20, "CSE"), ("Amit", 22, "ECE") ] cursor.executemany( "INSERT INTO student (name, age, branch) VALUES (?, ?, ?)", students ) conn.commit()
23. Read: Retrieving Records from a Table • To create a table, we use the following SQL statetemnt: SELECT * FROM table_name • Example: SELECT * FROM student
24. Python Code import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor() cursor.execute("SELECT * FROM student") rows = cursor.fetchall() for row in rows: print(row) conn.close()
25. fetchall() • It retrieves all the rows returned by the query. • The result is stored as a list of tuples [ (1, 'Rahul', 21, 'MECH'), (2, 'Priya', 20, 'CSE'), (3, 'Amit', 22, 'ECE') ] • Iterate through the results for row in rows: print(row) • Each iteration gives one record.
26. Python Code: Reading Specific Columns import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor() cursor.execute("SELECT name, branch FROM student") for row in cursor.fetchall(): print(row)
27. Python Code: Reading a Specific Record import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor() cursor.execute( "SELECT * FROM student WHERE id=?", (2,) ) row = cursor.fetchone() print(row)
28. UPDATE: Modifying Existing Records • To create a table, we use the following SQL statetemnt: UPDATE table_name SET col_name_x = ? WHERE col_name_y = ? • Example: UPDATE student SET age=? WHERE name=?
29. UPDATE: Modifying Existing Records • UPDATE student → Modify records in the student table • SET age=? → Set the new value for age • WHERE name=? → Identify which record should be modified
30. Python Code import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor() cursor.execute( "UPDATE student SET branch=? WHERE id=?", ("CSE", 3) ) conn.commit() conn.close()
31. DELETE: Removing Records • To create a table, we use the following SQL statetemnt: DELETE FROM table_name WHERE col_name_y = ? • Example: DELETE FROM student WHERE id=?
32. Python Code import sqlite3 conn = sqlite3.connect("students.db") cursor = conn.cursor() cursor.execute( "DELETE FROM student WHERE branch=?", ("CSE",) ) conn.commit() conn.close()
33. Practice Questions A college wants to maintain student attendance records using SQLite3. Create a database college.db with a table attendance containing id, student_name, branch, and attendance_percentage. Write a Python program to: • Create the table if it does not exist. • Insert at least 5 student records. • Display the names and attendance percentages of students having attendance below 75%. • Commit the changes and close the database connection.
34. Practice Questions A manufacturing unit stores machine maintenance information in a database. The table maintenance contains id, machine_name, last_service_days, and status. Write a Python program to: • Create the maintenance table. • Insert at least 5 machine records. • Retrieve and display machines whose last_service_days is greater than 90. • Update the status of one selected machine to "Maintenance Required". • Commit the changes and close the connection.
35. Practice Questions A workshop maintains its inventory using a database. The table inventory contains id, item_name, quantity, and price. Write a Python program to: • Create the table using SQLite3. • Insert at least 5 inventory items. • Display all items whose quantity is less than 10. • Update the quantity of a selected item after a new shipment is received. • Save the changes using commit() and close the connection.
36. Practice Questions A company stores employee information in an SQLite database. The table employee contains id, name, department, and salary. Write a Python program to: • Create the employee table. • Insert at least 5 employee records. • Display the names and salaries of employees earning more than ₹40,000. • Update the salary of a particular employee after a salary revision. • Commit and close the database connection.
37. Practice Questions A temperature-monitoring system stores sensor readings in an SQLite database. The table sensor_data contains id, sensor_name, temperature, and location. Write a Python program to: • Create the table if it does not already exist. • Insert at least 5 sensor readings. • Retrieve and display readings where the temperature is above 40°C. • Update the temperature reading of a particular sensor when recalibration is performed. • Commit the changes and close the database.
38. Practice Questions A college library wants to manage books using SQLite3. The table books contains id, title, author, and available. Write a Python program to: • Create the books table. • Insert at least 5 book records, where available stores "Yes" or "No". • Display all currently available books. • Update the availability of a book when it is issued. • Commit the update and close the database connection.
39. Practice Questions A manufacturing company records inspection results for its products. The table quality_check contains id, product_name, defect_count, and result. Write a Python program to: • Create the table and insert at least 5 product inspection records. • Display products having more than 2 defects. • Update the result of a selected product from "Pending" to "Rejected". • Delete a record for a product that is removed from the production batch. • Commit and close the database connection.
40. Practice Questions A mechanical workshop maintains a database of tools. The table tools contains id, tool_name, quantity, and condition. Write a Python program to: • Create the tools table and insert at least 5 records. • Display tools whose condition is "Damaged". • Update the condition of a selected tool after it has been repaired. • Delete a tool record that is no longer available in the workshop. • Commit and close the connection.
41. Practice Questions A department wants to store student examination marks in a database. The table marks contains id, student_name, subject, and marks. Write a Python program to: • Create the table and insert at least 5 records. • Display the names and marks of students who scored more than 75. • Update the marks of a student after a re-evaluation. • Delete a record belonging to a student who has withdrawn from the course. • Commit the changes and close the connection.
42. Practice Questions A factory stores machine performance information in SQLite3. The table machine contains id, machine_name, rpm, temperature, and status. Write a Python program to: • Create the table and insert at least 5 machine records. • Display machines having rpm greater than 2000 and temperature above 70°C. • Update the status of a selected machine to "Inspection Required". • Delete a machine record that has been permanently removed from the production line. • Commit and close the database connection.
43. Thank You