Query Records

Date: March 11th 2016
Last updated: March 11th 2016

import sqlite3

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# add extra data
data = [(24.0,), (35.0,), (34.5,), (43.9,), (13.3,), (31.0,), ]

# execute (add data and select all records)
cur.executemany('INSERT INTO scoreboard (score) VALUES (?)', data)
cur.execute("SELECT * FROM scoreboard")

rows = cur.fetchall()
for row in rows:
    print(row)

# commit
con.commit()

# close
con.close()

output

python3 main.py
# (1, 10.0, 'player')
# (2, 12.1, 'player')
# (3, 32.5, 'player')
# (4, 24.0, 'player')
# (5, 35.0, 'player')
# (6, 34.5, 'player')
# (7, 43.9, 'player')
# (8, 13.3, 'player')
# (9, 31.0, 'player')

Select scores by value

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# execute
cur.execute("SELECT * FROM scoreboard WHERE score >20.0")
rows = cur.fetchall()
for row in rows:
    print(row)

# commit
con.commit()

# close
con.close()

output

python3 main.py
# (3, 32.5, 'player')
# (4, 24.0, 'player')
# (5, 35.0, 'player')
# (6, 34.5, 'player')
# (7, 43.9, 'player')
# (9, 31.0, 'player')

Select scores between two values

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# execute
cur.execute("SELECT * FROM scoreboard WHERE score BETWEEN 10.0 AND 20.0")
rows = cur.fetchall()
for row in rows:
    print(row)

# commit
con.commit()

# close
con.close()

output

python3 main.py
# (1, 10.0, 'player')
# (2, 12.1, 'player')
# (8, 13.3, 'player')

Select top 5 scores

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# execute
cur.execute("SELECT * FROM scoreboard ORDER by score DESC LIMIT 5")

rows = cur.fetchall()
for row in rows:
    print(row)

# commit
con.commit()

# close
con.close()

output

python3 main.py
# (7, 43.9, 'player')
# (5, 35.0, 'player')
# (6, 34.5, 'player')
# (3, 32.5, 'player')
# (9, 31.0, 'player')