Python SQLite3, table not showing updates

Question

I am trying to create a database with SQLite3 in Python.

Creating a table appears to work, but whenever I try to insert data, it doesn't seem to add anything to the database as fetchall() returns nothing, However, if I try to create the ID again, it complains about unique constraint failed.

Initialization:

import sqlite3
conn = sqlite3.connect('login.db')
c = conn.cursor()

Table Creation:

c.execute("""CREATE TABLE Login (ID INTEGER PRIMARY KEY,
First TEXT NOT NULL,
Last TEXT NOT NULL,
Middle TEXT NOT NULL,
Gender TEXT NOT NULL);""")
conn.commit()

Data Insert:

c.execute("""INSERT INTO Login VALUES (6, 'First', 'Last', 'Hello', 'Male');""")
conn.commit()

Fetching Tables:

print(c.fetchall())

c.close()
conn.close()

When dropping the table into an online reader, it also appears empty.

EDIT: This is what is shown in the db reader, and in google sheets, large list of blanks / ";" then this

Try to specify columns in your INSERT statement.

Olvin Roght
– Olvin Roght

2021-01-30 20:35:41 +00:00
Commented Jan 30, 2021 at 20:35 — Olvin Roght
– Olvin Roght, Commented Jan 30, 2021 at 20:35
I did, it did nothing

Python Programmer
– Python Programmer

2021-01-30 20:36:35 +00:00
Commented Jan 30, 2021 at 20:36 — Python Programmer
– Python Programmer, Commented Jan 30, 2021 at 20:36

AKX · Accepted Answer · 2021-01-30 20:46:35Z

1

c.fetchall() would return all of the rows from a SELECT query, which you aren't doing.

import sqlite3
conn = sqlite3.connect('login.db')
c = conn.cursor()
c.execute("""CREATE TABLE Login (ID INTEGER PRIMARY KEY, First TEXT NOT NULL, Last TEXT NOT NULL, Middle TEXT NOT NULL, Gender TEXT NOT NULL);""")
conn.commit()
c.execute("""INSERT INTO Login VALUES (6, 'First', 'Last', 'Hello', 'Male');""")
conn.commit()
c.execute("SELECT * FROM login")
print(c.fetchall())

will happily print

[(6, 'First', 'Last', 'Hello', 'Male')]

As an aside, your code is vulnerable to SQL injection attacks, and you should do

import sqlite3

conn = sqlite3.connect("login.db")
c = conn.cursor()
c.execute(
    """CREATE TABLE Login (ID INTEGER PRIMARY KEY, First TEXT NOT NULL, Last TEXT NOT NULL, Middle TEXT NOT NULL, Gender TEXT NOT NULL);"""
)
conn.commit()
c.execute(
    "INSERT INTO Login (ID, First, Last, Middle, Gender) VALUES (?,?,?,?,?)",
    (6, "First", "Last", "Hello", "Male"),
)
conn.commit()
c.execute("SELECT * FROM login")
print(c.fetchall())

answered Jan 30, 2021 at 20:46

AKX

171k16 gold badges147 silver badges229 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Python Programmer Over a year ago

Thank you for your help.

Python Programmer Over a year ago

A few hours later I know but I looked into the SQL injection vulnerability part and it does seem like a big problem! This isn't production code but I didn't know about this vulnerability, thanks for pointing it out.

AKX Over a year ago

Even so, you would have bumped into the issue the first time you attempted to insert anything with quote characters :)

Collectives™ on Stack Overflow

Python SQLite3, table not showing updates

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related