sqlite3.OperationalError: near "WHERE": syntax error (Python 2, sqlite3)

Question

I can't seem to get past the following error:

Traceback (most recent call last):
  File "datascraper.py", line 352, in <module>
    URL))
sqlite3.OperationalError: near "WHERE": syntax error

It comes from the following code (line 352 marked):

Table = someSQLtable //Has columns (providername, [other columns], providerlink)
SQLDatabase = sqlite3.connect(someSQLDatabase.db)
DBControl = cursor(SQLDatabase)

Name = 'somestring'
URL = 'http://www.someurl.com/stuff/'

[...] # Random other lines

DBControl.execute('''INSERT INTO '''+Table+''' (providername, providerlink) 
    VALUES (?, ?) WHERE NOT EXISTS (
                                    SELECT * FROM '''+Table+'''
                                    WHERE '''+Table+'''.providerlink = ?
                                    );
352)                      ''', (Name, URL, URL))

For reference, the SQL commands wrapped up in the Python should look like this:

INSERT INTO someSQLtable (providername, providerlink) 
    VALUES ('somestring', 'http://www.someurl.com/stuff/') 
    WHERE NOT EXISTS (
         SELECT * FROM someSQLtable 
         WHERE someSQLtable.providerlink = 'http://www.someurl.com/stuff/')

And my goal is to check if the table already contains the entry in question by checking if the freshly retrieved link (which is unique) to the table, then writing to the table if it isn't already there.

Playing with whitespace shows that the exception comes up for the last URL on line 352.

I've already tried modifying the input to another string to see if that works, changing the code to use Python's string ops (horror!), and having a drink. Nothing seems to work so far.

CL. · Accepted Answer · 2014-06-06 08:15:32Z

4

The INSERT statement has no WHERE clause.

If you have a UNIQUE constraint (and if not, you should create one) on the providerLink column, you can simply use INSERT OR IGNORE:

INSERT OR IGNORE INTO someSQLtable(providername, providerlink)
    VALUES ('somestring', 'http://www.someurl.com/stuff/')

Otherwise, you have to replace the VALUES clause with a query:

INSERT INTO someSQLtable(providername, providerlink)
    SELECT 'somestring', 'http://www.someurl.com/stuff/'
    WHERE NOT EXISTS (
       SELECT * FROM someSQLtable
       WHERE providerlink = 'http://www.someurl.com/stuff/')

answered Jun 6, 2014 at 8:15

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lennart - Slava Ukraini · Accepted Answer · 2014-06-06 08:10:33Z

0

Perhaps it's different i sqllite, but normally you cant have a where clause on a values statement. Try something like:

INSERT INTO someSQLtable (providername, providerlink) 
SELECT 'somestring', 'http://www.someurl.com/stuff/'
FROM ( values (1) ) as T 
WHERE NOT EXISTS (
     SELECT * FROM someSQLtable 
     WHERE someSQLtable.providerlink = 'http://www.someurl.com/stuff/')

If ( values (1) ) as T is not supported, perhaps there is something similar to Oracles dual or DB2's sysibm.sysdummy1

answered Jun 6, 2014 at 8:10

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

3 Comments

user432 Over a year ago

I'm pretty sure you don't need the FROM: INSERT INTO table(...) SELECT '', '' WHERE NOT EXISTS (...) should work.

Lennart - Slava Ukraini Over a year ago

According to standard you have to select from something, but perhaps SQLITE loosens this.

user432 Over a year ago

Yes, take a look at the other answer.

Collectives™ on Stack Overflow

sqlite3.OperationalError: near "WHERE": syntax error (Python 2, sqlite3)

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related