In this post I will demonstrate how you can create SQL databases powered by SQLite as a database engine in Python. I will be using the sqlite3 engine. The SQLite driver is installed by default through the Python installation itself.
Below code snippet is taken directly from https://www.sqlitetutorial.net/sqlite-python/create-tables/. It implements functions for creating a database and obtaining a connection to it. The second function can be used for creating a table in the database.
# import sqlite module import sqlite3 # this function creates a database connection to a database file # params: database file name # returns: connection to database def create_connection(db_file): conn = None try: conn = sqlite3.connect(db_file) return conn except Error as e: print(e) return conn # this function creates a table in a database # params: reference handler to database connection, sql statement for table creation # returns: None def create_table(conn, create_table_sql): try: c = conn.cursor() c.execute(create_table_sql) except Error as e: print(e)
I will use above functions to create a database, access its connection and create a table in it. I start by creating the database and accessing its connection:
# creating a database and accessing a connection to it dbConnection = create_connection("newDatabase.db")
I will now access the connection to the database and create a new table in it:
# create table in database materialTable = """ CREATE TABLE IF NOT EXISTS material ( id integer PRIMARY KEY, description text NOT NULL, category text NOT NULL, price float); """ if dbConnection is not None: # create a table create_table(dbConnection, materialTable)
Now I can start writing entries into the database. For this I will define another function (taken from the source specifed at the beginning of this post):
# this function adds an entry to the material table in the database # params: database connection, sql entry # returns: None def create_entryMaterialTable(conn, entry): sql = ''' INSERT INTO material(description,category,price) VALUES(?,?,?) ''' cur = conn.cursor() cur.execute(sql, entry) conn.commit()
I now use above function to add a single material to the material table in the database:
materialEntry = ("rear lamp","lighting products",649.99) create_entryMaterialTable(dbConnection,materialEntry)
In upcoming posts I will demonstrate how you can update entries in the sql database via Python. Also, I will introduce all important sql commands for conducting a successful data query.
Data scientist focusing on simulation, optimization and modeling in R, SQL, VBA and Python