SQLite SELECT Query

SQLite

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

Read more on select query here.

USING SELECT query

We will collect records by using SELECT query by using execute method.
q="SELECT id,name,class,mark,sex 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,sex 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,sex 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


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.

Sqlite Connection insert Delete Order By


plus2net.com



Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer