I have been trying to work out how to insert into a SQL table for days. Nothing I try ever works and I just spent hours fixing all the errors, to find that I am back to the start where the insert statement doesnt insert anything. I only have a couple of hours left and I cant do anything else with my code until this is fixed, thanks.
I dont understand at all what is wrong, and there isnt any errors anymore. It just prints out the table without anything in it. I would like it to print out the inputs which I have (They are defined in the full code and I have checked they do work and print)
def insert_user():
import sqlite3
score = "0"
db = sqlite3.connect("Database.db")
cursor = db.cursor()
sql = "INSERT INTO users (username,firstname, surname, age, password , score) VALUES (?,?,?,?,?,?)"
db.execute(sql,(usernamei, first_name ,last_name , age, passwordi ,score))
db.commit()
result = cursor.fetchall()
print()
print("{0:<20} {1:<20} {2:<20} {3:<20} {4:<20} {5:<20}".format("userID","username","firstname","surname","age","password","score"))
print("====================================================================================================")
for each in result:
print("{0:<20} {1:<20} {2:<20} {3:<20} {4:<20} {5:<20}".format(each[1],each[2],each[3],each[4],each[5],each[6]))
insert_user()
When you INSERT, DELETE, UPDATE a Cursor is not used. To get data from a table you use (execute) SELECT SQL, the cursor will then contain the result set.
So you need to do the INSERTS and then execute a SELECT e.g. using "SELECT * FROM users" and then use result = cursor.fetchall().
instead of executing against the connection you execute against the cursor.
Something like (for testing the table is dropped and created and hard coded values are used (obviously adjust accordingly)):-
def insert_user():
import sqlite3
score = "0"
db = sqlite3.connect("Database.db")
sql = "DROP TABLE IF EXISTS users"
db.execute(sql)
sql = "CREATE TABLE IF NOT EXISTS users (userID INTEGER PRIMARY KEY, username TEXT UNIQUE, firstname TEXT, surname TEXT, age INTEGER, password TEXT, score INTEGER)"
db.execute(sql)
result = db.cursor()
sql = "INSERT INTO users (username,firstname, surname, age, password , score) VALUES (?,?,?,?,?,?)"
db.execute(sql,("testusername", "testfirstname","testsurname" , 10, "password" ,score))
db.commit()
result.execute("SELECT * FROM users")
print("{0:<20} {1:<20} {2:<20} {3:<20} {4:<20} {5:<20} {6:<20}".format("userID","username","firstname","surname","age","password","score"))
print("====================================================================================================")
for each in result:
print("{0:<20} {1:<20} {2:<20} {3:<20} {4:<20} {5:<20} {6:20}".format(each[0],each[1],each[2],each[3],each[4],each[5],each[6]))
insert_user()
Results in :-
E:\PYCharmPythonProjects\venv\Scripts\python.exe E:/PYCharmPythonProjects/Test001.py
userID username firstname surname age password score
====================================================================================================
1 testusername testfirstname testsurname 10 password 0
Process finished with exit code 0