Connecting MySQL with Python
Python is a high-level, general-purpose, and very popular programming language.
Python can be used in database applications. The most popular databases is MySQL.
To create a connection between the MySQL database and Python, the connect() method
of [Link] module is used.
There are the following steps to connect a python application to our database.
• Import [Link] module
• Create the connection object.
• Create the cursor object
• Execute the query
Step to configure SQL and Python at your system:
Step1: Download any python version at your system and install it in your system.
[Link]
Step2: Download MySQL server from the website as given i.e 8.0.34
[Link]
Step3: set the environment variable path for MySQL
Step4: After setting up the path in environment variable open command prompt and run
follow command:
MySQL – - version for checking the version of MySQL server installed at system
Python – - version for checking the python version installed at system.
PIP - - version for checking the PIP installed at the system.
Now open your workbench MySQL and login to your connection: local instance using
password.
Step5: Open your python editor and import your [Link], it will generate error.
Step6: Open the window command prompt and instal the [Link] by using the
command at window command prompt
pip install mysql-connector-python
Step7: connection creation
import [Link]
conn = [Link](host='localhost',password='admin1234',user='root')
if conn.is_connected():
print("Connection established")
[Link] MySQL Database
By using following statement we can create the MySql Database
CREATE DATABASE database_name:
import [Link]
mydb = [Link](
host = "localhost",
user = "yourusername",
password = "your_password"
)
# Creating an instance of 'cursor' class
cursor = [Link]()
# Creating a database with a name
[Link]("CREATE DATABASE Mydatabase")
If the database with the name ‘Mydatabase’ already exists then you will get an error,
otherwise no error.
[Link] check the databases that you created, use “SHOW DATABASES” – SQL statement i.e.
[Link](“SHOW DATABASES”)
1. import [Link]
#Create the connection object
2. myconn = [Link](host = "localhost", user = "root",passwd = "admi
n1234")
3. #creating the cursor object
4. cur = [Link]()
5. try:
6. dbs = [Link]("show databases")
7. except:
8. [Link]()
9. for x in cur:
10. print(x)
11. [Link]()
C. Creating the table
We can create the new table by using the CREATE TABLE statement of SQL.
import [Link]
#Create the connection object
myconn = [Link](host = "localhost", user = "root",passwd =
"admin1234",database = "pgdbda")
#creating the cursor object
cur = [Link]()
try:
#Creating a table with name class Employe four columns i.e., name, id, salary, and
department id
dbs = [Link]("create table Employee(name varchar(20) not null, id int(20) not null
primary key, salary float not null, Dept_id int not null)")
except:
[Link]()
[Link]()
print(dbs)
CRUD Operation in python
To perform CRUD (CREATE, READ, UPDATE and DELETE) operations in Python using MySQL.
Step 1: Create your database
import [Link]
db = [Link]( host="localhost", user="root", passwd="password")
# cursor object c
cur = [Link]()
# executing the create database statement
[Link]("CREATE DATABASE employee_db")
# fetching all the databases
[Link]("SHOW DATABASES")
for i in cur:
print(i)
cur = [Link]()
# closing the database connection
[Link]()
Step2: Creating Table
db = [Link]( host="localhost", user="root", passwd="password",
database="employee_db")
# cursor object cur
cur = [Link]()
# create statement for tblemployee
employeetbl_create = """CREATE TABLE `employee_db`.`tblemployee` (
`empid` INT NOT NULL AUTO_INCREMENT, `empname` VARCHAR(45) NULL,
`department` VARCHAR(45) NULL, `salary` INT NULL, PRIMARY KEY (`empid`))"""
[Link](employeetbl_create)
cur= [Link]()
# fetch tblemployee details in the database
[Link]("desc tblemployee")
# print the table details
for i in cur:
print(i)
# finally closing the database connection
[Link]()
Inserting Data into table:
INSERT INTO <TABLE_NAME> (column1, column2,...) VALUES (data1,data2,data3...);
import [Link]
# connecting to the mysql server
db = [Link]( host="localhost", user="root", passwd="password",
database="employee_db")
# cursor object cur
cur = [Link]()
# insert multirow for tblemployee
employeetbl_insert = """INSERT INTO tblemployee (empname, department,salary)
VALUES (%s, %s, %s)"""
# we save all the row data to be inserted in a data variable
data = [("Vani", "HR", "100000"), ("Krish", "Accounts", "60000"), ("Aishwarya", "Sales",
"25000"), ("Govind", "Marketing", "40000")]
# execute the insert commands for all rows and commit to the database
[Link](employeetbl_insert, data)
[Link]()
[Link]()
Reading / Selecting Data from a table
SELECT * FROM <TABLE_NAME>
import [Link]
db = [Link]( host="localhost", user="root", passwd="password",
database="employee_db")
# cursor object c
c = [Link]()
# select statement for tblemployee which returns all columns
employeetbl_select = """SELECT * FROM tblemployee"""
# execute the select query to fetch all rows
[Link](employeetbl_select)
# fetch all the data returned by the database
employee_data = [Link]()
# print all the data returned by the database
for e in employee_data:
print(e)
# finally closing the database connection
[Link]()
Updating Data in table:
UPDATE <TABLE_NAME> SET <COLUMN_NAME> = <VALUE> WHERE <PRIMARY KEY NAME>
=<PRIMARY KEY VALUE>
import [Link]
# connecting to the mysql server
db = [Link]( host="localhost", user="root", passwd="password",
database="employee_db"
)
# cursor object c
c = [Link]()
# update statement for tblemployee
# which modifies the salary of Vani
employeetbl_update = "UPDATE tblemployee\
SET salary = 115000 WHERE empid = 1"
# execute the update query to modify
# the salary of employee with
# employee id = 1 and commit to the database
[Link](employeetbl_update)
[Link]()
# finally closing the database connection
[Link]()
Deleting Data
DELETE FROM <TABLE_NAME> WHERE <PRIMARY KEY NAME> = <PRIMARY KEY VALUE>
import [Link]
# connecting to the mysql server
db = [Link](
host="localhost",
user="root",
passwd="password",
database="employee_db"
)
# cursor object c
c = [Link]()
# delete statement for tblemployee
# which deletes employee Aishwarya having empid 3
employeetbl_delete = "DELETE FROM tblemployee WHERE empid=3"
# execute the delete statement and commit to the database
[Link](employeetbl_delete)
[Link]()
# finally closing the database connection
[Link]()
