can't insert records in sqlite database by python3

Question

import sqlite3

conn = sqlite3.connect('animelist.sqlite')
cur = conn.cursor()

cur.execute('''DROP TABLE IF EXISTS Lists''')
cur.execute('''CREATE TABLE Lists (Title TEXT, Genre TEXT, Production TEXT, 
Year TEXT)''')

while True:
    a_title = input('Insert title: ')
    a_genre = input('Insert genre: ')
    a_production = input('Insert production: ')
    a_year = input('Insert year: ')

    cur.execute('''INSERT INTO Lists (Title, Genre, Production, Year) VALUES 
    (a_title, a_genre, a_production, a_year)''')

    print('Data is inserted')
    dbexit = input('EXIT (y/n): ')
    if dbexit == 'y':
        break
    elif dbexit == 'n':
        continue

I want to make my animation lists by using python, sqlite

but when I try this script,

it says 'sqlite3.OperationalError: no such column: a_title'

what am I missing?

You need to use placeholders to bind variables to your statement. There are a bunch of examples in the python sqlite library documentation - look for bits that call execute on queries with lots of question marks and pass a tuple of values to the function too. — Shawn
– Shawn, Commented Oct 21, 2018 at 5:01

Bimal Poudel · Accepted Answer · 2018-10-21 06:23:43Z

3

You should use parameterize queries.

import sqlite3

conn = sqlite3.connect('animelist.sqlite')
cur = conn.cursor()

cur.execute('''DROP TABLE IF EXISTS Lists''')
cur.execute('''CREATE TABLE Lists (Title TEXT, Genre TEXT, Production TEXT, Year TEXT)''')

a_title = 'Title'
a_genre = 'Genre'
a_production = 'Production'
a_year = 2018

cur.execute('''INSERT INTO Lists (Title, Genre, Production, Year) VALUES(?,?,?,?)''', (a_title, a_genre, a_production, a_year))
conn.commit()

Here, I am not taking user input. But it inserts the data to the table.

answered Oct 21, 2018 at 6:23

Bimal Poudel

1,2342 gold badges20 silver badges41 bronze badges

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

2 Comments

MINSIK Over a year ago

I tried everything answers but only It works when I added 'conn.commit()' could you explain me? I'm a newbie

Bimal Poudel Over a year ago

Connection Commit is to tell database write all the changes. See: stackoverflow.com/questions/2847999/…

Collectives™ on Stack Overflow

can't insert records in sqlite database by python3

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related