In this Python Database tutorial we are going to talk about SQLite ORM with Python, so Python is popular programming language for building web applications, data analysis and scientific computing. and the best feature that Python has is that we can work with different types of databases. SQLite is lightweight database engine that can be embedded in Python applications. Object Relational Mapping or ORM is a programming technique that allows you to work with databases using objects instead of SQL statements. in this article we want to talk about SQLite ORM in Python.
What is SQLite ORM ?
SQLite ORM is a Python library that provides a way to map Python objects to database tables. Using that you can work with databases using high level objects instead of writing low level SQL statements. with SQLite ORM you can create, read, update and delete (CRUD) records in a database without writing any SQL.
How to Use SQLite ORM in Python ?
For using SQLite ORM in Python, we need to install a library called SQLAlchemy. you can install it using pip:
|
1 |
pip install sqlalchemy |
After that you have installed SQLAlchemy, you can start using it to create a database and map Python objects to database tables, in this example we have created a User class that inherits from the declarative_base() class provided by SQLAlchemy. after that we define the tablename attribute to specify the name of the database table that corresponds to this class. we also defines three columns id, name and age. after that we creates an engine object that connects to a SQLite database called example.db. and lastly we call create_all() method on the metadata object to create the table in the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.orm import declarative_base Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) age = Column(Integer) engine = create_engine('sqlite:///example.db') Base.metadata.create_all(engine) |
Run the code you will see the database with table, but for right now we don’t have any data on that.
For adding a new record to the database, we can create a User object and adds it to a session, in this example we have created a session object by calling sessionmaker() function and passing in the engine object. after that we creates a new User object and add it to the session using add() method. and lastly we call the commit() method to save the changes to the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.orm import declarative_base, sessionmaker Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) age = Column(Integer) engine = create_engine('sqlite:///example.db') Session = sessionmaker(bind=engine) session = Session() user = User(name='Codeloop.org', age=25) session.add(user) session.commit() |
Run the code and we have added two records in our table.
For retrieving records from Python Database, we can use query() method, in this example we have used the query() method to retrieve all records from the users table of our Database in Python. after that we loop over the results and print out the id, name and age of each record.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
from sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.orm import declarative_base, sessionmaker Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) age = Column(Integer) engine = create_engine('sqlite:///example.db') Session = sessionmaker(bind=engine) session = Session() users = session.query(User).all() for user in users: print(user.id, user.name, user.age) |
This will be the result
Subscribe and Get Free Video Courses & Articles in your Email