Getting error when running "MINUS" between 2 databases

[marlonbown]

Goal: Keep content of TableA except items listed on TableB.

I am getting the following error. Code is below. Can you please let me know if you see where problem is when using the "MINUS' syntax?

Error:
(venv) user@3c0630078aad venv % python delmain2.py
Traceback (most recent call last):
  File "/Users/user/venv/delmain2.py", line 61, in <module>
    diff("apix.db", "APIX", "APIW")
  File "/Users/user/venv/delmain2.py", line 55, in diff
    res = connection_obj.execute(statement)
sqlite3.OperationalError: near "SELECT": syntax error
(venv) user@3c0630078aad venv % 

import sqlite3
def diff(database, tableA, tableB):
        #Testing with hardcoded syntax for now

        connection_obj = sqlite3.connect(database)
        cursor_obj = connection_obj.cursor()
        statement = '''SELECT * FROM APIX MINUS SELECT * FROM APIW'''
        res = connection_obj.execute(statement)
        return res

diff("apix.db", "APIX", "APIW")

[ibreeden]

connection_obj = sqlite3.connect(database)
cursor_obj = connection_obj.cursor()
statement = '''SELECT * FROM APIX MINUS SELECT * FROM APIW'''
res = connection_obj.execute(statement)

You must use the connection_obj to execute the query. Use the cursor_obj instead.

[deanhystad]

I find it annoying that all the sql tutorials make a cursor object. There are lots of things that a cursor object cannot do, and most of the things you can do with a cursor object can be done without one. Just use your connection object where you would normally use a cursor object (because you've been brainwashed into thinking they are requried!)

[marlonbown]

Hi, thanks for the quick replies.
I attempted to use cursor obj and now luck. Same error.
Please see message:
Traceback (most recent call last):
File "/Users/u/venv/delmain2.py", line 61, in <module>
diff("apix.db", "APIX", "APIW")
File "/Users/u/venv/delmain2.py", line 55, in diff
res = cursor_obj.execute(statement)
sqlite3.OperationalError: near "SELECT": syntax error

Code now is:

def diff(database, tableA, tableB):
        table1 = "APIX"
        table2 = "APIW"

        connection_obj = sqlite3.connect('apix.db')
        cursor_obj = connection_obj.cursor()
        statement = '''SELECT * FROM APIX MINUS SELECT * FROM APIW'''
        res = cursor_obj.execute(statement)
        return res

diff("apix.db", "APIX", "APIW")

======

I find it annoying that all the sql tutorials make a cursor object. There are lots of things that a cursor object cannot do, and most of the things you can do with a cursor object can be done without one. Just use your connection object where you would normally use a cursor object (because you've been brainwashed into thinking they are requried!)

[deanhystad]

Looking into this a tiny bit. Are you sure about "MINUS"? Looks like you should be using "EXCEPT".