Sqlite3 Insertion does not retain records with Python
<p>I am trying to insert multiple records to sqlite database. This is my test script. </p>

<pre><code>import sqlite3
from datetime import datetime
company = 'fghjk'
keyword = 'awesome'
filename = 'test.txt'
date_found = datetime.now()
conn = sqlite3.connect('C:\\sqlite\\test.db')
c = conn.cursor()

for i in range(0,4):
    insert_query = "INSERT into records (company,keyword,filename,date_found) values (?,?,?,?)"
    c.execute(insert_query, [company,keyword,filename,date_found])

select_query  = "SELECT * FROM records"
c.execute(select_query)
res = c.fetchall()
print res
for i in res:
    for b in i:
        print b
</code></pre>

<p>When I execute the script above <strong>twice</strong>, I am expecting to have 8 records, but it's always giving back 4 rows. Can someone enlighten me? </p>

<p>Output: </p>

<pre><code>1
fghjk
awesome
test.txt
2016-09-23 09:11:19.585000
2
fghjk
awesome
test.txt
2016-09-23 09:11:19.585000
3
fghjk
awesome
test.txt
2016-09-23 09:11:19.585000
4
fghjk
awesome
test.txt
2016-09-23 09:11:19.585000
</code></pre>

<p>This is my table's create statement. </p>

<pre><code>conn.execute('''CREATE TABLE records
      # (ID INTEGER PRIMARY KEY AUTOINCREMENT,
       # company           TEXT    NOT NULL,
       # keyword            INT     NOT NULL,
       # filename        CHAR(50),
       # date_found        DATETIME);''')
</code></pre>

<p>You can use the connection as a context manager. This will properly terminate the connection, including a commit.</p>

<pre><code>import sqlite3
from datetime import datetime
company = 'fghjk'
keyword = 'awesome'
filename = 'test.txt'
date_found = datetime.now()
conn = sqlite3.connect('test.db')
first = False

# use context manager here!!
with conn:
    c = conn.cursor()
    if first:
        conn.execute('''CREATE TABLE records
            (ID INTEGER PRIMARY KEY AUTOINCREMENT,
            company           TEXT    NOT NULL,
            keyword            INT     NOT NULL,
            filename        CHAR(50),
            date_found        DATETIME);''')

    for i in range(0,4):
        insert_query = "INSERT into records (company,keyword,filename,date_found) values (?,?,?,?)"
        c.execute(insert_query, [company,keyword,filename,date_found])

    select_query  = "SELECT * FROM records"
    c.execute(select_query)
    res = c.fetchall()
    print res
    for i in res:
        for b in i:
        print b
</code></pre>

python python-2.7 sqlite