How to check the existence of a row in SQLite with Python?

by

Since the names are unique, I really favor your (the OP’s) method of using fetchone or Alex Martelli’s method of using SELECT count(*) over my initial suggestion of using fetchall.

fetchall wraps the results (typically multiple rows of data) in a list. Since the names are unique, fetchall returns either a list with just one tuple in the list (e.g. [(rowid,),] or an empty list []. If you desire to know the rowid, then using fetchall requires you to burrow through the list and tuple to get to the rowid.

Using fetchone is better in this case since you get just one row, (rowid,) or None.
To get at the rowid (provided there is one) you just have to pick off the first element of the tuple.

If you don’t care about the particular rowid and you just want to know there is a hit,
then you could use Alex Martelli’s suggestion, SELECT count(*), which would return either (1,) or (0,).

Here is some example code:

First some boiler-plate code to setup a toy sqlite table:

import sqlite3
connection = sqlite3.connect(':memory:')
cursor=connection.cursor()
cursor.execute('create table components (rowid int,name varchar(50))')    
cursor.execute('insert into components values(?,?)', (1,'foo',))

Using fetchall:

for name in ('bar','foo'): 
    cursor.execute("SELECT rowid FROM components WHERE name = ?", (name,))
    data=cursor.fetchall()
    if len(data)==0:
        print('There is no component named %s'%name)
    else:
        print('Component %s found with rowids %s'%(name,','.join(map(str, next(zip(*data))))))

yields:

There is no component named bar
Component foo found with rowids 1

Using fetchone: 

for name in ('bar','foo'): 
    cursor.execute("SELECT rowid FROM components WHERE name = ?", (name,))
    data=cursor.fetchone()
    if data is None:
        print('There is no component named %s'%name)
    else:
        print('Component %s found with rowid %s'%(name,data[0]))

yields:

There is no component named bar
Component foo found with rowid 1

Using SELECT count(*):

for name in ('bar','foo'): 
    cursor.execute("SELECT count(*) FROM components WHERE name = ?", (name,))
    data=cursor.fetchone()[0]
    if data==0:
        print('There is no component named %s'%name)
    else:
        print('Component %s found in %s row(s)'%(name,data))

yields: 

There is no component named bar
Component foo found in 1 row(s)

More Related Contents:

  1. How can I get dict from sqlite query?
  2. Using a WHERE ___ IN ___ statement
  3. How to get a single result from a SQL query in python?
  4. Is this Python code vulnerable to SQL injection? (SQLite3)
  5. pysqlite: Placeholder substitution for column or table names?
  6. What is an efficient way of inserting thousands of records into an SQLite table using Django?
  7. Python SQLite3 SQL Injection Vulnerable Code
  8. Python and SQLite: insert into table
  9. SQLite parameter substitution problem
  10. How to execute raw SQL in Flask-SQLAlchemy app
  11. How to put parameterized sql query into variable and then execute in Python?
  12. How to retrieve inserted id after inserting row in SQLite using Python?
  13. python 3.2 UnicodeEncodeError: ‘charmap’ codec can’t encode character ‘\u2013’ in position 9629: character maps to
  14. pip install mysqlclient returns “fatal error C1083: Cannot open file: ‘mysql.h’: No such file or directory
  15. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  16. Filtering a Pyspark DataFrame with SQL-like IN clause
  17. Python CSV to SQLite
  18. Get data from pandas into a SQL server with PYODBC
  19. What if I don’t close the database connection in Python SQLite
  20. Insert list into my database using Python [duplicate]
  21. HDF5 – concurrency, compression & I/O performance [closed]
  22. Python SQL query string formatting
  23. SQLite not saving data between uses
  24. SQLAlchemy: how to filter date field?
  25. How to execute “left outer join” in SqlAlchemy
  26. What is the best way to access stored procedures in Django’s ORM
  27. Problem with regexp python and sqlite
  28. Why do you need to create a cursor when querying a sqlite database?
  29. How to open and convert sqlite database to pandas dataframe
  30. Passing SQLite variables in Python

Leave a Comment