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.
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.
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/
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.
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;
  • In a tuple
  • In a tuple within a list
  • In a single dimensional list
  • In a multi-dimensional list
 In this section we will look at adding the data for each method.

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



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'")



import sqlite3

connection = sqlite3.connect("sports.db")
cursor = connection.cursor()

cursor.execute("DELETE * FROM game_details WHERE gameID = 'G001'")

connection.commit()
connection.close()











​

			

		

	


	





	

		

		

			

				



  

    

      
      
    

  



			

		

	


	








	

		

			



	

		
			

				
					
						


SUGGESTIONS
We would love to hear from you



					
										
					
						








					
										
					
						


SUBSCRIBE 
To enjoy more benefits



					
									

		
	





We hope you find this site useful. If you notice any errors or would like to contribute material then please contact us.