0

I have a SQL Server 2017 database table with 10,000+ records with an EmailAddress column that needs to be updated for every row in the table incremented by 1 (i.e. a+1@email.com, a+2@email.com up to a+10000@email.com).

I'm thinking the easiest way to do this is with Python using pypyodbc SQLCommand and a for loop. Additionally, the SET value in the SQL command needs to retrieve from a xlsx file using xlrd that will go through each row and update the value of SET.

Other than scouring the web for answers, I've tried changing the code many different ways but can't seem to find an answer to this issue.

import pypyodbc
import xlrd

    connection = pypyodbc.connect('Driver=SQL Server;'
                                'Server=DESKTOP-YourMomsServer\SQLEXPRESS;'
                                'Database=testtime;'
                                'Trusted_Connection=yes;'
                                )

    cursor = connection.cursor()

    xlf = xlrd.open_workbook("testdatafile.xlsx")
    sh = xlf.sheet_by_name("Sheet1")

    SQLCommand = ("SELECT *  FROM anothertest")   
    cursor.execute(SQLCommand)
    for val in cursor:
        a, b = val
        for i in range(sh.nrows):
            c, d = sh.row_values(i)
            SQLCommmand = ("UPDATE anothertest SET test =" d "WHERE "c = a)
            cursor.execute(SQLCommand)
        #print(a,b)
    connection.commit() 
    connection.close()

The expected results is that the script would loop through the entire result set starting with Row-1 up to the last row- Row-10000 and update the EmailAddress column that is read from the xlsx file.

Error

"invalid syntax" in the SQLCommand

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
benfsmith3
  • 21
  • 1
  • 4
  • I have a SQL Server 2017 Database table with 10,000+ records with a EmailAddress column that needs to be UPDATED for every row in the table incremented by 1 (i.e. a+1@email.com, a+2@email.com up to a+10000@email.com). I'm thinking the easiest way to do this is with Python using pypyodbc SQLCommand and a for loop. Additionally, the SET value in the SQL command needs to retrieved from a xlsx file using xlrd that will go through each row and update the value of SET. – benfsmith3 Aug 03 '19 at 03:21
  • Could it be as simple as a typo? Try changing: "WHERE " to " WHERE " – Jim Jimson Aug 03 '19 at 05:37
  • Alternatively, you could do this with just SQL using the `row_number()` window function to generate an ascending series of integers. – rd_nielsen Aug 03 '19 at 14:14
  • Here is the updated for loop using python that allowed me to use variables to iterate through the entire result set and update every row in the database assigning the 'Email' column a unique email address. Thanks for the help in thinking this through. `for i in range(sh.nrows): c, d, e = sh.row_values(i) SQLCommand = ("UPDATE anothertest SET email = ? WHERE id = ?") VALUES = [e,c] cursor.execute(SQLCommand, VALUES) print(SQLCommand) connection.commit() connection.close()` – benfsmith3 Aug 04 '19 at 04:15

0 Answers0