The database is a collection of tables related to each other via columns. For most real-world projects, a database is very important. To create, access, and manipulate data we can use SQL (Structured Query Language).To avoid redundancy of data we can also use normalization.
For Python database Access programming, Python supports many database servers such as:
MySQL, Oracle, PostgreSQL, SQLite, Sybase, Microsoft SQL Server, MySQL, Microsoft Access, and many more.
Python Database Access supports Data Query Statements, Data Definition Language (DDL), and Data Manipulation Language (DML). The standard database interface for Python is Python DB-API. For that, we have the module MySQLdb for MySQL.
Defining 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.
How to Install MySQLdb?
To install the python MySQLdb module, we need to install Python’s current version i.e. 3.7. We need to find the location of Python Scripts where the pip command is located. First, open the cmd and reach the location of Python Scripts. To open cmd, press “Windows+R” and type cmd. Now reach where scripts are located.
Before proceeding, make sure you have MySQLdb installed on your systems. Just type the following code in your Python script and execute it.
If there show the following result, then it means MySQLdb module is not installed yet:
To install MySQLdb module, use the following command:
Database Connection:
Before connecting to a MySQL database, make sure of the followings −
- Create a database DEMODB.
- Create a table STUDENT in DEMODB.
- This table has fields FULL_NAME, AGE, CLASS.
- User ID “demouser” and password “9900” are set to access DEMODB.
- Python module MySQLdb is installed on your machine.
Creating DataBase:
To connect with MySQL database server from Python, we need to import the mysql.connector interface.
Syntax:
Creating Database Table:
MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases.
SQL commands are case insensitive i.e CREATE and create signify the same command.
Once a database connection is established, it’s easy to create tables or records into the database tables using the Execute Method of the created cursor.
Example:
Create Database table STUDENT:
#!/usr/bin/python
import MySQLdb
#opening database connection
db = MySQLdb.connect("localhost","demouser","9900","DEMODB" )
#preparing cursor object using cursor() method
cursorobj = db.cursor()
#Drop table if it already exist using execute() method.
cursorobj.execute("DROP STUDENT TABLE IF EXISTS ")
#Creating table as per requirement
mydbsql = """CREATE TABLE STUDENT (
FULL_NAME CHAR(20) NOT NULL,
AGE INT,
CLASS INT(1))"""
cursorobj.execute(mydbsql)
#Disconnect from server
db.close()
INSERT Operation:
In Database INSERT Operation is used to insert any new record to the database.
Example:
#!/usr/bin/python
import MySQLdb
#opening database connection
db = MySQLdb.connect("localhost","demouser","9900","DEMODB" )
#preparing cursor object using cursor() method
cursorobj = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO STUDENT(FULL_NAME,
AGE, CLASS)
VALUES ('BOB TOM', 18, 10 GRADE)"""
try:
# Execute the SQL command
cursorobj.execute(mydbsql)
# your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
UPDATE Operation:
In any Database, the UPDATE Operation is used to update one or more records available in the database.
The given procedure will updates the AGE records by 2 year.
Example:
#!/usr/bin/python
import MySQLdb
#opening database connection
db = MySQLdb.connect("localhost","demouser","9900","DEMODB" )
#preparing cursor object using cursor() method
cursorobj = db.cursor()
# Prepare SQL query to UPDATE required fields
mydbsql = "UPDATE STUDENT SET AGE = AGE + 2" % ('M')
try:
# Execute the SQL command
cursorobj.execute(mydbsql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
READ Operation:
On any Database, READ Operation used to fetch some useful information from the given database.
Once we have created database connection, we are ready to make a query into this database. There are 2 types of fetch method and one rowcount method.
1- fetchone():
- Fetches the very next row from the result set( An object that is returned when a cursor object is used to query a table) of the given query.
2- rowcount:
- Known as an Attribute. It returns an integer denoting the number of records that a call to execute() affected.
3- fetchall():
- Fetches the entire result set from the query. Already extracted records will be excluded.
Example:
#!/usr/bin/python
import MySQLdb
#opening database connection
db = MySQLdb.connect("localhost","demouser","9900","DEMODB" )
#preparing cursor object using cursor() method
cursorobj = db.cursor()
mydbsql = "SELECT * FROM STUDENT " % (1000)
try:
# Execute the SQL command
cursorobj.execute(mydbsql)
# Fetch all the rows in a list of lists.
results = cursorobj.fetchall()
for row in results:
fname = row[0]
age = row[1]
class = row[3]
# Now print fetched result
print "fname=%s,age=%d,class=%d" % \
(fname, age, class )
except:
print "Error: unable to fecth data"
# disconnect from server
db.close()
DELETE Operation:
In Database, DELETE Operation is used when there is a need to delete some records from database.
The procedure to delete the AGE records from STUDENT Database.
Example:
#!/usr/bin/python
import MySQLdb
#opening database connection
db = MySQLdb.connect("localhost","demouser","9900","DEMODB" )
#preparing cursor object using cursor() method
cursorobj = db.cursor()
#DELETE required records
mysqldb = ("DELETE AGE FROM STUDENT > '%d'" % )
try:
# Execute the MYSQLdb command
cursorobj.execute(mysqldb)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
Some operation used in Database to offer some other tasks or operations.
| ROLLBACK Operation | COMMIT Operation |
| The Rollback() method is used when there are some changes that are not needed, then you can revert back those changes completely by using this method. | The Commit Operation is used to give a green signal to the database to finalize the changes because, after this operation, no change can be reverted back. |
| Code: db.rollback() | Code: db.commit() |
Transaction Errors in Database:
While holding a transaction, there may come across different kinds of errors. The DataBase API defines a number of errors that must exist in each database module.
Given below are the lists of those errors.
1- Error:
- This is known as the Base Class for Errors and as a Subclass to StandardError.
2- DatabaseError:
- This is a subclass to Error and Uses for database errors.
3- DataError:
- This is a subclass of DatabaseError. Uses when there is an error in the data in Python .
4- InterfaceError:
- This is a subclass to Error and Uses for errors relating to the module for database access in Python.
5- Warning:
- This is a subclass of StandardError. In Python used for non-fatal issues.
6- InternalError:
- This is a subclass of DatabaseError. In Python, this is used for those errors internal to the module used for the database access.
7- NotSupportedError:
- This is a subclass of DatabaseError. Python raises this error, When we attempt to call functionality that it doesn’t support.
8- OperationalError:
- This is a subclass of DatabaseError. Python throws this error when it loses connection to a database.
9- ProgrammingError:
- This is a subclass of DatabaseError. This error caused by bad table names. May happen when we try to create a duplicate database in Python.
10- IntegrityError:
- This is a subclass of DatabaseError. Used for situations that would damage the relational integrity, such as uniqueness constraints or foreign keys.