Interface Python with MySQL is very important of database is nothing but an organized collection of data. Data is organized into rows, columns and
tables and it is indexed to make it easier to find relevant information. All companies whether large or
small use databases.
In order to connect to a database from within Python, you need a library(mysql connector) that provides connectivity functionality. Steps for Creating Database Connectivity Applications. There are mainly seven steps that must be followed in order to create a database connectivity application.
Step 1 : Start Python.
Step 2 : Import the packages required for database programming.
Step 3 : Open a connection to database.
Step 4 : Create a cursor instance.
Step 5 : Execute a query.
Step 6 : Extract data from result set.
Step 7 : Clean up the environment.
Step 1. Start Python
Start Python’s editor where you can create your Python scripts and python interface.
Step 2. Import mysql.connector Package
First of all you need to import mysql.connector package in your Python scripts. For this, write
import command as shown below:
import mysql.connector
or
import mysql.connector as sqLtor
Step 3: Open a connection to database.
The connect( ) function of mysql.connector establishes connection to a MySQL database and
requires four parameters, which are:
= mysql.connector.connect(host = , user = ,
passwd = , database = )
Step 4 : Create a cursor instance.
- When we connect to a database from within a script/program, then the query gets sent to the
server, where it gets executed, and the resultset (the set of records retrieved as per query) is sent
over the connection to you, in one burst of activity, i.e. in one go. And in order to do the processing
of data row by row, a special control structure is used, which is called Database Cursor.
Step 5 : Execute a query
- Once you have created a cursor, you can execute SQL query using execute( ) function with cursor
object as per following syntax:
.execute()
Step 6 : Extract data from result set.
Once the result of query is available in the form of a resultset stored in a cursor object, you can
extract data from the resultset using any of the following fetch( ) functions and python interface.
(i) = .fetchall( ) – It will return all the records retrieved as per query in a tuple
form.
(ii) = .fetchone( ) – It will return one record from the resultset as a tuple or a
list. First time it will return the first record, next time it will fetch the next record and so
on.
This method returns one record as a tuple : if there are no more records then it returns None.
(iii) =.fetchmany() – This method accepts number of records to fetch and
returns a tuple where each record itself is a tuple.
(iv) =.rowcount – The rowcount is a property of cursor object that returns
the number of rows retrieved from the cursor so far.
