Interface python with SQL Database
A 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. 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. 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.
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. 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.
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.
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.
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.
Why choose Python for database programming
Following are the reason to choose python for 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.
SQL Connectors
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 −
● Importing the API module.
● Acquiring a connection with the database.
● Issuing SQL statements and stored procedures.
● Closing the connection
Here we are using mysql as back end database because of it
is open source,free and portable and widely used. Any one of
mysql-connector or MySQLdb can be used for database
programming.
1. mysql-connector
MySQL-Connector enables Python programs to access
MySQL databases, using an API that is compliant with the
Python [Link] Specification v2.0 (PEP 249). It is
written in pure Python and does not have any dependencies
except for the Python Standard Library.
Steps to use mysql-connector
1. Download Mysql API ,exe file and install it.(click here to
download)
2. Install Mysql-Python Connector (Open command prompt
and execute command) >pip install mysql-connector
3. Now connect Mysql server using python
4. Write python statement in python shell import
[Link]
If no error message is shown means mysql connector is
properly installed.
2. MySQLdb
MySQLdb is an interface for connecting to a MySQL database server from
Python. It implements the Python Database API v2.0 and is built on top of
the MySQL C API.
Steps to use mysqlclient
1. First Upgrade pip command through > python –m pip install –upgrade
pip
2. Install mysqlclient through pip install mysqlclient
3. After successful installation check through import mysqldb
4. If it is installed no error will be displayed otherwise error message will be displayed
Establish connection
For database interface/database programming ,connection must be
[Link] establishing connection there must be mysql
installed on the system and a database and table is already [Link]
following way we can establish a connection with mysql database
through [Link].
import [Link]
mydb=[Link](host="localhost",user="root",passwd="
root“,database=“school”)
print(mydb)
Alternatively we can write the following statement if we are using
mysqldb
import MySQLdb
mydb = [Link]("localhost",“root",“root",“school" )
print(mydb)
In both way we are specifying host,user,password and database name
as [Link] is optional argument if we want to create
database through programming later on.
After successful execution of above statements in python following out
will be displayed
<[Link] object at
0x022624F0>
Otherwise an error message will be shown.
Cursor object :
The MySQLCursor class instantiates objects that can execute
operations such as SQL statements. Cursor objects interact with the
MySQL server using a MySQLConnection object.
How to create cursor object and use it
import [Link]
mydb=[Link](host="localhost",user="root",passwd="
root")
mycursor=[Link]()
[Link]("create database if not exists school")
[Link]("show databases")
for x in mycursor:
print(x)
Through line 4 we are creating a database named school if it is
already not created with the help of cursor object.
Line 5 executes the sql query show databases and store result in
mycursor as collection ,whose values are being fetched in x variable
one by [Link] execution of above program school database is
created and a list of available databases is shown.
How to create table at run time
Table creation is very easy ,if we are already well versed in sql table
creation then we have to just pass the create table query in execute()
method of cursor object. But before table creation we must open the
[Link] we are opening database school(through connect()
method) before student table creation.
import [Link]
mydb=[Link](host="localhost",user="root",pa
sswd="root",database="school")
mycursor=[Link]()
[Link]("create table student(rollno int(3) primary
key,name varchar(20),age int(2))")
On successful execution of above program a table named student with
three fields rollno,name,age will be created in school database.
We can check student table in mysql shell also,if required.
How to create table at run time
Table creation is very easy ,if we are already well versed in sql table
creationthen we have to just pass the create table query in execute()
method of cursor object. But before table creation we must open the
[Link] we are opening database school(through connect()
method) before student table creation.
import [Link]
mydb=[Link](host="localhost",user="root",passwd=
"root",database="school")
mycursor=[Link]()
[Link]("create table student(rollno int(3) primary key,name
varchar(20),age int(2))")
On successful execution of above program a table named student with
three fields rollno,name,age will be created in school database.
We can check student table in mysql shell also,if required.
How to change table structure/(add,edit,remove colum of a table)
at run time
To modify the structure of the table we just have to use alter table
[Link] program will add a column mark in the student table.
import [Link]
mydb=[Link](host="localhost",user="root",passwd=
"root",database="school")
mycursor=[Link]()
[Link]("alter table student add (marks int(3))")
[Link]("desc student")
for x in mycursor:
print(x)
Above program will add a column marks in the table student and will
display the structure of the table
How to fetch all records of a table at run time
import [Link]
mydb=[Link](host="localhost",user="root",passwd=
"root",database="school")
mycursor=[Link]()
[Link]("select * from student")
myrecords=[Link]()
for x in myrecords:
print (x)
[Link]() Method
The method fetches all (or all remaining) rows of a query result set
and returns a list of tuples. If no more rows are available, it returns an
empty list.
How to fetch one record of a table at run time
import [Link]
mydb=[Link](host="localhost",user="root",passwd=
"root",database="school")
mycursor=[Link]()
[Link]("select * from student")
row=[Link]()
while row is not None:
print(row)
row = [Link]()
[Link]() Method
This method retrieves the next row of a query result set and returns a
single sequence, or None if no more rows are available. By default,
the returned tuple consists of data returned by the MySQL server,
converted to Python objects.
[Link]() Method
rows = [Link](size=1)
This method fetches the next set of rows of a query result and returns
a list of tuples. If no more rows are available, it returns an empty list.
How to delete record of a table at run time
import [Link]
mydb=[Link](host="localhost",user="root",passwd=
"root",database="school")
mycursor=[Link]()
[Link]("delete from student where rollno=1")
[Link]()
In above program delete query will delete a record with rollno=[Link]() method is
necessary to call for database transaction.
How to update record of a table at run time
import [Link]
mydb=[Link](host="localhost",user="root",passwd=
"root",database="school")
mycursor=[Link]()
[Link]("update student set marks=99 where rollno=2")
[Link]()
In above program update query update the marks with 99 of rollno=2
Students are advised to develop menu driven program using above
concepts for better understating of python mysql database interface.
Manage Database Transaction
Database transaction represents a single unit of work. Any operation which modifies the
state of the MySQL database is a [Link] MySQL Connector provides the
following method to manage database transactions.
commit – [Link]() method sends a COMMIT
statement to the MySQL server, committing the current
transaction.
rollback – [Link] revert the changes made
by the current transaction.
AutoCommit – [Link] value can be
assigned as True or False to enable or disable the auto-commit
feature of MySQL. By default its value is False.
Manage Database Transaction:
try:
conn = [Link](host='localhost',database='school',user='root',
password='root')
[Link] = false
cursor = [Link]()
sql_update_query = """Update student set marks = 95 where rollno = 2"""
[Link](sql_update_query)
print ("Record Updated successfully ")
#Commit your changes
[Link]()
except [Link] as error :
print("Failed to update record to database rollback: {}".format(error))
#reverting changes because of exception
[Link]()
finally:
#closing database connection.
if(conn.is_connected()):
[Link]()
[Link]()
print("connection is closed")
In above program if update query is successfully executed then commit() method will be
executed otherwise exception error part will be executed where revert of update query
will be done due to [Link] finally we are closing cursor as well as [Link]
rollback or commit we have to set autocommit=false,just like [Link] = false in
above program otherwise rollback will not work
SQL COMMAND:
