Connect MySQL with pymysql

Posted under » MySQL » Python on 27 May 2021

Create a python 3.8 environment and install pymysql with pip. Activate that venv.

Of course 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 = 'LKY1sUgly'
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

#!/usr/bin/env python

import pymysql

endpoint = 'lms-contents.cs4.ap-southeast-1.rds.amazonaws.com'
dbuser = 'sandylam'
dbpassword = 'LKY1sUgly'
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()

# 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()
Source

web security linux ubuntu python django git Raspberry apache mysql php drupal cake javascript css AWS