[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.

element         Description
dialect Specify the type of DB. sqlite, mysql, postgresql, oracle,like mssql.
driver Specify the driver to connect to the DB. If not specified"default"Become a DB API.
username Specify the user name that can connect to the DB.
password Specify the password to connect to the DB.
host Specify the host name. localhost or IP address.
port Specify the port number. If you do not specify it, it seems to be the default port number?
database Specify the database name to connect to.
charset_type Specify the character code. utf8 or something.

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.

Field      Type        Null   Key   Default Extra
user_id int(11) NO PRI NULL auto_increment
first_name varchar(255) YES NULL
last_name varchar(255) YES NULL
age int(11) YES NULL

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()
user_id first_name last_name age
1 a a 20

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()
user_id first_name last_name age
1 a a 10

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 DocumentPlaySQLAlchemy: Introduction to SQLAlchemyI 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

Recommended Posts

Basic usage of SQLAlchemy
Basic usage of flask-classy
Basic usage of Jinja2
Super basic usage of pytest
Basic usage of PySimple GUI
Basic usage of Pandas Summary
Basic usage of Python f-string
Basic usage of Btrfs on Arch Linux
Basic operation of pandas
Summary of pyenv usage
Usage of Python locals ()
Basic knowledge of Python
Basic processing of librosa
[Introduction to Python] Basic usage of lambda expressions
(Beginner) Basic usage of Datastore on Google App Engine
[Introduction to Python] Basic usage of the library matplotlib
[Python] Correct usage of map
Convenient usage summary of Flask
Basic flow of anomaly detection
virtualenv Basic command usage memo
(Minimal) usage of django logger
XPath Basics (1) -Basic Concept of XPath
Sample usage of Python pickle
One-liner basic graph of HoloViews
[Python] Correct usage of join
Index of certain pandas usage
[2020/06 latest version] Basic usage of python dependency management tool poetry
Basic knowledge of Linux and basic commands
Summary of basic knowledge of PyPy Part 1
Summary of basic implementation by PyTorch
About the basic type of Go
Installation and easy usage of pytest
Basic grammar of Python3 system (dictionary)
[python] Correct usage of if statement
About the behavior of yield_per of SqlAlchemy
sqlalchemy
[Python] Basic pattern and usage of if statement (comparison operator and Boolean operator)
Basic study of OpenCV with Python
[Linux] Review of frequently used basic commands 2
Scraping the usage history of the community cycle
Basic writing of various programming languages (self-memo)
The usage of TensorBoard has changed slightly
Basic grammar of Python3 system (character string)
Basic grammar of Python3 series (list, tuple)
[Linux] Review of frequently used basic commands
Summary of SQLAlchemy connection method by DB
Non-logical operator usage of or in python
Python Basic Course (at the end of 15)
Basic level performance evaluation of programming languages
Basic grammar of Python3 system (included notation)
How to install fabric and basic usage
BESS Development Memo # 01: BESS Installation and Basic Usage