Updated April 17, 2023
Overview of Python Database Programming
Python programming language is typically accessing and implemented in all the major database systems like Oracle, Teradata, MySQL, PostgreSQL, etc. All these database can be accessed with their respective Python Application Program Interfaces (APIs), such as MySQLdb for MySQL, DB2 for Pydb2, dc_oracle2 & cx_oracle for Oracle, and ‘psycopg, PyGresQL & pyPgSQL’ for Postgresql. A few of the commonly used database connection objects in python are .cursor(), .commit(), .rollback(), .close(), etc. Each of these objects have other functions objects as well, for instance, the types of .cursor() object are .execute(), .executemany(), .fetchone(), .fetchmany(), .fetchall() and cursor.next().
The database is a collection of prearranged information that can effortlessly be used, managed, revised. The key features of a DB API are,
- Create a database connection
- Work on SQL statements and stored procedures
- The connection can be closed
Benefits of Python for Database Programming
- Compared to other languages, python programming is faster and easy.
- In python, mandatory operations like opening and closing the database connections are carried out by python itself. For all other programming languages, these types of operations are carried out specifically by the developer.
- The python database API’s support a wide extent of database setups, so it makes the task of connecting to the databases a much easier process.
Python DB API’s:
| Databases | Python DB API’s |
| MYSQL | MySQLdb |
| PostgreSQL | psycopg, PyGresQL, and pyPgSQL |
| Oracle | dc_oracle2 and cx_oracle |
| DB2 | Pydb2 |
Key Steps in Database Connectivity
From a python perspective, there are four major steps in the process of database connection using python. they are as below,
1. Creating the connection object
4. termination of the created connection
2. To accommodate the reading and writing process, declare a cursor
3. Database interactions
| Connection Objects | |
| Name | Description |
| .close() | Closes established connectivity with the database |
| .commit() | Commit pending transactions with the database |
| .rollback() | This transaction consent to will roll back to the start of a pending transaction |
| .cursor() | An object representing the cursor is created |
Python Cursor Objects
Following is a list of python cursor objects:
1) .execute()
The Sequel statement mentioned within this function is executed.
Syntax
execute(sql, [parameters])Example
import sqlite3
con = sqlite3.connect("UserDB")
cur = con.cursor()
cur.execute(" select * from emp ")
print cur.fetchone()
2) .executemany()
For all the listed parameters in the sequence, the given SQL statement is executed.
Syntax
executemany(sql, [parameters])Exampl
import sqlite3
def aplphabet_generator():
import string
for D in string.letters[:26]:
yield (D,)
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute(" create table characters( Aplphabet_Column ) ")
cur.executemany("insert into characters( Aplhabet_Column ) values (?)", char_generator())
cur.execute(" select Aplhabet_Column from characters ")
print cur.fetchall()
3) .fetchone()
Fetch one row of a query output.
Example
import MySQLdb as my
try:
db = my.connect(host="129.0.0.1",
user="admin",
passwd="admin",
db="emp"
)
cursor = db.cursor()
sql = "select * from dept#1 where id < 10"
number_of_rows = cursor.execute(sql)
while True:
row = cursor.fetchone()
if row == None:
break
print(row)
db.close()
4) .fetchmany()
Fetch a specific set of rows of a query result. the parameter specifies the number of rows per call. The number of rows being fetched largely depends on the involved array size of the cursor element. So as the number of rows indicated in the parameter given, the same number of rows must be attempted to be fetched. If this is not capable because of the specific rows available, then fewer rows may be returned.
Example
import MySQLdb as my
try:
db = my.connect(host="128.0.0.10",
user="admin",
passwd="admin",
db="emp"
)
cursor = db.cursor()
sql = "select * from dept_#1 where id < 10"
number_of_rows = cursor.execute(sql)
print(cursor.fetchmany(2)) # 2 row fetched
db.close()
5) .fetchall()
Fetch all the rows of a query. these rows are returned in a sequence. here the performance of the fetch could be impacted at times by the cursor array fetch. When the cursor array size is extremely large, then the amount of time taken to pull the rows will also be comparatively very high.
Example
import MySQLdb as my
try:
db = my.connect(host="128.0.0.10",
user="admin",
passwd="admin",
db="emp"
)
cursor = db.cursor()
sql = "select * from dept_#1 where id < 10"
number_of_rows = cursor.execute(sql)
print(cursor.fetchall()) db.close()
6) Cursor.scroll(value [, mode=’relative’ ])
Scrolls through the cursor according to the mentioned mode value. if the mode is relative, then the value mentioned is considered an offset; if the mode is absolute, then the value mentioned is the target position.
- Cursor.next(): The next row is returned from the currently executing sequel statement position.
- Cursor.–iter–(): Theaters the cursor suitable for iteration protocol.
- Cursor.lastrowid(): the row id of the lastly modified row is returned here.
Python Database Operations
The key operations of any database insert, delete, update and select. all these CRUD operations can be implied through python also. In python, mandatory operations like opening and closing the database connections are carried out by python itself. For all other programming languages, these types of operations are carried out specifically by the developer. The below example depicts the application of these operations.
Example
import sqlite3
db=sqlite3.connect('test.db')
qry1="insert into student (name, age, marks) values(?,?,?);"
qry2="update student set age=? where name=?;"
qry3="SELECT * from student;"
qry4="DELETE from student where name=?;"
students=[('Amarh', 17, 20), ('Deepika', 45, 87)]
try:
cur=db.cursor()
cur.executemany(qry1, students)
cur.execute(qry2, (19,'Deepak'))
db.commit()
cur.execute(qry3)
print(" record updated!! ")
cur.execute(qry4, ('Bill',))
db.commit()
print(" record deleted!! ")
except:
print(" error found")
db.rollback()
db.close()
Python Db Exception Hierarchy
- StandardError
- Warning
- Error
- InterfaceError
- DatabaseError
- DataError
- OperationalError
- IntegrityError
- InternalError
- ProgrammingError
- NotSupportedError
Python DB Oriented Constructors
- Date(year, month, day): Builds an object with a date value in it
- Time(hour, minute, second): Builds an object with a time value in it
- Timestamp(year, month, day, hour, minute, second): Builds an object with timestamp value in it
- Binary(string): An python object capable of holding binary values is constructed
- STRING type: Describes all the columns which are string type in the database
- NUMBER type: Describes all the columns which are of number type
- DATETIME type: Mentions all the date and time type columns present in the database
- ROWID type: Reaches the row id column in the database
Conclusion – Python Database Connection
Python definitely stands out to be one of the most flexible programming interfaces for database-oriented programming. The classified set of python DB-API’s makes the task of communicating with DB’s an efficient process irrespective of any database.
Recommended Articles
We hope that this EDUCBA information on “Python Database Connection” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
