Connect MySQL with pymysql

Posted under » MySQL » Python on 27 May 2021

Activate a venv that has PyMysql installed. Assuming you already have a MySQL database created.

Create a python file... say coon.py and make it able to execute.

#!/usr/bin/env python
import pymysql

endpoint = 'lms-contents.cs4.ap-southeast-1.rds.amazonaws.com'
dbuser = 'sandylam'
dbpassword = 'LKly'
database = 'LMS_contents'

# Open database connection
db = pymysql.connect(host=endpoint,user=dbuser,password=dbpassword,database=database)

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " % data)

# disconnect from server
db.close()

Go to the folder and execute ./coon.py It will show "Database version : 8.0.20".

Now let's read the database. Once the database connection is established, you are ready to make a query into this database. You can use either fetchone() method to fetch a single record or fetchall() method to fetch multiple values from a database table

• fetchone() − It fetches the next row of a query result set. A result set is an object that is returned when a cursor object is used to query a table.
• fetchall() − It fetches all the rows in a result set. If some rows have already been extracted from the result set, then it retrieves the remaining rows from the result set.
• rowcount − This is a read-only attribute and returns the number of rows that were affected by an execute() method.

... same con as above

# Open database connection
db = pymysql.connect(host=endpoint,user=dbuser,password=dbpassword,database=database)

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query read the database.
sql = "SELECT * FROM auth_user WHERE `username` LIKE '%weiling%'"

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      last_login = row[2] 
      username = row[4]
      # Now print fetched result
      print ("Date = %s, baboy = %s" % \
         (last_login, username))
except:
   print ("Error: unable to fetch data")

# disconnect from server
db.close()

You might want to use Peewee