Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
Yoo Song Won

How do I use SQL in my python code?

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!

Recommended Posts

Posted · Original PosterOP

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:

image.png.f20bdeca78f19c46891cc503b668434d.pngimage.png.f12740454325a715e0c01bf087debfe7.png

and here is the whole function for reference:

image.png.31286418a90bbfe6570f77531c8d2f79.pngimage.png.282d0c746b4cb2e3a163bed1cd996271.png

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

Link to post
Share on other sites
Posted (edited) · Best Answer

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 by duncannah

English not good. You should take what I say with a grain of salt

Link to post
Share on other sites
Posted · Original PosterOP
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.

 

image.png.f20bdeca78f19c46891cc503b668434d.png

 

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!

 

Link to post
Share on other sites
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.

 

image.png.f20bdeca78f19c46891cc503b668434d.png

 

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.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×