Python SQLite Select And Criteria

Hi,
In SQL databases(SQLite or others), we may want to query the records in the database with specific criteria.

What kind of criteria are they? there are many complex types of queries, of course, but in general

• We may want to get all the records
• We may want to query according to the value of a certain field (where)
• We may want to see a specific string expression between records.(like)
• By making a certain grouping (group by)
• In descending or ascending order (order by)

Let’s exemplify them one by one..
First of all, I created records for these in our database as follows

Necessary methods and objects for preparation

import sqlite3

conn = None
cursor = None


def init_connections():
    global conn
    global cursor
    conn = sqlite3.connect("contact_list.db")
    cursor = conn.cursor()

def close_connecton():
    cursor.close()
    conn.close()

Example 1-) Get all records

def select_my_contacts_get_all():
    query = "SELECT * FROM contact_table"
    cursor.execute(query)
    collect_data = cursor.fetchall()

    for next_data in collect_data:
        print('{}\t{} - {}'.format(next_data[0], next_data[1],
                                   (" Number: " + next_data[2] +
                                    " City: " + next_data[3] +
                                    " Alias: " + next_data[4])))




init_connections()
select_my_contacts_get_all()
close_connecton()

Results:

Example 2-) Fetch record with id 20

def select_my_contacts_fetch_record_with_id(id):
    cursor.execute("SELECT * FROM contact_table WHERE id = ?", (id,))
    collect_data = cursor.fetchone()
    print('{}\t{} - {}'.format(collect_data[0], collect_data[1],
                                   (" Number: " + collect_data[2] +
                                    " City: " + collect_data[3] +
                                    " Alias: " + collect_data[4])))


init_connections()
select_my_contacts_fetch_record_with_id(20)
close_connecton()

Results:

Example 3-) Fetch record with like 

I want to show those whose name starts with the letter T in the name column. I will do this with like.

def select_my_contacts_fetch_record_with_like():
    cursor.execute("SELECT * FROM contact_table where name LIKE 'T%'")
    collect_data = cursor.fetchall()
    for next_data in collect_data:
        print('{}\t{} - {}'.format(next_data[0], next_data[1],
                                   (" Number: " + next_data[2] +
                                    " City: " + next_data[3] +
                                    " Alias: " + next_data[4])))


init_connections()
select_my_contacts_fetch_record_with_like()
close_connecton()

Example 4-) Fetch record with Group By

I want to group by cities and i want to see how many they are.

def select_my_contacts_fetch_record_with_group_by():
    cursor.execute("SELECT COUNT(*) as total,city FROM contact_table GROUP BY city")
    collect_data = cursor.fetchall()
    print('{}   {}'.format("Total", "City"))
    for next_data in collect_data:
        print('{}       {}'.format(next_data[0], next_data[1]))


init_connections()
select_my_contacts_fetch_record_with_group_by()
close_connecton()

Example 5-) Fetch record with Order By

I want to see records in descending or ascending order

def select_my_contacts_fetch_record_with_order_by(type):
    query = "SELECT * FROM contact_table ORDER BY number "+type
    cursor.execute(query)

    collect_data = cursor.fetchall()
    for next_data in collect_data:
        print('{}\t{} - {}'.format(next_data[0], next_data[1],
                                   (" Number: " + next_data[2] +
                                    " City: " + next_data[3] +
                                    " Alias: " + next_data[4])))


init_connections()
select_my_contacts_fetch_record_with_order_by("asc")
print("\n")
select_my_contacts_fetch_record_with_order_by("desc")
close_connecton()