It’s almost Friday night, and the squad at H+S is ready to get cooking. Dim down the lights and slip into something more comfortable as we take you on this 100% organic flavor extravaganza. Tonight’s menu? A Python MySQL library: PyMySQL.
PyMySQL is lightweight and perfect for fulfilling MySQL queries. If you want bells and whistles, you’re probably barking up the wrong tree (and you probably should’ve used a DB other than MySQL in the first place).
Why write tutorials for technologies we openly trash talk? Out of necessity, of course! There’s nothing wrong with MySQL, most enterprises are married to it in some way. Thus, A great use case for PyMySQL is for usage in AWS lambda when working with large enterprise systems. We’ll get to that, but for now let’s cook up something good.
Heat up the Stove
Turn on the gas and prep the table to set with your favorite collection of plates! That’s right, we’re talking boilerplate. We knew this was coming; it seems like every time you want to do something tangibly cool, we need to get into the business of managing connections and whatnot.
To ease the pain, I’ll share with you a preferred method of handling opening connections with PyMySQL. Here we set a function to separate basic connection logic and error messaging from our app:
import sys import pymysql
import logger conn = None def openConnection():
global conn try:
if(conn is None): conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)
elif (not conn.open): conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)
except: logger.error("ERROR: Unexpected error: Could not connect to MySql instance.")sys.exit()Nothing fancy here: we set a global variable conn to serve as our connection, and have some basic logic on how to interact with our database. Running openConnection will attempt to connect to a MySQL db with supplied credentials, or throw an error if something goes horribly wrong.
Now we can keep this separate from the rest of our code. Out of sight, out of mind.
Meat and Potatoes
With the boring stuff out of the way, let’s dig in to some goodness. We’ll start off with a basic use case: selecting all rows from a table:
def getRecords():
try:
openConnection() with conn.cursor() as cur:
sql = "SELECT * FROM table"
cur.execute(sql)
result = cur.fetchall()
print(result)
cur.close()
conn.close()
except Exception as e:
print(e)
finally:
print('Query Successful') getRecords()We split our function into your standard try/except/finally breakdown. What we’re trying is opening a connection using the function we created earlier, and running queries against it.
The preferred syntax in PyMySQL is to keep our query in a single string, as seen in our variable sql. With our query ready, we need to execute the query, fetch the resulting records and print the result. We’re sure to close the connection once we’re done with executing queries… this is critical to ensure db connections don’t stay active.
Simple so far, but we’re about to kick it up a notch.
Selecting rows
You may have noticed we used .fetchall() to select all records. This is important to differentiate from .fetchone(), which simply selects the first record.
We can iterate over the rows resulting from .fetchall() with a simple loop, as shown in the example below. Beware: attempting to print the result of .fetchall() will simply result in a single integer, which represents the number of rows fetched. If there are instances where we know only one record should be returned, .fetchone() should be used instead.
def getRecords(table):
try:
openConnection() with conn.cursor() as cur:
sql = "SELECT * FROM %s"
cur.execute(sql, table)
result = cur.fetchall()
for row in result:
record = {
'id': row[0],
'name': row[1],
'email': row[2],
'phone': row[3]
}
cur.close()
conn.close()
except Exception as e:
print(e)
finally:
print('Query Successful')getRecords('table_name')Whoa, whats with the %s? This is how we pass arguments into queries in PyMySQL. The PyMySQL guys were kind enough to realize how obnoxious it is to constantly break strings to pass in variables — in the case of SQL queries, this beyond obnoxious and borderline unworkable. Remember that MySQL requires explicit quotations around passing string values, so queries such as these become a nonsensical jumble of escaped characters.
PROTIP: PyMySQL supports the distant cousin of single quotation marks for usage in queries: the diagonal single quote thing above the tilde~ in the upper-left hand corner for your keyboard, also known simple as `. If there comes a time to set a string within your query use this elusive quotation as such:
sql = "SELECT * FROM %s WHERE column_name = `somevalue`"Updating rows
Arguments can be passed as anything inside a query: they simply appear in the order in which they are passed. In the below example, we pass table as an argument, as well values we want updated, and the identifier for target rows:
def getRecords(table, data):
try:
openConnection() with conn.cursor() as cur:
sql = "UPDATE %s SET date=%s, numsent=%s WHERE email = %s"
cur.execute(sql, (table, data['date_sent'], data['status'],
data['email']))
conn.commit()
cur.close()
conn.close()
except Exception as e:
print(e)
finally:
print('Query Successful')
data = {
'date_sent': '12/01/2018',
'email': 'fakeemail@example.com',
'status': 'Confirmed'
} getRecords('table_name', data)Heads up: Note the line conn.commit(). Don’t forget that — this is what actually commits the update to the database. Forgetting this line and wasting hours debugging is somewhat of a rite of passage, but let’s just skip all that.
For dessert: Usage in AWS Lambda
It is my treat to share with you my world famous copy & paste recipe for AWS Lambda. Here we store all of our db credentials in a separate file called rds_config.py. We also enable logging to take us through what is happening each step of the way:
import sys
import logging
import rds_config
import pymysql #rds settings
rds_host = "rdsName.dfsd834mire.us-west-3.rds.amazonaws.com"
name = rds_config.db_username
password = rds_config.db_password
db_name = rds_config.db_name logger = logging.getLogger()
logger.setLevel(logging.INFO) conn = None def openConnection():
global conn try:
if(conn is None):
conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=10)
elif (not conn.open):
conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=10)
except:
logger.error("ERROR: Unexpected error: Could not connect to MySql instance.")
sys.exit()
logger.info("SUCCESS: Connection to RDS mysql instance succeeded")We thank you all for joining us in this adventure of tantalizing treats. May your data be clean and your stomachs full.
Bon appétit.
Comentarios recientes