Adding to the provided answer, I think there are even better improvements.
1) Using Dictionary instead of list
Why?, better readability, maintainability and possibly speed (getting from a dictionary is way faster than access data via list indexing, in large list)
import sqlite3
connection: sqlite3.Connection = sqlite3.connect(":memory:")
connection.row_factory = sqlite3.Row # This will make return a dictionary like object instead of tuples
cursor: sqlite3.Cursor = connection.cursor()
# create db
cursor.execute("CREATE TABLE user(name VARCHAR[100] UNIQUE, age INT, sex VARCHAR[1])")
data = (
{"name": "laks", "age": 444, "sex": "M"},
{"name": "kam", "age": 445, "sex": "M"},
{"name": "kam2", "age": 445, "sex": "M"},
)
cursor.executemany("INSERT INTO user VALUES(:name, :age, :sex)", data)
cursor.execute("SELECT * FROM user WHERE age >= ?", (445, ))
users = cursor.fetchall()
for user in users:
print(user["name"], user["age"], user["sex"])
Output
kam 445 M
kam2 445 M
2) Use a context manager to automatically commit - rollback
This is way better to handle transaction automatically and rollback.
import sqlite3
connection: sqlite3.Connection = sqlite3.connect(":memory:")
connection.row_factory = sqlite3.Row # This will make return a dictionary like object instead of tuples
cursor: sqlite3.Cursor = connection.cursor()
# create db
# con.rollback() is called after the with block finishes with an exception,
# the exception is still raised and must be caught
try:
with connection:
cursor.execute("CREATE TABLE user(name VARCHAR[100] UNIQUE, age INT, sex VARCHAR[1])")
except sqlite3.IntegrityError as error:
print(f"Error while creating the database, {error}")
data = (
{"name": "laks", "age": 444, "sex": "M"},
{"name": "kam", "age": 445, "sex": "M"},
{"name": "kam2", "age": 445, "sex": "M"},
)
# con.rollback() is called after the with block finishes with an exception,
# the exception is still raised and must be caught
try:
with connection:
cursor.executemany("INSERT INTO user VALUES(:name, :age, :sex)", data)
except sqlite3.IntegrityError as error:
print(f"Error while creating the database, {error}")
# Will add again same data, will throw an error now!
try:
with connection:
cursor.executemany("INSERT INTO user VALUES(:name, :age, :sex)", data)
except sqlite3.IntegrityError as error:
print(f"Error while creating the database, {error}")
cursor.execute("SELECT * FROM user WHERE age >= ?", (445, ))
users = cursor.fetchall()
for user in users:
print(user["name"], user["age"], user["sex"])
Output
Error while creating the database, UNIQUE constraint failed: user.name
kam 445 M
kam2 445 M
Documentatin
