Skip to content

Access Relation Databases with Python

Access Relation Databases with Python

Last Updated : 16 Jul, 2020

Databases are powerful tools for data scientists. DB-API is Python's standard API used for accessing databases. It allows you to write a single program that works with multiple kinds of relational databases instead of writing a separate program for each one. This is how a typical user accesses databases using Python code written on a Jupyter notebook, a Web-based editor.

There is a mechanism by which the Python program communicates with the DBMS:

The application program begins its database access with one or more API calls that connect the program to the DBMS.
Then to send the SQL statement to the DBMS, the program builds the statement as a text string and then makes an API call to pass the contents to the DBMS.
The application program makes API calls to check the status of its DBMS request and to handle errors.
The application program ends its database access with an API call that disconnects it from the database.

The two main concepts in the Python DB-API are:

1) Connection objects used for

Connect to a database
Manage your transactions.

Following are a few connection methods:

cursor(): This method returns a new cursor object using the connection.
commit(): This method is used to commit any pending transaction to the database.
rollback(): This method causes the database to roll back to the start of any pending transaction.
close(): This method is used to close a database connection.

2) Query objects are used to run queries.

This is a python application that uses the DB-API to query a database.

Python3

from dbmodule import connect  # Create connection object connection = connect('databasename', 'username', 'pswd')  # Create a cursor object cursor = connection.cursor()  # Run queries cursor.execute('select * from mytable') results = cursor.fetchall()  # Free resources cursor.close() connection.close()

First, we import the database module by using the connect API from that module. To open a connection to the database, you use the connection function and pass in the parameters that are the database name, username, and password. The connect function returns the connection object.
After this, we create a cursor object on the connection object. The cursor is used to run queries and get the results.
After running the queries using the cursor, we also use the cursor to fetch the results of the query.
Finally, when the system is done running the queries, it frees all resources by closing the connection. Remember that it is always important to close connections to avoid unused connections taking up resources.

Access Relation Databases with Python

H

hitainkakkar007

Improve

Article Tags :

Practice Tags :

python

Similar Reads

Python Database Tutorial

Python being a high-level language provides support for various databases. We can connect and run queries for a particular database using Python and without writing raw queries in the terminal or shell of that particular database, we just need to have that database installed in our system.A database

How to Connect Python with SQL Database?

In this article, we will learn how to connect SQL with Python using the MySQL Connector Python module. Below diagram illustrates how a connection request is sent to MySQL connector Python, how it gets accepted from the database and how the cursor is executed with result data.SQL connection with Pyth

SQL using Python

In this article, integrating SQLite3 with Python is discussed. Here we will discuss all the CRUD operations on the SQLite3 database using Python. CRUD contains four major operations -Â Note: This needs a basic understanding of SQL.Â Here, we are going to connect SQLite with Python. Python has a nati

Python MySQL Connector is a Python driver that helps to integrate Python and MySQL. This Python MySQL library allows the conversion between Python and MySQL data types. MySQL Connector API is implemented using pure Python and does not require any third-party library.Â This Python MySQL tutorial will

Python SQLite3 module is used to integrate the SQLite database with Python. It is a standardized Python DBI API 2.0 and provides a straightforward and simple-to-use interface for interacting with SQLite databases. There is no need to install this module separately as it comes along with Python after

Python MongoDB Tutorial

MongoDB is a popular NoSQL database designed to store and manage data flexibly and at scale. Unlike traditional relational databases that use tables and rows, MongoDB stores data as JSON-like documents using a format called BSON (Binary JSON). This document-oriented model makes it easy to handle com

Introduction to Psycopg2 module in Python

Psycopg is the most popular PostgreSQL adapter used in Â Python. Â Its works on the principle of the whole implementation of Python DB API 2.0 along with the thread safety (the same connection is shared by multiple threads). It is designed to perform heavily multi-threaded applications that usually cr

Top 7 Databases to Learn in 2025

A database is just like a room in an office where all the files and important information can be stored related to a project. Every company needs a database to store and organize the information. The information that we store can be very sensitive, so we always have to be careful while accessing or

Interface Python with an SQL Database

Python is an easy-to-learn language and connectivity of python with any SQL database is a much-desired option to have the persistence feature. Python is an object-oriented programming language and it is open source. Newcomers to the software industry including school children too can learn Python ea

Access Relation Databases with Python

Databases are powerful tools for data scientists. DB-API is Python's standard API used for accessing databases. It allows you to write a single program that works with multiple kinds of relational databases instead of writing a separate program for each one. This is how a typical user accesses datab