Python Database Connection

Python has the ability to connect to databases such as MySQL, Oracle, and more. With the help of modules like PyMySQL and psycopg2, we can easily establish a connection to the database and execute queries.

Syntax

The syntax for connecting to a database using PyMySQL module is as follows:

import pymysql

connection = pymysql.connect(
    host="hostname",
    user="username",
    password="password",
    db="database_name"
)

The syntax for connecting to a database using psycopg2 module is as follows:

import psycopg2

connection = psycopg2.connect(
    host="hostname",
    user="username",
    password="password",
    database="database_name"
)

Example

Here's an example of connecting to a MySQL database using PyMySQL module:

import pymysql

connection = pymysql.connect(
    host="localhost",
    user="root",
    password="password",
    db="test_db"
)

cursor = connection.cursor()
cursor.execute("SELECT * FROM users")

for row in cursor:
    print(row)

Here's an example of connecting to a PostgreSQL database using psycopg2 module:

import psycopg2

connection = psycopg2.connect(
    host="localhost",
    user="postgres",
    password="password",
    database="test_db"
)

cursor = connection.cursor()
cursor.execute("SELECT * FROM users")

for row in cursor:
    print(row)

Output

The output of the above examples will be the retrieved data from the users table in the test_db.

Explanation

In both examples above, we first import the required module (pymysql or psycopg2). After that, we establish a connection to the database by passing the necessary parameters such as host, user, password, and db or database.

Once the connection is established, we create a cursor object that helps us execute SQL queries. We use the execute() method to execute our desired query, and then we fetch the result using a for loop to iterate over the cursor object.

Use

Connecting to databases using Python is useful when we want to interact with the data present in the database. We can easily execute CRUD (Create, Read, Update, Delete) operations on the data without the need for third-party software.

Important Points

• Make sure to install the required module (pymysql or psycopg2) before using them.
• Make sure the values for the parameters (host, user, password, db or database) are correct and match with the database configuration.
• Always close the database connection after executing queries to avoid any memory leaks.

Summary

In this tutorial, we learned how to connect to a database using Python. We used the PyMySQL and psycopg2 modules to demonstrate connecting to a MySQL and PostgreSQL database, respectively. We also learned how to execute queries and read data from the database.