How database API works

DB-API is an acronym for DataBase Application Programming Interface and a library that lets Python connect to the database server. Depending on the relational DB library you use, they may have their own DB-API modules. Similar to the Web APIs we mostly deal with as developers, DB-API is a computing interface specifically for databases between server-side and database – it enables us to communicate with a database using certain protocols (e.g., TCP/IP).

When we work on a client-side web application, we often pull data from web APIs to display them to end-users and, if end-users modify/upload existing/new data, we have to make sure the CRUD operation is invoked accordingly. This client-server model is applied to many modern-day systems to interact with servers. Simply put, databases are the same, they interact using client-server interaction over a network. When an end-user makes a request, a browser will do the same to the web server. At that point, the web server becomes a client who makes a request to the database. This database then acts as a server to fulfill the request.

When we talk about data and transferring data over a network, the two main protocols, TCP and IPthey use IP address and Ports number, are involved. Since they are connection-based protocols, we always have to establish a connection from DB-API to database server over TCP/IP. In other words, we need to explicitly start a session for the connection and end the connection for a session:

# DB-API for PostgreSQL 
import psycopg2 

connection = psycopg2.connect('dbname=test')

# perform task

connection.close()

In each database session, there are many transactions that can occur. The session enables us to control each transaction like git. Just imagine, you make your code change for your task on a new branch and then add the branch before committing and pushing it to the stage. But, if a senior developer found a bug, you may end up reverting it, so to speak:

transaction.add('CREATE TABLE coffee (
    id INTEGER PRIMARY KEY,
    item STRING NOT NULL') ;
)
transaction.add('''
    INSERT INTO coffee (id, item) VALUES (%(id)s, %(item)s);',
    { 'id': 1, 'item': 'Cafe Latte' }
''')

transaction.commit()
transaction.rollback()

If you are familiar with git command lines, as I explained above, this may help you reach enlightenment for the database transaction. It’s intuitive and, as the database can sometimes fail, we can roll back to a previous point before the change was made.

Each transaction is an atomic unit of work to access the database that lets us read and write data. There are four characteristics that make the database more maintainable and reliable:

• Atomicity
• Consistency
• Isolation
• Durability

Just like git, each database operation transaction should meet these properties in order to prevent any errors from concurrent executions, power failure, and so on.

I hope my explanation for DB-API helps you understand the DB-API and the general idea of databases. Thank you for reading, and I hope you enjoy learning web development!