Interface python with MySQL
Introduction:
• To connect to a database from within a
programming application, you need a
framework that facilitates communication
between two different genres of software
(programming application and DBMS).
• To connect from Python to MYSQL, you need a
library called MySQL connector.
• You must import mysql.connector in the
Python program/script before writing code of
connectivity.
Install MySQL Connector
• Anaconda---environment---base(root)----OPEN
TERMINAL ----WRITE---PIP INSTALL MYSQL-
CONNECTOR
• Open Python IDLEFileopenscriptcopy path
• Paste that path in command prompt and write pip
install mysql-connector
• C:\Python\Python39\Scripts>pip install mysql-
connector
Steps for creating database connectivity
• Step 1. Start Python: start Python's editor where you can create your python
scripts, i.e; IDE or IDLE.
•
Step 2. Import the package required for database programming.
Here you need to import mysql.connector package in your python scripts.
•
Step 3. Open a connection: Here we need to establish a connection to MYSQL
database using connect(). This requires 4 parameters, the syntax for this is as
follows:
• <Connection_object> = mysql.connector.connect
(host= <host_name>,
user=<username>,
password =<password> ,
[database = <database>])
Con = mysql.connector.connect
(host= “localhost”,
user=“root”,
password =“root” ,
database = “school”)
How to check host name
• Step 4. Create a cursor instance.
A database cursor is a special control structure that facilities the
row by row processing of records in the result set i.e set of
records retrieved as per query.
Here we have to create an instance of cursor by
using cursor(), the syntax for the following is as follows:
<cursorobject> =<connectionobject>.cursor()
i.e; in the above connection we can create cursor() by writing:
cursor =con.cursor()
• Step 5. Execute a query:
• Once you have created a cursor ,you can
execute SQL query using execute() function with
cursor object.
• here we use the execute() with following syntax.
< cursorobject>.execute(<sql query string>)
cursor.execute("select*from data”)
Step 6. Extract data from result set.
Here you can fetch the data from the resultset
by using fetch( ) functions.
[fetchall(), fetchmany(<n>), fetchone()]
Parameterized Query
• pip install tabulate
• from tabulate import tabulate
• L=[(1,"Nisha",100),(2,"neha",99),(3,"yug",77)]
• print(tabulate(L,headers=['Roll no','Name','Marks']))
• Ouput:
Roll no Name Marks
--------- ------ -------
1 Nisha 100
2 neha 99
3 yug 77
• Create database class12 ,create table student
• Add data ,update data ,delete data and show
data in tabular format in python only.
