mySQL query in python script [duplicate]

Question

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

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
– JCVanHamme, Commented May 31, 2017 at 12:24

errata · Accepted Answer · 2017-05-31 12:27:09Z

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.

answered May 31, 2017 at 12:27

errata

6,07110 gold badges59 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JCVanHamme Over a year ago

cursor.execute will do the substitution, as long as it's passed a tuple or dictionary of parameters.

errata Over a year ago

@JCVanHamme I see... Didn't know about that to be honest :)

errata Over a year ago

Although this is accepted as an answer, this issue might be solved in a way AKX suggested too...

AKX · Accepted Answer · 2017-05-31 12:24:59Z

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.

answered May 31, 2017 at 12:24

AKX

171k16 gold badges146 silver badges228 bronze badges

Collectives™ on Stack Overflow

mySQL query in python script [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related