The sqlalchemy.orm.sessionmaker() function allows you to create a sqlalchemy.orm.session.sessionmaker class that contains attributes and methods that allow you to interact with the database. You can use the following methods to manage session data:
- The add() method, which adds or replaces the record that's bound to the instantiated object of a Base subclass in the corresponding record within the database
- The delete() method, which deletes the record that's bound to the object
- The commit() method, which applies the changes to the database
Now that our tables have been assigned and created, we can insert data. This insertion is done by creating instances of the class, which is created with MyTable. At this point, all we have is an instance of the objects at the ORM abstraction level, but nothing has been saved in the database yet. To do this, we first need to create a session:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
This session object is our database manager. According to the SQLAlchemy documentation, this object allows us to recover a connection from a group of connections that's maintained by the engine, and maintains it until we confirm all of the changes or close the session object. Now that we have our session, we can add a new object to it and confirm our changes in the database:
object = MyTable(message="Hello World!")
session.add(object)
session.commit()
Once we have our session defined, we can perform queries related to Base instantiate objects from sessions and the tables linked to them.
For performing queries, we can use the following methods:
- query.first() returns to the first object that's found in a search
- query.all() returns a list type object with all the objects resulting from a search
- query.filter() returns a Query object with the objects found when executing a search that satisfies the logical expression on the attributes of the class, which is entered as an argument
Now that we have data, we can take advantage of the ORM query language to retrieve our data:
query = session.query(MyTable)
instance = query.first()
print (instance.message) # Hello World!
The Session class allows you to add new objects or update existing ones in the database. To add new objects, we can use the session.add(obj) method. You can also add a list of objects by using the session.add_all([obj1, obj2, obj3]) method.
When calling the add() method, an INSERT will be made in the database and a commit() will be performed to confirm the session data. In this example, we are using the session object for access, thus inserting or updating data in the SQLite database.
You can find the following code in the insert_data.py file:
#!/usr/local/bin/python3
from datetime import date
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import Author, Book
# connection with sqlite database
engine = create_engine('sqlite:///books_authors.db', echo=True)
# get sesion
Session = sessionmaker(bind=engine)
session = Session()
# inserting authors
author_1 = Author('Author1')
author_2 = Author('Author2')
author_list = (author_1, author_2)
session.add_all(author_list)
session.commit()
In the previous code block, we defined the connection with the SQLite database, got a session, and inserted some authors with the add_all() method from the session object. In the following code block, we're using the Book model to insert one book instance with the add() and commit() methods:
# inserting books
book1 = Book('Book1', date(2019, 1, 1), '123456789')
book1.authors.append(author_1)
session.add(book1)
session.commit()
# book query
book = session.query(Book).filter(Book.isbn=='123456789').first()
print(book)
# modifying book data
book.title = 'Learning Python Networking'
session.commit()
print(book)
At this point, it is important to realize how the relationship between the book and the author is established through the append method. We will also use query method from the session object to execute a database query by applying a specific filter. Finally, we will modify the title of the book and save its information with the commit() method.
After you execute the previous insert_data.py script, you can check the book_authors.db file to see whether the book information has been updated in the database. You can open this file with the SQLite browser, which is available at https://sqlitebrowser.org.