brief introduction

SQLAlchemy is an ORM framework based on Python implementation. The framework is based on the DB API and uses the relational object mapping for database operation. In short, it is to convert classes and objects into SQL, and then use the data API to execute SQL and obtain the execution results.

install

pip3 install sqlalchemy

Component

• Engine: the engine of the framework
• Connection Pooling: database connection pool
• Dialect: select the type of DB API to connect to the database
• Schema/Types: schema and types
• SQL expression language: SQL expression language

SQLAlchemy itself cannot operate the database. It must be a third-party plug-in such as pymsql. Dialect is used to communicate with the data API. Different database APIs are called according to different configuration files, so as to realize the operation of the database, such as:

"""
MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
    
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
    
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
    
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
    
//More: http://docs.sqlalchemy.org/en/latest/dialects/index.html
"""

How to reverse generate models in django

python manage.py inspectdb > app/models.py

Simple use

SQLAlchemy can only create tables and delete tables. It cannot be modified on the original tables. If you want to modify them, you can modify them in the database, and then modify them on the corresponding classes

Execute native sql (not commonly used)

import time
import threading
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine

engine = create_engine(
    "mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8",
    max_overflow=0,  # Exceeds the maximum number of connections created outside the connection pool size
    pool_size=5,  # Connection pool size
    pool_timeout=30,  # There is no maximum waiting time for threads in the pool, otherwise an error is reported
    pool_recycle=-1  # How often does the connection recycle (reset) occur to the threads in the thread pool
)
def task(arg):
    conn = engine.raw_connection()
    cursor = conn.cursor()
    cursor.execute(
        "select * from app01_book"
    )
    result = cursor.fetchall()
    print(result)
    cursor.close()
    conn.close()

for i in range(20):
    t = threading.Thread(target=task, args=(i,))
    t.start()

orm use (key)

Connect

from sqlalchemy import create_engine

create_engine() returns an instance of Engine, and it represents the core interface for handling details through database syntax. In this case, database syntax will be interpreted as Python's class method

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test',echo=True)

When the connection echo parameter is True, each executed sql statement is displayed

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test')

Declaration image

• By using the Declarative method, we can create some mapping classes that contain guidelines that describe the actual database tables to be mapped.
• The mapping class defined by the Declarative method is based on a base class, which is the directory that maintains the relationship between the class and the data table - what we call the Declarative base class. In a common module entry, an application usually only needs one instance of base. We create a base class through the declarative_base() function:

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

With this base, we can define any number of mapping classes according to this base:

class User(Base):
    __tablename__ = 'users'  # Database table name
    id = Column(Integer, primary_key=True)  # id primary key
    name = Column(String(32), index=True, nullable=False)  # name column, index, cannot be empty
    # email = Column(String(32), unique=True)
    #datetime.datetime.now can't be bracketed. After that, it will always be the current time
    # ctime = Column(DateTime, default=datetime.datetime.now)
    # extra = Column(Text, nullable=True)

    __table_args__ = (
        # UniqueConstraint('id', 'name', name='uix_id_name'), #Joint only
        # Index('ix_id_name', 'name', 'email'), #Indexes
    )

Note: a class constructed with Declarative requires at least one tablename attribute and one primary key row.

Generating table

SQLAlchemy can't generate tables automatically by making migrations and migrates similar to django. We need to generate tables ourselves

def init_db():
    """
    //Create database tables from classes
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/aaa?charset=utf8",
        max_overflow=0,  # Exceeds the maximum number of connections created outside the connection pool size
        pool_size=5,  # Connection pool size
        pool_timeout=30,  # There is no maximum waiting time for threads in the pool, otherwise an error is reported
        pool_recycle=-1  # How often does the connection recycle (reset) occur to the threads in the thread pool
    )

    Base.metadata.create_all(engine)

Change table fields

SQLAlchemy does not support to modify, add or delete the fields in the table after the creation of the table, so there are two ways to modify the fields in the table:

1. Modify the database manually, and then modify the fields on the corresponding classes
2. Delete the table, modify the fields, and then create the table

Delete table

def drop_db():
    """
    //Delete database tables by class
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/aaa?charset=utf8",
        max_overflow=0,  # Exceeds the maximum number of connections created outside the connection pool size
        pool_size=5,  # Connection pool size
        pool_timeout=30,  # There is no maximum waiting time for threads in the pool, otherwise an error is reported
        pool_recycle=-1  # How often does the connection recycle (reset) occur to the threads in the thread pool
    )

    Base.metadata.drop_all(engine)

Complete code

import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index

Base = declarative_base()

class Users(Base):
    __tablename__ = 'users'  # Database table name
    id = Column(Integer, primary_key=True)  # id primary key
    name = Column(String(32), index=True, nullable=False)  # name column, index, cannot be empty
    age = Column(Integer, default=0)
    # email = Column(String(32), unique=True)
    #datetime.datetime.now can't be bracketed. After that, it will always be the current time
    # ctime = Column(DateTime, default=datetime.datetime.now)
    # extra = Column(Text, nullable=True)

    __table_args__ = (
        # UniqueConstraint('id', 'name', name='uix_id_name'), #Joint only
        # Index('ix_id_name', 'name', 'email'), #Indexes
    )

def init_db():
    """
    //Create database tables from classes
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/aaa?charset=utf8",
        max_overflow=0,  # Exceeds the maximum number of connections created outside the connection pool size
        pool_size=5,  # Connection pool size
        pool_timeout=30,  # There is no maximum waiting time for threads in the pool, otherwise an error is reported
        pool_recycle=-1  # How often does the connection recycle (reset) occur to the threads in the thread pool
    )

    Base.metadata.create_all(engine)

def drop_db():
    """
    //Delete database tables by class
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/aaa?charset=utf8",
        max_overflow=0,  # Exceeds the maximum number of connections created outside the connection pool size
        pool_size=5,  # Connection pool size
        pool_timeout=30,  # There is no maximum waiting time for threads in the pool, otherwise an error is reported
        pool_recycle=-1  # How often does the connection recycle (reset) occur to the threads in the thread pool
    )

    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    # drop_db()
    init_db()

Common data types

data type	Explain
Integer	Integer, mapped to an int type in the database.
Float	Floating point type, mapped to the database as float type. He's 32.
Double	Double precision floating-point type, mapped to the database is double type, occupying 64 bits.
String	Variable character type, which is mapped to the varchar type in the database
Boolean	Boolean type, which is mapped to the tinyint type in the database.
DECIMAL	Point type. It is specially designed to solve the problem of floating-point type precision loss. It is recommended to use this data type when storing money related fields. Two parameters need to be passed when this type is used. The first parameter is used to mark how many digits this field can always store. The second parameter indicates how many digits are after the decimal point.
Enum	Enumeration type. Specifies that a field can only be a few values specified in the enumeration, not other values. In the ORM model, Enum is used as enumeration
Date	Storage time, can only store month day. Mapping to a database is of type date. In Python code, you can use datetime.date to specify
DateTime	Storage time, which can store year, day, hour, minute, second, millisecond, etc. Mapping to a database is also of type datetime. In Python code, you can use datetime.datetime to specify.
Time	Storage time, which can store hours, minutes and seconds. Mapping to a database is also a time type. In Python code, you can use datetime.time to get here and there.
Text	Stores a long string. Generally, it can store more than 6W characters. If it is out of this range, you can use the type LONGTEXT. Mapping to a database is the text type.
LONGTEXT	Long text type, mapped to the database as long text type.

Common parameters of Column

parameter	details
default	Default value
nullable	Is it empty?
primary_key	Primary key
unique	Is it unique?
autoincrement	Is it self increasing?
onupdate	Performed on update
name	Properties after database mapping
index	Index or not

Common operations (CURD)

Create an instance of a mapping class

user1 = User(name='hades', age=18)
user2 = User(name='bonnie', age=16)

Create Session session

Ready to talk with the database. ORM establishes a connection with the database through the Session

When the application is loaded for the first time, we define a Session class (when we declare create engine()), which provides factory services for the new Session object.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

This custom session class creates a session object bound to the database. If you need to establish a connection with the database, you only need to instantiate a session object

session =Session()

Although the above session has been associated with the database Engine, no connection has been opened. When it is used for the first time, it will retrieve whether there is a connection from a connection pool maintained by the Engine. If there is, it will keep the connection until we commit all changes and / or close the session object.

add()/add_all()

# Add one more
session.add(user1)
session.add(user2)

# Add more than one, you can add different mapping instances
# session.add_all([user1, user2, Hosts(ip='127.0.0.1')])

commit()

At this point, we can think that the newly added object instance is still waiting; the user1 object does not now represent a row of data in the database. Session does not keep SQL connected until the flush process is used. If this data is queried, all waiting information will be refreshed as soon as possible, and the query results will be published immediately.

1. commit() allows you to commit all remaining changes to the database.
2. Note: submit and query will execute all the waiting information.
3. All additions, modifications, and deletions require commit

 session.commit()

rollback()

session.rollback()

Query (key)

Create a query object through the query() method of Session. The number of parameters of this function is variable. The parameters can be any class or description set of a class

The following is an example of an iterative output User class:

Query first

session.query(Users).filter_by(name='lqz').first()

sort

session.query(User).order_by(User.id).all()

# desc(): in descending order, make sure to add ()
session.query(User).order_by(User.id.desc()).all()

# asc(): ascending
session.query(User).order_by(Users.name.desc(),User.id.asc()).all()

Query also supports ORM descriptions as parameters. At any time, entities of multiple classes or column based entity expressions can be used as parameters of the query() function. The return type is tuple:

session.query(User.name,User.fullname)

session.query(User,User.name).all()

Alias

• Field alias: label() is equivalent to row.name

session.query(User.name.label("name_label")).all()

• aliased()

from sqlalchemy.orm import aliased
user_alias = aliased(User,name='user_alias')

session.query(user_alias,user_alias.name).all()

The basic operations of Query include LIMIT and OFFSET. It is convenient to use python array slicing and order by.

Limit, for paging, range

Query only the second and third data

session.query(User).order_by(User.id)[1:3]

filter

Use keyword variables to filter query results. Both filter and filter by use

• An expression is passed by filter, and a parameter is passed by filter

session.query(User).filter(User.name=='hades').all()

session.query(User).filter_by(name='bonnie').all()

The difference between filter and filter by:

• Filter: you can use > < and so on, but the column must be: table. Column, the equal sign of filter is==
• filter: composite query not supported
• Filter by: can write columns directly, does not support < > filter by equals to yes==
• Filter by can support composite query

Filtering method

• equals

  session.query(User).filter(User.name == 'ed')

• not equals

  session.query(User).filter(User.name != 'ed')

• like

  session.query(User).filter(User.name.like('%ed%'))

• in

  query.filter(User.name.in_(['ed','wendy','jack']))
  
  # Subquery
  session.query(User).filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))

• not in

  query.filter(~User.name.in_('ed','wendy','jack'))

• is null

  session.query(User).filter(User.name == None) 

• is not null

  session.query(User).filter(User.name != None)

• and

  session.query(Users).filter(and_(User.name =='ed',User.fullname =='Ed Jones')) # and
  
  session.query(Users).filter(User.name == 'ed',User.fullname =='Ed Jones') # and
  
  session.query(Users).filter(User.name == 'ed').filter(User.fullname == 'Ed Jones')# and

• or

  query.filter(or_(User.name='ed', User.name='wendy'))

• Placeholder lookup

  #: value and: name are equivalent to placeholders. params is used to pass parameters
  session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()

Custom query sql

session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()

Statistical counting

count = session.query(User).filter(User.name.like("%t%")).count()

Grouping

session.query(func.count(User.name),User.name).group_by(User.name)

having

having as a filter for grouping

session.query(func.min(User.id), func.avg(User.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

Aggregate function

• func.count: count the number of rows. It has the same effect as count

  fc=session.query(func.count(User.name),User.name).group_by(User.name).all()

• func.avg: Average

  fc=session.query(func.avg(User.age),User.name).group_by(User.name).all()

• func.max: find the maximum value

  fc=session.query(func.max(User.age),User.name).group_by(User.name).all()

• func.min: minimum value

  fc=session.query(func.min(User.age),User.name).group_by(User.name).all()

• func.sum: Sum

  fc=session.query(func.sum(User.age),User.name).group_by(User.name).all()

modify

• First, query out the object first, and then assign a new value to the object field

  obj = session.query(User).filter(User.name=='hades').first()
  obj.age = 27
  session.commit()  # Must submit

• Second: update() method, need to pass in a dictionary

  session.query(User).filter(User.name=='hades').update({'age':27})
  session.commit()  # Must submit

• Third, it is added on the original basis, similar to the F query in django

  For example: age plus 1 year

  Note: it must be combined with synchronize session

  • String: synchronize? Session = false
  • Number type: synchronize? Session = evaluate

  session.query(User).filter(User.id > 0).update({User.name: User.name + "099"}, synchronize_session=False)
  # session.query(User).filter(User.id > 0).update({"age": User.age + 1}, synchronize_session="evaluate")
  # session.commit()

delete()

session.query(Users).filter(Users.id > 4).delete()
session.commit()