The library sqlite3 is the python library which allows for creating sqlite databases. For the first step, let’s check if this database already exists in our folder. If it does we will remove it with os. Next, we connect to the database with sqlite3.connect, which if the database is not there creates it.

import sqlite3
import os

#First make sure if the database is already there that we erase it prior to this lesson
if os.path.exists("Basics.db"):
    os.remove("Basics.db")
#Import sqlite3 which will allow us to create a SQL database and also
conn = sqlite3.connect('Basics.db')
#We create a connection to a sqlite database called Basics.db
#If this is not yet created then it gets created automatically

We can create a cursor as so… this cursor is the object we use to execute all our queries.

#The first step in working with this is creating a cursor
#The cursor will let us execute SQL commands
cur = conn.cursor()

The way we create a table, which is the representation of data within a database, is through “CREATE TABLE” followed by the table name and then in parentheses the variable names and their types. Finally, you must end the query with a semi-colon. Let’s observe an example, we use cur.execute() to execute a query.

#We use c.execute() to execute things in SQL. Let's create a table of students and grades
#Create table makes our table followed by the name of the table and in parantheses the column names and types
#The variables are name and grade, and there tpe is varchar (similar to a string) with 100 characters and an integer
query = """CREATE TABLE students(
name varchar(100),
grade int
);"""
cur.execute(query)

If you notice we named the table students and then we declare two variables name (of the type varchar) and grade (of the type integer). With varchar(100), the number in parantheses is the number of characters. We’ll begin to cover more variables as we go through this course but for now understand these two. An important note on creating tables, you can’t create a table again if it already exists, notice how this causes an error:

#What happens if we try and make the same table twice?
cur.execute(query)

DROP TABLE followed by the table name allows us to drop a specific table from the database. So this would allow us to drop and then create again the database.

#You can delete a table with drop followed by the name
#And then add it back
query = "DROP TABLE students;"
cur.execute(query)
query = """CREATE TABLE students(
name varchar(100),
grade int
);"""
cur.execute(query)