Once the database is created, we can now populate it, as demonstrated in this code listing:
# sqlalchemy_db.py (part 1)
1 from sqlalchemy import create_engine
2 from sqlalchemy.orm import sessionmaker
3 from sqlalchemy_declarative import Tools, Base
4
5 engine = create_engine("sqlite:///sqlalchemy_example.db")
6 Base.metadata.bind = engine
7
8 DBSession = sessionmaker(bind=engine)
9 session = DBSession()
10
11 box_knife = Tools(name="Box Knife", size="Small", price=15)
12 drill = Tools(name="Drill", size="Medium", price=35)
13 axe = Tools(name="Axe", size="Large", price=55)
14 putty_knife = Tools(name="Putty Knife", size="Small", price=25)
15 hammer = Tools(name="Hammer", size="Small", price=25)
Continuing with reimplementing the SQLite example, we are filling the SQLAlchemy DB with the same tools inventory. Line 1 imports the DB engine module, while line 2 imports the session-making module. Line 4 imports the important classes from the previously created sqlalchemy_declarative.py file.
Lines 6 and 7 create the DB engine and bind it to the Base class so the declarative classes can be accessed by the session.
Lines 9 and 10 establish the communications with the database and provide the staging area for the objects that will populate the DB. Until the data is committed to the database, they are stored in the temporary session. If desired, the changes can be undone using session.rollback().
Lines 11-15 provide the actual data that will be placed in the database. Each row of the database is represented by a separate variable definition:
# sqlalchemy_db.py (part 2)
1 screwdriver = Tools(name="Screwdriver", size="Small", price=10)
2 crowbar = Tools(name="Crowbar", size="Large", price=60)
3 items = (box_knife, drill, axe, putty_knife, hammer, screwdriver, crowbar)
4 session.add_all(items)
5 session.commit()
Here, line 3 creates a tuple of all the items to be added to the database, then line 4 uses the tuple to fill the DB with one command. Finally, we commit the changes to the database in line 5.
To retrieve the data from the database, we will use the following code:
# sqlalchemy_retrieval.py (part 1)
1 from sqlalchemy_declarative import Base, Tools
2 from sqlalchemy import create_engine
3 from sqlalchemy.orm import sessionmaker
4
5 engine = create_engine("sqlite:///sqlalchemy_example.db")
6
7 Base.metadata.bind = engine
8
9 DBSession = sessionmaker()
10 DBSession.bind = engine
11 session = DBSession()
We've seen lines 1-11 before, so we'll move to the main code in the following snippet:
# sqlalchemy_retrieval.py (part 2)
1 # Query all entries in database
2 tools = session.query(Tools).all()
3 for tool in tools:
4 print(tool.name)
5
6 # Return first entry in database
7 tool = session.query(Tools).first()
8 print("\n" + tool.name)
9
10 # Return the tool with given price
11 priced_tool = session.query(Tools).filter(Tools.price == 10).one()
12 print("\n" + priced_tool.name + "\n")
Line 2 queries the DB for all the entries within the database; since we only have one table, this is not a problem. Obviously, if you have a lot of data in a database, you wouldn't do this. With the query complete, we use a for loop to iterate over the returned list and print the names of the tools in lines 3 and 4.
Line 7 does a similar query, but only returning the first entry in the database, which is subsequently printed in line 8. (To keep the final output separated for clarity, a newline character has been added.)
Line 11 performs a query that looks for the first item that matches the provided filter; in this case, we are looking for an item with a price = 10. The item is then printed, separated from the other output:
# sqlalchemy_retrieval.py (part 3)
1 # Return all the tools with a given price
2 priced_tools = session.query(Tools).filter(Tools.price == 25).all()
3 for tool in priced_tools:
4 print(tool.name)
Line 2 looks for all entries that match a price of 25. The resultant list is then iterated through to provide the name of the matching tools.
When this program is run, the results should look like the following screenshot:
Obviously, there is more to DBs and SQLAlchemy than can be covered here. Hopefully, this provided you with an idea of how Python can be used to work with DBs, and some of the tools available depending on your needs.
From here, we will move into the main portion of this book: writing a program with real-world applications and significant complexity that, when finished, should give the reader confidence in being a software developer.