-1

I have this query :

INSERT INTO lytnobjects.devices (id,idedge,uniqueref,constructeur,ipaddress,macaddress,
                                 hostname,devicetype,isfirewall,isvisible,iscorporate,
                                 ishub,osname,osversion,datecreation,lasttrafic,
                                 hourtrafic,daytrafic,monthtrafic) 
VALUES ('e1e455e98b6ed0037a58d0c1f5dc245a',3183,'TODO','TODO','192.168.143.49',
        'b0:0c:d1:bb:36:1c','HPBB361C','Other',False,False,False,False,'','',
        '2021-10-29T00:58:53.709','2021-01-01T00:00:00','0/0','0/0','0/0')

When I execute the query using python 3.9 and psycopg2_binary (PostgreSQL), I get an error : unterminated quoted string at or near "'HPBB361C"

conn is the opened connection to the database (AWS RDS PostgreSQL) sql is a string with the query above

def SQLExec(conn,sql):
    try: cur = conn.cursor()
    cur.execute(sql)
except (Exception, psycopg2.DatabaseError) as error:
    print("***** ERROR:",error)
    cur.close()

If I execute the same request from pgAdmin, I get no error ! There is no missing quote as you can see in the query, and no reason to point an error at this place!

So, I have a string (sql) with the query ("INSERT ...")

  1. I call execute from psycopg2, and get an error: unterminated quoted string at or near "'HPBB361C"
  2. I copy/paste the same string into pgAdmin, and the query is executed with no error

The same string (query)

Any idea why I get an error from my python app? I am looking for an answer since many hours, but find no explanation, and I don't know how to fix the problem (which doesn't exist for me)

Your help is very appreciated Thank you

Jmcambot
  • 19
  • 6
  • This is a sample query, but I get the same error with different values, sometimes it is accepted, and sometimes not, without any visible difference (and only from psycopg2) – Jmcambot Oct 28 '21 at 23:46
  • 3
    To get an answer you will need to update your question with the actual Python code that is failing. – Adrian Klaver Oct 28 '21 at 23:59
  • 1
    When you ask *why do I get an error in my Python code?* and don't post your Python code, it's pretty much impossible to answer your question. I guess you thought we could magically read your screen from where we're sitting to see your code, but we can't. – Ken White Oct 29 '21 at 00:45
  • Sorry, the question was not so much about the python code (which works every days), but it was about the different answer when sending the query using psycopg2, and sending it using pdAdmin. The 1st one is giving an error that the second one doesn't give, and btw there is no error in the query `code` def SQLExec(conn,sql): try: cur = conn.cursor() cur.execute(sql) except (Exception, psycopg2.DatabaseError) as error: print("***** ERROR:",error) cur.close() – Jmcambot Oct 29 '21 at 10:03
  • Add the code as update to your question so folks don't have to dig through the comments and so it can be properly formatted. It is about the Python code as that is where it fails. I'm betting it is due to how `sql` is built, which you are not showing. So add that to the Python code you add to your question. – Adrian Klaver Oct 29 '21 at 15:51

1 Answers1

1

I finaly found the answer! I build the sql query (string) using some variables coming from various sources, like Amazon S3 for instance.

I assumed that the variable was really a string, with nothing "bizarre" in it... But in fact, sometimes, the "string" was ended with a "\x00" char, that is not displayed, so the string looks just normal :-/

When I execute my query (string) with psycopg2, it receives the extra \x00 char, which ends the string at this place! This is why it says there is a missing quote

I put a trace in the code to display the .encode() version of my string, and it revealed the \x00 at the end. So now I "clean" all string variables used in my queries, just with myvariable.replace("\x00","")

And it works now. There is probably a more conventional way to fix this...

I hope it may help somebody sometime! ;-)

Jmcambot
  • 19
  • 6