In this Python Database tutorial we are going to learn about MySQL ORM in Python , so first of all let’s talk about ORM, Object Relational Mapping or ORM is a programming technique, Using that you can interact with databases using object oriented programming or OOP principles. ORM tools abstract the underlying database schema and allows developers to manipulate data in the database using high level programming constructs.
What is Python MySQL ORM ?
Python MySQL ORM is a Python library that provides an abstraction layer over MySQL databases using OOP principles. it maps database tables to Python classes and rows in the table to instances of the corresponding Python class.
Python MySQL ORM provides a way to interact with MySQL databases using Python objects and methods, and it will eliminate the need to write SQL queries manually. This allows you to write cleaner, more maintainable code that is easier to understand and debug.
Now let’s start our practical example, first of all we need to install an ORM library that supports MySQL databases. there are several popular Python ORM libraries available like SQLAlchemy, Django ORM and Peewee, in this article we are going to talk about SQLAlchemy, it is a popular ORM library that supports multiple databases including MySQL.
SQLAlchemy Installation
First of all we need to install SQLAlchemy and you can use pip for that.
|
1 |
pip install SQLAlchemy |
After installation of SQLAlchemy, we need to connect our MySQL database. for this we need to create SQLAlchemy engine object, and it represents a connection to the database, also make sure that you have created a mysql database, in my case it is codeloop.
|
1 2 3 |
from sqlalchemy import create_engine engine = create_engine('mysql://username:password@localhost/codeloop') |
After connecting to MySQL database, we can create a table using SQLAlchemy declarative base. declarative base allows you to define database tables as Python classes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
from sqlalchemy import create_engine from sqlalchemy.orm import declarative_base from sqlalchemy import Column, Integer, String engine = create_engine('mysql://root:@localhost/codeloop') Base = declarative_base() class Customer(Base): __tablename__ = 'customers' id = Column(Integer, primary_key=True) name = Column(String(50)) address = Column(String(100)) # Create the table Base.metadata.create_all(engine) |
If you run this code, it will create a table at name of customers in our codeloop databases like this, but right now we don’t have any data in the table.
We have created our table. now it is time to insert some data to our customers table. for inserting data into the table, we can create an instance of the Customer class and add it to a session.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
from sqlalchemy import create_engine from sqlalchemy.orm import declarative_base from sqlalchemy import Column, Integer, String engine = create_engine('mysql://root:@localhost/codeloop') Base = declarative_base() class Customer(Base): __tablename__ = 'customers' id = Column(Integer, primary_key=True) name = Column(String(50)) address = Column(String(100)) from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind=engine) session = Session() customer = Customer(name='Parwiz', address='this is my address') session.add(customer) session.commit() |
If you run the code, the new data will be added, and this will be the result
For retrieving data from the table, we can use the query() method of the session object. query() method returns SQLAlchemy query object, which you can use to filter and order the data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
from sqlalchemy import create_engine from sqlalchemy.orm import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker engine = create_engine('mysql://root:@localhost/codeloop') Base = declarative_base() class Customer(Base): __tablename__ = 'customers' id = Column(Integer, primary_key=True) name = Column(String(50)) address = Column(String(100)) Session = sessionmaker(bind=engine) session = Session() customers = session.query(Customer).all() for customer in customers: print("ID" + str(customer.id)) print("Name : " + customer.name) print("Address : " + customer.address) |
Run the code and this will be the result
Also you can update the data, for updating data in the table, you can retrieve the record you want to update, modify its attributes and then commit the changes to the database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
from sqlalchemy import create_engine from sqlalchemy.orm import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker engine = create_engine('mysql://root:@localhost/codeloop') Base = declarative_base() class Customer(Base): __tablename__ = 'customers' id = Column(Integer, primary_key=True) name = Column(String(50)) address = Column(String(100)) Session = sessionmaker(bind=engine) session = Session() customer = session.query(Customer).filter(Customer.name == 'Codeloop').first() customer.address = 'This is updated' session.commit() |
For deleting data from the table, you can retrieve the record you want to delete and then call the delete() method of the session object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
from sqlalchemy import create_engine from sqlalchemy.orm import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker engine = create_engine('mysql://root:@localhost/codeloop') Base = declarative_base() class Customer(Base): __tablename__ = 'customers' id = Column(Integer, primary_key=True) name = Column(String(50)) address = Column(String(100)) Session = sessionmaker(bind=engine) session = Session() customer = session.query(Customer).filter(Customer.name == 'Codeloop').first() session.delete(customer) session.commit() |
Subscribe and Get Free Video Courses & Articles in your Email