Basic energy use of sqlalchemy

First, Introduction

　　SQLAlchemy is an ORM (object-relational Mapping) framework used to map a relational database to an object. The framework builds on the DB API, transforms classes and objects into SQL, and then executes the SQL using the API and obtains the execution results.

Second, the composition

• Schema/types, architecture and type
• SQL exprression language,sql Expression language
• Engine, Frame engines
• Connection Pooling, database connection pool
• Dialect, select the type of DB API to connect to the database

Third, the basic use of 1, process:

1) Users submit their lives through an ORM object

2) command to SQLAlchemy Core (Schema/types SQL Expression Language) into SQL

3) using Engine/connectionpooling/dialect for database operations

　　　3.1) match the user's pre-configured Egine

3.2) egine remove a link from the connection pool

3.3) Call the DB API via dialect based on the link to transfer SQL to it to execute

SQLAlchemy itself cannot operate the database, it must have been pymsql and other third-party plug-ins, dialect used to communicate with the data API, depending on the configuration file to call different database APIs, so as to achieve the operation of the database, such as:

Mysql-python    mysql+mysqldb://<user>:<password>@
2. Enable
　　　　If we do not rely on sqlalchemy conversion and write our own SQL statements, it means that we can start directly from the 3rd phase of execution, in fact, we can only use the SQLAlchemy to execute pure SQL statements, as follows:
From SQLAlchemy import create_engine# 1 Preparation # requires a pre-installed pymysql# configured to use the database, here in MySQL for example # need to create a good database in advance: "Create databases DB1 CharSet utf8;# 2 Create engine egine=create_engine (' Mysql+pymysql://[email Protected]/db1?charset=utf8 ') # 3 Execute sql# egine.execute (' CREATE table if not EXISTS user (id int PRIMARY KEY auto_increment,name char (32)); # Cur=egine.execute (' INSERT into user values (% (ID) s,% (name) s); ', Name= ' Ming ', id=3) # 4 Query Cur=egine.execute (' SELECT * From user ') Cur.fetchone () #获取一行cur. Fetchmany (2) #获取多行cur. Fetchall () #获取所有行
3. ORM
1) Create a table
 fromSQLAlchemyImportCreate_engine fromSqlalchemy.ormImportSessionmaker fromSqlalchemy.ext.declarativeImportDeclarative_base fromSQLAlchemyImportColumn, Integer, String, DateTime, Enum, ForeignKey, UniqueConstraint, ForeignKeyConstraint, Indexegine= Create_engine ('Mysql+pymysql://[email Protected]:3306/db1?charset=utf8', max_overflow=5)#Create a base class, and each table created later needs to inherit this classBase =declarative_base ()#Create a single table: Line of BusinessclassBusiness (Base):__tablename__=' Business'ID= Column (Integer, Primary_key=true, autoincrement=True) bname= Column (String (+), Nullable=false, index=True)#Many-to-one: multiple services can belong to a line of business, multiple lines of business cannot contain the same serviceclassService (Base):__tablename__='Service'ID= Column (Integer, Primary_key=true, autoincrement=True) sname= Column (String (+), Nullable=false, index=True) IP= Column (String), nullable=False) Port= Column (Integer, nullable=False) business_id= Column (Integer, ForeignKey ('business.id'))    __table_args__=(UniqueConstraint (IP, port, name='Uix_ip_port'), Index ('Ix_id_sname', ID, sname))#One -to-one: a role can only manage a line of business, a line of business can only be managed by one roleclassRole (Base):__tablename__='role'ID= Column (Integer, Primary_key=true, autoincrement=True) Rname= Column (String (+), Nullable=false, index=True) Priv= Column (String), nullable=False) business_id= Column (Integer, ForeignKey ('business.id'), unique=True)#Many-to-many: multiple users can be the same role, multiple role can contain the same userclassUsers (Base):__tablename__='Users'ID= Column (Integer, Primary_key=true, autoincrement=True) uname= Column (String (+), Nullable=false, index=True)classUsers2role (Base):__tablename__='Users2role'ID= Column (Integer, Primary_key=true, autoincrement=True) UID= Column (Integer, ForeignKey ('users.id')) RID= Column (Integer, ForeignKey ('role.id'))    __table_args__=(UniqueConstraint (UID, RID, name='Uix_uid_rid'),    )#Create all Tablesdefinit_db (): Base.metadata.create_all (egine)#Delete all tables in the databasedefdrop_db (): Base.metadata.drop_all (egine)if __name__=='__main__': init_db ()
Note: Another way to set the foreign key is ForeignKeyConstraint ([' other_id '], [' othertable.other_id '])
2) Operation table
Table structure
 fromSQLAlchemyImportCreate_engine fromSqlalchemy.ext.declarativeImportDeclarative_base fromSQLAlchemyImportColumn,integer,string,foreignkey fromSqlalchemy.ormImportSessionmakeregine=create_engine ('Mysql+pymysql://[email Protected]:3306/db1?charset=utf8', max_overflow=5) Base=declarative_base ()#Many-to-one: Assume that multiple employees can belong to one department, and multiple departments cannot have the same employee (only when the company is created, the employee is used as a camel, and the employee has several jobs)classDep (Base):__tablename__='DEP'ID=column (integer,primary_key=true,autoincrement=True) Dname=column (String), nullable=false,index=True)classEmp (Base):__tablename__='EMP'ID=column (integer,primary_key=true,autoincrement=True) ename=column (String (+), nullable=false,index=True) dep_id=column (Integer,foreignkey ('dep.id'))definit_db (): Base.metadata.create_all (egine)defdrop_db (): Base.metadata.drop_all (Egine) drop_db () init_db () Session=sessionmaker (bind=Egine) Session=session ()
Increase
ROW_OBJ=DEP (dname=' sales '# by keyword, without specifying an ID, because it is a self-growing Session.add (row_ obj) session.add_all ([    dep (dname=' technology '),    dep (dname='  Operations '),    Dep (dname=' personnel '),]) Session.commit ()
By deleting
Session.query (DEP). Filter (Dep.id > 3). Delete () Session.commit ()
Change
Session.query (DEP). Filter (Dep.id > 0). Update ({'dname':'Wow, haha .'}) Session.query (DEP). Filter (Dep.id> 0). Update ({'dname':D ep.dname+'_SB'},synchronize_session=False) session.query (DEP). Filter (Dep.id> 0). Update ({'ID':D ep.id*100},synchronize_session='Evaluate') Session.commit ()
Check
#check all, take all fieldsRes=session.query (DEP). All ()#For row in Res:print (row.id,row.dname)#check all, take the specified fieldRes=session.query (Dep.dname). order_by (Dep.id). All ()#For row in Res:print (row.dname)Res=session.query (Dep.dname). First ()Print(RES)#(' Wow haha _sb ',)#Filter CheckRes=session.query (DEP). Filter (Dep.id > 1,dep.id <1000)#comma delimited, default to andPrint([(Row.id,row.dname) forRowinchRES])
Basic energy use of sqlalchemy