sqlite insert not adding any records

Question

This is my code:

import os
path = '/home/test/'
os.system(f'rm -vf {path}myDB.db')
import sqlite3
import datetime
conn = sqlite3.connect(f"{path}myDB.db")
conn.execute(
    '''
    CREATE TABLE logs
    (id INTEGER PRIMARY KEY,
    userid INTEGER NOT NULL,
    email TEXT NOT NULL,
    ip_address TEXT,
    date DATE
    );
    '''
)

clients = [
    {'userid': 26026, 'email': '[email protected]', 'ip': '1.1.1.158', 'date': f'{datetime.datetime.now()}'},
    {'userid': 31010, 'email': '[email protected]', 'ip': '1.1.1.10', 'date': f'{datetime.datetime.now()}'},
    {'userid': 26076, 'email': '[email protected]', 'ip': '1.1.1.160', 'date': f'{datetime.datetime.now()}'},
]

for out in clients:
    conn.execute(
        f"""
        INSERT INTO logs (id, userid, email, ip_address, date)
        VALUES (NULL, {out['userid']}, '{out['email']}', '{out['ip']}', '{out['date']}')
        """)

conn.close()
conn = sqlite3.connect(f"{path}myDB.db")
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute('SELECT * FROM logs')
output = []
for r in c.fetchall():
    output.append(dict(r))

for x in output: x

conn.close()

It only creates the file and the table, but does not insert any values.

What is wrong with my code? I tried some simpler samples and they worked and did insert, but with the real-world example, they don't work.

I also see this output after running INSERT:

<sqlite3.Cursor object at 0x7f393d757bc0>
<sqlite3.Cursor object at 0x7f393d7578c0>
<sqlite3.Cursor object at 0x7f393d757bc0>

Answer 1

You didn't commit the changes to the database before closing:

import os

path = "/tmp/"
os.system(f"rm -vf {path}myDB.db")
os.system(f"touch {path}myDB.db")

import datetime
import sqlite3

conn = sqlite3.connect(f"{path}myDB.db")
conn.execute(
    """
    CREATE TABLE logs
    (id INTEGER PRIMARY KEY,
    userid INTEGER NOT NULL,
    email TEXT NOT NULL,
    ip_address TEXT,
    date DATE
    );
    """
)

clients = [
    {
        "userid": 26026,
        "email": "[email protected]",
        "ip": "1.1.1.158",
        "date": f"{datetime.datetime.now()}",
    },
    {
        "userid": 31010,
        "email": "[email protected]",
        "ip": "1.1.1.10",
        "date": f"{datetime.datetime.now()}",
    },
    {
        "userid": 26076,
        "email": "[email protected]",
        "ip": "1.1.1.160",
        "date": f"{datetime.datetime.now()}",
    },
]

for out in clients:
    conn.execute(
        f"""
        INSERT INTO logs (id, userid, email, ip_address, date)
        VALUES (NULL, {out['userid']}, '{out['email']}', '{out['ip']}', '{out['date']}')
        """
    )

conn.commit()     # <--- put commit() here!

conn.close()
conn = sqlite3.connect(f"{path}myDB.db")
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute("SELECT * FROM logs")

output = []

for r in c.fetchall():
    output.append(dict(r))

for x in output:
    print(x)

conn.close()

Then the result is:

{'id': 1, 'userid': 26026, 'email': '[email protected]', 'ip_address': '1.1.1.158', 'date': '2024-06-16 10:58:07.105912'}
{'id': 2, 'userid': 31010, 'email': '[email protected]', 'ip_address': '1.1.1.10', 'date': '2024-06-16 10:58:07.105922'}
{'id': 3, 'userid': 26076, 'email': '[email protected]', 'ip_address': '1.1.1.160', 'date': '2024-06-16 10:58:07.105924'}