Python SQlite3 insert with params

Question

Problem:

I have a problem with inserting data into SQlite with python. At this moment i am new to python so this must be beginner mistake.

Errors :

OperationalError: unrecognized token: "{"

What I have tried:

I have read many tutorials and tried many options as shown below in the code examples.But i cannot get this to work without error for some reason.

Some examples :

cursor.execute("INSERT INTO wanted_movie (tmdb_id, name, year) VALUES ({tmdb_id}, {name}, {year})".format(
        tmdb_id=str(data['id']), name=str(data['title']), year=str(data['release_date']).split('-')[0]))

cursor.execute("INSERT INTO wanted_movie (tmdb_id, name, year) VALUES ({tmdb_id}, {name}, {year})", {
        "tmdb_id": str(data['id']), "name": str(data['title']), "year": str(data['release_date']).split('-')[0]})
    conn.commit()

Question :

Can someone help me to insert a row correctly with SQlite3 in a pythonic way?

The specific error is missing the ** to unpack the dictionary in the second example. However, please read docs.python.org/2/library/sqlite3.html and don't use str.format to interpolate variables! — jonrsharpe
– jonrsharpe, Commented Feb 12, 2015 at 22:40

Cristian Ciupitu · Accepted Answer · 2015-02-12 23:19:14Z

5

More than a pythonic way there is a sqlitic way

From the docs

Instead, use the DB-API’s parameter substitution. 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.

One of your examples can be solved by using

values = (data['id'],data['title'],data['release_date'])
cursor.execute("INSERT INTO wanted_movie (tmdb_id, name, year) VALUES (?, ?, ?)",values)

edited Feb 12, 2015 at 23:19

Cristian Ciupitu

21.1k7 gold badges56 silver badges80 bronze badges

answered Feb 12, 2015 at 22:46

Bhargav Rao

52.6k29 gold badges130 silver badges142 bronze badges

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

1 Comment

Cristian Ciupitu Over a year ago

As the Python Database API Specification v2.0 mentions, the ? placeholder can also be found out from the module's paramstyle global, i.e. sqlite3.paramstyle is 'qmark'.

Collectives™ on Stack Overflow

Python SQlite3 insert with params

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related