Python Mysqldb update operation with variables

Question

I have a (maybe very simple) question, but I do not find the solution myself...

The database includes links. These include numbers. Now I want to insert in a separate column "nummer" the number that is included in each of the links.

Below works for one number.

Now I want to use a for-loop over several numbers and extract the number from all the links and write it into the "Nummer" field.

How to modify using %i or %d?

Thanks in advance for your help!

import MySQLdb

connection = MySQLdb.connect(
    host="192.168.0.101",
    db='xxxxxxx',
    user="xxxxxxx", passwd="xxxxxxx"
    )
connection.apilevel = "2.0"
connection.threadsafety = 2
connection.paramstyle = "format" 

cursor=connection.cursor()

sql="UPDATE analysen SET Nummer = 247687  WHERE `Link` like '%247687%'"  
cursor.execute(sql)
connection.commit()

abarnert · Accepted Answer · 2013-11-15 21:56:37Z

1

To make a for loop over several numbers, it's just for n in numbers:.

The only tricky bit is how to parameterize a LIKE query. The way to do it is to put a normal %s parameter into the query, then wrap the parameter value in % characters. So:

sql = "UPDATE analysen SET Nummer = %s WHERE Link like %s"
for n in numbers:
    cursor.execute(sql, [n, '%{}%'.format(n)])

Or:

sql = "UPDATE analysen SET Nummer = %s WHERE Link like %s"
cursor.executemany(sql, ([n, '%{}%'.format(n)] for n in numbers)

answered Nov 15, 2013 at 21:56

abarnert

368k54 gold badges626 silver badges691 bronze badges

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

Collectives™ on Stack Overflow

Python Mysqldb update operation with variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related