How do I use SQL in my python code?

[Yoo Song Won]

I'm trying to add an SQL statement for a high score instead of a regular text file for my school project using Python and Pygame. This is as my teacher has told me that I will get an overall higher score using an SQL statement rather than a text file. 

 

However, after hours of looking at tutorials and notes on SQL, I still don't know how to use it in my code and what parts of my code to replace or add onto. Should I make a whole new function or should I just I just replace certain parts of existing code I've created. I've already imported sqlite3 but that's pretty much it.

 

Can someone please show me what I can possibly do to implement a high score system with SQL? I understand that using SQL is overkill for a single integer value, but like I said, it will get me a higher grade if I use it instead of a read and write process with a text file.

 

Here are what I think are the relevant pieces of code that need to be changed:

and here is the whole function for reference:

I have also attached the actual code for the game I'm making so that you may edit it as you wish.

 

Thank you so very much in advance if you show me. I will deeply appreciate it!!!

 

HopBall.py

settings.py

sprites.py

highscore.txt

[duncannah]

You can use sqlite.

 

https://docs.python.org/2/library/sqlite3.html

 

You create a db file, create a table, then add and modify when needed

 

Here's an example that I made sure works

import sqlite3

conn = sqlite3.connect('database.db')
c = conn.cursor()

c.execute('''CREATE TABLE IF NOT EXISTS scores (name text, highscore int)''')
conn.commit()

## Get the highscore
name = "duncan"

# check if row exists
c.execute('SELECT 1 FROM scores WHERE name=?', [name])
if c.fetchone() is None:
  c.execute('INSERT INTO scores VALUES (?, 0)', [name])
  conn.commit()
  highscore = 0
else:
  c.execute('SELECT highscore FROM scores WHERE name=?', [name])
  highscore = c.fetchone()

## Set the highscore
highscore = 1

# check if row exists
c.execute('SELECT 1 FROM scores WHERE name=?', [name])
if c.fetchone() is None:
  c.execute('INSERT INTO scores VALUES (?, ?)', [name, highscore])
else:
  c.execute('UPDATE scores SET highscore=? WHERE name=?', [highscore, name])

conn.commit()

## Close the connection
conn.close()

 

EDIT: Just ran the code, fixed it and it works!

Edited February 26, 2019 by duncannah

[Yoo Song Won]

On 2/26/2019 at 10:06 PM, duncannah said:

You can use sqlite.

 

https://docs.python.org/2/library/sqlite3.html

 

You create a db file, create a table, then add and modify when needed

 

Here's an example that I made sure works

import sqlite3

conn = sqlite3.connect('database.db')
c = conn.cursor()

c.execute('''CREATE TABLE IF NOT EXISTS scores (name text, highscore int)''')
conn.commit()

## Get the highscore
name = "duncan"

# check if row exists
c.execute('SELECT 1 FROM scores WHERE name=?', [name])
if c.fetchone() is None:
  c.execute('INSERT INTO scores VALUES (?, 0)', [name])
  conn.commit()
  highscore = 0
else:
  c.execute('SELECT highscore FROM scores WHERE name=?', [name])
  highscore = c.fetchone()

## Set the highscore
highscore = 1

# check if row exists
c.execute('SELECT 1 FROM scores WHERE name=?', [name])
if c.fetchone() is None:
  c.execute('INSERT INTO scores VALUES (?, ?)', [name, highscore])
else:
  c.execute('UPDATE scores SET highscore=? WHERE name=?', [highscore, name])

conn.commit()

## Close the connection
conn.close()

 

EDIT: Just ran the code, fixed it and it works!

Thank you so very much!! This is incredibly helpful!!

 

However, I would like to know if there's a way to read from the database file just for the high score value as well.

 

This is so that I'm able to show that I can also write into, as well as read the file as well so that I may display it on the user interface.

 

 

This is what I have for the text file read and I'd like to replace this code with and SQL read statement.

 

Again thank you so much with what you've shown! I'm definitely going to get higher marks cuz of your help!

 

[lacion]

4 minutes ago, Yoo Song Won said:

Thank you so very much!! This is incredibly helpful!!

 

However, I would like to know if there's a way to read from the database file just for the high score value as well.

 

This is so that I'm able to show that I can also write into, as well as read the file as well so that I may display it on the user interface.

 

 

This is what I have for the text file read and I'd like to replace this code with and SQL read statement.

 

Again thank you so much with what you've shown! I'm definitely going to get higher marks cuz of your help!

 

as your working on a game, I would recommend not to use SQL at all, use a fast in-memory database or an embedded nosql one.

give ZODB, pickledb or tinydb a try.

 

you can save you in-memory database to disc when your gameplay ends, or when the game shuts down.