1 Introduction to basic knowledge
1.1 ORM Framework Introduction
ORM (Object Ralational Mapping) is used to map the Object represented by the Object model to the relational model database structure based on S Q L. When we operate entity objects, we do not need to deal with complex SQ L statements any more. We only need to operate the attributes and methods of entity objects simply.
Common ORM frameworks
SQLAlchemy:SQLAlchemy uses a data mapping pattern, and its unit of work primarily makes it necessary to restrict all database operation code to a specific database session where it controls the life cycle of each object.
SQLObject: is a popular object relationship manager used to provide an object interface to a database where tables are classes, behavior instances, and columns are attributes. SQLObject includes a Query language based on Python objects, making SQL more abstract and giving applications a lot of database independence.
Storm: Is a Python ORM that maps objects between a single or multiple database and Python. To support dynamic storage and retrieval of object information, it allows developers to build complex queries across data tables. A table class in Stom need not be a subclass of a framework-specific base class. Each table class is a subclass of sqlobject. sqlobject.
Django’s ORM: Because Django’s ORM is tightly built into the Web framework, it is not recommended, if at all, to use its ORM in a standalone, non-Django Python project. Django, one of the most popular Python Web frameworks, has its own ORM. Django’s ORM is more aligned with direct manipulation of SQL objects than SQLAlchemy, exposing SQL objects that simply map data tables directly to Python classes.
1.2 introduce SQLAlchemy
Sqlalchemy is an open source framework for Python ORM that allows you to quickly and easily build database models
SQLAlchemy framework
- Engine: The Engine of the framework
- Connection Pooling: database Connection pool
- Dialect: Select the DB API type to connect to the database
- Schema/Type: indicates the Schema and Type
- SQL Expression Language: SQL Expression Language
SQLALchemy can’t operate the database itself, so you need to rely on the Pymysql third-party module. Dialect is used to communicate with the data API, which will call different database apis based on the configuration files
Connect to a database using Pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
2 use SQLAlchemy
2.1 Install SQLAlchemy and check whether the installation is successful
# installation
pip install SQLAlchemy
Check whether the installation is successfulC: Users\ LSL \Desktop> Python Python 3.7.0rc1 (v3.7.0rc1:dfad352267, Jun 12 2018, 07:05:25) [MSC v.1914 64 bit (AMD64)] on win32 Type"help"."copyright"."credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.3.18'Copy the code2.2 Use SQLAlchemy to operate on tables in the database
2.2.1 Creating a connection object is to connect to a local database
Create_engine create_engine
engine = create_engine(‘dialect+driver://username:password@host:port/database’)
Dialect — Database type
MySQLdb: No module named ‘MySQLdb’/ImportError: No module named ‘pymysql’, PIP install pymysql
Username — the database username
Password — The user password
Host Server ADDRESS
Port to port
Database database
Create a connection:
from sqlalchemy import create_engine
Connect to local test database
engine = create_engine('mysql+pymysql://root:root@localhost:3306/testdab'.Mysql > select * from testdab; mysql > select * from testdab; mysql > select * from testdab;
encoding='utf-8'.# Encoding format
echo=True, Whether to enable logging output of SQL execution statements
pool_recycle=-1, # How long does it take to reclaim (reset) a connection from a thread in the pool (default: -1)
poolclass=NullPool # Unlimited number of connections
)Copy the code2.2.2 Simple Query: Use SQL statements
result = engine.execute("select * from students")// The argument in execute() is the SQL statement of the queryprint(result.fetchall()) // Prints the result of the queryCopy the code2.2.3 Creating a Mapping
After creating the mapping, we can reduce the number of SQL statements that operate on the database. Instead, we can operate on the data in the database by operating on the classes that we have created for the tables in the database.
Create a py file to map the table text2.py
# Introduce declarative_base to use
from sqlalchemy.ext.declarative import declarative_base
Mysql > select * from students; select * from students; select * from students; select * from students; select * from students
from sqlalchemy import Column, Integer, String
# reputation Base
Base = declarative_base()
The #User class is a mapping to the table __tablename__ points to, that is, the data table STUDENTS
class User(Base):
The # STUDENTS table is already in my local database, Testdab
__tablename__ = 'students'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64),nullable=False)
__table_args__ = {
"mysql_charset": "utf8"
}Copy the codeBefore you do anything, you also need to create a Session. The main purpose of a Session is to establish a Session with the database. It maintains all the database objects that you load and associate with it. It is an entry point to a database Query. In Sqlalchemy, database queries are performed through Query objects. Session provides the interface to create Query objects.
Create a session
session = sessionmaker(engine)
mySession = session()Copy the codeNext, we can manipulate the data table STUDENTS by manipulating the User class
2.2.4 query
Query all data in students table
result = mySession.query(News).all()
print(result[0])Copy the codeQuery first data in students table
result = mySession.query(User).first()
print(result.name) Print object propertiesCopy the codeQuery data by id (id=2)
result = mySession.query(User).filter_by(id=2).first()
print(result.name)Copy the codeYou can customize filter conditions
result = mySession.query(User).filter(text("id>:id")).params(id=2).all()Copy the codeQuery by primary key
result = mySession.query(User).get(2)Copy the codeThis section describes other query operations
all()Returns all results of the query. The danger with this approach is if there is a large amount of data and it is not usedlimitClause limits all results to be loaded into memory. It returns oneThe list ofIf no result is found, an empty list is returned.first()Returns the first result of the query,If no result is found, returnNone..scalar()This method is related to.one_or_none()Have the same effect. If there are many results, throwsqlalchemy.orm.exc.MultipleResultsFoundThe exception. If there is only one result, return it; no result is returnedNone.one()If only one result can be queried, return it; otherwise, an exception is thrown. Throw when there is no resultsqlalchemy.orm.exc.NoResultFoundWhen there is more than one result castsqlalchemy.orm.exc.MultipleResultsFound.one_or_none()Compared with theone()The only difference is that an exception is no longer thrown but returned if the query does not produce any resultsNone.get()This is a very special method. It is used to return query results based on the primary key, so it takes the primary key of the object to be queried. Returns if there is no result for that primary keyNoneOtherwise, the result is returned.
2.2.5 Adding Data (Add a data name= “little red”), make sure to commit
user = User(name="Little red")
mySession.add(user)
mySession.commit()
Copy the code2.2.6 Deleting Data (Based on THE ID)
mySession.query(User).filter(User.id == 1).delete()
mySession.commit()Copy the code2.2.7 Modifying Data (Modify a data by changing the name of Red to White)
mySession.query(User).filter(User.name=="Little red").update({"name":"White"})
mySession.commit()Copy the code3 Code for querying common conditions
Table name: User 1. Query session.query(User).filter(user.name =='Joe'Limit (1).one().first().limit(1).one().first().limit(1).one()limitRestrict queries,limit(1).one() Upgrade the first.count() query the total number of conditions} 2. Primary key query session.query(User).get(0) Primary key query ID=0 3. Offset (n) Limits the first N and displays the last N +1Select all after the thirdSession. The query (User). Offset (3) all () 4. Slice slice ()Slice (1,3) is the same as python's slice, which starts at 0 and displays 1,2 elementsSession.query (User).slice(1,,3).all() 5.order_by() default ascending order session.query(User).order_by(user.id).all() 6.desc() descending order Session.query (User).order_by(desc(user.id)).all() 7.like fuzzy matching, same as SQL session.query(User).filter(user.neme.Wu '%'Form operator import * 9.in_() contains# Query whether the user information of Tang Ren and Wu Xinxi is included
session.query(User).filter(User.name.in_(['tang dynasty'.'Wu Xinxi'])).all() 10.notin_() does not include 11.is_Select * from mobile phone where id = nullsession.query(User).filter(User.phone==None).all() session.query(User).filter(User.phone.is_(None)).all() 12. isnot() 13. Or_ Condition or relation# query name== wu Xinxi or Tang ren user information
session.query(User).filter(or_(User.name=='tang dynasty',User.name=='Wu Xinxi'1. Count group_by# query all passwords and count the same numberfrom sqlalchemy import func ssession.query(db_user.psw,func.count(db_user.psw)).group_by(db_user.psw).all() 2.having Having allows us to sift through groups of data,whereClauses filter records before aggregating, that is, before group by and having clauses. The HAVING clause filters group records after aggregation. This data does not exist in the real table, and it lives through some function. That is, the first group screeningSelect * from password (s) where password (s) = 1
ssession.query(db_user.psw,func.count(db_user.psw)).group_by(db_user.psw).having(func.count(db_user.psw)>1).all()
3.sum
# count the sum of all ids
ssession.query(func.sum(db_user.id)).all()
4.max
# maximum ID
ssession.query(func.max(db_user.id)).all()
5.min
# minimum idSsession.query (func.min(db_user.id)).all() 6.lable alias Lable alias cannot be used in having 7.extract time element from sqlalchemy import extractCopy the code4 Total code
#import
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import NullPool
Create a connection object to connect to a local database
engine = create_engine('mysql+pymysql://root:root@localhost:3306/testdab',
encoding='utf-8'.# Encoding format
echo=True, Whether to enable logging output of SQL execution statements
pool_recycle=-1, # How long does it take to reclaim (reset) a connection from a thread in the pool (default: -1)
poolclass=NullPool # Unlimited number of connections
)
# reputation Base
Base = declarative_base()
Create a session
session = sessionmaker(engine)
mySession = session()
Create classes that inherit base classes and describe the structure of the database with basic types
class User(Base):
__tablename__ = 'students'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64),nullable=False)
__table_args__ = {
"mysql_charset": "utf8"
}
# SQL statement query
result = engine.execute("select * from students")
print(result.fetchall())
# query first
result = mySession.query(User).first()
print(result.name) Print object properties
# select * from all
result = mySession.query(User).all()
print(result[0])
Select * from 'id' where id = 2
result = mySession.query(User).filter_by(id=2).first()
print(result.name)
Query 0,2
result = mySession.query(User).filter(User.id>1).limit(2).offset(0).all()
print(result)
Insert new data
user = User(name="Little red")
mySession.add(user)
mySession.commit()
result = mySession.query(User).filter_by(name="Little red").first()
print(result.name)
# Modify existing data
mySession.query(User).filter(User.name=="Little red").update({"name":"White"})
mySession.commit()
result = mySession.query(User).filter_by(name="White").first()
print(result.name)
# delete data
mySession.query(User).filter(User.id == 1).delete()
mySession.commit()
result = mySession.query(User).first()
print(result.name) Print object propertiesCopy the code