A brief introduction to SQLAlchemy under Python

SQLAlchemy is an open source software under the Python programming language. Provides SQL toolkit and object-relational mapping (ORM) tools, SQLAlchemy is released under the MIT license. It implements a complete enterprise-grade persistence model using simple Python language, designed for efficient and high-performance database access. SQLAlchemy pays great attention to database size and performance.

This section analyzes the use of SQLAlchemy through a set of examples.

Using SQLAlchemy requires at least three parts of code, which are to define tables, define database connections, and perform logical operations such as add, delete, modify, and query.

Example of defining table:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
Base=declarative_base()
class Accout(Base):
    __tablename__=u'accout'
    id=Column(Integer,primary_key=True)
    user_namr=Column(String(50),nullable=False)
    password=Column(String(200),nullable=False)
    title=Column(String(50))
    salary=Column(Integer)

    def is_active(self):
        #假设所有
        return True

    def get_id(self):
        #返回账号ID，用方法返回属性值提高了表的封装性。
        return self.id

    def is_authenticated(self):
        #假设已经通过验证
        return True

    def is_anonymous(self):
        #具有登陆名和密码的账号不是匿名用户
        return False

The code to parse the defining table is as follows:

• SQLAlchemy must be introduced before the SQLAlchemy table .ext.declarative_base, and defines an instance Base of it. All tables must inherit from Base. In this example, an account table class Account is defined.

• The actual name account of the table in the database is defined through the __tablename__ attribute.

• Introduce the Column, Integer, and String types in the sqlalchemy package because they need to be used to define columns in the table. This example defines five columns in the Account table, namely integer id and salary, as well as string types user_name, password, and title.

• When defining a column, you can define constraints by passing parameters to Column. In this example, the id column is defined as the primary key through the primary_key parameter, and user__name and password are defined as non-null through the nullable parameter.

• You can also customize other functions in the table. This example defines several functions commonly used in user authentication: is__activite(), get__id(), is__authenticate() and is_anonymous().

The sample code to define the database connection is as follows:

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session,sessionmaker
from contextlib import contextmanager

db_connect_string='mysql://v_user:v_pase@localhost:3306/test_database?charset=utf8'

ssl_args={
    'ssl':{
        'cert':'/home/ssl/client-cert.pem',
        'key':'/home/shouse/ssl/client-key.pem',
        'ca':'/home/shouse/ssl/ca-cert.pem'
    }
}
engine=create_engine(db_connect_string,connect_args=ssl_args)
SessionType=scoped_session(sessionmaker(bind=engine,expire_on_commit=False))
def GetSession():
    return SessionType()

@contextmanager
def session_scope():
    session=GetSession()
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()

The code to parse the data part of this connection is as follows:

• Introducing database and session engines: sqlalchemy.create_engine, sqlalchemy.orm.scoped_session, sqlalchemy.orm.sessionmaker.

• Define the database string needed to connect to the database. In this example, the MySQL database is connected. The string format is [databse_type]://[user_name]:[password]@[domain]:[port]/[database]?[parameters]. In this example, in addition to the necessary connection information, the charset parameter is also passed in, specifying the UTF-8 encoding method to decode the string in the database.

• Use create_engine to create the database engine. If the database has an SSL link enabled, you need to pass in the file path of the SSL client certificate here.

• Use scoped_session(sessionmaker(bind=engine)) to create the session type SessionType, and define the function GetSession() to create an instance of SessionType.

At this point, you can use the GetSession() function to create a database session and perform database operations. However, in order to enable subsequent database operation codes to automatically perform transaction processing, the context function session_scope() is defined in this example. The way to define a context function in Python is to add the contextmanager decorator in the contextlib package. Execute the following logic in the context function: establish a database session at the beginning of the function, and a database transaction will be automatically established at this time: rollback the transaction when an exception occurs; close the connection when exiting. The transaction commit operation is automatically performed when the connection is closed.

Code for database operations:

from sqlalchemy import or_,orm
def InsertAccount(user,passwd,title,salary): #新增操作
    with session_scope() as session:
        account=orm.Account(user_name=user,passwd=passwd,title=title,salary=salary)
        session.add(account)

def GetAccount(id=None,user_name=None): #查询操作
    with session_scope() as session:
        return session.query(orm.Account).filter(
            or_(orm.Account.id==id,orm.Account.user_name=user_name)
        ).first()

def DeleteAccount(user_name): #删除操作
    with session_scope() as session:
        account=GetAccount(user_name=user_name)
        if account:
            session.delete(account)

def UpdateAccount(id,user_name,password,title,salary):  #更新操作
    with session_scope() as session:
        account=session.query(orm.Account).filter(orm.Account.id==id).first()
        if not account:return
        account.user_name=user_name
        account.password=password
        account.salary=salary
        account.title=title


InsertAccount("Mark","123","Manager",3000)  #调用新增操作
InsertAccount("帅哥","456","Boss",2000)  #调用新增操作
GetAccount(2) #调用查询操作
DeleteAccount("Mark")
UpdateAccount(1,"admin","none","System admin",2500)

This example demonstrates the four most commonly used record-based operations in the database: add, search, delete, and update. The analysis of this part of the code is as follows:

• Use the import statement to introduce the package orm where the data table (Account) is located. Use or_ when introducing multi-condition queries.

• In each function, the context function session_scope() is enabled through the with statement, through which the session object is obtained and new things are automatically started.

• In InsertAccount, create a new table Account instance and add it to the database through session.add. Since the transaction will be automatically committed when the context function exits, there is no need to explicitly call session.commit() for the new addition to take effect.

• Query is performed through the query statement in GetAccount. The query conditions are set by filter. Multiple query conditions can be connected using or_ or and_.

• Query the object through GetAccount in DeleteAccount. If found, directly call session.delete() to delete the object.

• In InsertAccount(), query the record according to the ID through query. If the query is found, modify the record by setting the properties of the object.

• The result of the query statement is a collection of objects. The first() function after the query statement is used to extract the first object in the collection. If the first() function is replaced with the all() function, the query will return the collection.

Connection methods of mainstream databases

Orm database operation methods such as SQLAlchemy can shield business developers from the differences between different databases, so that When database migration is required (such as MySQL to SQLite), you only need to change the database connection string.

The following table lists the writing methods of database strings when SQLAlchemy connects to mainstream databases:

The above is the detailed content of A brief introduction to SQLAlchemy under Python. For more information, please follow other related articles on the PHP Chinese website!