CHAPTER-15 CLASS XII
PYTHON MYSQL Connectivity
BASICS OF DATABASE CONNECTIVITY
1. Python MySQL
Python can be used in database applications.
One of the most popular database is MySQL.
2. MySQL Database
To be able to experiment with the code examples, you should have MySQL installed on
your computer.
You can download a free MySQL database at [Link]
BASICS OF DATABASE CONNECTIVITY
3. Installation of MySQL Driver
• Python needs a MySQL driver to access the MySQL database.
• In this topic we will use the driver "MySQL Connector".
• It is recommended that to use PIP to install "MySQL Connector".
• PIP is most likely already installed in your Python environment.
• Navigate your command line to the location of PIP, and type
the following:
Download and install "MySQL Connector":
Now you have downloaded and installed a MySQL driver.
PYTHON – MySQL CONNECTIVITY
STEPS TO BE FOLLOWED TO CONNECT MYSQL WITH FUNCTIONS
[Link] Python
[Link] module in the program [Link]
[Link] establishment using connect function
[Link]()
[Link] a cursor object for the program cursor()
[Link] the query [Link]()
[Link] the result from MySQL using functions
fetchone() fetchall() fetchmany() rowcount
7. Close the environment close ()
PYTHON – MySQL CONNECTIVITY
PYTHON – MySQL CONNECTIVITY
Program for testing MySQL Connector
To test if the installation was successful, or if you already have
"MySQL Connector" installed, create a Python page with the
following content:
import [Link]
If the above code was executed with no errors, "MySQL Connector"
is installed and ready to be used.
PYTHON – MySQL CONNECTIVITY
Program for Creating Connection
Start by creating a connection to the database.
Use the username and password from your MySQL database:
import [Link]
mydb = [Link]
( host="localhost",
user="root",
password ="1234"
)
print(mydb)
[Link]()
Now you can start querying the database using SQL statements.
PYTHON – MySQL CONNECTIVITY
Creating a database named "SCHOOL":
import [Link]
mydb = [Link]
( host="localhost",
user="root",
password="1234"
)
mycursor = [Link]()
[Link]("CREATE DATABASE SCHOOL")
[Link]()
If the above code was executed with no errors, you have successfully created a
database.
PYTHON – MySQL CONNECTIVITY
Check if Database Exists
You can check if a database exists by listing all databases in your
system by using the "SHOW DATABASES" statement:
1. Start Python
import [Link] 2. [Link]
mydb = [Link]( 3. [Link]()
host="localhost",
user="root",
password="1234"
)
mycursor = [Link]() 4. cursor()
[Link]("SHOW DATABASES") 5. [Link]()
for x in mycursor: 6. fetchone() / fetchall() /
print(x) fetchmany() / rowcount()
[Link]() 7. close()
PYTHON – MySQL CONNECTIVITY
to create table in Mysql database.
import [Link]
mydb=[Link](host="localhost",user="root",
passwd="1234",database="school")
mycursor=[Link]()
[Link]("create table student(rollno int(3) primary
key,name varchar(20),age int(2))")
[Link]()
PYTHON – MySQL CONNECTIVITY
to insert data in Mysql database.
import [Link]
mydb = [Link]( host="localhost",
user="root",password="sql123", database="CLASS12")
mycursor = [Link]()
[Link]("INSERT INTO student VALUES(101,’BABU’, 18)")
[Link]()
print([Link], "record inserted.")
[Link]()
PYTHON – MySQL CONNECTIVITY
to display data from Mysql database
import [Link]
mydb = [Link](host="localhost",
user="root", password="sql123",
database="CLASS12")
mycursor = [Link]()
[Link]("SELECT * FROM Information")
myresult = [Link]()
for x in myresult:
print(x)
[Link]()
PYTHON – MySQL CONNECTIVITY
to update data in Mysql database.
import [Link]
mydb = [Link](host="localhost",
user="root", password="1234", database="school")
mycursor = [Link]()
[Link]("UPDATE student SET age = '17' WHERE
name = 'Ravi' ")
[Link]()
print([Link], "record(s) affected")
[Link]()
PYTHON – MySQL CONNECTIVITY
to delete data in Mysql database.
import [Link]
mydb = [Link](host="localhost",
user="root", password="1234", database="school")
mycursor = [Link]()
[Link](" DELETE FROM STUDENT WHERE
NAME=‘RAVI’ ")
[Link]()
print([Link], "record(s) affected")
[Link]()
THANK YOU