import sqlite3
# Create a database and open the database.
# If the database already exists just opens the database
conn = sqlite3.connect('users.db')
c = conn.cursor()
# Create a users table if the table does not exists
c.execute('''CREATE TABLE IF NOT EXISTS users(name TEXT, age INTEGER)''')
# commit changes and close database connect
conn.commit()
conn.close()
# Insert values with parameter queries to prevent sql injections
conn = sqlite3.connect('users.db')
c = conn.cursor()
name = "John"
number = 20
c.execute("INSERT INTO users VALUES (?, ?)", (name, number))
# commit changes and close database connect
conn.commit()
conn.close()
# Insert many values with parameter queries to prevent sql injections
users = [
("James", 10),
("Smith", 21),
("Jerome", 11)
]
conn = sqlite3.connect('users.db')
c = conn.cursor()
c.executemany("INSERT INTO users VALUES (?, ?)", users)
# commit changes and close database connect
conn.commit()
conn.close()
# Print values from table
conn = sqlite3.connect('users.db')
c = conn.cursor()
for row in c.execute('SELECT * FROM users'):
print(row)
# list comprehension to store all names and ages in separate lists
names = [row[0] for row in c.execute('SELECT * FROM users')]
ages = [row[1] for row in c.execute('SELECT * FROM users')]
# list comprehension store all names and ages in a list
combined = [f"Name: {row[0]} - Age: {row[1]}" for row in c.execute('SELECT * FROM users')]
# print names and ages list
print(names, ages)
# print name and ages command list
print(combined)
# close connection
conn.close()
# Print all rows in a list of tuples using fetchall
conn = sqlite3.connect('users.db')
c = conn.cursor()
result = c.execute("SELECT * FROM users")
print(result.fetchall())
conn.close()
# output
# ('John', 20)
# ('James', 10)
# ('Smith', 21)
# ('Jerome', 11)
# ['John', 'James', 'Smith', 'Jerome'] [20, 10, 21, 11]
# ['Name: John - Age: 20', 'Name: James - Age: 10', 'Name: Smith - Age: 21', 'Name: Jerome - Age: 11']
# [('John', 20), ('James', 10), ('Smith', 21), ('Jerome', 11)