Check if row exists in SQLite with Python

Question

I want to know if a row exists already in one of my tables, in this case coll. In order to do this I played around with SQLite in the shell a little and stumbled upon SELECT EXISTS(SELECT 1 FROM coll WHERE ceeb="1234"). In SQLite this works perfectly and it returns either a 0 or a 1-- which is exactly what I wanted. So, with code in hand, I wrote up a quick Python script to see if I could get this to work for me before sticking it into my program. This is what I came up with:

import sqlite3

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

sceeb = int(raw_input(":> "))

ceeb_exists = c.execute('SELECT EXISTS(SELECT 1 FROM coll WHERE ceeb="%d" LIMIT 1)' % sceeb)

print ceeb_exists

Instead of assigning ceeb_existsa 1 or a 0 it gives me an output that looks like <sqlite3.Cursor object at 0x01DF6860>. What am I doing wrong here?

Martijn Pieters · Accepted Answer · 2016-05-04 13:54:08Z

4

The execution of a query always results in 0 or more rows. You'd need to fetch those rows; a SELECT EXISTS query results in 1 row, so you'd need to fetch that row.

Rows always consist of 1 or more columns, here you get one, so you could use tuple assignment (note the , comma after ceeb_exists):

c.execute('SELECT EXISTS(SELECT 1 FROM coll WHERE ceeb="%d" LIMIT 1)' % sceeb)
ceeb_exists, = c.fetchone()

However, using an EXISTS query is a bit redundant here; you could just test if there is any row returned. You should also use query parameters to avoid a SQL injection attack (you are asking a user to give you the ceeb value, so that is easily hijacked):

c.execute('SELECT 1 FROM coll WHERE ceeb=? LIMIT 1', (sceeb,))
ceeb_exists = c.fetchone() is not None

cursor.fetchone() returns None if there is no row available to fetch, the is not None test turns that into True or False.

edited May 4, 2016 at 13:54

answered May 4, 2016 at 13:47

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

2 Comments

Ian Pringle Over a year ago

Works wonders, thanks! And this is just for my own use at work, so I don't need to worry about injection attacks.

Martijn Pieters Over a year ago

@IanPringle: it is always good practice to use query parameters; it also makes it possible for the database driver to create prepared execution plan for that query and re-use it for different parameters, for example.

DeepSpace · Accepted Answer · 2016-05-04 13:44:00Z

1

.executes() returns a cursor object as you can see. In order to print the results of the query you need to iterate over it:

for result in exists:
    print result

answered May 4, 2016 at 13:44

DeepSpace

82.1k12 gold badges119 silver badges166 bronze badges

1 Comment

Ian Pringle Over a year ago

This worked, but it required a little cleaning up of the data with regex or something to make it usable. But I did not know that about cursor objects, thanks!

Collectives™ on Stack Overflow

Check if row exists in SQLite with Python

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related