CODEWITHSUNDEEP
pythonsqldatabasesqlite
bjornasm
bjornasm

Reputation: 2318

Unrecognized token in SQLite statement

I am inserting data from one table to another, however for some reason I get "unrecognized token". This is the code:

cur.execute("INSERT INTO db.{table} SELECT distinct latitude, longitude, port FROM MessageType1 WHERE latitude>={minlat} AND latitude<={maxlat} AND longitude>= {minlong} AND longitude<= {maxlong}".format(minlat = bottomlat, maxlat = toplat, minlong = bottomlong, maxlong = toplong, table=tablename))

This translates to the following, with values:

INSERT INTO db.Vardo SELECT distinct latitude, longitude, port FROM MessageType1 WHERE latitude>=69.41 AND latitude<=70.948 AND longitude>= 27.72 AND longitude<= 28.416

The error code is the following:

sqlite3.OperationalError: unrecognized token: "70.948 AND"

Is the problem that there is three decimal points?

This is the create statement for the table:

cur.execute("CREATE TABLE {site} (latitude, longitude, port)".format(site = site))

Upvotes: 2

Views: 31949

Answers (2)

Sam CD
Sam CD

Reputation: 2097

Try using ? for your parameters: 

cur.execute("INSERT INTO db.? SELECT distinct latitude, longitude, port FROM MessageType1 WHERE latitude>=? AND latitude<=? AND longitude>= ? AND longitude<= ?",(bottomlat, toplat, bottomlong, toplong, tablename))

Upvotes: 1

alecxe
alecxe

Reputation: 473863

Don't make your SQL queries via string formatting, use the driver's ability to prepare SQL queries and pass parameters into the query - this way you would avoid SQL injections and it would make handling of passing parameters of different types transparent:

query = """
    INSERT INTO 
        db.{table} 
    SELECT DISTINCT
        latitude, longitude, port 
    FROM 
        MessageType1 
    WHERE 
        latitude >= ? AND 
        latitude <= ? AND 
        longitude >= ? AND 
        longitude <= ?
""".format(table=tablename)
cur.execute(query, (bottomlat, toplat, bottomlong, toplong))

Upvotes: 9

Related Questions