Python sqlite SELECT to collect records from table

We are using our student table. Check how to create database and student table here.

Read more on select query here.

SELECT query with SQLite database by using cursor, fetchall, fetchone with LIMIT to get records.

Connection using SQLchemy

The path shown below is from google drive, this may change based on your system. Check this tutorial with Video on how to mount SQLite database to google drive.
You can keep SQLite database in local machine and use the path to connect and manage your database.

from sqlalchemy import create_engine
#my_conn=create_engine("sqlite:////content/drive/MyDrive/db/my_db.db")
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")

In windows system, the absolute path is used in above code.

USING SELECT query

We will collect records by using SELECT query by using execute method.

q="SELECT id,name,class,mark,gender from student"
my_cursor=my_conn.execute(q)
for row in my_cursor:
    print(row[0],row[1],row[2],row[3],row[4])

In above code we used my_cursor ( cursor ) as iterator and displayed the records by looping. The output of this code is here.

1 John Deo Four 75 female
2 Max Ruin Three 85 male
3 Arnold Three 55 male
------------
------------
34 Gain Toe Seven 69 male
35 Rows Noump Six 88 female

We can check the data type of the cursor.

print(type(my_cursor)) # <class 'sqlite3.Cursor'>

fetchall()

We can get a list from the cursor by using fetchall()

q="SELECT id,name,class,mark,gender FROM student"
my_cursor=my_conn.execute(q)
data_rows=my_cursor.fetchall()
print(type(data_rows)) # <class 'list'>
for row in data_rows:
    print(row[0],row[1],row[2],row[3],row[4])

Some time we may be expecting a single record to return from the table, here better to use fetchone()

fetchone()

We will get single record matching to the WHERE condition.

q="SELECT id,name,class,mark,gender FROM student WHERE id=8 "
my_cursor=my_conn.execute(q)
data_row=my_cursor.fetchone()
print(type(data_row)) # <class 'tuple'>
print(data_row[0],data_row[1],data_row[2],data_row[3],data_row[4])

Output ( First line shows the data type of data_row and second line the data )

<class 'tuple'>
8 Asruid Five 85 male

Using error handling

We can display error message if there is any error by using try except . We will also select all class Four.

q="SELECT * FROM  student WHERE class='Four' "
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4])
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output

1 John Deo Four 75 female
4 Krish Star Four 60 female
-------------
-------------

Let us change the query to q="SELECT * FROM student WHERE class1='Four' " , Note that there is no column as class1. Here is the output

error:  no such column: class1

Number of records selectd

We can display number of records returned by using len() ( We can use len() function as sqlite3.Cursor is iterable )

print("Number of Records ",len(my_cursor.fetchall()))

Output

Number of Records  9

Total number of records in a table

Used count query without any WHERE condition to get total number of records.

r_set=my_conn.execute("SELECT count(*) as no from STUDENT")
data_row=r_set.fetchone()
nume=data_row[0] # 35

Using Parameters

Video Tutorial on Parameterized query

Always use parameterized query when the data is coming from unknown sources. Use ? as placeholder and a provide a tuple to pass the value to query or execute() method. This is required to prevent injection attack.

We have used placeholders ( ? ) in our query and note that my_data is a tuple used for passing value to execute() method for our query.

my_data=('Seven','Six') # tuple to pass values to execute method
q="SELECT * FROM  student WHERE class=? or class=? "
try:
    my_cursor=my_conn.execute(q,my_data)
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4])    
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output

9 Tes Qry Six 78 male
11 Ronald Six 89 female
12 Recky Six 94 female
-------------
-------------

Using one placeholder

my_data=('Seven',) # tuple to pass values to execute method
q="SELECT * FROM  student WHERE class=?  "
try:
    my_cursor=my_conn.execute(q,my_data)
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4])    
except sqlite3.Error as my_error:
  print("error: ",my_error)

Output

13 Kty Seven 88 female
14 Bigy Seven 88 female
22 Reggid Seven 55 female
24 Tiddy Now Seven 78 male
25 Giff Tow Seven 88 male
26 Crelea Seven 79 male
28 Rojj Base Seven 86 female
29 Tess Played Seven 55 male
32 Binn Rott Seven 90 female
34 Gain Toe Seven 69 male

SELECT command is used along with different other SQL commands while fetching records.