Solving 'Unrecognized Token' Error While Using SQLite Insert Command

Question

I keep getting an OperationalError: Unrecognized Token. The error hapens when I'm attempting to insert data into my SQLite database using an SQLite Insert command. What do I need to do to correct this error or is there a better way I should go about inserting data into my database? The data is water level data measured in meters above chart datum and is gathered from water level gauge data loggers throughout the Great Lakes region of Canada and the US. The script uses the Pandas library and is hardcoded to merge data from water level gauging stations that are located in close proximity to each other. I'd like to use the insert command so I can deal with overlapping data when adding future data to the database. I won't even begin to pretend I know what I'm talking about with databases and programming so any help would be appreciated in how I can solve this error!

I've tried altering my script in the parameterized query to try and solve the problem without any luck as my research has said this is the likely culprit

    # Tecumseh. Merges station in steps due to inability of operation to merge all stations at once. Starts by merging PCWL station to hydromet station followed by remaining PCWL station and 3 minute time series
    final11975 = pd.merge(hydrometDF["Station11975"], pcwlDF["station11995"], how='outer', left_index=True,right_index=True)
    final11975 = pd.merge(final11975, pcwlDF["station11965"], how='outer', left_index=True,right_index=True)
    final11975 = pd.merge(final11975, cts, how='outer', left_index=True,right_index=True)
final11975.to_excel("C:/Users/Andrew/Documents/CHS/SeasonalGaugeAnalysis_v2/SeasonalGaugeAnalysis/Output/11975_Tecumseh.xlsx")
    print "-------------------------------"
    print "11975 - Tecumseh"
    print(final11975.info())
    final11975.index = final11975.index.astype(str)
    #final11975.to_sql('11975_Tecumseh', conn, if_exists='replace', index=True)
    #Insert and Ignore data into database to eliminate overlaps
        testvalues = (final11975.index, final11975.iloc[:,0], final11975.iloc[:,1], final11975.iloc[:,2])
        c.execute("INSERT OR IGNORE INTO 11975_Tecumseh(index,11975_VegaRadar(m),11995.11965), testvalues")
        conn.commit()

I'd like the data to insert into the database using the Insert And Ignore command as data is often overlapping when its downloaded. I'm new to databases but I'm under the impression that the Insert and Ignore command will illiminate overlapping data. The message I receive when running my script is:

</> <Exception has occurred: OperationalError
unrecognized token: "11975_Tecumseh"
  File "C:\Users\Documents\CHS\SeasonalGaugeAnalysis_v2\SeasonalGaugeAnalysis\Script\CombineStations.py", line 43, in <module>>
    c.execute("INSERT OR IGNORE INTO 11975_Tecumseh(index,11975_VegaRadar(m),11995.11965), testvalues") </>

Answer 1

The error you are getting is because the table name 11975_Tecumseh is invalid as it stands as it is not suitably enclosed.

If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:

• 'keyword' A keyword in single quotes is a string literal.
• "keyword" A keyword in double-quotes is an identifier. [keyword] A
• keyword enclosed in square brackets is an identifier.

• This is not

standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility. keyword A

• keyword enclosed in grave accents (ASCII code 96) is an identifier.
  • This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility. For resilience when confronted with historical SQL statements, SQLite will sometimes bend the quoting rules above:

If a keyword in single quotes (ex: 'key' or 'glob') is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.

If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

Programmers are cautioned not to use the two exceptions described in the previous bullets. We emphasize that they exist only so that old and ill-formed SQL statements will run correctly. Future versions of SQLite might raise errors instead of accepting the malformed statements covered by the exceptions above.

SQL As Understood By SQLite - SQLite Keywords

• The above is applied to invalid names, which includes names that start with numbers and names that include a non numeric inside parenthesises.

If 11975_Tecumseh is the actual table name then it must be enclosed e.g. [11975_Tecumseh]

Likewise the columns

• index
• 11975_VegaRadar(m)
• and 11995.11965

Also have to be suitably enclosed.

Doing so you'd end up with

"INSERT OR IGNORE INTO [11975_Tecumseh]([index],[11975_VegaRadar(m)],[11995.11965]), testvalues"

The the issues is that ,testvalues is syntactically incorrect. after the columns to insert into i.e. ([index],[11975_VegaRadar(m)],[11995.11965]) the keyword VALUES with the three values should be used.

An example of a valid statement is :

"INSERT INTO [11975_Tecumseh] ([index],[11975_VegaRadar(m)],[11995.11965]) VALUES('value1','value2','value3')"

As such

c.execute("INSERT INTO [11975_Tecumseh] ([index],[11975_VegaRadar(m)],[11995.11965]) VALUES('value1','value2','value3')")

would insert a new row (unless a constrain conflict occurred)

However, I suspect that you want to insert values according to variables in which case you could use:

"INSERT INTO [11975_Tecumseh] ([index],[11975_VegaRadar(m)],[11995.11965]) VALUES(?,?,?)"

• the question marks being place-holders/bind values

• SQL As Understood By SQLite- INSERT

The above would then be invoked using :

c.execute("INSERT INTO [11975_Tecumseh] ([index],[11975_VegaRadar(m)],[11995.11965]) VALUES(?,?,?)",testvalues);

#Working Example :

import sqlite3
drop_sql = "DROP TABLE IF EXISTS [11975_Tecumseh]"
crt_sql = "CREATE TABLE IF NOT EXISTS [11975_Tecumseh] ([index],[11975_VegaRadar(m)],[11995.11965])"
testvalues = ("X","Y","Z")

c = sqlite3.connect("test.db")
c.execute(drop_sql)
c.execute(crt_sql)

insert_sql1 = "INSERT INTO [11975_Tecumseh] " \
              "([index],[11975_VegaRadar(m)],[11995.11965]) " \
              "VALUES('value1','value2','value3')"
c.execute(insert_sql1)

insert_sql2 = "INSERT OR IGNORE INTO '11975_Tecumseh'" \
              "('index','11975_VegaRadar(m)',[11995.11965])" \
              " VALUES(?,?,?)"

c.execute(insert_sql2,(testvalues))
cursor = c.cursor()
cursor.execute("SELECT * FROM [11975_Tecumseh]")
for row in cursor:
    print(row[0], "\n" + row[1], "\n" + row[2])
c.commit()
cursor.close()
c.close()

#Result

##Row 1

value1 
value2 
value3

##Row 2

X 
Y 
Z

Answer 2

As per SQL Standards, You can create table or column name such as "11975_Tecumseh" and also Tecumseh_11975, but cannot create table or column name begin with numeric without use of double quotes.

c.execute("INSERT OR IGNORE INTO '11975_Tecumseh'(index,'11975_VegaRadar(m)',11995.11965), testvalues")