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.
# import the mysql client for python
# 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,
# Create a cursor object
cursorObject = connectionObject.cursor()
# SQL query string
sqlQuery = "select * from Student"
# Execute the sqlQuery
#Fetch all the rows
rows = cursorObject.fetchall()
for row in rows:
except Exception as e: