0

I am trying to go through each query in a SQL file and execute it in my python script using psycopg2. Each query has an id which I replace before executing.

The first query in the sql file is the following:

select * from subscriber where org_id = '1111111111';

I get the old id and replace it with the new id that I am looking for

id_regex = re.compile("\d{10,}")
m = id_regex.search(q)
old_id = m.group(0)
new_q = q.replace(old_id, new_id)

I then execute the queries on the following manner

for index, cmd in enumerate(cmds):
        # ... (other stuff here)
        elif cmd != '\n':
            new_cmd = p_helper.replace_id(org_id, cmd)
            logger.debug("Running Command:\n" + new_cmd)
            try:
                if not test_run:
                    db_cursor.execute(new_cmd)
            except psycopg2.Error as e:
                logger.error(e.pgerror)
        else:
            pass
            # DO NOTHING

When I run my program I get the following error:

ERROR:  syntax error at or near "select"
LINE 1: select * from subscriber where org_id = '9999999999';
        ^

Every query after the first doesn't run

ERROR:  current transaction is aborted, commands ignored until end of transaction block

I ran the select query manually in psql and it worked perfectly so I don't think the problem is the syntax of the statement. I think it has something to do with the formatting of queries that psycopg2 takes. I'm not sure exactly what to change, I have looked at other SO posts and could not figure out what I needed to change. It'd be great if someone could help me figure this out. Thanks!

Versions

python: 2.7.6

psycopg2: 2.4.5

pvg
  • 2,673
  • 4
  • 17
  • 31
bbakp3
  • 65
  • 2
  • 9
  • what does new_cmd log? – khajvah Mar 24 '17 at 15:00
  • `Running Command: select * from subscriber where org_id = '9999999999';` – bbakp3 Mar 24 '17 at 15:02
  • is org_id str or int? – oshaiken Mar 24 '17 at 15:08
  • Try `logger.debug("Running command: %r", new_cmd)`. I suspect that you have invisible characters at the start of your line, likely a BOM mark in a UTF-16 file. I can reproduce the case with the BOM. In general, avoid logging unknown string values using `+` or `%s` format; always use `%r` for unknown values. – 9000 Mar 24 '17 at 15:09
  • it now prints `Running Command: "\xef\xbb\xbfselect * from subscriber where org_id = '9999999999';\n"` – bbakp3 Mar 24 '17 at 15:18
  • Any idea on how to remove them? – bbakp3 Mar 24 '17 at 15:20
  • What version of python are you running and how are you reading this file? You generally don't need to remove them, specifying the correct encoding takes care of it. – pvg Mar 24 '17 at 15:27
  • version 2.7.6. To read the files I do the following `file_read = open(file_name, 'r') cmds = file_read.readlines()` – bbakp3 Mar 24 '17 at 15:29
  • It's probably utf8 with BOM, the encoding for that is 'utf-8-sig', see http://stackoverflow.com/questions/8898294/convert-utf-8-with-bom-to-utf-8-with-no-bom-in-python and the zillion other hits for python2 utf8 bom searches. Open the file in a sane text editor and check what encoding it guesses, just to be sure. – pvg Mar 24 '17 at 15:32
  • Yeah, I checked in sublime text and it said 'UTF-8 with BOM' – bbakp3 Mar 24 '17 at 15:42
  • I'll look into removing the BOM thanks so much for your help! – bbakp3 Mar 24 '17 at 15:44

0 Answers0