Sqlite date field error for transactions
<p>trying to build two databases, one for houses, and another for the dates and prices that they were sold for in the last 16 years </p>

<pre><code>conn = sqlite3.connect('houses_in_london.db')
database = conn.cursor()
database.execute('CREATE TABLE houses (id INTEGER PRIMARY KEY, address TEXT,'
                 ' area TEXT NOT NULL, postcode TEXT, bedrooms TEXT)')
database.execute('CREATE TABLE transactions (transaction_id INTEGER, house_id INTEGER, '
                 'FOREIGN KEY(house_id) REFERENCES houses(id), date TEXT, sale_price INTEGER )')
database.commit()
database.close()
</code></pre>

<p>if you guys notice, I am trying to put a date field in the transactions table to mark each sale and have it as a text field, but It returns </p>

<pre><code>Traceback (most recent call last):
  File "/Users/saminahbab/Documents/House_Prices/final_spider.py", line 14, in &lt;module&gt;
    database.execute('CREATE TABLE transactions (transaction_id INTEGER, house_id INTEGER, '
sqlite3.OperationalError: near "DATE": syntax error
</code></pre>

<p>which does not make sense to me as I am just trying to create a text date field, which should pass, and I can do analysis with pandas which can read in that field and turn it into date objects.
anyone can help me make the date field?</p>

<p>From the docs: </p>

<blockquote>
  <p>CREATE TABLE includes one or more column definitions, optionally followed by a list of table constraints.</p>
</blockquote>

<p><a href="https://www.sqlite.org/lang_createtable.html" rel="nofollow">https://www.sqlite.org/lang_createtable.html</a></p>

<p>This is syntactiaclly a table constraint and MUST go after all column definitions:</p>

<pre><code>FOREIGN KEY(house_id) REFERENCES houses(id)
</code></pre>

<p>This is a column definition and can be between other column definitions:</p>

<pre><code>house_id INTEGER REFERENCES houses(id)
</code></pre>

python database sqlite