Python/Pyodbc/SQL - Updating a table and setting a field to a CSV File

pstumps 0
Viewed 60

I am trying to use pyodbc to update an existing MS Access database table with a very long multiline string. The string is actually a csv that has been turned into a string.

The query I am trying to use to update the table is as follows:

          query = """
                  UPDATE Stuff 
                  SET Results = '{}'
                  WHERE AnalyteName = 
                  '{}'
                  """.format(df, analytename)

The full printed statement looks as follows:

UPDATE Stuff
SET Results = 
'col a,col b,col c,...,col z,
 Row 1,a1,b1,c1,           
 ...,...,...,...,
 Row 3000,a3000,b3000,c3000'
WHERE AnalyteName = 'Serotonin'

However this does not seem to be working, and I keep getting the following error:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. (-3503) (SQLExecDirectW)')

Which I assume is due to the format of the csv string I am trying to use to update the table with.

I have tried using INSERT and inserting a new row with the csv string and other relevant information and that seems to work. However, I need to use UPDATE as I will eventually be adding other csv strings to these columns. This leads me to believe that there is A) Something is wrong with the syntax of my UPDATE query (I am new to SQL syntax) or B) I am missing something from the documentation regarding UPDATE queries.

Is executing an UPDATE query like this possible? If so, where am I going wrong?

pyodbc ms-access pandas python
2 Answers

It would be determined by the table's field type. For large amounts of text you'd need a blob field in your database table. A blob field will store binary info so using blob will not 'see' illegal characters.

usersoCO0

Answering my own question in case anyone else wants to use this.

It turns out what I was missing was brackets around the table column fields from my UPDATE statement. My final code looked something like this.

csv = df.to_csv(index=False)
name = 'some_name'
query = """
        UPDATE Stuff 
        SET 
            [Results] = ?
        WHERE 
            [AnalyteName] = ?
        """
self.cursor.execute(query, (csv, name))

I've seen several other posts here where brackets were not around the column names. However, since this is MS Access, I believe they were required for this query, or rather this specific query since it included a very long strong in the SET statement.

I welcome anyone else here to provide a more efficient method of performing this task or someone else who can provide more insight into why this is what worked for me.

pstumps0
Related