This tool will help you create a SQLight database. This tool was created for Schreiner University's Intro to Databases class but is open and available to the public. To use this tool please clone this repo to your local machine or online editor and follow the instructions below.
Click here to install git for mac
Click here to install git for windows
Click here to install git for Linux
Either install a text editor or IDE onto your local machine or choose a web editor capable of interacting with git
Setting up Python for Visual Studio Code
I'm including documentation from SQLight's official page in case you need a refresher
Either use the native features of your editor to clone this repository to your machine or web editor or navigate to the folder where you would like this script to reside on your machine before entering the following command into your terminal:
git clone https://github.com/JasonBoyett/SU_database_utilityIf you do not know how to navigate in your terminal please refer to the following documentation:
On line 10 of make_db.py you will find a string literal that should look like this:
db_path = r"example.db"change the contents of the string to the desired name of your database with .db at the end
db_path = r"my_database.db"If there is a specific location on your machine where you would like the database to be saved use the Path() method from pathlib to indicate the location where you would like the file to be created.
db_path = Path("Documents/my_database.db")For more info on the Path() method click here
On lines 37 - 40 I give an example of how I would define three different tables
TABLE_ONE_NAME = "games"
TABLE_TWO_NAME = "orders"
TABLE_THREE_NAME = "customers"
table_names = (TABLE_ONE_NAME, TABLE_TWO_NAME, TABLE_THREE_NAME)You could also define the names directly in the table_names tuple
table_names = ("games", "orders", "customers")The first way is considered best practice since you may have to re-use the names of the tables. But in the case of this script either option works the same.
If you want to add another table either create a new constant for it and add it to the table_names tuple
TABLE_ONE_NAME = "games"
TABLE_TWO_NAME = "orders"
TABLE_THREE_NAME = "customers"
ADDED_TABLE_NAME = "added_table"
table_names = (TABLE_ONE_NAME, TABLE_TWO_NAME, TABLE_THREE_NAME, ADDED_TABLE_NAME)Or you could define one more name in the table_names tuple
table_names = ("games", "orders", "customers", "added_table")On lines 48 - 72 I define the schema for the database. In each of the tuples I provide a name for each column of my tables and an associated data type and then add them to another tuple that holds all my table schemas
table_one_schema = (
"gameName text",
"developer text",
"release_year integer"
)
table_two_schema = (
"orderID text",
"customerID text",
"gameName text",
"priceInCents integer"
)
table_three_schema = (
"customerID text",
"firstName text",
"lastName text"
)
table_schemas = (
schema_string_table_one,
schema_string_table_two,
schema_string_table_three
)Then on lines 78 - 87 I use a helper function to generate a SQL command to create the schemas and then I add them to a tuple
schema_string_table_one = build_schema(table_names[0], schemas[0])
schema_string_table_two = build_schema(table_names[1], schemas[1])
schema_string_table_three = build_schema(table_names[2], schemas[2])
table_SQL_strings = (
schema_string_table_one,
schema_string_table_two,
schema_string_table_three
)To add another table you would define another table you would first make another schema and add it to table_schemas
table_one_schema = (
"gameName text",
"developer text",
"releaseYear integer"
)
table_two_schema = (
"orderID text",
"customerID text",
"gameName text",
"priceInCents integer"
)
table_three_schema = (
"customerID text",
"firstName text",
"lastName text"
)
added_table_schema = (
"someData text",
"someValue integer"
)
table_schemas = (
schema_string_table_one,
schema_string_table_two,
schema_string_table_three,
added_table_schema
)Then you would add another helper function
schema_string_table_one = build_schema(table_names[0], schemas[0])
schema_string_table_two = build_schema(table_names[1], schemas[1])
schema_string_table_three = build_schema(table_names[2], schemas[2])
schema_string_added = build_schema(table_names[3], schemas[3])
table_SQL_strings = (
schema_string_table_one,
schema_string_table_two,
schema_string_table_three,
schema_string_added
)On lines 92 - 117 I add data to the tables. All you have to do here is fill the appropriate data for your database into the quotes and if you have more than three tables make a new tuple to represent it.
table_one = (
("Minecraft", "Notch", 2011 ),
("Sekiro Shadows Die Twice", "From Software", 2019),
("Call of Duty 4", "Infinity Ward", 2007),
("Fortnight", "Epic Games", 2017),
("Dark Souls", "From Software", 2011)
)
table_two = (
("Example Order", "1234JD", "Call of Duty 4", "2998"),
("Example Order 2", "4321jd", "Sekiro", "4499"),
("Example Order 3", "1234JD", "Fortnight", "1299"),
("Example Order 4", "135JB", "Dark Souls", "1599")
)
table_three = (
("1234JD", "John", "Doe"),
("4321jd", "Jane", "Doe"),
("135JB", "Jim", "Bob")
)
tables = (
table_one,
table_two,
table_three
)To add an extra table simply make another tuple to represent it and then add it to the tables tuple
table_one = (
("Minecraft", "Notch", 2011 ),
("Sekiro Shadows Die Twice", "From Software", 2019),
("Call of Duty 4", "Infinity Ward", 2007),
("Fortnight", "Epic Games", 2017),
("Dark Souls", "From Software", 2011)
)
table_two = (
("Example Order", "1234JD", "Call of Duty 4", "2998"),
("Example Order 2", "4321jd", "Sekiro", "4499"),
("Example Order 3", "1234JD", "Fortnight", "1299"),
("Example Order 4", "135JB", "Dark Souls", "1599")
)
table_three = (
("1234JD", "John", "Doe"),
("4321jd", "Jane", "Doe"),
("135JB", "Jim", "Bob")
)
added_table = (
("some data here")
)
tables = (
table_one,
table_two,
table_three,
added_table
)Everything after this section is meant to feed the data into your new database and you shouldn't need to touch it.