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