What is ORM?
We are designing a class that implements SQL queries. Here ORM provides a bridge between SQL(relational database) and Python objects.
Classes — Models(table)
Properties — columns
Instance(object) —a row
### Attributes
- The first test is concerned solely with making sure that our dogs have all the required attributes and that they are readable and writable.
- The `__init__` method takes `name` and `breed` as arguments and saves them as instance attributes. `__init__` should also create an `id` instance attribute.
- ==> this also means, bc SQL automatically create id number, we don’t actually have to create id.
import sqlite3 #database
CONN = sqlite3.connect('lib/dogs.db') #connection
CURSOR = CONN.cursor() #pointer for the connection, row by row
class Dog:
#constructor / init method
def __init__(self, name, breed, id=None): # arguments => instance attr
self.id = id #id we don't know so our default will be None
self.name = name
self.breed = breed
check
$pytest -x
$python3 lib/debug.py
dog = Dog(name="joey", breed="cocker spaniel")
dog.name
dog.breed### `create_table()`
Create a `create_table()` class method that will create the `dogs` table if it does not already exist. The table should have columns for an id, a name, and a breed.
what is a class method? it affects a table. it’s going to affect the entire table, not just one row, one object, one instance.
Create_table method will take an SQL query and it will execute that query.
It execute the CURSOR.
Cursor is our connection to the database.
google
"create table if not exists sql"
#dog.py
import sqlite3 #database
CONN = sqlite3.connect('lib/dogs.db') #connection
CURSOR = CONN.cursor() #pointer for the connection, row by row
class Dog:
.
..
@classmethod #class method affects the whole table, not just one row
def create_table(cls): #cls take in this class as a parameter
#google SQL queries
query = """CREATE TABLE IF NOT EXISTS dogs (
id INTEGER PRIMARY KEY,
name TEXT,
breed TEXT
)"""
CURSOR.execute(query) #CURSOR takes the `query` and executes it
CONN.commit() #commit it check
$pytest
We successfully create a table!
table has been create!!
### `drop_table()`
- This class method should drop the `dogs` table if it does exist- pretty much the opposite of `create_table()`.
google
`drop table if exist`
@classmethod #class method for the whole class, not only one instance
def drop_table(cls): #takes in class as an argument
#query
query = """
DROP TABLE IF EXISTS dogs
"""
CURSOR.execute(query) #CURSOR takes the `query` and executes it
CONN.commit()check
$pytest -x### `save()`
Create an instance method `save()` that saves a `Dog` object to your database.
#instance method/ not class method / takes in self / not cls
def save(self):
#google SQL insert into
# notice that I'm not putting ID here bc it's autogenerated
query = """
INSERT INTO dogs (name, breed)
VALUES (?, ?)
"""
#pass in name and breed as arguments / not ID
CURSOR.execute(query, (self.name, self.breed))
#commit
CONN.commit()
self.id = CURSOR.lastrowidcheck
$pytest -x
$python3 lib/debug.py
Dog.drop_table()
Dog.create_table()
hammurabi = Dog("hammurabi", "golden doodle")
hammurabi.save()
sql = """
SELECT * FROM dogs
WHERE name='hammurabi'
LIMIT 1
"""
CURSOR.execute(sql)
CURSOR.execute(sql).fetchone()
### `create()`
This is a class method that should:
- Create a new row in the database.
- Return a new instance of the `Dog` class.
Think about how you can re-use the `save()` method to help with this one.
@classmethod
def create(cls, name, breed):
dog = cls(name, breed) #create an instance
dog.save() # take save method that we built already to insert into our table
return dog #return the object$pytest
it's not going to pass bc id NONE but we will deal with this later### `new_from_db()`
This is an interesting method. Ultimately, the database is going to return an array representing a dog’s data. We need a way to cast that data into the appropriate attributes of a dog. This method encapsulates that functionality.
You can even think of it as `new_from_array()` (though don’t name it that- your tests will fail!) Methods like this, that return instances of the class, are known as constructors, just like calling the class itself, except that they extend that functionality without overwriting `__init__`.
@classmethod #class method
#grabbing a row
def new_from_db(cls, row): #we got this dog class, and one row
dog = cls(
name=row[1], #row at index 1 is name
breed=row[2] #row at index 2 is breed
id=row[0] #row at index 0 is id
)
print(dog.name, dog.breed)
return dogcheck
ipdb> Dog.drop_table()
ipdb> Dog.create_table()
ipdb> sql = """
INSERT INTO dogs (name, breed)
VALUES ('joey', 'cocker spaniel')
"""
ipdb> CURSOR.execute(sql)
<sqlite3.Cursor object at 0x102702880>
ipdb> sql = """
SELECT * FROM dogs
WHERE name='joey'
LIMIT 1
"""
ipdb> row = CURSOR.execute(sql).fetchone()
ipdb> joey = Dog.new_from_db(row)
ipdb> joey.name
'joey'
ipdb> joey.breed
'cocker spaniel'### `get_all()`
- This class method should return a list of `Dog` instances for every record in the `dogs` table.
ipdb>
#1. CURSOR.execute(sql)
# -- this runs the query but we want to see the row
ipdb>
#2. CURSOR.execute(sql).fetchall()
ipdb>
#3. print([one_row for one_row in CURSOR.execute(sql).fetchall()])
ipdb>
#4. print([Dog.new_from_db(one_row) for one_row
in CURSOR.execute(sql).fetchall()])
ipdb>
#5. return [Dog.new_from_db(row) for row in CURSOR.execute(sql).fetchall()]==
=
@classmethod
def get_all(cls):
sql = """
SELECT * FROM dogs
"""
return [cls.new_from_db(row) for row in CURSOR.execute(sql).fetchall()]
dog1 = Dog("Fiona", "Chihuahua1")
dog2 = Dog("Baboi", "Chihuahua2")
dog3 = Dog("Ginger", "Chihuahua3")
Dog.create_table()
dog1.save()
dog2.save()
dog3.save()
Dog.get_all()
=
find_by_name(cls, name):
@classmethod
def find_by_name(cls, name):
sql = """
SELECT * FROM dogs
WHERE name = ?
LIMIT 1
"""
row = CURSOR.execute(sql, (name,)).fetchone()
if not row:
return None
return Dog(
name=row[1],
breed=row[2],
id=row[0]
)
=
check
$debug.py
ipdb> Dog.find_by_name("hammurabi")=
find_by_id(cls, id):
@classmethod
def find_by_id(cls, id):
sql = """
SELECT * FROM dogs
WHERE id = ?
LIMIT 1
"""
row = CURSOR.execute(sql, (id,)).fetchone()
if not row:
return None
return Dog(
name=row[1],
breed=row[2],
id=row[0]
)=
=
=
=
=
@classmethod def find_or_create_by(cls, name=None, breed=None):
@classmethod
def find_or_create_by(cls, name=None, breed=None):
sql = """
SELECT * FROM dogs
WHERE (name, breed) = (?, ?)
LIMIT 1
"""
row = CURSOR.execute(sql, (name, breed)).fetchone()
if not row:
sql = """
INSERT INTO dogs (name, breed)
VALUES (?, ?)
"""
CURSOR.execute(sql, (name, breed))
return Dog(
name=name,
breed=breed,
id=CURSOR.lastrowid
)
return Dog(
name=row[1],
breed=row[2],
id=row[0]
)=
def update(self):
def update(self):
sql = """
UPDATE dogs
SET name = ?,
breed = ?
WHERE id = ?
"""
CURSOR.execute(sql, (self.name, self.breed, self.id))
CONN.commit()=
import sqlite3
CONN = sqlite3.connect('lib/dogs.db')
CURSOR = CONN.cursor()
class Dog:
def __init__(self, name, breed, id=None):
self.id = id
self.name = name
self.breed = breed
@classmethod
def create_table(cls):
sql = """
CREATE TABLE IF NOT EXISTS dogs
(id INTEGER PRIMARY KEY,
name TEXT,
breed TEXT)
"""
CURSOR.execute(sql)
@classmethod
def drop_table(cls):
sql = """
DROP TABLE IF EXISTS dogs
"""
CURSOR.execute(sql)
def save(self):
sql = """
INSERT INTO dogs (name, breed)
VALUES (?, ?)
"""
CURSOR.execute(sql, (self.name, self.breed))
# solution for bonus methods
self.id = CURSOR.lastrowid
@classmethod
def create(cls, name, breed):
dog = cls(name, breed)
dog.save()
# note that this dog will not have an id
# the id is created for the database record, not the instance
# the update() bonus method will not work correctly
# until this is addressed
return dog
@classmethod
def new_from_db(cls, row):
dog = cls(
name=row[1],
breed=row[2],
id=row[0]
)
return dog
@classmethod
def get_all(cls):
sql = """
SELECT * FROM dogs
"""
return [cls.new_from_db(row) for row in CURSOR.execute(sql).fetchall()]
@classmethod
def find_by_name(cls, name):
sql = """
SELECT * FROM dogs
WHERE name = ?
LIMIT 1
"""
row = CURSOR.execute(sql, (name,)).fetchone()
if not row:
return None
return Dog(
name=row[1],
breed=row[2],
id=row[0]
)
@classmethod
def find_by_id(cls, id):
sql = """
SELECT * FROM dogs
WHERE id = ?
LIMIT 1
"""
row = CURSOR.execute(sql, (id,)).fetchone()
if not row:
return None
return Dog(
name=row[1],
breed=row[2],
id=row[0]
)
@classmethod
def find_or_create_by(cls, name=None, breed=None):
sql = """
SELECT * FROM dogs
WHERE (name, breed) = (?, ?)
LIMIT 1
"""
row = CURSOR.execute(sql, (name, breed)).fetchone()
if not row:
sql = """
INSERT INTO dogs (name, breed)
VALUES (?, ?)
"""
CURSOR.execute(sql, (name, breed))
return Dog(
name=name,
breed=breed,
id=CURSOR.lastrowid
)
return Dog(
name=row[1],
breed=row[2],
id=row[0]
)
def update(self):
sql = """
UPDATE dogs
SET name = ?,
breed = ?
WHERE id = ?
"""
CURSOR.execute(sql, (self.name, self.breed, self.id))
