Querying a MySQL Table using PyMySQL

PyMySQL is a Python client which can be used to connect to MySQL.A MySQL database table can be queried with connection and cursor objects.

MySQL Updated 7 Mar 2017 2 min read

Overview

To Query a MySQL database server using a Python Program the client machine needs to have a database client API. There are several client APIs to choose from and the focus here is how to use PyMySQL to connect to a MySQL database server and query a database table.

PyMySQL is a MySQL Client written entirely using the Python Programming Language. PyMySQL works with the Python implementations - CPython, PyPy and IronPython.

Steps required to query a MySQL database using PyMySQL

  • Using PyMySQL Module a Connection Object needs to be created.
  • A connection object can be created by passing the destination database server, user name on the database server, associated password and the database name.
  • A cursor type can be specified as well so that results can be retrieved in a way preferred by the developer. For Example, specifying the cursor type as pymysql.cursors.DictCursor the results of a query can be obtained as name value pairs - with column name as name to access the database cell value.
  • Once a connection object is obtained the next step is to create a cursor object.
  • Using the Cursor object a SQL query can be executed.
  • By making a fetchall() call on cursor object, the SQL query results to the MySQL database can be retrieved into a Python dictionary.
  • Doing a dictionary lookup with the column names can retrieve the value of individual columns in a row.

Example

python
# import the mysql client for python

import pymysql

# Create a connection object

dbServerName    = "127.0.0.1"

dbUser          = "root"

dbPassword      = ""

dbName          = "test"

charSet         = "utf8mb4"

cusrorType      = pymysql.cursors.DictCursor

connectionObject   = pymysql.connect(host=dbServerName, user=dbUser, password=dbPassword,

                                     db=dbName, charset=charSet,cursorclass=cusrorType)

try:

    # Create a cursor object

    cursorObject        = connectionObject.cursor()

    # SQL query string

    sqlQuery            = "select * from Student"

    # Execute the sqlQuery

    cursorObject.execute(sqlQuery)

    #Fetch all the rows

    rows                = cursorObject.fetchall()

    for row in rows:

        print(row["id"])

        print(row["firstname"])

        print(row["lastname"])

        print(row["courseid"])

except Exception as e:

    print("Exeception occured:{}".format(e))

finally:

    connectionObject.close()

Output

Output
1

John

Adams

154

2

Jefferson

Thomas

154

3

Ada

Lovelace

154

4

Alan

Turing

154