Data Handling in Python -- Part 3
SQL calls using SQLLite through SQLAlchemy
As I mentioned before, I was a database programmer by trade. I spent much of my early computer career automating small businesses, including my father-in-law’s insurance inspection and audit business.
“I’ll marry your daughter on one condition,” I told him jokingly. I would never say something like this except in jest. “That you let me move your business into the computer age.” I worked in the office a bit and out in the field a bit until I learned about his business. Then, we purchased a computer, and I spent my weekends building a data system in DBase IV using the Clipper Dbase code compiler. I also participated in developing several other verticle market DBase application development projects, including being the programmer of a piece of software called SwapBase that my partner and I sold to local pawnshops.
Many years later, I moved to Microsoft Access, and programmed custom applications for various businesses, and taught at New Horizons Computer Learning Center how to do the same.
I owned, developed, and ran a website for scheduling and researching Mountain Man Rendezvous nationwide for many years. It was written in .ASP (Microsoft Visual Basic) and used ODBC SQL calls to an MS Access database. I won’t tell it’s name just yet.
So, for me, the panacea of learning a computer programming language is building relational database applications with them. That’s my thing. Any app running on your phone that stores and displays information in little boxes, such as an email app, likely stores a hidden .db file on your phone and accesses it using SQLite.
So, yes, I had a ton of fun the other day plowing into Python’s SQL programming abilities after skimming the Python 2 tutorial on object classes. I didn’t do much of the example work from the tutorial because it was very straightforward and contained no surprises for me, but instead, I decided to practice object classes and SQL database access simultaneously by finding an object-based database API for Python. It turns out that Python has one built right into it called SQLAlchemy. I didn’t have an SQL server to play with, so I learned to use the SQLite driver in SQLAlchemy to build and connect to local .db files as if it were on an SQL server.
I ditched the Python 2 tutorial on Python.org this time and asked Gemini to be my instructor. I’ve included that transcript below and merged it with the copy/paste from PowerShell so that you can see me struggle, ask Gemini for help, read and try the response, and move on I took some additional time to do that so that you can see my complete journey in context with the research. I also had to spread this installment out between several Substack posts because Gemini provides lots of details that added lots of size and made the whole thing too large for a single Substack email. I’ll follow this with part 4 where I practice by building a grocery list database from scratch without code direct examples from Gemini.
Getting SQLAlchemy installed was a big part of the adventure, but I’ve excluded the back-and-forth of getting there. Gemini had to tell me how to build a Python virtual environment, and I’ll need to just detail that myself later in another post because Gemini talks too much, and this post was too large even for two separate Substack emailable posts. I just included the command lines for creating and activating the virtual environment, which I called “pythonlearn”…
PS C:\Users\WHousley> python -m venv pythonlearn
PS C:\Users\WHousley> pythonlearn\Scripts\activate
Just as before, you’ll get to see my entire experience, even when I look stupid. I do this so that people at least as slow as me will get to see how I get over the hurdles that I encountered, except where it was overly distracting and wordy.
My questions to Gemini are in Heading 3 style to separate them from Gemini’s responses. My practice in the PowerShell window is in code font like this and seperated from the dialog with Gemini by a divider like the one you see below.
Tell me about how to use SQL object classes and operations in Python.
Using SQL object classes and operations in Python typically involves an Object-Relational Mapper (ORM). An ORM allows you to interact with a relational database using Python objects, abstracting away the need to write raw SQL queries.
Here's a breakdown of how to use SQL object classes and operations in Python, focusing on the popular SQLAlchemy ORM:
1. Installing SQLAlchemy:
First, install SQLAlchemy using pip:
Bash
pip install sqlalchemy
2. Defining Object Classes (Models):
Create Python classes that represent your database tables.
Use SQLAlchemy's declarative base to define the structure of your tables.
Python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
def __repr__(self):
return f"<User(name='{self.name}', age='{self.age}')>"
3. Creating an Engine and Session:
Create an engine that connects to your database.
Create a session that manages your interactions with the database.
Python
engine = create_engine('sqlite:///mydatabase.db') # Example using SQLite
Base.metadata.create_all(engine) # Create tables in the database
Session = sessionmaker(bind=engine)
session = Session()
4. Performing Database Operations:
Creating Objects (Inserting Data):
Python
new_user = User(name='John Doe', age=30)
session.add(new_user)
session.commit()
Querying Objects (Retrieving Data):
Python
users = session.query(User).all() # Get all users
for user in users:
print(user)
john = session.query(User).filter_by(name='John Doe').first() #get first user with the name John Doe.
print(john)
older_users = session.query(User).filter(User.age > 25).all() #get all users who are older than 25.
for user in older_users:
print(user)
Updating Objects:
Python
user_to_update = session.query(User).filter_by(name='John Doe').first()
if user_to_update:
user_to_update.age = 31
session.commit()
Deleting Objects:
Python
user_to_delete = session.query(User).filter_by(name='John Doe').first()
if user_to_delete:
session.delete(user_to_delete)
session.commit()
5. Closing the Session:
When you're finished, close the session.
Python
session.close()
Key Concepts:
Engine: The engine manages the connection to the database.
Session: The session manages the interactions with the database, including adding, querying, updating, and deleting objects.
Models: Python classes that represent database tables.
Columns: Attributes of the model classes that represent database columns.
Queries: SQLAlchemy's query language allows you to retrieve data from the database.
Transactions: SQLAlchemy uses transactions to ensure data consistency.
Advantages of using an ORM:
Abstraction: ORMs abstract away the complexities of SQL, making it easier to work with databases.
Object-Oriented Approach: You can work with database data using Python objects.
Portability: ORMs can often work with multiple database systems.
Security: ORMs can help prevent SQL injection vulnerabilities.
Considerations:
Performance: ORMs can sometimes introduce performance overhead compared to raw SQL.
Complexity: ORMs can add complexity to your code, especially for advanced database operations.
Learning Curve: There is a learning curve associated with using ORMs.
SQLAlchemy is a powerful and flexible ORM that's widely used in Python. It provides a comprehensive set of features for working with relational databases.
PS C:\Users\WHousley> python -m venv pythonlearn
PS C:\Users\WHousley> pythonlearn\Scripts\activate
(pythonlearn) PS C:\Users\WHousley> python
Python 3.13.2 (tags/v3.13.2:4f8bb39, Feb 4 2025, 15:23:48) [MSC v.1942 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
(pythonlearn) PS C:\Users\WHousley> pythonlearn\Scripts\pip.exe install sqlalchemy
Collecting sqlalchemy
Downloading sqlalchemy-2.0.39-cp313-cp313-win_amd64.whl.metadata (9.9 kB)
Collecting greenlet!=0.4.17 (from sqlalchemy)
Downloading greenlet-3.1.1-cp313-cp313-win_amd64.whl.metadata (3.9 kB)
Collecting typing-extensions>=4.6.0 (from sqlalchemy)
Downloading typing_extensions-4.12.2-py3-none-any.whl.metadata (3.0 kB)
Downloading sqlalchemy-2.0.39-cp313-cp313-win_amd64.whl (2.1 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 2.1/2.1 MB 6.6 MB/s eta 0:00:00
Downloading greenlet-3.1.1-cp313-cp313-win_amd64.whl (299 kB)
Downloading typing_extensions-4.12.2-py3-none-any.whl (37 kB)
Installing collected packages: typing-extensions, greenlet, sqlalchemy
Successfully installed greenlet-3.1.1 sqlalchemy-2.0.39 typing-extensions-4.12.2
[notice] A new release of pip is available: 24.3.1 -> 25.0.1
[notice] To update, run: python.exe -m pip install --upgrade pip
(pythonlearn) PS C:\Users\WHousley> python
Python 3.13.2 (tags/v3.13.2:4f8bb39, Feb 4 2025, 15:23:48) [MSC v.1942 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
Explicit pathing to pip.exe within the virtual environment did the trick. The sqlalchemy has now been installed. How do I use it?
Excellent! Getting pip working within your virtual environment is a crucial step. Now that SQLAlchemy is installed, let's dive into how to use it.
Basic SQLAlchemy Usage:
Here's a step-by-step guide with explanations:
1. Import Necessary Modules:
Python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
2. Define a Model (Object Class):
Create a Python class that maps to a database table.
Use
declarative_base()to create a base class for your models.
Python
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
def __repr__(self):
return f"<User(name='{self.name}', age='{self.age}')>"
3. Create an Engine and Session:
Create an engine to connect to your database.
Create a session to manage database interactions.
Python
engine = create_engine('sqlite:///mydatabase.db') # Use SQLite for simplicity
Base.metadata.create_all(engine) # Create tables
Session = sessionmaker(bind=engine)
session = Session()
4. Perform Database Operations:
Create (Insert) Data:
Python
new_user = User(name='Alice', age=25)
session.add(new_user)
session.commit()
Read (Query) Data:
Python
users = session.query(User).all() # Get all users
for user in users:
print(user)
john = session.query(User).filter_by(name='Alice').first() #Get the first user with the name 'Alice'
print(john)
older_users = session.query(User).filter(User.age > 20).all() #Get all users older than 20.
for user in older_users:
print(user)
Update Data:
Python
user_to_update = session.query(User).filter_by(name='Alice').first()
if user_to_update:
user_to_update.age = 26
session.commit()
Delete Data:
Python
user_to_delete = session.query(User).filter_by(name='Alice').first()
if user_to_delete:
session.delete(user_to_delete)
session.commit()
5. Close the Session:
Python
session.close()
Complete Example:
Python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
age = Column(Integer)
def __repr__(self):
return f"<User(name='{self.name}', age='{self.age}')>"
engine = create_engine('sqlite:///mydatabase.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
# Create
new_user = User(name='Alice', age=25)
session.add(new_user)
session.commit()
# Read
users = session.query(User).all()
for user in users:
print(user)
# Update
user_to_update = session.query(User).filter_by(name='Alice').first()
if user_to_update:
user_to_update.age = 26
session.commit()
# Delete
user_to_delete = session.query(User).filter_by(name='Alice').first()
if user_to_delete:
session.delete(user_to_delete)
session.commit()
session.close()
Key Points:
Database Connection:
Replace
'sqlite:///mydatabase.db'with your database connection string.For other databases (PostgreSQL, MySQL, SQL Server), you'll need to use the appropriate connection string format.
Models:
Define your models to match your database tables.
Use appropriate column types (e.g.,
Integer,String,Date).
Sessions:
Sessions manage database transactions.
Always commit your changes to persist them.
Close your sessions when you're finished.
Queries:
Use
session.query()to retrieve data.Use
filter_by()andfilter()to apply conditions.
This should give you a good starting point for using SQLAlchemy. Experiment with different database operations and explore the SQLAlchemy documentation for more advanced features.
>>> from sqlalchmy import create_engine, Column, Integer, String
Traceback (most recent call last):
File "<python-input-0>", line 1, in <module>
from sqlalchmy import create_engine, Column, Integer, String
ModuleNotFoundError: No module named 'sqlalchmy'
>>> from sqlalchemy import create_engine, Column, Integer, String
>>> from sqlalchemy.ext.declarative import declarative_base
>>> from sqlalchemy.orm import sessionmaker
>>> Base=declarative_base()
<python-input-4>:1: MovedIn20Warning: The ``declarative_base()`` function is now available as sqlalchemy.orm.declarative_base(). (deprecated since: 2.0) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
Base=declarative_base()
>>> class User(Base):
... __tablename__='users'
... id=Column(Integer, primary_key=True)
... name=Column(String)
... age=Column(Integer)
... def __repr__(self):
... return f"<User(name='{self.name}',age'{self.age}')>"
...
>>> engine = create_engine('sqlite:///mydatabase.db')
>>> Base.metadata.create_all(engine)
>>> session = sessionmaker(bind=engine)
>>> Session = sessionmaker(bind=engine)
>>> session = Session()
>>> new_user = User(name='Alice', age=25)
>>> session.add(new_user)
>>> sesson.commit()
Traceback (most recent call last):
File "<python-input-13>", line 1, in <module>
sesson.commit()
^^^^^^
NameError: name 'sesson' is not defined. Did you mean: 'session'?
>>> session.commit()
>>> users = session.query(User).all()
>>> for user in users:
... print(user)
...
<User(name='Alice',age'25')>
>>> for user in users:
... print(user.name)
...
Alice
>>> for user in users:
... print(user.age)
...
25
>>> john = session.query(User).filter_by(name='Alice').first()
>>> print(john)
<User(name='Alice',age'25')>
>>> for user in users:
... print('%-10s %5d' % user.name, user.age)
...
Traceback (most recent call last):
File "<python-input-21>", line 2, in <module>
print('%-10s %5d' % user.name, user.age)
~~~~~~~~~~~~^~~~~~~~~~~
TypeError: not enough arguments for format string
>>> for user in users:
... print('%-10s' % user.name, '%5d' % user.age)
...
Alice 25
>>> for user in users:
... print('%-10s' % user.name, ', ', '%5d' % user.age)
...
Alice , 25
>>> older_users = session.query(User).filter(User.age > 20.all()
File "<python-input-24>", line 1
older_users = session.query(User).filter(User.age > 20.all()
^
SyntaxError: invalid decimal literal
>>> older_users = session.query(User).filter(User.age > 20).all()
>>> for user in olderusers:
... print(user)
...
Traceback (most recent call last):
File "<python-input-26>", line 1, in <module>
for user in olderusers:
^^^^^^^^^^
NameError: name 'olderusers' is not defined. Did you mean: 'older_users'?
>>> for user in older_users:
... print(user)
...
<User(name='Alice',age'25')>
>>> user_to_update = session.query(User).filter_by(name='Alice'.first()
... if user_to_update:
... user_to_update.age = 26
... session.commit()
...
File "<python-input-28>", line 1
user_to_update = session.query(User).filter_by(name='Alice'.first()
^
SyntaxError: '(' was never closed
>>> user_to_update = session.query(User).filter_by(name='Alice').first()
... if user_to_update:
... user_to_update.age = 26
... session.commit()
...
>>> personname=input()
Bill
>>> input().__doc__
"str(object='') -> str\nstr(bytes_or_buffer[, encoding[, errors]]) -> str\n\nCreate a new string object from the given object. If encoding or\nerrors is specified, then the object must expose a data buffer\nthat will be decoded using the given encoding and error handler.\nOtherwise, returns the result of object.__str__() (if defined)\nor repr(object).\nencoding defaults to 'utf-8'.\nerrors defaults to 'strict'."
>>> new_user = User(name=input("Enter Name: "), age=int(input("Enter Age: ")))
Enter Name: Bob
Enter Age: 50
>>> session.commit()
>>> for user in users:
... print('%-10s' % user.name, ', ', '%5d' % user.age)
...
Alice , 26
>>> older_users = session.query(User).filter(User.age > 30).all()
>>> for user in older_users:
... print(user)
...
>>> new_user = User(name=input("Enter Name: "), age=int(input("Enter Age: ")))
Enter Name: Bob
Enter Age: 50
>>> session.add(new_user)
File "<python-input-38>", line 1
session.add(new_user)
IndentationError: unexpected indent
>>> session.add(new_user)
File "<python-input-39>", line 1
session.add(new_user)
IndentationError: unexpected indent
>>>
>>>
>>>
>>> new_user = User(name='Alice', age=25)
>>> >>> session.add(new_user)
File "<python-input-44>", line 1
>>> session.add(new_user)
^^
SyntaxError: invalid syntax
>>> new_user = User(name='Bob', age=50)
>>> session.add(new_user)
>>> session.commit()
>>> older_users = session.query(User).filter(User.age > 30).all()
>>> for user in older_users:
... print(user)
...
<User(name='Bob',age'50')>
>>> users=session.query(User).all()
>>> for user in users:
... print(user)
...
<User(name='Alice',age'26')>
<User(name='Bob',age'50')>
>>> new_user = User(name=input("Enter Name: "), age=int(input("Enter Age: ")))
Enter Name: Harold
Enter Age: 45
>>> session.add(new_user)
>>> session.commit
<bound method Session.commit of <sqlalchemy.orm.session.Session object at 0x000001F58DDC5010>>
>>> session.commit()
>>> users=session.query(User).all()
>>> for user in users:
... print(user)
...
<User(name='Alice',age'26')>
<User(name='Bob',age'50')>
<User(name='Harold',age'45')>
>>> new_user=User(name='Sally', age=25)
>>> session.add(new_user)
>>> new_user=User(name='Phil', age=35)
>>> session.add(new_user)
>>> session.commit()
>>> users=session.query(User).all()
>>> for user in users:
... print(user)
...
<User(name='Alice',age'26')>
<User(name='Bob',age'50')>
<User(name='Harold',age'45')>
<User(name='Sally',age'25')>
<User(name='Phil',age'35')>
>>> for i in range(1,11):
... new_user = User(name=input("Enter Name: "), age=int(input("Enter Age: ")))
... session.add(new_user)
... session.commit()
...
Enter Name: Jason
Enter Age: 65
Enter Name: Terrance
Enter Age: 13
Enter Name: Joel
Enter Age: 45
Enter Name: Bill
Enter Age: 63
Enter Name: Julie
Enter Age: 62
Enter Name: Cliffard
Enter Age: 67
Enter Name: phillis
Enter Age: 75
Enter Name: Robert
Enter Age: 23
Enter Name: Ralph
Enter Age: 43
Enter Name: Dean
Enter Age: 21
>>> users = session.query(User).all()
>>> for user in users:
... print('%-10s' % user.name, ', ', '%5d' % user.age)
...
Alice , 26
Bob , 50
Harold , 45
Sally , 25
Phil , 35
Jason , 65
Terrance , 13
Joel , 45
Bill , 63
Julie , 62
Cliffard , 67
phillis , 75
Robert , 23
Ralph , 43
Dean , 21
>>> older_users=session.query(User).filter(User.age>30).all()
>>> for user in older_users:
... print('%-10s' % user.name, ', ', '%5d' % user.age)
...
Bob , 50
Harold , 45
Phil , 35
Jason , 65
Joel , 45
Bill , 63
Julie , 62
Cliffard , 67
phillis , 75
Ralph , 43
>>> session.close()