Querying A MySQL Table Using PyMySQL

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 MySQL 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:

# 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:

 

1

John

Adams

154

2

Jefferson

Thomas

154

3

Ada

Lovelace

154

4

Alan

Turing

154

 


Copyright 2020 © pythontic.com