MySQL Eight: ORM Framework SQLAlchemy

First, Introduction

SQLAlchemy is an ORM framework in the Python programming language that builds on database APIs and uses relational object mappings for database operations, in short: converting objects to SQL and then executing SQL using the data API and getting execution results.

1. Installation

PIP3 Install SQLAlchemy

2. Architecture and Process

The first step: the user submits the command via an ORM object

Step two: Convert the command to SQLAlchemy Core (Schema/types SQL Expression Language) to SQL

Step three: Use Engine/connectionpooling/dialect for database operations

1) match the user's pre-configured Egine

2) egine Remove a link from the connection pool

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

The above process analysis can be broadly divided into two stages:

First stage (Process 1-2): Replace the SQLAlchemy object with an executable SQL statement

Second phase (Process 3): Handing SQL statements to database execution

If we do not rely on sqlalchemy conversion and write our own SQL statement, it means that can be executed directly from the second phase, we can only execute pure SQL statements with SQLAlchemy, the code is as follows:

From SQLAlchemy import Create_engine

#1, prepare

#需要事先安装好pymysql

#需要事先创建好数据库: Create Database db1 charset UTF8;

#2, creating engines

Egine=create_engine (' Mysql+pymysql://[email Protected]/db1?charset=utf8 ')

#3, Execute SQL

Egine.execute (' CREATE table if not ' EXISTS t1 (id int PRIMARY KEY auto_increment,name char (32));

Cur=egine.execute (' INSERT into T1 values (%s,%s); ', [(1, "Egon1"), (2, "Egon2"), (3, "Egon3")]) #按位置传值

Cur=egine.execute (' INSERT into T1 values (% (ID) s,% (name) s); ', name= ' Egon4 ', id=4) #按关键字传值

#4 the newly inserted row's self-increment ID

Print (CUR.LASTROWID)

#5, query

Cur=egine.execute (' select * from T1 ')

Cur.fetchone () #获取一行

Cur.fetchmany (2) #获取多行

Cur.fetchall () #获取所有行

3. DB API

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:

1) Mysql-python

Mysql+mysqldb://<user>:<password>@

2) Pymysql

Mysql+pymysql://<username>:<password>@

3) Mysql-connector

Mysql+mysqlconnector://<user>:<password>@

4) Cx_oracle

Oracle+cx_oracle://user:[email protected]:p ort/dbname[?key=value&key=value ...]

Second, create a table

In ORM:

Class ===> Table

Object ==> a row of records in a table

Four tables: line of business, service, user, role, use ORM to create them, and establish their direct relationships

Third, increase and revise the search

Table structure

1, increase

2. By deleting

3, change

4. Check

Iv. Other Inquiries related

1. Prepare tables and data

2. Condition, wildcard, limit, sort, group, list, combination

3, sub-query

There are three forms of subqueries, note: The SQL for subqueries must be wrapped in parentheses, especially in form three.

Form one: A subquery is used as a table, calling subquery ()

Form two: Sub-query as in range, call In_

Form Three: Sub-query as a field after select, call As_scalar ()

V. Positive investigation and counter-investigation

1. Table modification

2, the standard connection table query

#示例: Querying employee names and their department names

Res=session.query (emp.ename,dep.dname). Join (DEP) #迭代器

For row in Res:

Print (row[0],row[1]) #等同于print (row.ename,row.dname)

3, based on the relationship, reverse investigation

#SQLAlchemy的relationship在内部帮我们做好表的链接

#查询员工名与其部门名 (Positive check)

Res=session.query (EMP)

For row in Res:

Print (Row.ename,row.id,row.depart.dname)

#查询部门名以及该部门下的员工 (Reverse check)

Res=session.query (DEP)

For row in Res:

# Print (Row.dname,row.xxoo)

Print (Row.dname,[r.ename for R in Row.xxoo])

MySQL Eight: ORM Framework SQLAlchemy