Sqlite python insert into table error

Question

having trouble with these two functions was wondering if people could tell me where I am going wrong this is a separate function as part of a spider that searches through a website of house prices

def save_house_to_db(id, address, postcode, bedrooms):
    conn = sqlite3.connect('houses_in_london.db')
    d = conn.cursor()
    d.execute('INSERT INTO TABLE houses (id, address, postcode, bedrooms) VALUES (%d %s %s %d)' %(id, str(address), str(postcode), float(bedrooms)))
    d.commit()
    d.close()

def save_transactions_to_db(id, sale_price, date):
    conn = sqlite3.connect('houses_in_london.db')
    d = conn.cursor()
    d.execute('INSERT INTO TABLE transactions (transaction_id NOT NULL AUTO_INCREMENT, house_id, date, sale_price) VALUES'
              '(%d %s %s)' %(id, sale_price, str(date)))
    d.commit()
    d.close()

here is the error raised:

Traceback (most recent call last):
  File "/Users/saminahbab/Documents/House_Prices/final_spider.py", line 186, in <module>
    final_function(link_set=areas,id_counter=40)
  File "/Users/s/Documents/House_Prices/final_spider.py", line 158, in final_function
    page_stripper(link=(root+page), id_counter=id_counter)
  File "/Users/s/Documents/House_Prices/final_spider.py", line 79, in page_stripper
    save_house_to_db(id=float(id_counter), address=address, postcode=postcode, bedrooms=bedrooms)
  File "/Users/s/Documents/House_Prices/final_spider.py", line 25, in save_house_to_db
    d.execute('INSERT INTO TABLE houses VALUES (%d %s %s %d)' %(id, str(address), str(postcode), float(bedrooms)))
sqlite3.OperationalError: near "TABLE": syntax error

and for reference here is the execute for the databse

# conn = sqlite3.connect('houses_in_london.db')
# database = conn.cursor()
# database.execute('CREATE TABLE houses (id INTEGER PRIMARY KEY, address TEXT,'
#                  'postcode TEXT, bedrooms TEXT)')
#
# database.execute('CREATE TABLE transactions (transaction_id NOT NULL AUTO_INCREMENT, house_id INTEGER '
#                  ' REFERENCES houses(id), date TEXT, sale_price INTEGER )')

as always, thank you for the support

In inserting to `houses` do you have a reason why you do not let the DB generate the integer primary keys, but explicitly pass them on insert. — Ilja Everilä, Oct 04 '16 at 11:46

score 1 · Accepted Answer · edited May 23 '17 at 12:06

You have many issues:

INSERT-clause has no TABLE keyword
You're trying to pass variables to an SQL query using string formatting; don't do it, ever – use placeholders, or face the consequences
Your VALUES-clause is missing commas between the value-expressions
The sqlite3 module uses "?" as a placeholder instead of percent formatters
"transaction_id NOT NULL AUTO_INCREMENT" is not a valid column name
"AUTO_INCREMENT" is not valid SQLite syntax and you probably meant for transaction_id to be INTEGER PRIMARY KEY – also AUTOINCREMENT should usually not be used

The below functions fix some of the errors, barring the DDL-corrections to the transactions table.

def save_house_to_db(id, address, postcode, bedrooms):
    conn = sqlite3.connect('houses_in_london.db')
    d = conn.cursor()
    # Remove the TABLE "keyword"
    d.execute('INSERT INTO houses (id, address, postcode, bedrooms) '
              'VALUES (?, ?, ?, ?)', (id, address, postcode, bedrooms))
    d.commit()
    d.close()

def save_transactions_to_db(id, sale_price, date):
    conn = sqlite3.connect('houses_in_london.db')
    d = conn.cursor()
    # This here expects that you've fixed the table definition as well
    d.execute('INSERT INTO transactions (house_id, date, sale_price) '
              'VALUES (?, ?, ?)', (id, sale_price, date))
    d.commit()
    d.close()

thanks Lija, going through the corrections now, much appreciated. the reason I didnt do an autogenerator for houses, is that I thought I would need the number as a foreign key when inputting the transactions in the transaction tables and it would be the most efficient way to do that — entercaspa, Oct 04 '16 at 13:06
Have a look [here](http://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python) on how to fetch last inserted row id through the cursor in python sqlite3. You can then let the DB generate the id and use it for related `transactions`. — Ilja Everilä, Oct 04 '16 at 16:13

Sqlite python insert into table error

1 Answers1