Querying data with common words using python connector — avoid code duplication

I am new to SQL and relatively new to python. I’m trying to fetch some information from the database using mysql.connector. And I found myself copying and pasting a lot. I tried using LIKE so that I could just execute once and collect all that includes the common word, but I couldn’t figure out the correct way to do it…Could someone help me with simplifying this?

cur = connection.cursor()
cur.execute("SELECT val FROM jDev.SystemVar WHERE name = %(name)s", {'name': "pr_screen_height"})
screen_height_mm = int(cur.fetchone()[0])
cur.execute("SELECT val FROM jDev.SystemVar WHERE name = %(name)s", {'name': "pr_screen_width"})
screen_width_mm = int(cur.fetchone()[0])
cur.execute("SELECT val FROM jDev.SystemVar WHERE name = %(name)s", {'name': "pr_screen_distance"})
screen_distance_mm = int(cur.fetchone()[0])

cur.execute("SELECT msg FROM jDev.BMsg WHERE type = %(type)s", {'type': "DeviceMessage"})
dev_msgs = cur.fetchall()
cur.execute("SELECT msg FROM jDev.BMsg WHERE type = %(type)s", {'type': "ZeroMessage"})
zero_msgs = cur.fetchall()

>Solution :

Define a function that contains the common parts of all the repeated code.

def get_col_from_table(col, table, matchcol, matchval, just_one = True):
    cur = connection.cursor()
    sql = f'SELECT {col} FROM {table} WHERE {matchcol} = %({matchcol})s"
    cur.execute(sql, {matchcol: matchval})
    if just_one == 'one':
        row = cur.fetchone()
        return row and row[0]
    else:
        return cur.fetchall()

screen_height_mm = int(get_col_from_table('val', 'jDev.SystemVar', 'name', 'pr_screen_height'))
dev_msgs = get_col_from_table('msg', 'jDev.BMsg', 'type', 'DeviceMessage', just_one = False)

Leave a Reply