[PYTHON] Basic usage of SQLAlchemy

What is SQLAlchemy?

SQLAlchemy is one of the most popular ORMs in Python. An ORM is an Object Relational Mapper. Simply put, there is a one-to-one correspondence between a table and a class, and data can be acquired and changed via the methods of that class.

What are the advantages of ORM?

1. Absorbs the difference between different DBs

Since the same source code can be used regardless of the type of DB, there is no need to rewrite the code when using multiple DBs together or when changing the DB.

2. You don't have to write SQL

SQL is used to operate DB such as MySQL, SQLite, and PostgreSQL, but SQLAlchemy allows you to operate DB to "Pythonic" without writing SQL directly.

How to use SQLAlchemy

As a simple flow, set which DB and how to connect. (The one that holds the settings is called the engine) After that, mapping is performed and a session is created. Then, DB operation is performed using that session.

1. Create a DB engine

from sqlalchemy import create_engine
engine=create_engine("{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?charset={charset_type})

Create an instance of the engine by writing as follows. The explanation of each element is as shown in the table below.

For example, it looks like the following. (driver, port and charset_type are not specified.)

engine=create_engine("mysql://scott:tiger@localhost/foo")

2. Create a model class (write a table definition)

First, create a model base class.

from sqlalchemy.ext.declarative import declarative_base
Base=declarative_base()

Then, by extending this base class, it becomes a model class that can be handled by ORM. For example, write the class as follows. It is also possible to add methods when defining a model class.

from sqlalchemy.schema import Column
from sqlalchemy.types import Integer, String
class User(Base):
    __tablename__="user" #Specify table name
    user_id=Column(Integer, primary_key=True)
    first_name=Column(String(255))
    last_name=Column(String(255))
    age=Column(Integer)
    def full_name(self):#Method that returns full name
        return "{self.first_name} {self.last_name}"

The class defined above is a map of the table as shown in the table below.

To create this table in the DB

Base.metadata.create_all(engine)

This will create a batch of table groups that inherit from Base. The details of the table definition are as follows. Table definition in SQLAlchemy

What is metadata

The word metadata appears in the above code, but metadata is an object that holds various information in the DB. By using this metadata, you can also bring table definitions from an existing DB.

3. Create a session

SQLAlchemy executes queries via a session. In the first place, a session is a series of units from establishing a connection to disconnecting it. (The image is like a string that connects DB and Python code)

Create a session Create a class with sessionmaker. (If the engine used is constant, specify it at this time)

from sqlalchemy.orm import sessionmaker
SessionClass=sessionmaker(engine) #Create a class to create a session
session=SessionClass()

The detailed handling is as follows. How to handle session in SQLAlchemy

4. Perform CRUD processing

CRUD is a collective name for the following functions. ・ Create ・ Read ・ Update ・ Destroy

Note that the DB will not be updated unless you close the session or commit () explicitly.

INSERT When a new object is added () to session, it becomes an INSERT target.

user_a=User(first_name="first_a", last_name="last_a", age=20)
session.add(user_a)
session.commit()

SELECT Use query to retrieve data from the table.

users=session.query(User).all() #Returns all records in the user table as an array containing the classes
user=session.query(User).first() #Returns the first record in the user table as a class

For more information, see Resources.

UPDATE If you change the object retrieved from the session, it will be UPDATEd.

user_a=session.query(User).get(1) #User added above_id=1 record
user_a.age=10
session.commit()

DELETE If you delete () an object retrieved from the session, it will be subject to DELETE.

user_a=session.query(User).get(1)
session.delete(user_a)
session.commit()

Alternatively, you can delete the ones that match the search conditions.

session.query(User).filter(User.user_id=1).delete()
session.commit()

5. When using an existing table

In 3, you created a class that maps a new table, but you may want to create a class that maps an existing table. As a procedure, pass metadata to the Base class, match \ _ \ _ tablename__ with the existing table name, and set autoload to True. As a method of passing metadata to the Base class, a method of passing engine when creating the Base class

Base=declarative_base(bind=engine)

Or how to pass metadata when creating a Base class

from sqlalchemy.schema import MetaData
meta=MetaData(engine)
meta.reflect() #Get metadata, meta=MetaData(engine, reflect=True)Same as
Base=declarative_base(metadata=meta) 

Or how to give it after creating the Base class

Base=declarative_base()
Base.metadata.bind=engine 

There is. For example, if there was a table with the table name visiting \ _user.

Base=declarative_base(bind=engine)
class Exisiting_user(Base): #Any class name is ok
    __tablename__="exisiting_user" 
    __table_args__={"autoload": True}

And it is sufficient.

References

This article was written with reference to the following information. ・ Official Document ・ PlaySQLAlchemy: Introduction to SQLAlchemy ・ I want to enjoy database definition with SQLAlchemy -[Python ORM] Summary of basic SQL queries with SQLAlchemy -About the generation of model from existing DB by automap of sqlalchemy