SQLite- Insert using foreign key - Python

Question

I am basically having issues with the syntax and can't find much information anywhere. I have two tables. Category_tb and Information_tb.

Category_tb Has column of category_name(text). Information_tb Has title(text) and category_id(INTEGER)

I am not using the foreign key I am using ROWID of Category_tb. Here are the SQL quires. Basically I am having a problem with the insertion of data into Information_tb using Category_tb.ROWID as a foreign key.

title = 'Some title'
categories = 'Crime'
self.curr.execute("""
                    insert into information_tb values (?,?)""",(
                        [title],
                        ["""select ROWID from Category_tb where Category_tb.category_name = [categories] """]
                        ))

Any help would be appreciated. Thanks in advance.

jignatius · Accepted Answer · 2020-02-28 08:44:22Z

1

The values clause of the insert statement should be column names. It can't contain a select statement. However, you can specify the values with a select statement.

Try this:

self.curr.execute("insert into information_tb (title, category_id) select ?, ROWID from Category_tb where Category_tb.category_name = ?",
      (title, categories))

Remember to commit afterward to save the changes.

answered Feb 28, 2020 at 8:44

jignatius

6,5442 gold badges19 silver badges37 bronze badges

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

4 Comments

ahsan mukhtar Over a year ago

Not exactly what I wanted but I got a general idea. I wanted to get the ROWID of the category the that matched categories and inserted that ID into category_id of information_tb. I worte this quries but still getting an error, please take a look at it. self.curr.execute(""" insert into information_tb (title, category_id) Values (?,select ROWID from category where category.category = ?)""" ,(title, categories[0])) Getting an error "sqlite3.OperationalError: near "select": syntax error"

jignatius Over a year ago

@ahsanmukhtar You don't need values (.... Please copy the code I've given.

ahsan mukhtar Over a year ago

I did I got this SQLite error sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

jignatius Over a year ago

@ahsanmukhtar You have to enclose the values in parentheses: (title, categories). The brackets are important.

Collectives™ on Stack Overflow

SQLite- Insert using foreign key - Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related