CODEWITHSUNDEEP
pythonsqlite
JED
JED

Reputation: 1694

Python SQLite3 query concatenating two values

I have a database (student.db) that contains one table (Students).
Each row in students contains (ID, FName, LName).
There is an existing row (0, John, Doe).

I want to be able to enter a variable as a full name, find that name in the database, and delete that row.



Here's what I came up with... but it doesn't seem to work:

import sqlite3    

conn = sqlite3.connect('student.db')
c = conn.cursor()

selection = "John Doe"

c.execute('DELETE FROM Students WHERE (FName + " " + LName =?', (selection,))

I don't get an error message. I'm guessing that's because it simply doesn't find any entries that match the WHERE clause.

Is there a way to properly write a WHERE clause that incorporates multiple values from the row of interest?

I am very new to this, so I apologize if this is a dumb question. I'm trying to make a Kivy app that creates a ListView of student names. Then you can select a student from the list and click "Delete" to remove that student from the ListView dictionary and from the database.

Upvotes: 0

Views: 1306

Answers (2)

phd
phd

Reputation: 94676

For concatenation most SQL implementations use '||' operator. See SQLite docs.

c.execute("DELETE FROM Students WHERE (FName || ' ' || LName = ?", (selection,))

Upvotes: 2

roganjosh
roganjosh

Reputation: 13185

Working with names is difficult but I think I'm over-thinking your question. Assuming that you just want to query two fields, you can split the name into a first_name and a last_name, and delete from the DB where that combination is satisfied.

selection = "John Doe"
first_name, last_name = selection.split()

query = """
        DELETE FROM Students 
        WHERE FName = ? AND LName = ?
        """

c.execute(query, (first_name, last_name))
conn.commit()

Upvotes: 0

Related Questions