Interface python with mysql
Steps to Create Database Connectivity Application using: [Link]
Step 1: Install MySQL Connector
Before connecting Python with MySQL, install the mysql-connector-python package.
Command (run in terminal or CMD):
C:\> pip install mysql-connector-python
Step 2: Import the Module
At the top of your Python file, import the connector.
import [Link]
Step 3: Establish a Connection to the Database
Use the connect() method to create a connection object.
conn = [Link]( host="localhost", user="root",
password="yourpassword", database="dps")
Step 4: Create a Cursor Object
A cursor lets you execute SQL queries.
cur = [Link]()
Step 5: Execute SQL Queries
Use the cursor’s execute() method to run SQL commands like INSERT, SELECT, UPDATE,
DELETE, etc.
Example — Create a table:
[Link]("CREATE TABLE emp (emp_id INT PRIMARY KEY, emp_name VARCHAR(50), salary
FLOAT, dept VARCHAR(30))")
Step 6: Commit Changes
For changes like INSERT, UPDATE, or DELETE, you must commit them to save
permanently.
[Link]()
Step 7: Close the Cursor and Connection
Always close your cursor and database connection to free resources.
[Link]()
[Link]()
Definition: Cursor()
The cursor() method is used to create a cursor object which acts as a control structure — it
allows you to execute SQL queries and fetch data from the database.
cur = [Link]()
Here,
conn is your database connection object.
cur (the cursor) is used to execute SQL commands like SELECT, INSERT, UPDATE,
etc.
2. [Link]():
fetchall() retrieves all the rows from the result of the executed query and returns them as a
list of tuples.
[Link]("SELECT * FROM emp")
rows = [Link]()
for row in rows:
print(row)
3. [Link](): fetchone() retrieves the next single row from the result set of a
query.
If there are no more rows, it returns None.
[Link]("SELECT * FROM emp")
row = [Link]()
print(row)
4. [Link](size):
fetchmany(size) retrieves the next set of rows (number specified by size) from the
result of a query.
It returns a list of tuples.
[Link]("SELECT * FROM emp")
rows = [Link](3) # fetch next 3 rows
print(rows)
5. [Link] :
rowcount is a property (not a method) that shows the number of rows affected by the last
executed SQL statement.
[Link]("DELETE FROM emp WHERE dept='HR'")
print([Link], "row(s) deleted")
Programs
#Write a program to enter the data in mysql table :emp by using mysql database :dps
import [Link]
# Step 1: Connect to MySQL
conn = [Link]( host="localhost", user="root", password="root", db="dps")
# Step 2: Create a cursor
cur = [Link]()
# Step 3: Take input from user
emp_id = int(input("Enter Employee ID: "))
emp_name = input("Enter Employee Name: ")
salary = float(input("Enter Salary: "))
# Step 4: Write the INSERT query using format()
query = "INSERT INTO emp (emp_id, emp_name, salary, dept) VALUES ({}, '{}', {}, )".format(
emp_id, emp_name, salary)
# Step 5: Execute and commit
[Link](query)
[Link]()
print("Record inserted successfully!")
# Step 6: Close connection
[Link]()
[Link]()
#Program to update data in mysql
import [Link]
conn = [Link]( host="localhost", user="root",
password="root",database="dps")
cur = [Link]()
cols = ["emp_id", "emp_name", "salary"]
print("Columns you can update:", cols)
col = input("Enter column name to update: ")
empid = int(input("Enter Employee ID whose data you want to update: "))
newval = input("Enter new value: ")
query = "UPDATE emp SET {} = '{}' WHERE emp_id = {}".format(col, newval, empid)
[Link](query)
[Link]()
print("Record updated successfully!")
[Link]()
[Link]()
#Program to Select data in mysql
import [Link]
conn = [Link]( host="localhost",user="root",password="root",database="dps")
cur = [Link]()
[Link]("SELECT * FROM emp")
rows = [Link]()
print("Employee Data:")
for row in rows:
print(row)
[Link]()
[Link]()
#Program to delete data in mysql
import [Link]
conn = [Link](host="localhost",user="root",password="root",db="dps")
cur = [Link]()
# Display current data
[Link]("SELECT * FROM emp")
rows = [Link]()
print("Employee Data before deletion:")
for row in rows:
print(row)
# Take input for deletion
empid = int(input("\nEnter Employee ID to delete: "))
# Delete query
query = "DELETE FROM emp WHERE emp_id = {}".format(empid)
[Link](query)
[Link]()
print("Record deleted successfully!")
# Display data after deletion
[Link]("SELECT * FROM emp")
rows = [Link]()
print("\nEmployee Data after deletion:")
for row in rows:
print(row)
[Link]()
[Link]()