Python UPDATE query with parameters in different places

Question

I'm writing a simple script that checks if user account is about to expire. I'm having a problem with an UPDATE query - it doesn't update, basically. All examples I've found on the internet seem to use tuples to update rows, however my case requires parameters to be apart from each other.

I'm completely new to Python (I started literally yesterday). My database is MySQL (almost all examples on the web use SQLite) and I can't change that. I use Python 3 and the server is running on Ubuntu 18.04. I tried replacing %s with ? or :variable. I also tried insecure way of doing this (SQL Injection vulnerable) and it didn't work either.

This is my current code:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import mysql.connector
from mysql.connector import Error
import datetime
try:
    mydb = mysql.connector.connect(
        host="localhost",
        user="rootery-root",
        passwd="example",
        database="playground"
        )

    sqlCursor = mydb.cursor()
    sqlCursor.execute("SELECT id, email, last_email_date FROM users WHERE soon_expires = 1")
    sqlResult = sqlCursor.fetchall()
    setLastMailCmd = """UPDATE users SET last_email_date =%s WHERE id =%s"""
    today = datetime.date.today()

    for i in range(0, len(sqlResult)):
        id = sqlResult[i][0]
        email = sqlResult[i][1]
        lastemaildate = sqlResult[i][2]
        daydiff = lastemaildate - today
        setLastMail = sqlCursor.execute(setLastMailCmd, (id, today))
        if daydiff.days >= 30:
            print "Sending mail and updating \"last_email_date\"."
            setLastMail
        else:
            print "%s already received an e-mail this month - ignored." % email
            setLastMail  # debugging purposes

except mysql.connector.Error as error:
    print("SQL connection error.".format(error))

finally:
    if (mydb.is_connected()):
        sqlCursor.close()
        mydb.close()
        print("Disconnected from database.")
        print(today)

I expected it to update my table with data provided by the for loop, however it does nothing at all.

Answer 1

Try using functions more. You put everything in one place and it's not easy to debug.

Your problem is the way you use setLastMail # debugging purposes. It does nothing.

What would be better:

mydb = mysql.connector.connect(
    host="localhost",
    user="rootery-root",
    passwd="example",
    database="playground"
)

sqlCursor = mydb.cursor()

def set_last_email(id):
    stmt = """UPDATE users SET last_email_date =%s WHERE id =%s"""
    today = datetime.date.today()
    sqlCursor.execute(stmt, (id, today))

And then just execute your set_last_email(id).

Remember to make cursor global, otherwise it won't be available in your function. Or acquire it directly in your function from global connection.

That's of course a dummy example, but you need to start somewhere :)