Shulou(Shulou.com)06/01 Report--

In the previous article, I introduced the native pymysql way to manipulate mysql. In this article, we will learn the rules for SQLAchemy to use mysql.

The ORM framework comes in two forms:

The first is DB first, which requires manual creation of databases and tables, and then the ORM framework, a method for automatically generating code classes.

The second is code first, which is a method of manually creating a database and then automatically generating tables and data by writing code classes, the ORM framework.

SQLAlchemy is an ORM framework under the Python programming language, which is based on the database API. It uses relational object mapping to operate the database. In short, it converts the object into SQL, and then uses the data API to execute SQL and get the execution result.

Function:

Provide simple rules, 2. Automatically converted to SQL statement installation: pip3 install SQLAlchemyoreasy_install SQLAlchemy

The structure diagram of SQLAlchemy is as follows:

SQLAlchemy itself will not connect to the database, he is through the "DBAPI" module api interface to achieve the database connection, and it itself will be more Dialect inside the settings to determine what kind of database you are, how to transform the function of sql statements.

Use Engine/ConnectionPooling/Dialect for database operations, Engine uses ConnectionPooling to connect to the database, and then execute the SQL statement through Dialect.

Common DBAPI is introduced and used in the official sqlachemy. Here are a few examples:

MySQL-Python mysql+mysqldb://:@ [:] / pymysql mysql+pymysql://:@/ [?] MySQL-Connector mysql+mysqlconnector://:@ [:] / cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname [? key=value&key=value...] For more details, see: http://docs.sqlalchemy.org/en/latest/dialects/index.html uses method # import module from sqlalchemy import create_engine# to create a connection. Engine = create_engine ("mysql+pymysql://kk:123@localhost:3306/pysqltest", max_overflow=5) # executes SQLcur = engine.execute ("insert into T1 (name) VALUES ('rr')") to create a connection through create_engine

Result screenshot

Other operations #! / usr/bin/env python#-*-coding:utf-8-*-from sqlalchemy import create_engineengine = create_engine ("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5) # execute SQL# cur = engine.execute (# "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22') 3) "#) # New insert row self-increment ID# cur.lastrowid# executes SQL# cur = engine.execute (#" INSERT INTO hosts (host, color_id) VALUES (% s,% s) ", [('1.1.1.22lines, 3), (' 1.1.1.221 lines, 3),] #) # executes SQL# cur = engine.execute (#" INSERT INTO hosts (host, color_id) VALUES (% (host) s) % (color_id) s) ", # host='1.1.1.99', color_id=3#) # execute SQL# cur = engine.execute ('select * from hosts') # get the first row of data # cur.fetchone () # get the nth row of data # cur.fetchmany (3) # get all data # cur.fetchall () the use of the ORM function to create a single table operation from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint Index,CHAR,VARCHARfrom sqlalchemy.orm import sessionmaker,relationshipfrom sqlalchemy import create_engineengine=create_engine ("mysql+pymysql://kk:123@localhost:3306/pysqltest?charset=utf8", max_overflow=5) Base=declarative_base () # create a single table class Users (Base): _ _ tablename__ = 'users' id = Column (Integer,primary_key=True) name = Column (String (32)) gender = Column (CHAR (32)) _ table_args__ = (UniqueConstraint (' id','name') Name='uix_id_name'), Index ('ix_id_name','name','gender') # Index to write the name first) def init_db (): Base.metadata.create_all (engine) def drop_db (): Base.metadata.drop_all (engine) init_db ()

Execution result:

Increase operation

To manipulate the data in the table, you need the ability to get a pymysql-like cursor, which in this case is a session.

Session = sessionmaker (bind=engine) session= Session () Class-> Table object-> Row from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index,CHAR,VARCHARfrom sqlalchemy.orm import sessionmaker,relationshipfrom sqlalchemy import create_engineengine=create_engine ("mysql+pymysql://kk:123@localhost:3306/pysqltest?charset=utf8", max_overflow=5) session=sessionmaker (bind=engine) session=session () Base=declarative_base () # create single table class Users (Base): _ _ tablename__ = 'users' id = Column (Integer) Primary_key=True) name= Column (String (32)) gender = Column (CHAR (32)) _ table_args__ = (UniqueConstraint ('id','name',name='uix_id_name'), Index (' ix_id_name','name') 'gender') # Index to write the name first) def init_db (): Base.metadata.create_all (engine) def drop_db (): Base.metadata.drop_all (engine) # add function obj1 = Users (name='xx',gender=' female') session.add (obj1) # method to add a values # obj2= [# Users (name='yy',gender=' male'), # Users (name='zz',gender=' female') # Users (name='rr',gender=' male'), #] # session.add_all (obj2) session.commit () session.close () lookup function # find user_list=session.query (Users). All () for row in user_list: print (row.id,row.name,row.gender) # user_type_list = session.query (UserType.id,UserType.title). Filter (UserType.id > 2) # for row in user_type_list:# print (row.id Row.title) delete session.query (Users) .filter (Users.id = = 4). Delete () modify session.query (Users) .filter (Users.id = = 1). Update ({'gender':' Neutral'}) # for adding variable length strings, use the following session.query (Users) .filter (Users.id > 2). Update ({Users.name: Users.name + "099"} Synchronize_session=False) # automatically add all numbers to one with the following session.query (Users) .filter (Users.id > 2) .update ({"num": Users.num + 1}, synchronize_session= "evaluate") session.commit () other

+ reference articles

# conditional ret = session.query (Users). Filter_by (name='alex'). All () ret = session.query (Users) .filter (Users.id > 1, Users.name = = 'eric'). All () ret = session.query (Users). Filter (Users.id.between (1Magazine 3), Users.name =' eric'). All () ret = session.query (Users) .filter (Users.id.in_ 4]). All () ret = session.query (Users) .filter (~ Users.id.in_ ([1mai 3je 4])). All () ret = session.query (Users) .filter (Users.id.in_ (session.query (Users.id). Filter_by (name='eric')). All () from sqlalchemy import and_, or_ret = session.query (Users) .filter (and_ (Users.id > 3) Users.name = = 'eric'). All () ret = session.query (Users) .filter (or_ (Users.id)

< 2, Users.name == 'eric')).all()ret = session.query(Users).filter( or_( Users.id < 2, and_(Users.name == 'eric', Users.id >

3), Users.extra! = ")) .all () # wildcard ret = session.query (Users) .filter (Users.name.like ('e%')). All () ret = session.query (Users) .filter (~ Users.name.like ('e%')). All () # restrict ret = session.query (Users) [1:2] # sort ret = session.query (Users). Order_by (Users.name.desc ()) .all () ret = session.query (Users) .order_by (Users.name.desc () Users.id.asc (). All () # grouping from sqlalchemy.sql import funcret = session.query (Users). Group_by (Users.extra). All () ret = session.query (func.max (Users.id), func.sum (Users.id), func.min (Users.id)). Group_by (Users.name). All () ret = session.query (func.max (Users.id), func.sum (Users.id) Func.min (Users.id). Group_by (Users.name) .join (func.min (Users.id) > 2). All () # join table ret = session.query (Users, Favor) .filter (Users.id = = Favor.nid). All () ret = session.query (Person) .join (Favor). All () ret = session.query (Person) .join (Favor) Isouter=True) .all () # combination Q1 = session.query (Users.name) .filter (Users.id > 2) Q2 = session.query (Favor.caption) .filter (Favor.nid)

< 2)ret = q1.union(q2).all()q1 = session.query(Users.name).filter(Users.id >

2) Q2 = session.query (Favor.caption) .filter (Favor.nid

< 2)ret = q1.union_all(q2).all()子查询# 1.# select * from b where id in (select id from tb2)# 2 select * from (select * from tb) as B# q1 = session.query(UserType).filter(UserType.id >

0). Subquery () # result = session.query (Q1). All () # print (result) # select# id, # (select * from users where users.user_type_id=usertype.id) # from usertype # session.query (UserType,session.query (Users) .filter (Users.id = = 1). Subquery () # session.query (UserType,Users) # result = session.query (UserType.id,session.query (Users). As_scalar ()) # print (result) # result = session.query (UserType.id,session.query (Users) .filter (Users.user_type_id==UserType.id). As_scalar ()) # print (result)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.