Sqlite Check If a Row Exists

Check if row exists in SQLite with Python

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.

How to check if a row exist in the SQLite table with a condition

So, you can use something like select count(*) ... or select (count(*) > 0) as found ... as the base query.

When you call executeQuery, you will get a ResultSet in return, from this, you need to determine it's contents.

In your case, you are only expecting a single row result, so you can simply use ResultSet#next to move to the first row and then extract the column value from it...

public void ftpTableCheck(String host, String port, String username, String password) {
    try {
        String query = "SELECT (count(*) > 0) as found FROM ftp WHERE Host LIKE ? AND Username LIKE ?";
        PreparedStatement pst = conn.prepareStatement(query);
        pst.setString(1, host);
        pst.setString(2, username);

        try (ResultSet rs = pst.executeQuery()) {
            // Only expecting a single result
            if (rs.next()) {
                boolean found = rs.getBoolean(1); // "found" column
                if (found) {
                    // You have rows
                } else {
                    // You have no rows
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Take a closer look at JDBC Database Access and Using Prepared Statements for more details

Check if a row exists in sqlite3?

Don't call fetchone(); just treat the cursor as an iterator:

for row in c.execute("SELECT name, age FROM ..."):
    name, age = row
    break
else:
    print("not found")

Android - SQlite check if value in row exists

Update you Insert Query

    String name = edtName.getText().toString().trim();
        String query = "Select * From STUDENTS where name = '"+name+"'";
        if(sqLiteHelper.getData(query).getCount()>0){
            Toast.makeText(getApplicationContext(), "Already Exist!", Toast.LENGTH_SHORT).show();
        }else{
            sqLiteHelper.insertData(
                    name,
                    edtPrice.getText().toString().trim(),
                    imageViewToByte(imageView)

            );
Toast.makeText(getApplicationContext(), "Added successfully!", Toast.LENGTH_SHORT).show();

        }

Android Sqlite: Check if row exists in table

Just do like

 Cursor cursor = null;
 String sql ="SELECT PID FROM "+TableName+" WHERE PID="+pidValue; 
 cursor= db.rawQuery(sql,null);
 Log("Cursor Count : " + cursor.getCount());

 if(cursor.getCount()>0){
  //PID Found
 }else{
 //PID Not Found 
 }
 cursor.close();

How to check if record is present in sqlite in C

You can exec your query differently

    sql = "Select * from COMPANY where id = 2";
    struct sqlite3_stmt *selectstmt;
    int result = sqlite3_prepare_v2(db, sql, -1, &selectstmt, NULL);
    if(result == SQLITE_OK)
    {
       if (sqlite3_step(selectstmt) == SQLITE_ROW)
       {
          // record found
       }
       else
       {
          // no record found
       }
    }
    sqlite3_finalize(selectstmt);

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

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)

---

Related Topics