Python-SQL queries with variables

Question

I am having problems when I run a query to return the information of the students, I get this error:

File "I:\A2\SQLite\Create Table.py", line 140, in searchStudent cur.execute("SELECT Student_ID, First_Name, Last_Name FROM School WHERE First_Name IS "+firstName) sqlite3.OperationalError: no such column: Joshua

Here is the code, Joshua was the input for the name.

firstName = input("\nWhat is the first name of the student you would like to search for?")

con = sqlite3.connect("School.sql")
cur = con.cursor()

cur.execute("SELECT Student_ID, First_Name, Last_Name FROM School WHERE First_Name IS "+firstName)

print(cur.fetchall())

The database has 30 entries, holding the Student_ID (primary key), First_Name (text), Last_Name (text), Year (integer), Form_Group(text) and House(text).

The query should return the details of all students with the same first name. This should include their Student_ID, First_Name and Last_Name.

What is causing the error?

Edit: I tried:

WHERE First_Name = "+firstName

But that brought the same error.

WHERE First_Name ="+firstName insted of IS

Mihai
– Mihai

2015-09-29 08:16:24 +00:00
Commented Sep 29, 2015 at 8:16 — Mihai
– Mihai, Commented Sep 29, 2015 at 8:16
I tried, but it still returns the same error :(

user5387789
– user5387789

2015-09-29 08:18:34 +00:00
Commented Sep 29, 2015 at 8:18 — user5387789
– user5387789, Commented Sep 29, 2015 at 8:18

Mureinik · Accepted Answer · 2015-09-29 08:20:28Z

2

String literals in SQL need to be surrounded with single quotes ('), otherwise the database interprets them as object names - and as you've seen, fails when it can't find the column.

cur.execute("SELECT Student_ID, First_Name, Last_Name FROM School WHERE First_Name = '" + firstName + "'")

However, using string concatination like this is a bad practice, and leaves your application open to SQL-injection attacks. You would be advised to use a prepared statement instead:

cur.execute("SELECT Student_ID, First_Name, Last_Name FROM School WHERE First_Name = ?", firstName)

Also, note that comparing values in SQL is done by the = operator, not the is operator.

answered Sep 29, 2015 at 8:20

Mureinik

316k54 gold badges398 silver badges405 bronze badges

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

Collectives™ on Stack Overflow

Python-SQL queries with variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related