Interface of Python with an
SQL database
Syllabus
Interface of python with an SQL database:
- connecting SQL with Python,
- performing insert, update, delete queries using cursor,
- display data by using fetchone(), fetchall(), rowcount,
- creating database connectivity applications
Need of Python-MySQL connectivity
In general, during the execution of a program, data is inputted by the user
and output is displayed accordingly.
But this input and output data is not stored anywhere because all program
execution takes place inside the RAM which is a temporary memory and as
soon as we close the program/IDE, its contents get erased.
Thus, when next time program is executed again, it requires a new set of
inputs from the user.
Need of Python-MySQL connectivity
This limitation can be overcome by
- fetching the input from the user (through a Python program) in a
database, and
- sending the output in a database which is not directly accessed by
the user.
Installing MySQL connector
To establish connectivity between Python and MySQL, we require Python
DB-API which is a set of tools used by an Application Program to
communicate with the Operating System or other programs such as DBMS.
This API includes the following:
- Importing the API module
- Acquiring a connection with the database
- Issuing SQL statements and stored procedures
- closing the connection
Installing MySQL connector
In order to install MySQL connector, we can use the following command in
CMD (run it as an admin):
pip install mysql-connector-python
connecting Python and MySQL
Once we install MySQL connector, let’s establish the connection between
Python and MySQL from Python IDE:
if the above statements are executed successfully, then we will received this
kind of output..
Creating Cursor Object
In order to execute SQL statements from Python IDE, we need to create a
cursor object which will allows Python code to execute database command in
a database session.
cursor object is created using cursor method by the connection object
returned by connect() method,
myCursor = [Link]()
Once a cursor object is created, we can use execute method to execute SQL
queries from Python.
Program-1: Creating a database
As mentioned earlier, database queries can be executed in Python using
execute() method
Program-2: Show databases
Program-3: create a table inside database
Program-4: show tables in a database
Program-5: using alter table command
Program-6: Inserting data in table
Program-7: inserting multiple values
Program-8: updating values
Program-9: deleting records
Reading values from the table
In order to fetch data from database using Python IDE, we will be using select
statement as per the data requirement, like
[Link](“select * from student”)
Data from the database can be retrieved using cursor object along with any of
the below functions:
a.) fetchall()
b.) fetchone()
c.) fetchmany()
Reading values from the table: fetchall()
Reading values from the table: fetchone()
Reading values from the table: fetchmany()
Reading values from the table: fetchmany()
if we try to fetch more than number of records in the database, then
it would return available number of records only.
Return of datatype incase there is no value
in the database: fetchall()
Return of datatype incase there is no value
in the database: fetchone()
Return of datatype incase there is no value
in the database: fetchmany(n)
Reading values from the table
function returns data type to return
fetchall() all the rows of a query result set list of tuples
fetchone() next row of a query result set tuple/None
fetchmany(n) specified number of rows list of tuples
Note:
- default value of n is 1
- If there is no value in a resultset, an empty list [] is returned in case of
fetchall() and fetchmany(), and in case of fetchone(), special data type
None is returned