Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 How to pass variable as an argument to be used in a function?
#1
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.
Quote
#2
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
Quote
#3
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.
Quote
#4
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
Quote
#5
(Jan-15-2020, 08:42 PM)buran Wrote: you need to add cnxn.commit() after executing the statement

oh man, and it really was that simple. Wall

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.
Quote
#6
(Jan-15-2020, 08:47 PM)t4keheart Wrote: 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
Quote
#7
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.
Quote
#8
(Jan-15-2020, 08:58 PM)buran Wrote: 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!
Quote
#9
(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
t4keheart likes this post
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Pass by reference vs Pass by value leodavinci1990 1 160 Nov-20-2019, 02:05 AM
Last Post: jefsummers
  random variable function? Novice_fanatic 4 234 Oct-08-2019, 01:22 PM
Last Post: jefsummers
  Declaring a Global Variable in a Function Bob1948 4 275 Sep-14-2019, 11:16 PM
Last Post: ichabod801
  Can a function modify a variable outside it? Exsul 3 315 Jul-30-2019, 12:47 AM
Last Post: cvsae
  Pass command line argument with quotes koticphreak 3 569 May-31-2019, 11:18 PM
Last Post: ichabod801
  How to pass a variable to subprocess.check_call champweller 1 437 May-26-2019, 07:10 AM
Last Post: heiner55
  How to pass a dictionary as an argument inside setup function of unittest nilaybnrj 1 508 May-11-2019, 03:18 AM
Last Post: keames
  Using function argument in lists comprehension. blackknite 5 523 Apr-23-2019, 09:59 PM
Last Post: snippsat
  Passing a local variable outside of a function malonn 8 506 Apr-15-2019, 01:50 AM
Last Post: malonn
  Pass variable script return twice output problem Faruk 8 635 Dec-26-2018, 11:57 AM
Last Post: Faruk

Forum Jump:


Users browsing this thread: 1 Guest(s)