Class 12 Computer Science – Interfacing Python with MySQL
1. Introduction
Interfacing Python with MySQL allows developers to interact with a MySQL database using Python scripts. This is commonly achieved using the mysql-connector-python library.
2. Installing MySQL Connector
To interface with MySQL, install the required connector:
pip install mysql-connector-python
3. Connecting to MySQL Database
Use the following Python script to establish a connection with MySQL:
import mysql.connector
Establishing connection:
conn = mysql.connector.connect( host="localhost", user="your_username", password="your_password", database="your_database")
Creating cursor object:
cursor = conn.cursor()
4. Creating a Database
To create a new database:
cursor.execute("CREATE DATABASE data1")
5. Creating a Table
Create a table inside a database:
cursor.execute(""" CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) )""")
6. Inserting Data
Insert data into a table:
query = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("Lovejot", "lovejot@gmail.com")
cursor.execute(query, values)
conn.commit()
7. Retrieving Data
Fetch data from a table:
query = "SELECT * FROM users" cursor.execute(query) for row in cursor.fetchall(): print(row)
8. Updating Data
Modify existing records:
query = "UPDATE users SET email = %s WHERE name = %s"
values = ("Aman@gmail.com", "Amandeep")
cursor.execute(query, values)
conn.commit()
9. Deleting Data
Remove records from a table:
query = "DELETE FROM users WHERE name = %s"
values = ("Amandeep",)
cursor.execute(query, values)
conn.commit()
10. Closing the Connection
Always close the database connection when done:
cursor.close() conn.close()
11. Exception Handling
Handle errors while connecting to the database:
try:
conn = mysql.connector.connect(host="localhost", user="root", password="", database="test")
cursor = conn.cursor()
print("Connected Successfully")
except mysql.connector.Error as err:
print("Error:", err)
finally:
if conn.is_connected():
cursor.close()
conn.close()
