psycopg2.ProgrammingError: syntax error at or near "select"

Question

I have a python script, which read a sql file and execute the sql command stored in it. But when executing it I got below error:

psycopg2.ProgrammingError: syntax error at or near "select"
LINE 1: select * from image

the sql file content is:

select * from image

which is simple and should be correct.

the code throwing the error(the last line, more specificately):

 cur=conn.cursor()
 string=open(script,'r',encoding='utf-8').read()#script is the sql file
 cur.execute(string)

is there anyone can advise?

----update-----

below is the function in the python script. I don't post the whole script since it is too long.

def list_(csv, sql=None , script=None , host = None, dbname=None , user=None , pwd=None):
    print(sql)
    print(script)
    if (sql):
        print("sql")
        with conn2db(host,dbname,user,pwd) as conn:
            cur = conn.cursor()
            cur.execute(sql)
    if (script):
        print("script")
        with conn2db(host,dbname, user, pwd) as conn:
            cur = conn.cursor()
            string =  open(script, 'r', encoding='utf-8').read()
            print(string)
            cur.execute(string)
            #cur.execute(open(script, 'r', encoding='utf-8').read())
    with open(csv,'w') as file:
        for record in cur:
            mystr=str(record)[1:-2] if str(record)[-1]==',' else str(record)[1:-1]
            file.write(mystr+'\n')
            #file.write('\n')

I'd not be surprised if this is actually an encoding issue. Could you attach the script file? — N. Wouda
@N.Wouda Can you tell me how to attach a file? I donot know how to do it. Yes, I also guess it has something to do with the encoding. so I tried to change the encoding(deleting the encoding='utf-8') but it didnot work. — ZhaoGang
@N.Wouda the python script file is quite long and have about 100+ lines. Will post it here later since it is in my office computer. — ZhaoGang
I checked the encoding of the sql fie withfile -bi $'picture.sql' and the result is text/plain; charset=utf-8 Seems that the encoding is correct. — ZhaoGang
Try changing cur.execute(string) to cur.execute(string.strip()). — Keith

BryceH BryceH · Accepted Answer · 2018-09-04T01:44:35

Seeing the connection string as well as your schemas and tables in your database would help. Please confirm that these are all correct. Additionally, running .strip() on the SQL string after reading it from the file or adding a semi-colon to the end of the SQL string is worth a try.

psycopg2.ProgrammingError: syntax error at or near "select"

2 Answers