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>This more concise syntax does the needeful</p>

<pre><code>database.execute(
   'CREATE TABLE transactions (transaction_id INTEGER, house_id INTEGER '
   'REFERENCES houses(id), date TEXT, sale_price INTEGER )')
</code></pre>

<p>Alternatively, you need to move the constraints to the end of the create statement</p>

<pre><code>database.execute(
    'CREATE TABLE transactions (transaction_id INTEGER, house_id INTEGER, '
    'date TEXT, sale_price INTEGER,
    'FOREIGN KEY(house_id) REFERENCES houses(id) )')
</code></pre>

python database sqlite