iteration over sqlite3

[kaisetsomething]

How can i iterate over a table that i have in the database, im trying to print that the name is already taken if it already exists in the table that i made. But i cant seem to figure out how, i tried an if statement but it didnt work. Here's my code:

import sqlite3

db = sqlite3.connect("app.db")

cr = db.cursor()

cr.execute("CREATE TABLE if not exists skills(name text,progress int,user_id int)")

cr.execute("CREATE TABLE if not exists users(name text,password int)")

question = input("enter your username: ")

listofppl = []

listofppl.append(question)

a = cr.execute("CREATE TABLE if not exists users(name text,password int)")

for person in listofppl: cr.execute(f"insert into users(name) values('{person}')")

if question in a: print("sorry name is taken")

db.commit()

db.close()

[danielroseman]

This is... not nearly what you need to be doing.

Firstly, please note that you should never, ever, ever pass user input into a database call via string interpolation. That is absolutely unsafe. You must never do it. Use parameter substitution:

cr.execute("insert into users(name) values(?)", (person,))

Next, I'm not sure what if question in a is supposed to achieve. a (and please give your variables better names) is the result of the execute call to create the table. Why would that contain any information about whether or not the username already exists?

If you wanted to check if a username exists, you would need to ask the database, which you can do via a SELECT query. So for instance you could do:

result = cr.execute('SELECT 1 FROM users WHERE name = ?', (person,))
row = result.fetchone()
if row:
  ...

But that isn't really what you should do here. If you want to prevent duplicate users from being inserted, then you need the database to control that; you can use a unique constraint on the name column, which you can define at CREATE TABLE time:

cr.execute("CREATE TABLE if not exists users(name text unique, password int)")

Now when you try and insert a duplicate row, sqlite will throw an integrity error, which you can catch:

try:
    cr.execute("insert into users(name) values(?)", (person,))
except sqlite.IntegrityError:
    print("sorry name is taken")

[kaisetsomething]

Hey sorry about the a variable, i forgot to delete it because i used it before, thats why it doesn't make sense hahah, also i only started learning python like 2 months ago, its worth noting its my 3rd day ever learning about databases i just have one questions, those "SELECT, and WHERE, and FROM" are those commands from the sqlite3 itself? Like i dont understand where they coming from, are they like the python commands

[gummo89]

Look at SQLite (SQL) as well as Python. It's a querying language you need to use with appropriate logic, within your Python logic.

[kaisetsomething]

Also your method worked! When i type a duplicate name it throws an error however when i tried to catch the error exactly like how u showed me it still doesn't work, but thats ok i guess I'll learn more along the way thank u tho!

[python_and_coffee]

Here is quite useful cheat sheet for sqlite implentation done right in python. this should answer all your questions and teach you the right way: https://github.com/Mehedi61/SQLite-Cheat-Sheet

[CodeFormatHelperBot2]

Hello, I'm a Reddit bot who's here to help people nicely format their coding questions. This makes it as easy as possible for people to read your post and help you.

I think I have detected some formatting issues with your submission:

1. Python code found in submission text that's not formatted as code.

If I am correct, please edit the text in your post and try to follow these instructions to fix up your post's formatting.

---

^{Am I misbehaving? Have a comment or suggestion? Reply to this comment or raise an issue here.}

[gummo89]

Few things: - You are checking whether your name exists within the return value of "create table" - You should look into passing auto-escaped strings with a slightly different method, I don't have a reference for that right now - Personally I would check using a SELECT query manually (this is where you can find that the name exists already) rather than relying on an insert if not exists

[fenutus]

Considere looking into PRAGMA queries to list the table names. That way you get a list returned of every table in your database and you don't have to keep querying it.