Basic energy use of sqlalchemy

Source: Internet
Author: User

Tags: database connection statement foreign lang. com SQL Date commit set

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>@<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:[email protected]:p ort/dbname[?key=value&key=value ...]
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: