0

I've been stuck in this problem for a long time, i hope somebody could enlighten me. I have a sql database i would like to update. Here are some pieces of the code. I extracted data from sql to Python, then apply function hex_to_string & slicing bin and I plan to update the SQL database. I don't have any ID in the database, but I have the DATETIME which differentiates the entry. 

query = """ select P from Table """

cnxn = pyodbc.connect(conn_str)
cnxn.add_output_converter(pyodbc.SQL_VARBINARY, hexToString)
cursor: object = cnxn.cursor()

cursor.execute(query)
dtbs= cursor.fetchall()
row_list=[]
ln = len(dtbs)

 cursor.execute(query)

for i in range(ln):
    row=cursor.fetchval()
    result=slicing_bin(row)
    result_float = [float("{0:.2f}".format(i)) for i in result]
    row_list.append(result_float)

crsr = cnxn.cursor()
crsr.execute(query)
aList = [item[0] for item in crsr.fetchall()]
for aValue in aList:
    crsr.execute("""UPDATE Table SET P=? WHERE DATETIME=?""", (row_list, aValue))
crsr.close()
cnxn.commit()

When I run this code, I got an error message,

File "C:/Users/r/.PyCharmCE2018.3/config/scratches/Finalcombined2.py", line 64, in crsr.execute("""UPDATE Access.dbo.M_PWA SET P_PULSES=? WHERE DATETIME=?""", (row_list, aValue)) pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Column, parameter, or variable #1: Cannot find data type READONLY. (2715) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180); [42000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Parameter or variable '@P1' has an invalid data type. (2724)")

Please Help, thanks.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Crn
  • 45
  • 1
  • 8
  • 2
    [DATETIME](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017) is a reserved word in T-SQL, so if you are using that as a column name you may have to enclose it in square brackets. – Gord Thompson Mar 14 '19 at 00:05
  • Thank you! @GordThompson. On the other hand, the program still doesnt work. – Crn Mar 14 '19 at 01:07

1 Answers1

1

Hummm, I would have guessed that Gord was right. That's certainly the first thing I'd look at. Ok, here is a small sample of how I do updates in MS Access, from Python. 

#import pypyodbc
import pyodbc

# MS ACCESS DB CONNECTION
pyodbc.lowercase = False
conn = pyodbc.connect(
    r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
    r"Dbq=C:\\path_here\\Northwind.mdb;")

# OPEN CURSOR AND EXECUTE SQL
cur = conn.cursor()

# Option 1 - no error and no update
cur.execute("UPDATE dbo_test SET Location = 'New York' Where Status = 'Scheduled'");
conn.commit()


cur.close()
conn.close()

Can you adapt this to your specific scenario?

ASH
  • 20,759
  • 19
  • 87
  • 200
  • Thanks! yes, sorry i did not include the full code on the question because i am afraid it is going to be too long and confusing. And as for the [], if you could help me, do i write it like where [DATETIME] =? or DATETIME[]=?, appreciate it :) thankyou – Crn Mar 14 '19 at 05:55
  • 1
    @r-can Based on this post [How to deal with SQL column names that look like SQL keywords?](https://stackoverflow.com/questions/285775/how-to-deal-with-sql-column-names-that-look-like-sql-keywords) it probably should be `[DATETIME]=?`: `"""UPDATE Table SET P=? WHERE [DATETIME]=?"""` – chickity china chinese chicken Mar 15 '19 at 00:54