Faculty of Engineering & Technology
Sankalchand Patel College of Engineering, Visnagar
Python Programming
(2ET1000110T)
Unit-06
Python MySQL
Prepared By
Mr. Mehul S. Patel
Department of Computer Engineering & Information Technology
Content
• Environment Setup
• Creating new databases
• Create Table
• CRUD Operations
• Transactions
INTRODUCTION
✓The Python programming language has powerful features for
database programming.
✓Python supports various databases like MySQL, Oracle, Sybase,
PostgreSQL, etc.
✓Python also supports Data Definition Language (DDL), Data
Manipulation Language (DML) and Data Query Statements.
✓For database programming, the Python DB API is a widely used
module that provides a database application programming interface.
BENEFITS OF PYTHON FOR DATABASE PROGRAMMING
✓Programming in Python is arguably more efficient and faster
compared to other languages.
✓Python is famous for its portability.
✓It is platform independent.
✓Python supports SQL cursors.
✓In many programming languages, the application developer needs to
take care of the open and closed connections of the database, to avoid
further exceptions and errors. In Python, these connections are taken
care of.
✓Python supports relational database systems.
✓Python database APIs are compatible with various databases, so it is
very easy to migrate and port database application interfaces.
PYTHON INTEGRATION WITH MYSQL
Total 5 modules available in python to communicate with a
MySQL and provides MySQL database support to our applications and
they are:-
1. MySQL Connector Python
2. PyMySQL 4. mysqlclient
3. MySQLDB 5. OurSQL
You can choose any of the above modules as per your
requirement. The way of accessing the MySQL database remains the
same. We discuss MySQL Connector Python Throughout this chapter.
MYSQL CONNECTOR PYTHON
✓ MYSQL Connector Python is module or library available in
python to communicate with a MySQL
✓MySQL Connector Python is written in pure Python, and it is self-
sufficient to execute database queries through python.
✓It is an official Oracle-supported driver to work with MySQL and
python.
✓It is Python 3 compatible, actively maintained.
MYSQL CONNECTOR PYTHON
PIP Command to install MySQL Connector Python
pip install mysql-connector-python
If you are facing any problem while installing, please mention the
version of the module and then try to install again. Refer to the above
table to install the correct version.
pip install mysql-connector-python==8.0.11
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
✓Install MySQL Connector Python using pip.
✓Use the [Link]() method of MySQL Connector
Python with required parameters to connect MySQL.
✓Use the connection object returned by a connect() method to create
a cursor object to perform Database Operations.
✓The [Link]() to execute SQL queries from Python.
✓Close the Cursor object using a [Link]() and MySQL database
connection using [Link]() after your work completes.
✓Catch Exception if any that may occur during this process.
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
Follow the steps:-
Step 1: Start the Python
Step 2: Import Package
Step 3: Open Connection or Connect to database
Step 4: Create a cursor
Step 5: Execute Query
Step 6 Extract data from the result set
Step 7. Close the connection or clean up the environment.
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
Step 1: Start the Python
Start the Python IDLE editor to write the script
Step 2: Import MySQL Connector Python Package.
import [Link]
Or
import [Link] as SQLCon
Step 3: Open Connection or Connect to database.
Mycon is a connection object
Mycon=[Link](host='localhost', user='root',
password='‘, database='mysql’)
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
What is Database Connection Object?
A Database connection object controls the connection to the
database. It represents a unique session with a database
connected from within a script or program.
One can check the connection by writing the following code.
If mycon.is_connected():
print(“Successfully Connected”)
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
What is cursor?
A database cursor is a special control structure that
facilitates the row by processing of records in the result set.
What is result set?
Result set refers to the logical set of records that are
fetched from the database by executing an SQL query. It is the
set of records retrieved as per the query.
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
Step 4: Create a cursor.
Mycon is a connection object
For example:
EmpCursor = [Link]()
Cursor Object
Step 5: Execute Query
[Link](“select * from emp”)
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
Step 6: Extract data from the result set.
After retrieving the records from the DB using SQL Select
Query. You need to extract records from the result set.
You can extract the result set using any of the following fetch functions/
cursor methods.
.fetchone() .fetchmany(n) .fetchall()
Cursor other methods are: -
.close() .callproc() .nextset()
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
1 .fetchone()
Fetch the next row of a query result set, returning a single
sequence, or None when no more data is available
Data=[Link]()
V_count=[Link]
print(“Total Rows retrieved : “,V_count)
print(data)
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
2 .fetchmany(n)
Fetch many(n) method will return only the n number of rows
from the result set in the form of tuple containing the records.
Data=[Link](4)
V_count=[Link]
print(“Total Rows retrieved : “,V_count)
for row in data:
print(row)
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
3 .fetchall()
Fetch all method will return all the rows from the result set in the
form of tuple containing the records.
Data = [Link]()
V_count = [Link]
print(“Total Rows retrieved : “, V_count)
for row in data:
print(row)
STEPS TO CONNECT MYSQL DATABASE IN PYTHON
USING MySQL Connector Python
Step 7. Close the connection or clean up the environment.
Example:
[Link]()
CREATING TABLE - PYTHON PROGRAM
Python Program to Create Table
import [Link] as mysql
db = [Link]( host = "localhost", user = "root", passwd =
"dbms", database = “Emp2019" )
cursor = [Link]()
[Link]("CREATE TABLE users (name VARCHAR(255),
user_name VARCHAR(255))")
SHOW ALL TABLES - PYTHON PROGRAM
Python Program to show all tables.
import [Link] as mysql
db = [Link]( host = "localhost", user = "root", passwd = "",
database = “Emp2019" )
cursor = [Link]()
[Link]("SHOW TABLES")
tables = [Link]()
for table in tables:
print(table)
PARAMETERISED QUERIES
You can run the queries with parameters
For example:
V_marks=56
Select * from student where marks > v_marks
These kind of queries are called as parameterised
queries.
FORMING QUERY STRINGS
To form a parameterised queries there are two methods.
1. % formatting – (OLD STYLE)
2. .format() – (NEW STYLE )
FORMING QUERY STRINGS
1. % formatting – (OLD STYLE)
S= “Select * from emp where empid=%s and dept=‘%s’ “ % ( 1006 , ‘Biotech’)
Example 2: Another Method to use %s :-
Ram=8
Id=2
Input=(ram,id)
Qry= “Update comp set ram=%s where id=%s”
[Link](Qry,input)
FORMING QUERY STRINGS
2. .format() – (NEW STYLE )
New style of creating SQL Query stringss
involves the use of .format() method of the str
type.
“We have {0} hectares planted to {1}”
.format(49,”Okra”)
Resultant string will be:
“We have 49 hectares planted to okra”
Contd…
FORMING QUERY STRINGS
2. .format() – (NEW STYLE )
SQL_St=“Select * from student where
makrs>{} and section=‘{}’ “ .format(70,’B’)
After execution SQL_St variable stores:-
“Select * from student where marks >70 and
section=‘B’
INSERT QUERY
To Insert a record in a table use cursor object. When you
perform insert or update remember to commit the transaction.
MyQuery=“Insert into student ( rollno,name, marks) values ({},’{}’,{} ) “
.format(1203,’Raman’,67.6)
[Link](MyQuery)
[Link]()
UPDATE QUERY
To Update a record in a table use cursor object. When you
perform insert or update remember to commit the transaction.
MyQuery=“update student set marks={}“ where marks={}”
.format(84,66)
[Link](MyQuery)
[Link]()
Other library
• import MySQLdb
conn = [Link](…)
• import pymysql
conn = [Link] ( … )
ThankYou