DATABASES WITH PYTHON
This section covers using Python to create, read, write and edit databases. Using SQLite dbBrowser and the sqlite library for Python.
This section follows on from the database theory section.
This section follows on from the database theory section.
WHY USE PYTHON WITH SQL
Using Python to manipulate or populate your databases means you can automate the task of data management and storage. Using Python you can take user inputs and write them to your database or you can read and scrape other files such as word docs or spreadsheets and take the data to your database. You could even look at web scrapping and populate your database from the results.
Most tasks you can do in database platforms such as SQLite you can automate by using Python.
Most tasks you can do in database platforms such as SQLite you can automate by using Python.
GETTING STARTED
Firstly you will need to download SQLite, it is FREE, a very small program and easy to install. Either go to the link below or follow the instructions on the video to download SQLite.
SQLite will provide you with a platform to browse your data so you can see the changes you have made through your Python code.
SQLite can be downloaded from: https://sqlitebrowser.org/dl/
SQLite will provide you with a platform to browse your data so you can see the changes you have made through your Python code.
SQLite can be downloaded from: https://sqlitebrowser.org/dl/
Why SQLite and how to download?
|
INSTALL SQLITE FOR PYTHON
Installing the SQLite module should be easy.
1: On a PC Open the 'Command prompt' and on a Mac Open the 'Terminal'
2: Type pip3 install sqlite
If you are using the standard Python Idle or a platform such as Sublime text then SQLite should now work on your computer.
1: On a PC Open the 'Command prompt' and on a Mac Open the 'Terminal'
2: Type pip3 install sqlite
If you are using the standard Python Idle or a platform such as Sublime text then SQLite should now work on your computer.
CREATING A NEW DATABASE
The following code shows how to create a new database, how to create a table and how to place a record into the table.
import sqlite3#If not already installed go to terminal or Command prompt and type: pip3 install sqlite3 connection = sqlite3.connect("sports.db")#Create a new database or connect to an existing database cursor = connection.cursor()#The cursor simulates python using your cursor in sqlite #The next block of code creates a table and fields with data types - not needed if the table is already create cursor.execute("""CREATE TABLE game_details ( gameID PRIMARY KEY, gameName text, gameDeveloper text)""") cursor.execute("INSERT INTO game_details VALUES 'G001', 'Tetris','Pajitnov')")#Inserting a single item of new data into the table connection.commit()#Action the changes to the database
Note: You should only run the code that creates the table once. After the table has been created you can comment out the section of creating the table or you can use an if statement, check if the table already exists and only create it if it has not already been created. SEE EXAMPLE BELOW to check if the table already exists using the CREATE TABLE IF NOT EXISTS method..
cursor.execute("""CREATE TABLE IF NOT EXISTS game_details ( gameID PRIMARY KEY, gameName text, gameDeveloper text)""")
ADDING MULTIPLE RECORDS OF DATA
This section looks at adding multiple records to your table. If you already have the data held in a list or tuple before you start you need to check how your data is held, such as;
USING A TUPLE TO ADD MULTIPLE RECORDS
- In a tuple
- In a tuple within a list
- In a single dimensional list
- In a multi-dimensional list
USING A TUPLE TO ADD MULTIPLE RECORDS
If your data is stored in a tuple then you could use the method below to iterate through the tuple and populate the database. This method should work even if the tuple is made larger as it divides the tuple by the number of fields per record. It then uses a variable called 'record' as a marker to when the new record starts.
import sqlite3 sports = sqlite3.connect("eSports.db") cursor = sports.cursor() gameList = ("G008", "Tetris","Pajitnov","G009","Donkey Kong","Rare") tupleLen = ((len(gameList))/3)#3 fields per record so dividing the list by 3 gives the number of loops needed record = 0# Used to represent each record starting with G006 then G007 in this example for i in range (int(tupleLen)): sports.execute("INSERT INTO game_details VALUES ('{}','{}','{}')".format(gameList[record],gameList[record+1],gameList[record+2])) record = record + 3 #Shifts from the first record to the next because each record has 3 elements sports.commit() sports.close()
USING A TUPLE WITHIN A LIST TO ADD MULTIPLE RECORDS
If your data is stored in a tuple within a list then...
USING A LIST TO ADD MULTIPLE RECORDS
You can use the same method as with a tuple. If your data is stored in a list then you could use the method below to iterate through the list and populate the database. This method should work even if the list is made larger as it divides the list by the number of fields per record. It then uses a variable called 'record' as a marker to when the new record starts.
import sqlite3 sports = sqlite3.connect("eSports.db") cursor = sports.cursor() gameList = ["G006", "Tetris","Pajitnov","G007","Donkey Kong","Rare"] listLen = ((len(gameList))/3)#3 fields per record so dividing the list by 3 gives the number of loops needed record = 0# Used to represent each record starting with G006 then G007 in this example for i in range (int(listLen)): sports.execute("INSERT INTO game_details VALUES ('{}','{}','{}')".format(gameList[record],gameList[record+1],gameList[record+2])) record = record + 3 #Shifts from the first record to the next because each record has 3 elements sports.commit() sports.close()
USING A 2 DIMENSIONAL LIST TO ADD MULTIPLE RECORDS
If your data is stored within a multi-dimensional list then you can use a for loop ti iterate through your list and populate your database as can be seen in the example below.
import sqlite3 sports = sqlite3.connect("eSports.db") cursor = sports.cursor() gameList = [["G001", "Tetris","Pajitnov"],["G002","Donkey Kong","Rare"]] for i in range (len(gameList)): sports.execute("INSERT INTO game_details VALUES ('{}','{}','{}')".format(gameList[i][0],gameList[i][1],gameList[i][2])) sports.commit() sports.close()
USER INPUT AND APPENDING A TABLE
This section looks at taking a user input and appending it to your table. Notice it uses the .format method to pass in the variables to the SQL statement.
import sqlite3 connection = sqlite3.connect("sports.db") cursor = connection.cursor() #Allow the user to choose how many items of data to add then add them to the table noOfGames = int(input("How many games would you like to add to the database?")) for i in range (noOfGames):#Loop and ask the user for the detail to add gID = input("Please enter the game ID for Game " + str (i+1) + ":")#Remember this is a PK so need to check if unique gName = input("Please enter the game Name for Game " + str (i+1) + ":")#Remember the input needs to be checked if they are a valid datatype matching the db setup gDeveloper = input("Please enter the game Developer for Game " + str (i+1) + ":") cursor.execute("INSERT INTO game_details VALUES ('{}', '{}','{}')".format(gID,gName,gDeveloper))#Use the .format method to insert data from variables connection.commit()
SELECTING DATA FROM THE DATABASE
This section looks at selecting all of the data from a specified table, fetching it to Python and then converting it from a Tuple to a List. Check out the code below and feel free to copy and paste.
import sqlite3 connection = sqlite3.connect("sports.db") cursor = connection.cursor() #Selecting all of the data from a specified table cursor.execute("SELECT * FROM game_details") myData = (cursor.fetchall())#This line fetches the data that was requested on the line above print (myData)#If you print you will notice this is a tuple #To change the tuple to a 2D list myList = [] for i in range (len(myData)): myList.append(list(myData[i]))#Create a 2 dimensional list from the data fetched from your database print (myList)#You will now see this is a 2D list and you can now easily manipulate the list in Python connection.commit() connection.close()
The method above shows selecting from all fields, if you want to select only certain fields from a table the rather than using the * (all) method simply list the fields you want to select. See below for an example:
SELECT gameID, gameName FROM game_details
SELECT gameID, gameName FROM game_details
CHANGING AN ITEM OF DATA
This section looks at updating a record within your database. This example uses the 'Leaderboard Project' and the code changes the name of the game developer that has the gameId of G001 by using the UPDATE method.
import sqlite3 connection = sqlite3.connect("sports.db") cursor = connection.cursor() cursor.execute("UPDATE game_details SET gameDeveloper = 'Pajitnov' WHERE gameID = 'G001'") connection.commit() connection.close()
DELETE AN ITEM OF DATA
Deleting an item of data is straight forward, you simply need to decide what you want to delete and where you want to delete it from, for example:
cursor.execute("DELETE * FROM game_details WHERE gameID = 'G001'")
cursor.execute("DELETE * FROM game_details WHERE gameID = 'G001'")
import sqlite3 connection = sqlite3.connect("sports.db") cursor = connection.cursor() cursor.execute("DELETE * FROM game_details WHERE gameID = 'G001'") connection.commit() connection.close()