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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

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.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

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
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Spyder Quirk? global variable does not increment when function called in console rrace001 1 57 Sep-18-2020, 02:50 PM
Last Post: deanhystad
  passing variable to function Rejoice 4 394 Sep-11-2020, 03:27 AM
Last Post: Pleiades
  [split] Creating a variable as a function DPaul 23 551 Sep-07-2020, 05:20 PM
Last Post: DPaul
  Pass by object reference when does it behave like pass by value or reference? mczarnek 2 179 Sep-07-2020, 08:02 AM
Last Post: perfringo
  Creating a variable as a function JarredAwesome 4 500 Sep-06-2020, 05:08 AM
Last Post: buran
  Function will not return variable that I think is defined Oldman45 6 299 Aug-18-2020, 08:50 PM
Last Post: deanhystad
  Need help with for loop and variable value substitution in a function rsurathu 2 277 Jul-21-2020, 06:47 AM
Last Post: rsurathu
  Pass integers to datetime.date function florian 3 219 Jul-18-2020, 04:43 AM
Last Post: scidam
  How to pass multiple arguments into function Mekala 4 428 Jul-11-2020, 07:03 AM
Last Post: Mekala
  How to pass multiple values from one sample to nc variable? Baloch 0 239 Jun-01-2020, 09:27 PM
Last Post: Baloch

Forum Jump:


Users browsing this thread: 1 Guest(s)