How to fetch data from database in Python

Hey guys, in this post we will learn how to fetch data from database in python or how to select row of table. We will fetch the data using MySQL Connector module.

Steps for fetching data:

Below are some steps which we will be using for fetching data.

Connect to Database: First connect to database you can also follow below post for database connection.

How to connect MySQL Database in Python

Use Select Query: Now create a select query for fetching records to database table as per requirement.

eg: select field1, field2, field3 from table where field = value;

Create Object: Create cursor object from connection class and use connection.cursor() method to create a cursor object, using connection.cursor() method we can create a cursor object.

Execute Query: Now execute select query using cursor.execute() method.

Fetch Records: Now fetch records using fetchall() method.

Iterate Row: Using the loop iterate a row list and then access each row individually.

Close Connection: Now using cursor.close() method close the connection.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='')

    sql_select_Query = "select * from User"
    cursor = connection.cursor()
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()
    print("Total records: ", cursor.rowcount)

    print("\nPrinting each row")
    for row in records:
        print("Id = ", row[0], )
        print("Name = ", row[1])
        print("Email  = ", row[2], "\n")

except mysql.connector.Error as e:
    print("Data Fetching Error :", e)
finally:
    if connection.is_connected():
        connection.close()
        cursor.close()
        print("connection closed")

How to fetch data using Python variables as parameters

We can use parameters with the select query like using where clause we can pass variables for this, we can use placeholders “%s”.

import mysql.connector
def fetchUser(id):
    try:
        connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='')
        cursor = connection.cursor()
        sql_select_query = """select * from User where id = %s"""
        cursor.execute(sql_select_query, (id,))
        record = cursor.fetchall()

        for row in record:
            print("Id = ", row[0], )
            print("Name = ", row[1])
            print("Email  = ", row[2], "\n")

    except mysql.connector.Error as error:
        print("Error: Fetch error: {}".format(error))

    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("Connection closed")

fetchUser(1)

Fetch single row: Using the fetchone function we can fetch the single row from the table.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='')
    mySql_select_Query = "select * from User"
    cursor = connection.cursor(buffered=True)
    cursor.execute(mySql_select_Query)
    record = cursor.fetchone()
    print(record)

except mysql.connector.Error as error:
        print("Error: Fetch error: {}".format(error))
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("Connection closed")

Fetch records using the column names.

We can fetch the records using column name with fetchall() function.

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',database='pythondb',user='root',password='')
    sql_select_Query = "select * from User"
    cursor = connection.cursor(dictionary=True)
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()
    print("Fetching records with column name")
    for row in records:
        id = row["Id"]
        name = row["Name"]
        email = row["Email"]
        print(id, name, email)

except Error as e:
    print("Error: Fetch error", e)
finally:
    if connection.is_connected():
        connection.close()
        cursor.close()
        print("Connection closed")

Keep Learning 🙂