Database Programming

An organization runs mainly on the pattern of decisions made to secure its future in the market. Therefore, it is essential to store an organization’s data for future decision-making. The database provides such a facility by allowing storage of a wide range of data. The database consists of an organized collection of structured data. Database systems allow creating various files to store data and provide the ability to enter details, update information, run queries on the data, develop relationships of the table, and provide desired results.

Database Programming in Python

Like many other programming languages, Python also provides storage of relevant and vital information through the use of the database. Most importantly, it supports a wide range of databases. For instance, MySQL, Oracle, Sybase, GadFly, Microsoft SQL Server 2000, Informix, Interbase, and PostgreSQL are few well-known databases. It also supports many database languages like Data Definition Language (DDL), Data Manipulation Language (DML), and Data Query statements. Python uses the Database API to provide a programming interface to database applications.

Database API in Python

There is an API in Python that is independent of any engine, known as DB API. This API provides the ability to write independent scripts and access any database engine. There are different implementations available for relevant databases. For example, the API implementation supporting MySQL is MySQLdb, and for PostgreSQL, there are psycopg, PyGresQL, and pyPgSQL modules. Moreover, for Oracle, there are dc_oracle2 and cx_oracle. In short, the database API for Python supports a wide range of platforms and provides connections objects, cursor objects, shared exceptions, and numerous other modules to manipulate information.

Advantages of using Database Programming in Python

Python database programming has some unique advantages over other languages. Following are some of these advantages:

· Faster Approach: Performing database-specific operations in python is an efficient and way faster approach than in other languages.

· Portability: Python platform provides portability over other systems. It provides similar interfaces over various systems. Extension of modules is the native ability of Python programming. Moreover, the database in Python is independent of the platform.

· Compatibility: Database programming supports SQL cursor operations. It also provides support to relational database systems. There is an ease of migrating database application interfaces since it has compatibility with several databases.

· Connection Objects: Python database programming takes care of opening and closing database connections, making it more efficient than others.

Database Programming Example in Python

The users need to connect Python with the MySQL database server to perform database operations in Python. Therefore, the initial step is to import the mysql.connector interface for the connection between the two. Following is an example of creating a database in Python:

#creating database in Python
import mysql.connector
example_dataBase = mysql.connector.connect(
 host ="localhost",
 user ="user1",
 passwd ="ab12"
)

cursorObject = example_dataBase.cursor()
cursorObject.execute("CREATE DATABASE testing_database")

The above piece of code creates a database as testing_database in Python with a specific host, username, and password. The users can set the username and password of their choice and choose to host it on any server. However, in the example app, the user is using localhost as the database host.

Creating table in the database

Like any other database, the users can create the tables in the database using Python. Following is the code to create a table in the tesing_database:

import mysql.connector
example_dataBase = mysql.connector.connect(
 host = "localhost",
 user = "user1",
 passwd = "ab12",
 database = "testing_database"
)
cursorObject = example_dataBase.cursor()
bookInformation = """CREATE TABLE BOOK(
 TITLE VARCHAR(20) NOT NULL,
 AUTHOR VARCHAR(20) NOT NULL,
 PAGES INT,
 CODE INT NOT NULL,
 SECTION VARCHAR(5),
 COPIES INT
 )"""

cursorObject.execute(bookInformation)
example_dataBase.close()

The code above creates a table called bookInformation, which has fields like TITLE, AUTHOR, PAGES, CODE, SECTION, and COPIES. Finally, the users can insert, delete, select and search from the database using various options that MYSQL provides to manipulate the database data. Hence, the users can conveniently store and access their data in the database using Python language.

Other useful articles: