Load data from the database#
In this article, you will learn how to create a data model and synchronize it with a database. You can use this for various purposes, e.g., questions and answers in a quiz game, a high score, loading game data, …
Erster Schritt: Erstellen des Modells:#
Erstelle in einer neuen Datei ein Modell für deine Daten, so wie du sie in Python speichern würdest.
A high score, for example, you could store as a simple list in which you store names and scores, like this:
[(Andreas, 100), (Martin, 200), (Julius,50)]
The order of the data does not matter yet, because you can sort it at any time
So könnte deine Klasse zunächst aussehen:
Datei: highscore_model.py:
import sqlite3
class Highscore:
def __init__(self, scores = []):
self.scores : list = scores
The class contains an attribute scores, which is either read from the parameter of the init method or contains an empty list.
Setting up the database#
With the program DBBrowser for sqlite, you can set up the database.
Richte eine Tabelle mit 3 Feldern ein: Name, Punktzahl und ID.
Bemerkung
Why do I need an ID?
If you want to expand your program later, the individual records in the table can only be identified if you add a unique attribute. Therefore, it makes sense to add an ID.
Save sample data in the database so you can test reading it later:
Read from the database#
Next, you can implement a method to read the data.
Addiere die folgende Klasse zu deiner Datenbank:
@classmethod
def from_db(cls):
connection = sqlite3.connect("highscore_db.db")
cursor = connection.cursor()
sql = f'SELECT Name, Score FROM Highscore'
cursor.execute(sql)
rows = cursor.fetchall()
connection.close()
return cls(rows)
The method is a classmethod - These methods are used to create objects of a class (like a kind of Factory), in this case, a new highscore object is created with data from the database.
First, a connection is established. Here, the filename of your sqlite3 database must be specified.
Then a cursor is created that can read SQL queries from the database and execute them.
Mit cursor.fetchone bzw. cursor.fetchall() können diese Daten dann zurückgegeben werden.
Bemerkung
Task: Sort the high score results in descending order.
You can test your program by creating a highscore object at the end of the file. But don’t forget to delete it when you import the file later!
hs = Highscore.from_db()
print(hs.scores)
In die Datenbank schreiben#
Add the following function to write to the database:
def create(self, name, score):
connection = sqlite3.connect("highscore_db.db")
cursor = connection.cursor()
sql = f"INSERT INTO Highscore (Name, Score) VALUES ('{name}', '{score}')"
cursor.execute(sql)
connection.commit()
connection.close()
self.scores.append((name,score))
Here, a connection is first established again and a cursor is created, which this time sends an INSERT request to the database to add a new record.
To keep the local data and the data in the database synchronized, the record is also deleted in the table.
CRUD#
CRUD steht für:
Create, einen Datensatz anlegen,
Read, Datensatz lesen,
Update, Datensatz aktualisieren
Delete oder Destroy, Datensatz löschen.
These are the operations you need to consider when you want to connect to the database.
Erstellen#
The create function was implemented with the create function.
Lesen#
With the from_db function, the entire Highscore table in our database was loaded into the self.scores list.
Aktualisieren#
You can update a record (e.g., if a player repeats the game.)
Addiere dazu folgende Methode:
def update(self, score_id, name, new_score):
connection = sqlite3.connect("highscore_db.db")
cursor = connection.cursor()
sql = f"UPDATE Highscore SET Name = '{name}', Score = '{new_score}' WHERE ID='{score_id}'"
cursor.execute(sql)
connection.commit()
connection.close()
for score in self.scores:
if score[0] == score_id:
self.scores.remove(score)
self.scores.append((score_id, name, new_score))
Löschen#
Addiere die folgende Methode:
def delete(self, score_id):
connection = sqlite3.connect("highscore_db.db")
cursor = connection.cursor()
sql = f"DELETE FROM Highscore WHERE ID='{score_id}'"
cursor.execute(sql)
connection.commit()
connection.close()
for score in self.scores:
if score[0] == score_id:
self.scores.remove(score)
Vollständiger Code:
import sqlite3
class Highscore:
def __init__(self, scores = []):
self.scores : list = scores
@classmethod
def from_db(cls):
connection = sqlite3.connect("highscore_db.db")
cursor = connection.cursor()
sql = f'SELECT ID, Name, Score FROM Highscore'
cursor.execute(sql)
rows = cursor.fetchall()
connection.close()
return cls(rows)
def create(self, name, score):
connection = sqlite3.connect("highscore_db.db")
cursor = connection.cursor()
sql = f"INSERT INTO Highscore (Name, Score) VALUES ('{name}', '{score}')"
cursor.execute(sql)
connection.commit()
connection.close()
self.scores.append((name,score))
def update(self, score_id, name, new_score):
connection = sqlite3.connect("highscore_db.db")
cursor = connection.cursor()
sql = f"UPDATE Highscore SET Name = '{name}', Score = '{new_score}' WHERE ID='{score_id}'"
cursor.execute(sql)
connection.commit()
connection.close()
for score in self.scores:
if score[0] == score_id:
self.scores.remove(score)
self.scores.append((score_id, name, new_score))
def delete(self, score_id):
connection = sqlite3.connect("highscore_db.db")
cursor = connection.cursor()
sql = f"DELETE FROM Highscore WHERE ID='{score_id}'"
cursor.execute(sql)
connection.commit()
connection.close()
for score in self.scores:
if score[0] == score_id:
self.scores.remove(score)
hs = Highscore.from_db()
#hs.create("Max Meier", 200)
hs.update(2, 'Max Meier2', 200)
hs.delete(2)
print(hs.scores)
Integrate your model into your application.#
When you have written your model, you can integrate it into your base program.
Here, for example, a program was written where you have to dodge balls. As soon as you get hit. The game initially looks like this:
from miniworlds import *
import random
import highscore_model
world = World(200,600)
enemies = []
player = Circle(100,500)
player.radius = 20
my_score = 0
score_actor = Number(10,10)
@player.register
def on_key_pressed_a(self):
self.move_left()
@player.register
def on_key_pressed_d(self):
self.move_right()
@player.register
def on_detecting_right_border(self):
self.move_back()
@player.register
def on_detecting_left_border(self):
self.move_back()
def create_enemy():
enemy = Circle(random.randint(20,180), 50)
enemy.radius = random.randint(10,30)
enemies.append(enemy)
@world.register
def act(self):
global my_score
if self.frame % 100 == 0:
create_enemy()
for enemy in enemies:
enemy.move_down()
if "bottom" in enemy.detect_borders():
enemies.remove(enemy)
enemy.remove()
my_score += 1
score_actor.set_number(my_score)
world.run()
This game now needs two functionalities:
After the game ends, the game will be deleted.
The high score is displayed.
Change the act method first:
@world.register
def act(self):
global my_score
if self.frame % 100 == 0:
create_enemy()
for enemy in enemies:
enemy.move_down()
if "bottom" in enemy.detect_borders():
enemies.remove(enemy)
enemy.remove()
my_score += 1
score_actor.set_number(my_score)
if enemy in player.detect_actors():
world.reset()
name = world.ask.text(f"You reached {my_score} points! Enter your name")
new_highscore(name, my_score)
The last if statement includes the functionality. The world is deleted, a new high score is requested, which is then displayed with the new_highscore function.
This looks like this:
def new_highscore(name, points):
highscore = highscore_model.Highscore.from_db()
highscore.create(name, points)
scores = highscore.from_db().scores
scores.sort()
for index, ranking in enumerate(scores[0:10]):
t = Text((20, index * 40))
t.text = f"{ranking[0]} - Points: {ranking[1]}"
t.font_size = 10
First, the current high score is loaded, then a new entry is created, and then the first 10 elements of the high score are displayed on the screen using new actors.
Den kompletten Code findest du hier