Python SQLite3 query concatenating two values

Question

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.

Well, the super-simple approach here would just be to split John Doe in two, and use AND in your query. That would get you past the first hurdle, but I think it might cause problems later on because names are notoriously difficult to work with (some people have one name, some might have 5, etc). What is the real source of `"John Doe" in your example? — roganjosh
– roganjosh, Commented May 28, 2017 at 18:19
do you want the selection to be spliited to FName: Joe and LName: Doe and then use it in your query? — olisch
– olisch, Commented May 28, 2017 at 18:20
The only reason I don't want to do split is because you could have names like "Mary Ann" for a first name... which could cause problems. The name "John Doe" comes from a python dictionary. The dictionary is currently set up as a list of full names. I'm sure the dictionary could be modified, but I was trying to follow a embellish upon a tutorial I found for Kivy youtu.be/dxXsZKmD3Kk?list=PLGLfVvz_LVvTn3cK5e6LjhgGiSeVlIRwt — JED
– JED, Commented May 28, 2017 at 19:00

phd · Accepted Answer · 2018-06-09 08:38:42Z

2

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

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

edited Jun 9, 2018 at 8:38

answered May 28, 2017 at 20:06

phd

97.2k14 gold badges165 silver badges223 bronze badges

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

Comments

roganjosh · Accepted Answer · 2017-05-28 18:31:38Z

0

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()

answered May 28, 2017 at 18:31

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

4 Comments

JED Over a year ago

The only reason I don't want to use selection.split() is because you could have names like "Mary Ann Robinson" where Mary Ann is the "first name"

roganjosh Over a year ago

In which case, how do you expect the query to handle that case?

roganjosh Over a year ago

@JED It's possible that you can store this data more easily. I should have waited for an answer to my comment. If the source of the data is the database itself (then reported on the screen) then this isn't an issue.

JED Over a year ago

I think that I need to come to terms with the fact that I am completely overthinking this tutorial video lol. But yes, if this were being done for a real life application, then the way that the full name of interest is being created should be different.

Collectives™ on Stack Overflow

Python SQLite3 query concatenating two values

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related