0

I was able to execute the delete SQL query in Python with pypyodbc as below

cur.execute("delete from table_a where a ='a';").commit()

However, I failed to run the delete SQL with a subquery

cur.execute("delete from table_a where a in ( select a from table_b );").commit()

will return 

pypyodbc.Error: ('HY010', '[HY010] [unixODBC][Driver Manager]Function sequence error')

How could I run a delete SQL with subquery?

Decula
  • 494
  • 4
  • 16
  • Are you using the Vertica ODBC driver or something different? I am having trouble imagining that error occuring on your delete statement. That error should occur only if the ODBC functions are executed out of sequence. Do you have autocommit on? Possibly if you do then the commit you have may cause this error. – woot Sep 15 '16 at 13:09
  • @woot I tried to set autocommit on and off, add and remove commit() after the query, none of them working. – Decula Sep 15 '16 at 17:01
  • I've never seen this behavior. When you move the commit to later, the error happens on the execute itself? The only thing you are doing differently that I can see is putting a semicolon at the end of your queries. I thought you should only do that if you have multiple commands to run. – woot Sep 16 '16 at 00:41

1 Answers1

0

The cause of this issue is pypyodbc not work with the delete command that actually delete nothing.

If I run delete from table_a where a ='a'; twice, first time it will success and second time return error.

To run delete command with subquery, I need to check whether subquery really have records.

Yuca
  • 6,010
  • 3
  • 22
  • 42
Decula
  • 494
  • 4
  • 16