Chapter 6
Part 1: Connecting MySQL
Part 1: Connecting MySQL
Content of Part 1
• 1. Introduction
• 2. Connecting MySQL and Python
• 3. Creating a Connection
• 4. Database Operations
• 5. Basic Queries using Python and MySQL
Python 3
1. Introduction
• When we create useful applications using Python, we need to store the data
somewhere in a database so that it can be used later. One great relational
database that we can use is MySQL
• We can easily connect Python with the MySQL database and do the
database operations using Python. For that, we need to set up the
MySQL database on our system and connect it to Python.
• In this chapter, let’s see how we can set up the MySQL database and
connect it to Python. We will also learn how we can do basic operations like
creating databases, creating tables, inserting data, retrieving data, etc.
Python 4
1. Install MySQL and Workbench
• Step 1: Install MySQL ver 9.0.1
• Download: [Link]
• Guide to install:
[Link]
[Link]
• Step 2: Install Workbench (it is a visual design and modeling database
access tool for the MySQL server).
• Download: Mysql Workbench:
[Link]
• User manual to MySQL Workbench:
[Link]
Python 5
2. Connecting MySQL and Python
• Step 1: install a MySQL connector package
pip install mysql-connector-python
Python 6
3. Creating a Connection
• Import the [Link] module:
import [Link] Name of dataserver
Or ip address dataserver
• Create a connection for dataserver using the connect() method:
mydb = [Link](
host="localhost",
user="username",
password="password")
print(mydb)
We setup this value at time installing mySQL
Python 7
3. Creating a Connection
• Create a connection for database:
import [Link]
mydb = [Link](
host="localhost",
user="username",
password="password",
database="namedatabase")
print(mydb)
Python 8
4. Database Operations
• Create a database using cursor() method:
mycursor = [Link]()
[Link]("CREATE DATABASE namedatabase")
• Display the databases that are existing:
mycursor = [Link]()
[Link]("SHOW DATABASES")
for x in mycursor:
print(x)
Python 9
5. Basic Queries using Python and MySQL
• Create a table:
mycursor = [Link]()
[Link]('Create table Student(RollNo int(5),
Name varchar(30))')
• Show a table:
mycursor = [Link]()
[Link]('SHOW TABLES')
for x in mycursor:
print(x)
Python 10
5. Basic Queries using Python and MySQL
• Insert data into the table:
mycursor = [Link]()
sql = 'INSERT INTO student (RollNo, Name) VALUES (%s, %s)'
val = ("1", "John")
[Link](sql, val)
[Link]()
• Insert multiple rows:
mycursor = [Link]()
sql = 'INSERT INTO student (RollNo, Name) VALUES (%s, %s)'
val = [("2", "Bob"),("3","Elisa"),("4", "Maxx")]
[Link](sql, val)
[Link]()
Python 11
5. Basic Queries using Python and MySQL
• Retrieve data from the table:
mycursor = [Link]()
[Link]("SELECT * FROM student")
myresult = [Link]()
for x in myresult:
print(x)
• fetchall() method will return all rows in table
• fetchone() method will return the first row
Python 12
5. Basic Queries using Python and MySQL
• Sort the result alphabetically:
mycursor = [Link]()
sql = "SELECT * FROM customers ORDER BY name"
[Link](sql)
myresult = [Link]()
for x in myresult:
print(x)
• Use the DESC keyword to sort the result in a descending order:
mycursor = [Link]()
sql = "SELECT * FROM customers ORDER BY name DESC"
[Link](sql)
myresult = [Link]()
for x in myresult:
print(x)
Python 13
5. Basic Queries using Python and MySQL
• Delete record:
mycursor = [Link]()
sql="DELETE FROM customers WHERE address =' 21 Tran Phu'"
[Link](sql)
[Link]()
• Delete a Table:
mycursor = [Link]()
sql = "DROP TABLE customers"
[Link](sql)
[Link]()
• Drop Only if Exist:
mycursor = [Link]()
sql = "DROP TABLE IF EXISTS customers"
[Link](sql)
Python 14
5. Basic Queries using Python and MySQL
• Update Table:
mycursor = [Link]()
sql = "UPDATE customers SET address = 'Canyon 123' WHERE
address = 'Valley 345'"
[Link](sql)
[Link]()
• Using the placeholder %s method
mycursor = [Link]()
sql = "UPDATE customers SET address=%s WHERE address=%s"
val = ("Valley 345", "Canyon 123")
[Link](sql, val)
[Link]()
Python 15
5. Basic Queries using Python and MySQL
• Limit the Result:
mycursor = [Link]()
[Link]("SELECT * FROM customers LIMIT 5")
myresult = [Link]()
• Start from position 3, and return 5 records:
mycursor = [Link]()
[Link]("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = [Link]()
Python 16
5. Basic Queries using Python and MySQL
• Join Two or More Tables:
mycursor = [Link]()
sql = "SELECT [Link] AS user, [Link] AS favorite \
FROM users INNER JOIN products ON [Link] =
[Link]"
[Link](sql)
myresult = [Link]()
• LEFT JOIN:
sql = "SELECT [Link] AS user, [Link] AS favorite\
FROM users LEFT JOIN products ON [Link] = [Link]“
• RIGHT JOIN:
sql = "SELECT [Link] AS user, [Link] AS favorite\
FROM users RIGHT JOIN products ON [Link] = [Link]"
Python 17
Practice Part 1
Python 18