CODEWITHSUNDEEP
pythonmysqlloopsfor-loop
chelly
chelly

Reputation: 47

Updating MySQL DB Using Python For Loop Does not Work

I am trying to update the data from 'Active' to 'Retired by loop through a list of devices from the specific text file.

Somehow, however, it does not filter the list of devices from the text file and update the corresponding data, making no changes to the database at all.

Could it have something to do with my for statement, or mysql statement that I came up with? Regardless of how many times I fix MYSQL, it still results the same.

What could be the problem?

Please take a look at the code below and see if there is any mistake I have made with regards to MYSQL-wise or Python-wise.

Thank you in advance for your great help. Much appreciated. 

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=############;'
                      'Database=########;'
                      'Trusted_Connection=yes;')



cursor = conn.cursor()
cursor.execute('SELECT id, device_id, model_number, serial_number_1,\
status_1, user_name_1 FROM [Footprint].[fpscdb001_cmdb_004].[desktop]')

results = []
with open('H:\list.txt') as inputfile:
    results = inputfile.read().splitlines()

SQL = """UPDATE [Footprint].[fpscdb001_cmdb_004].[desktop]
         SET status_1 = "Retired" 
         WHERE device_id == %s"""

try:
    for i in results:
        cursor.execute(SQL, results[i])
    cursor.commit()
   # print(rowcount)

except:
    conn.rollback()

finally:
    conn.close()

Upvotes: 1

Views: 847

Answers (2)

David Culbreth
David Culbreth

Reputation: 2796

Building on the answer that @RToyo wrote, you may be able to do this a little more quickly

we can build a list of "?" placeholders in the SQL, and then pass each item safely to the ODBC holder, using the * notation to explode the array of device id's into the ODBC execute() function. This allows you to both execute only one query, and do it securely, too

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=############;'
                      'Database=########;'
                      'Trusted_Connection=yes;')



cursor = conn.cursor()
cursor.execute('SELECT id, device_id, model_number, serial_number_1,\
status_1, user_name_1 FROM [Footprint].[fpscdb001_cmdb_004].[desktop]')

results = []
with open('H:\list.txt') as inputfile:
    results = inputfile.read().splitlines()

SQL = """UPDATE [Footprint].[fpscdb001_cmdb_004].[desktop]
         SET status_1 = "Retired" 
         WHERE device_id in ({})""".format(("?, " * len(results))[0:-2])

try:
    if len(results) > 0:
        cursor.execute(SQL, *results)

except:
    conn.rollback()

finally:
    conn.close()

Hope this helps someone.

Upvotes: 1

RToyo
RToyo

Reputation: 2877

It looks like the problem is both your SQL and your Python.

There is a problem with your SQL at this part: WHERE device_id == %s. In SQL, there is no ==. Instead, you use a single = to both set and check values. You should use WHERE device_id = ?.

In addition, you're using %s as a placeholder in your query. I'm not familiar with pyodbc, but a quick check of the docs looks like you should be using the ? as a placeholder.

So try this:

SQL = """UPDATE [Footprint].[fpscdb001_cmdb_004].[desktop]
         SET status_1 = "Retired" 
         WHERE device_id = ?"""

Upvotes: 2

Related Questions