sqlite insert not adding any records [duplicate]

Question

This question already has answers here:

Sqlite insert query not working with python? (2 answers)

Closed 1 year ago.

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>

• jackal: Notwithstanding potentially degraded runtime performance you could add autocommit=True to sqlite3.connect()
• Saeed: @SIGHUP thanks, but TypeError: autocommit is an invalid keyword argument for this function when I use sqlite3.connect(autocommit=True). Am I doing anything wrong?
• jackal: You may have an out-of-date Python environment. You also missed out the path to the DB file. The autocommit keyword is certainly valid in Python 3.12.4
• Saeed: @SIGHUP what's odd for me is that, my linter is vscode shows I have this keyword for this function and I check the source file and I see I have it:( I use python 3.10.13. I also checked it with isolation_level and it uses a lot of RAM. I think commit() method is better

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'}