PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Python Select from MySQL Table

Python Select from MySQL Table

Updated on: March 9, 2021 | 39 Comments

This article demonstrates how to select rows of a MySQL table in Python.

You’ll learn the following MySQL SELECT operations from Python using a ‘MySQL Connector Python’ module.

  • Execute the SELECT query and process the result set returned by the query in Python.
  • Use Python variables in a where clause of a SELECT query to pass dynamic values.
  • Use fetchall(), fetchmany(), and fetchone() methods of a cursor class to fetch all or limited rows from a table.

Further Reading:

  • Solve Python MySQL Exercise
  • Read Python MySQL Tutorial (Complete Guide)

Table of contents

  • Prerequisites
  • Steps to fetch rows from a MySQL database table
  • Use Python variables as parameters in MySQL Select Query
  • Select limited rows from MySQL table using fetchmany and fetchone
    • Example to fetch fewer rows from MySQL table using cursor’s fetchmany
    • Fetch single row from MySQL table using cursor’s fetchone
  • Python Fetch MySQL row using the column names
  • Select MySQL column value into a Python Variable
  • Next Steps:

Prerequisites

Before moving further, Please make sure you have the following in place: –

  • Username and password to connect MySQL
  • MySQL table name from which you want to select data.

For this lesson, I am using a ‘Laptop’ table present in my MySQL server.

If a table is not present in your MySQL server, you can refer to our article to create a MySQL table from Python.

You can also download a SQL query file, which contains SQL queries for table creation and data so that you can use this table for your INSERT operations.

MySQL Laptop table with data
MySQL Laptop table with data

Steps to fetch rows from a MySQL database table

Follow these steps: –

How to Select from a MySQL table using Python

  1. Connect to MySQL from Python

    Refer to Python MySQL database connection to connect to MySQL database from Python using MySQL Connector module

  2. Define a SQL SELECT Query

    Next, prepare a SQL SELECT query to fetch rows from a table. You can select all or limited rows based on your requirement. If the where condition is used, then it decides the number of rows to fetch.
    For example, SELECT col1, col2,…colnN FROM MySQL_table WHERE id = 10;. This will return row number 10.

  3. Get Cursor Object from Connection

    Next, use a connection.cursor() method to create a cursor object. This method creates a new MySQLCursor object.

  4. Execute the SELECT query using execute() method

    Execute the select query using the cursor.execute() method.

  5. Extract all rows from a result

    After successfully executing a Select operation, Use the fetchall() method of a cursor object to get all rows from a query result. it returns a list of rows.

  6. Iterate each row

    Iterate a row list using a for loop and access each row individually (Access each row’s column data using a column name or index number.)

  7. Close the cursor object and database connection object

    use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes.

python select from MySQL Table
Python select from MySQL Table

Example

In this example, we are fetching all the rows from the Laptop table and copying them into Python variables so we can use it in our program.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')

    sql_select_Query = "select * from Laptop"
    cursor = connection.cursor()
    cursor.execute(sql_select_Query)
    # get all records
    records = cursor.fetchall()
    print("Total number of rows in table: ", cursor.rowcount)

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

except mysql.connector.Error as e:
    print("Error reading data from MySQL table", e)
finally:
    if connection.is_connected():
        connection.close()
        cursor.close()
        print("MySQL connection is closed")

Output: –

Total number of rows in Laptop is:  7
Printing each laptop record

Id =  1
Name =  Lenovo ThinkPad P71
Price  =  6459.0
Purchase date  =  2019-08-14 

Id =  2
Name =  Area 51M
Price  =  6999.0
Purchase date  =  2019-04-14 

Id =  3
Name =  MacBook Pro
Price  =  2499.0
Purchase date  =  2019-06-20 

Id =  4
Name =  HP Pavilion Power
Price  =  1999.0
Purchase date  =  2019-01-11 

Id =  5
Name =  MSI WS75 9TL-496
Price  =  5799.0
Purchase date  =  2019-02-27 

Id =  6
Name =  Microsoft Surface
Price  =  2330.0
Purchase date  =  2019-07-23 

Id =  7
Name =  Acer Predator Triton
Price  =  2435.0
Purchase date  =  2019-08-15 

MySQL connection is closed

Note: Use the following methods to fetch data returned by a cursor.execute()

  • cursor.fetchall() to fetch all rows
  • cursor.fetchone() to fetch a single row
  • cursor.fetchmany(SIZE) to fetch limited rows

Use Python variables as parameters in MySQL Select Query

We often need to pass variables to SQL select query in where clause to check some conditions. Let’s say the application wants to fetch laptop price by giving any laptop id at runtime. To handle such a requirement, we need to use a parameterized query.

A parameterized query is a query in which placeholders (%s) are used for parameters and the parameter values supplied at execution time.

cursor.execute("SELECT Price FROM Laptop WHERE id = "ID from application")

Example

import mysql.connector

def get_laptop_detail(id):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='electronics',
                                             user='pynative',
                                             password='pynative@#29')

        cursor = connection.cursor()
        sql_select_query = """select * from laptop where id = %s"""
        # set variable in query
        cursor.execute(sql_select_query, (id,))
        # fetch result
        record = cursor.fetchall()

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

    except mysql.connector.Error as error:
        print("Failed to get record from MySQL table: {}".format(error))

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

get_laptop_detail(1)
get_laptop_detail(2)

Output:

Id =  1
Name =  Lenovo ThinkPad P71
Join Date =  6459.0
Salary  =  2019-08-14 

connection is closed
Id =  2
Name =  Area 51M
Join Date =  6999.0
Salary  =  2019-04-14 

MySQL connection is closed

Select limited rows from MySQL table using fetchmany and fetchone

In some situations, fetching all the rows from a table is a time-consuming task if a table contains thousands of rows.

If we fetch all rows, we need more space and processing time. So it is essentials to use the fetchmany() method of cursor class to fetch fewer rows.

Syntax of the cursor’s fetchmany()

rows = cursor.fetchmany(size=row_size)

Cursor’s fetchmany() methods return the number of rows specified by size argument, defaults value of size argument is one. For example, if the specified size is 5, then it returns five rows.

Note: If a table contains a row lesser than the specified size, then fewer rows are returned.

Syntax of fetchone()

row = cursor.fetchone()

This method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list.

This method returns a single record or None if no more rows are available.

The fetchone() method is internally used by a fetchall() and fetchmany() to fetch rows.

Example to fetch fewer rows from MySQL table using cursor’s fetchmany

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')

    mySql_select_Query = "select * from laptop"
    cursor = connection.cursor()
    cursor.execute(mySql_select_Query)
    row_count = 2
    records = cursor.fetchmany(row_count)

    print("Total number of rows is: ", cursor.rowcount)
    print("Printing ", row_count, " Laptop record using cursor.fetchmany")
    for row in records:
        print(row)

except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("connection is closed")

Output:

Total number of rows is:  2
Printing  2  Laptop record using cursor.fetchmany
(1, 'Lenovo ThinkPad P71', 6459.0, datetime.date(2019, 8, 14))
(2, 'Area 51M', 6999.0, datetime.date(2019, 4, 14))

Note: If you are getting MySQL Unread result error set buffered=True in connection like connection.cursor(buffered=True).

Fetch single row from MySQL table using cursor’s fetchone

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')

    mySql_select_Query = "select * from laptop"
    cursor = connection.cursor(buffered=True)
    cursor.execute(mySql_select_Query)
    record = cursor.fetchone()
    print(record)

except mysql.connector.Error as error:
    print("Error while connecting to MySQL", error)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Output: –

Printing first record (1, 'Lenovo ThinkPad P71', 6459.0, datetime.date(2019, 8, 14))
MySQL connection is closed

Python Fetch MySQL row using the column names

You can also retrieve result using columns names instead of id. For example, you would like to do something like this.

records = cursor.fetchall()

for row in records:
    val1 = row["columnName1"], )
    val2 = row["columnName2"])
    val3 = row["columnName3"])

If you try to fetch data using column name directly, you will receive a TypeError: tuple indices must be integers or slices, not str.

To select records from my MySQL table using a column name, we only need to change the cursor creation. Replace the standard cursor creation with the following code, and you are ready to fetch records from my MySQL table using a column name.

cursor = connection.cursor(dictionary=True)

Why set dictionary=True? because MySQLCursorDict creates a cursor that returns rows as dictionaries so we can access using column name (here column name is the key of the dictionary)

In the following example, I have selected all the records from my MySQL table using a column name instead of the column’s integer index.

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='Electronics',
                                         user='pynative',
                                         password='pynative@#29')
    sql_select_Query = "select * from Laptop"
    # MySQLCursorDict creates a cursor that returns rows as dictionaries
    cursor = connection.cursor(dictionary=True)
    cursor.execute(sql_select_Query)
    records = cursor.fetchall()
    
    print("Fetching each row using column name")
    for row in records:
        id = row["Id"]
        name = row["Name"]
        price = row["Price"]
        purchase_date = row["Purchase_date"]
        print(id, name, price, purchase_date)

except Error as e:
    print("Error reading data from MySQL table", e)
finally:
    if connection.is_connected():
        connection.close()
        cursor.close()
        print("MySQL connection is closed")

Output:

Fetching each row using column name
1 Lenovo ThinkPad P71 6459.0 2019-08-14
2 Area 51M 6999.0 2019-04-14
3 MacBook Pro 2499.0 2019-06-20
4 HP Pavilion Power 1999.0 2019-01-11
5 MSI WS75 9TL-496 5799.0 2019-02-27
6 Microsoft Surface 2330.0 2019-07-23
7 Acer Predator Triton 2435.0 2019-08-17
10 Lenovo ThinkPad P71 6459.0 2019-08-14
11 Lenovo ThinkPad P71 6459.0 2019-08-14
MySQL connection is closed

Select MySQL column value into a Python Variable

Let’s see how to execute the following SELECT SQL Query and store the table’s column value into a Python variable for further processing.

Ig you execute the below code you will get {u’Price’: u’7000′}.

cursor.execute("SELECT Price FROM Laptop WHERE id = 21")
print (cursor.fetchone() )

You can get the output like

{u'Price': u'7000'}

Let’s see how to extract just the column value (7000) and save it in a variable to do some calculations so you can give a 10% discount on it.

import mysql.connector

try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')

    sql_Query = "select price from laptop where id =%s"
    id = (1,)
    cursor = connection.cursor()
    cursor.execute(sql_Query, id)
    record = cursor.fetchone()

    # selecting column value into variable
    price = float(record[0])
    print("Laptop price is : ", price)

except mysql.connector.Error as error:
    print("Failed to get record from database: {}".format(error))

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

Output:

Laptop price is :  6459.0
MySQL connection is closed

Let’s understand this example:

  • You can see in the above example after fetching a row using a cursor.fetchone() we converted column value to appropriate type using value = float(record[0]).
  • We retrieved the 0th index value from the result set.

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python MySQL

  • Python MySQL Connection Guide
  • Python MySQL Insert
  • Python MySQL Select
  • Python MySQL Update
  • Python MySQL Delete
  • Call MySQL Stored Procedure
  • Python MySQL Parameterized Query
  • Python MySQL Transactions
  • Python MySQL Connection Pooling
  • Python MySQL BLOB
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com