SQL is the most common programming language used for managing databases, and is used in many large companies for it's robustness and simplicity. But what if we want to integrate it into a larger and more versatile program? This is where an Object Relational Manager comes in! In this post I will talk about and show some examples of the basics of communicating with an SQL database using sqlite3. Most of this will be done through a standardized ORM like SQAlchemy in a professional setting, but it's good to understand what's going on under the hood.
Before we start talking to our database we'll need to import sqlite3 into our python file.
import sqlite3
After importing it, we set up our connection to our database and the cursor through which we'll interact with it. Not that if the file you are trying to connect to doesn't exist, this line will create it before connecting.
CONN = sqlite3.connect('pets.db')
CURSOR = CONN.cursor()
In order to track our database information in python, we'll create a class that corresponds to the information contained in a database table. For our example we'll create a database of pets for the user to track.
class Pet:
all = {}
def __init__(self, name, species, id=None):
self.id = id
self.name = name
self.species = species
In this example, id, name, and species are columns in our table, and each instance of the class would be a row. We will use the dictionary class attribute all to store individual instances of rows from our database so that we don't have to pull them from the database every time we reference them. We'll leave ID as None on initialization, as it will be assigned after we create our actual database entry for the object.
Now that our framework is starting to come together, let's look at some examples of running SQL code through our class to build and manipulate our database. Before we do anything, we need to create a table. The syntax is near identical to SQL itself, but is passed as a special string to our CURSOR to run on our database.
@classmethod
def create_table(cls):
sql = """
CREATE TABLE IF NOT EXISTS pets (
id INTEGER PRIMARY KEY,
name TEXT,
species TEXT)
"""
CURSOR.execute(sql)
CONN.commit()
Next we'll need to be able to add information into our database. After all, what's a database table without information to organize? We'll do this with a class method 'create' to create an instance, and an instance method 'save' to save itself into the database and the class.
@classmethod
def create(cls, name, species):
pet = cls(name, species)
pet.save()
return pet
def save(self):
sql = """
INSERT INTO pets (name, species)
VALUES (?, ?)
"""
CURSOR.execute(sql, (self.name, self.species))
CONN.commit()
self.id = CURSOR.lastrowid
type(self).all[self.id] = self
Note that we wrote in '?' symbols instead of the actual values we want to use in our SQL code. The CURSOR.execute function can recognize these and will replace them in order with the values that we pass to it. In this case, self.name and self.species. Then we grab the id of the newly inserted row from the database, and use that as the key for our python dictionary of our instances.
Now that we have the basics of creating information for our database we can write a short test script to demonstrate.
from pet import Pet
def seed_database():
Pet.create_table()
fido = Pet.create("Fido", "Dog")
print(fido)
seed_database()
What will this print to the console?
<pet.Pet object at 0x7f8fc4940cd0>
We are creating an object at least, let's update Pet class to override the basic print functionality, we can do this with the special function 'repr'.
def __repr__(self):
return f"<Pet {self.id}: {self.name}, {self.species}>"
This function take the instance of the class and returns a formatted string to easily display our information.
<Pet 1: Fido, Dog>
This shows that it's working, but it's nothing you couldn't do with just Python objects. The obvious advantage of a database is that it's saved to a separate file so your data persists between executions of your program. Let's separate this into a simple script to seed the database, and one to print it out to demonstrate. And while we're at it, we'll add a few more functions to our Pet class.
import sqlite3
CONN = sqlite3.connect('database.db')
CURSOR = CONN.cursor()
class Pet:
# Dictionary of objects saved to the database.
all = {}
def __init__(self, name, species, id=None):
self.id = id
self.name = name
self.species = species
def __repr__(self):
return f"<Pet {self.id}: {self.name}, {self.species}>"
@classmethod
def create_table(cls):
""" Create a new table to persist the attributes of Animal instances """
sql = """
CREATE TABLE IF NOT EXISTS pets (
id INTEGER PRIMARY KEY,
name TEXT,
species TEXT)
"""
CURSOR.execute(sql)
CONN.commit()
@classmethod
def drop_table(cls):
sql = """
DROP TABLE IF EXISTS pets;
"""
CURSOR.execute(sql)
CONN.commit()
@classmethod
def create(cls, name, species):
""" Initialize a new Pet instance and save the object to the database """
pet = cls(name, species)
pet.save()
return pet
def save(self):
sql = """
INSERT INTO pets (name, species)
VALUES (?, ?)
"""
CURSOR.execute(sql, (self.name, self.species))
CONN.commit()
self.id = CURSOR.lastrowid
type(self).all[self.id] = self
def update(self):
sql = """
UPDATE pets
SET name = ?, location = ?
WHERE id = ?
"""
CURSOR.execute(sql, (self.name, self.location, self.id))
CONN.commit()
def delete(self):
sql = """
DELETE FROM pets
WHERE id = ?
"""
CURSOR.execute(sql, (self.id,))
CONN.commit()
# Delete the dictionary entry using id as the key
del type(self).all[self.id]
# Set the id to None
self.id = None
@classmethod
def get_all(cls):
sql = """
SELECT *
FROM pets
"""
rows = CURSOR.execute(sql).fetchall()
return [cls.instance_from_db(row) for row in rows]
Our seed script:
from pet import Pet
def seed_database():
Pet.drop_table()
Pet.create_table()
fido = Pet.create("Fido", "Dog")
lucy = Pet.create("Lucy", "Turtle")
borris = Pet.create("Borris", "Goldfish")
seed_database()
And our final test script that adds one more entry to the database and displays it's contents.
from pet import Pet
def add_and_display():
Pet.create("Bob", "Chicken")
for pet in Pet.get_all():
print(pet)
add_and_display()
Now if we want to reset our database and give it some initial values, we simply run:
$ python lib/seed.py
We can see that it persists by running:
$ python lib/display.py
<Pet 1: Fido, Dog>
<Pet 2: Lucy, Turtle>
<Pet 3: Borris, Goldfish>
<Pet 4: Bob, Chicken>
Each time we run our display script it will add another Bob to the table!
<Pet 1: Fido, Dog>
<Pet 2: Lucy, Turtle>
<Pet 3: Borris, Goldfish>
<Pet 4: Bob, Chicken>
<Pet 5: Bob, Chicken>
This is far from everything that goes into a good ORM, but it's a good framework to understand what's going on under the hood of a standardized Object Relational Manager.
Top comments (0)