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|
|PostgreSQL||psycopg, PyGresQL, and pyPgSQL|
|Oracle||dc_oracle2 and cx_oracle|
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
|.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:
The Sequel statement mentioned within this function is executed.
import sqlite3 con = sqlite3.connect("UserDB") cur = con.cursor() cur.execute(" select * from emp ") print cur.fetchone()
For all the listed parameters in the sequence, the given SQL statement is executed.
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()
Fetch one row of a query output.
import MySQLdb as my try: db = my.connect(host="126.96.36.199", 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()
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.
import MySQLdb as my try: db = my.connect(host="188.8.131.52", 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()
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.
import MySQLdb as my try: db = my.connect(host="184.108.40.206", 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.
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
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.
This is a guide to Python Database Connection. Here we discuss the Key Steps in Database Connectivity and the Benefits of Python for Database Programming. You may also have a look at the following articles to learn more –