how to insert variable in sqlite3 request python

Question

Dears,

how can I check if pos_cli from database is equal to variable pos_id? for now with code below I get the following error

cur.execute("CREATE TABLE IF NOT EXISTS Magnit_Coor (pos_cli INTEGER PRIMARY KEY, lat INTEGER, long INTEGER);")
cur.execute('SELECT * FROM Magnit_pos')
data = cur.fetchall()
while True:
    for coo in data:
        full_add = coo[6:11]
        pos_id = coo[0]
        print (pos_id)
        yand_add = ", ".join(full_add)
        g = cur.execute('SELECT EXISTS (SELECT * FROM Magnit_Coor WHERE pos_cli = (?))',pos_id)
        g = cur.fetchone()[0]

error below

10001
Traceback (most recent call last):
  File "geoco.py", line 17, in <module>
    g = cur.execute('SELECT EXISTS (SELECT * FROM Magnit_pos WHERE pos_cli = (?))',pos_id)
ValueError: parameters are of unsupported type

The initial code to create Magnit_pos table and pos_cli especially below

cur.execute("DROP TABLE IF EXISTS Magnit_Pos;")
cur.execute(
    "CREATE TABLE Magnit_Pos (pos_cli INTEGER PRIMARY KEY, magnit_name TEXT, codesfa TEXT, codewsot TEXT, pos_sap TEXT, source_dc TEXT, zip TEXT, region TEXT, area TEXT, city TEXT, street TEXT, house TEXT, build TEXT);")

with open('magnit.csv') as csvfile:
    magnit = csv.reader(csvfile, delimiter=';')
    print(magnit)
    for row in magnit:
        print(row[0])
        # to_db = [unicode(row[0], "utf8"), unicode(row[1], "utf8")]
        cur.execute("INSERT INTO Magnit_Pos (pos_cli, magnit_name, codesfa, codewsot, pos_sap, source_dc, zip, region, area, city, street, house, build) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", row)

are you expecting people to guess what is inside data variable? — Evgeny
– Evgeny, Commented Jun 9, 2018 at 19:43

r2evans · Accepted Answer · 2018-06-09 20:00:10Z

1

From python's sqlite3 documentation (emphasis mine):

Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method.

So you should be using:

    g = cur.execute('SELECT EXISTS (SELECT * FROM Magnit_Coor WHERE pos_cli = (?))',(pos_id,))

answered Jun 9, 2018 at 20:00

r2evans

167k8 gold badges92 silver badges176 bronze badges

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

Collectives™ on Stack Overflow

how to insert variable in sqlite3 request python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related