Interface python
with MySQL
Database
All companies whether large or small use
databases. So, it become necessary to
develop project/software using any
programming language like python in
such a manner which can interface with
such databases which support SQL
Generalised form of Interface of python with SQL Database
can be understood with the help of this diagram
Form/any user interface designed in any programming language is Front
End where as data given by database as response is known as Back-End
database
Using SQL in any of the DBMS ,databases and table can be
created and data can be accessed, updated and maintained.
The Python standard for database interfaces is the Python DB-
API.
Python Database API supports a wide range of database
servers, like msql , mysql, postgressql, Informix, oracle, Sybase
etc.
SQL is just a query language, it is not a database.
To perform SQL queries, we need to install any database for
example Oracle, MySQL, MongoDB, PostGres SQL, SQL Server,
DB2 etc.
Why choose Python for
Following are the reason to choose python for database programming
database programming
• Programming more efficient and faster compared to other languages.
• Portability of python programs.
• Support platform independent program development.
• Python supports SQL cursors.
• Python itself take care of open and close of connections.
• Python supports relational database systems.
• Porting of data from one DBMS to other is easily possible as it support
large range of APIs for various databases.
We must download a separate DB API module for each database we need to access.
Suppose we need to access an Oracle database as well as a MySQL database, we
must download both the Oracle and the MySQL database modules .
The DB API provides a minimal standard for working with databases using Python
structures and syntax wherever possible. This API includes the following −
SQL Connectors
● Importing the API module.
● Acquiring a connection with the database.
● Issuing SQL statements and stored procedures.
● Closing the connection
PYTHON – MySQL CONNECTIVITY
• 1. Start Python
• 2. Import module in the program [Link]
• 3. Connection establishment using connect function
[Link]()
• 4. Create a cursor object for the program cursor()
• 5. Execute the query [Link]()
• 6. Fetch the result from MySQL using functions fetchone()
fetchall() fetchmany() rowcount()
• 7. Close the environment close()
PYTHON – MySQL CONNECTIVITY
To test if the installation was successful, or
if you already have "MySQL Connector"
Program for testing installed, create a Python page with the
MySQL Connector following content:
Python •import
Statement: [Link]
If the above code was executed with no
errors, "MySQL Connector" is installed and
ready to be used
Cursor object
The MySQLCursor class Cursor objects interact
instantiates objects that with the MySQL server
can execute operations using a
such as SQL MySQLConnection
statements. object.
Fetch the result from MySQL using
functions
[Link]() Method This
[Link]() Method The method retrieves the next row of a
method fetches all (or all remaining) query result set and returns a single
rows of a query result set and returns a sequence, or None if no more rows are
list of tuples. If no more rows are available. By default, the returned tuple
available, it returns an empty list. consists of data returned by the MySQL
server, converted to Python objects.
[Link]() Method rows
= [Link](size=1) This method rowcount : Rows affected by Query. We
fetches the next set of rows of a query can get number of rows affected by the
result and returns a list of tuples. If no query by using rowcount. We will use
more rows are available, it returns an one SELECT query here.
empty list.
Program for Creating
Connection
+ Start by creating a connection to the
database.
+ Use the username and password from
your MySQL database:
Now you can start querying the database
using SQL statements.
Manage Database Transaction
Python MySQL Connector
Database transaction Any operation which modifies
provides the following method
represents a single unit of the state of the MySQL
to manage database
work. database is a transaction.
transactions.
AutoCommit –
commit –
[Link]
[Link]() rollback –
value can be assigned as True
method sends a COMMIT [Link]
or False to enable or disable
statement to the MySQL server, revert the changes made by
the auto-commit feature of
committing the current the current transaction.
MySQL. By default its value is
transaction.
False.
+PYTHON – MySQL CONNECTION
+ Step 1: Import Module
+import [Link]
+ Step 2: Establish Connection
+con = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
+ Step 3: Create Cursor Object
+cur = [Link]()
+ Step 4: Execute SELECT Query
+[Link]("SELECT * FROM student")
+ METHOD 1: Using fetchone()
+ Fetches one row at a time
+row = [Link]()
while row is not None:
print(row)
row = [Link]()
+ Used when reading records one-by-one
Returns None when no more records
+ METHOD 2: Using fetchall()
+ Fetches all rows at once
+rows = [Link]()
for row in rows:
print(row)
+ Returns list of tuples
Easy to use
+ INSERT Example
+q = "INSERT INTO student VALUES( {},’{}’ )“.format (1,"Ali")
[Link](q)
[Link]()
+ UPDATE Example
+[Link]("UPDATE student SET marks=95 WHERE roll=1")
[Link]()
+ DELETE Example
+[Link]("DELETE FROM student WHERE roll=1")
[Link]()
+ Final Step: Close Connection
+[Link]()
MEMORY TRICK (Very Important)
connect → cursor → execute → fetch → commit (if needed) → close
