Sqlite python insert into table error
<p>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 </p>

<pre><code>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()
</code></pre>

<p>here is the error raised:</p>

<pre><code>Traceback (most recent call last):
  File "/Users/saminahbab/Documents/House_Prices/final_spider.py", line 186, in &lt;module&gt;
    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
</code></pre>

<p>and for reference here is the execute for the databse </p>

<pre><code># 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 )')
</code></pre>

<p>as always, thank you for the support </p>

<p>You have many issues:</p>

<ul>
<li><a href="https://www.sqlite.org/lang_insert.html" rel="nofollow">INSERT-clause</a> has no TABLE keyword</li>
<li>You're trying to pass variables to an SQL query using string formatting; don't do it, ever â use placeholders, or <a href="https://en.wikipedia.org/wiki/SQL_injection" rel="nofollow">face the consequences</a></li>
<li>Your VALUES-clause is missing commas between the value-expressions</li>
<li>The <a href="https://docs.python.org/3/library/sqlite3.html" rel="nofollow">sqlite3 module</a> uses "?" as a placeholder instead of percent formatters</li>
<li>"transaction_id NOT NULL AUTO_INCREMENT" is not a valid column name</li>
<li>"AUTO_INCREMENT" <a href="http://stackoverflow.com/questions/508627/auto-increment-in-sqlite-problem-with-python">is not valid SQLite syntax</a> and you probably meant for transaction_id to be <code>INTEGER PRIMARY KEY</code> â also <a href="https://www.sqlite.org/autoinc.html" rel="nofollow">AUTOINCREMENT should usually not be used</a></li>
</ul>

<p>The below functions fix some of the errors, barring the DDL-corrections to the <code>transactions</code> table.</p>

<pre><code>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()
</code></pre>

python database sqlite