How to pass variable as an argument to be used in a function?

[t4keheart]

Hi everyone,
I'm new to python for forgive me if my fundamental idea of this concept is way off.

I have 2 python programs. One of them contains the following function. It's purpose being to connect to a db and run a query. I want to be able to pass some variable to it, and use the variables contained value in the sql query (note the ** portion of query):

def updateSent():
    cnxn = pyodbc.connect('UID='+dbUser+';PWD='+dbPassword+';DSN='+dbHost)
    cursor = cnxn.cursor()
    update_stmt = "UPDATE WKM_sms_outbound SET status = 'sent' WHERE msg_id = **value from msgId variable**"
    cursor.execute(update_stmt)

Then be able to call it from program 2 like this:

from program1 import updateSent
updateSent(msgId)

Is this possible? I'd imagine that I'm doing it incorrectly, but have googled for a while now and am unable to find exactly what I'm looking for.

Thank you in advance.

[buran]

something within following lines:

def updateSent(msg_id):
    with pyodbc.connect('UID='+dbUser+';PWD='+dbPassword+';DSN='+dbHost) as cnxn: # I assume user, password and host will have fixed, real values
        cursor = cnxn.cursor()
        update_stmt = "UPDATE WKM_sms_outbound SET status='sent' WHERE msg_id=?"
        row_count = cursor.execute(update_stmt, msg_id).rowcount
    cnxn.close()
    return row_count # this way your function will return number of affected rows

take a close look at pyodbc docs:
https://github.com/mkleehammer/pyodbc/wiki
at least for Connection and Cursor objects.

Check also the connection string depending on your database

[t4keheart]

Thank you Buran,

That's an interesting solution, and I will have to try it.
On my own I came up with this:

def updateSent(msgId):
    cnxn = pyodbc.connect('UID='+dbUser+';PWD='+dbPassword+';DSN='+dbHost)
    cursor = cnxn.cursor()
    update_stmt = "UPDATE WKM_sms_outbound SET status = 'sent' WHERE msg_id = " + msgId
    cursor.execute(update_stmt)
    cnxn.close()

However, it does not work to update the database. It's odd, because I added lines to the function to print both the msgId object, and the update_stmt objects, and those both print the correct strings... it's just as if the query isn't running at all.

I will give your example a try.

[buran]

you don't commit.
you need to add cnxn.commit() after executing the statement
or create connection with autocommit=True (default is False)
in my case the with context manager commits automatically, but does not close the connection (as one would expect)
that's why I advised you to look closely at the docs
also using concatenation your solution is prone for SQL injection if msgId comes from untrutsed source

[t4keheart]

you need to add cnxn.commit() after executing the statement

oh man, and it really was that simple.

well, thanks again. I will review the docs... looks like I really should.
also, this program is just being developed for in-house use... all sources using it are trusted so sql injection isn't a concern with this.

[buran]

all sources using it are trusted so sql injection isn't a concern with this.

still better to adhere the best practices, i.e. don't learn bad things

[buran]

Note that in many cases you may want to make sure everything went smooth - i.e. no errors, number of affected rows (insert/update/delete operations) as expected. In this case you will not autocommit, but handle the error or check affected rows before commit.

[t4keheart]

Note that in many cases you may want to make sure everything went smooth - i.e. no errors, number of affected rows (insert/update/delete operations) as expected. In this case you will not autocommit, but handle the error or check affected rows before commit.

It did actually work exactly how I wanted to. It's a new table in the db I created for this purpose so, not much that can go wrong there.
Next step is to try to write some function that can "listen" to the db table, and initiate/run when a new row is posted to it.

I appreciate your tips and guidance on this!

[buran]

(i) one option is to have script running in background that checks the database regularly (e.g. every x seconds)
(ii) another option is to have a trigger in the DB that would do what you want (inc. call external command) and being triggered when new row inserted in given table
(iii) some combination of (i) and (ii)

check https://stackoverflow.com/a/670697/4046632
Even if not using MySQL same will apply for other DBs