0

I need to execute query against DB, to check if file exist in DB and what is file modified time. function arguments are name(file name) and mtime1 (mod time from filesystem). I use script:

def chkModTS(name,mtime1):
import mysql.connector
cnx = mysql.connector.connect(user='uname', password='pass', host='192.168.1.1', database='newpms')
cursor = cnx.cursor()
query=("SELECT mtime FROM pf_scanOrd WHERE filename=%s")
query_data=(name)
cursor.execute(query,query_data)
print(cursor.rowcount)
if cursor.rowcount==-1:
    chkModTS.upd=1        
    print('pole DBs')
    return
for (mtime) in cursor:
    print(float(mtime[0])!=mtime1)
    if float(mtime[0])!=float(mtime1):
        chkModTS.upd=1
        print('need update')
        return     
cursor.close()
cnx.close()

When i run this function, result would be:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1

cant figure out, need help

Kalle
  • 69
  • 7
  • Try adding port=3306 in your connection parameter – Satya May 31 '17 at 12:21
  • 1
    `cursor.execute` expects a tuple or a dictionary to supply the parameters. In your code, `query_data` is not a tuple. Change it to `query_data = (name,)` to create a tuple. – JCVanHamme May 31 '17 at 12:24

2 Answers2

1

You're likely just missing a comma in defining query_data.

.execute() needs a tuple as the second parameter. Just wrapping a value in parentheses does nothing, so try:

query_data = (name,)

to make query_data an actual tuple.

AKX
  • 152,115
  • 15
  • 115
  • 172
1

You have an error in your query string:

"SELECT mtime FROM pf_scanOrd WHERE filename=%s"

having a %s placeholder but not substituting it with any value. You should use it like:

"SELECT mtime FROM pf_scanOrd WHERE filename=%s" % filename_variable

where filename_variable will hold the filename you want to query.

errata
  • 5,695
  • 10
  • 54
  • 99
  • `cursor.execute` will do the substitution, as long as it's passed a tuple or dictionary of parameters. – JCVanHamme May 31 '17 at 12:29
  • @JCVanHamme I see... Didn't know about that to be honest :) – errata May 31 '17 at 12:30
  • Although this is accepted as an answer, this issue might be solved in a way [AKX suggested](https://stackoverflow.com/a/44284769/175793) too... – errata May 31 '17 at 12:33