SqlAlchemy and additions to SQL statements

ORM Lake

Once upon a time, the programmer because of fear SQL and in the development of the careful writing of SQL, heart always panic, in case of careless SQL statement error, bad database what to do? or in order to get some data, what inside and outside connections, function stored procedures and so on. No doubt, do not understand these, how all feel twisted, maybe one day jumped into the pit, called every day should not, shouted to not answer.

ORM, let the fear of SQL developers, in the pit saw the climb out of the rope, as if the sky is not so dark, at least again dark, we also have eyes. As the name implies, ORM Object relational mapping, in short, is to table map one (table) of the database into a programming language class (Class).

pythonThere are many of the more famous ORM frameworks, and the top of the list SQLAlchemy is the framework of the Python world ORM . Rivers and lakes,,, peewee strom pyorm SQLObject each lead, but ultimately still sqlalchemy disdain.

SQLAlchemy Introduction

The SQLAlchemy is divided into two parts, one for ORM object mapping and the other for the core SQL expression . The first one is very well understood, the pure ORM, the latter is not ORM, but DBAPI the encapsulation, of course, also provides a lot of methods to avoid directly writing SQL, but through some SQL expression. The use of SQLAlchemy can be divided into three ways.

• Using SQL expression, write SQL expressions by SQLAlchemy, Introduction to write SQL
• Write SQL directly using raw SQL
• Use ORM to avoid writing SQL directly

This article first explores the use of the SQL Expresstion section of SQLAlchemy. The main is to follow the official SQL Expression Language Tutorial. Introduction

Why learn SQL Expresstion instead of directly on ORM? Because the latter two are the basis of ORM. And, that is, not using ORM, the latter two can do a good job, and the code is more readable. Use SQLAlchemy purely as DBAPI. First, SQLAlchemy built-in database connection pool, solve the connection operation related cumbersome processing. Secondly, to provide convenient and powerful log function, finally, complex query statements, relying on simple ORM is more difficult to achieve.

Actual Connection Database

First you need to import the SQLAlchemy library and then establish a database connection, which is used here mysql . by create_engine means of

from sqlalchemy import create_engineengine = create_engine("mysql://root:@localhost:3306/webpy?charset=utf8",encoding="utf-8", echo=True)

create_enginemethod to make a database connection and return a DB object. The parameters inside are indicated

Database type://Username: password (no password is empty, do not fill in) @ Database host address/database name? code
echo = True is to facilitate console logging output some SQL information, default is False

Through this engine object can be directly execute query, for example, engine.execute("SELECT * FROM user") can also get connections through the engine query, such as conn = engine.connect() through the conn.execute() method of query. What is the difference between the two?

• The way to execute SQL directly using the engine's execute, called connnectionless执行 ,
• Get conn with Engine.connect () and execute SQL via Conn, calledconnection执行
  The main difference is whether the transaction mode is used, and if the transaction is not involved, the two methods have the same effect. The latter is recommended for use on the website.

Defining tables

Define the data table, in order to do the operation of SQL expression, after all, the SQL expression table is determined by SQLAlchemy, if the database already exists the data table needs to be defined? Of course, here is actually a mapping relationship, if not specified, the query expression is not known to be attached to the operation of that table, of course, when defined, note the table name and field names, code and data must be consistent. Once defined, you can create a data table that, once created, runs the created code again and the database is not created.

# -*- coding: utf-8 -*-__author__ = ‘ghost‘from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey# 连接数据库 engine = create_engine("mysql://root:@localhost:3306/webpy?charset=utf8",encoding="utf-8", echo=True)# 获取元数据metadata = MetaData()# 定义表user = Table(‘user‘, metadata,        Column(‘id‘, Integer, primary_key=True),        Column(‘name‘, String(20)),        Column(‘fullname‘, String(40)),    )address = Table(‘address‘, metadata,        Column(‘id‘, Integer, primary_key=True),        Column(‘user_id‘, None, ForeignKey(‘user.id‘)),        Column(‘email‘, String(60), nullable=False)    )# 创建数据表，如果数据表存在，则忽视metadata.create_all(engine)# 获取数据库连接conn = engine.connect()

inserting insert

With data tables and connection objects, the database operation is simple.

>>> i = User.insert () # Use Query >>> I <sqlalchemy.sql.dml.insert object at 0x0000000002637748>>& gt;> Print I # internal component SQL statement INSERT into "user" (ID, Name, fullname) VALUES (: ID,: Name,: FullName) >>> u = dict (n Ame= ' Jack ', Fullname= ' Jack Jone ') >>> r = Conn.execute (i, **u) # Executes the query, the first one is the query object, the second parameter is an Insert data dictionary, if multiple objects are inserted, Put the object dictionary in the list >>> R<sqlalchemy.engine.result.resultproxy objects at 0x0000000002ef9390>>>> R.inserted_primary_key # Returns the Insert Row primary key id[4l]>>> addresses[{' user_id ': 1, ' email ': ' [email protected] '}, {' user_id ': 1, ' email ': ' [email protected] '}, {' user_id ': 2, ' email ': ' [email protected] '}, {' user_id ': 2, ' Email ': ' [email protected]ol.com '}]>>> i = Address.insert () >>> r = Conn.execute (i, addresses) # plug   Enter multiple records >>> R<sqlalchemy.engine.result.resultproxy object at 0x0000000002eb5080>>>> r.rowcount #返回影响的行数4L >>> i = User.insert (). VALUES (Name= ' Tom', fullname= ' Tom Jim ') >>> i.compile () <sqlalchemy.sql.compiler.sqlcompiler object at 0x0000000002f6f390 >>>> print i.compile () INSERT into "user" (name, FullName) VALUES (: Name,: FullName) >>> print I.compi Le (). params{' fullname ': ' Tom Jim ', ' name ': ' Tom '}>>> r = Conn.execute (i) >>> r.rowcount1l

Query Select

The query method is very flexible, most of the time use Sqlalchemy.sql the following select methods

>>> s = select([user])  # 查询 user表>>> s<sqlalchemy.sql.selectable.Select at 0x25a7748; Select object>>>> print sSELECT "user".id, "user".name, "user".fullname FROM "user"

If you need to query a custom field, use the user's cloumn object, for example

>>> user.c  # 表 user 的字段column对象<sqlalchemy.sql.base.ImmutableColumnCollection object at 0x0000000002E804A8>>>> print user.c[‘user.id‘, ‘user.name‘, ‘user.fullname‘]>>> s = select([user.c.name,user.c.fullname])>>> r = conn.execute(s)>>> r<sqlalchemy.engine.result.ResultProxy object at 0x00000000025A7748>>>> r.rowcount  # 影响的行数5L>>> ru = r.fetchall()  >>> ru[(u‘hello‘, u‘hello world‘), (u‘Jack‘, u‘Jack Jone‘), (u‘Jack‘, u‘Jack Jone‘), (u‘jack‘, u‘jack Jone‘), (u‘tom‘, u‘tom Jim‘)]>>> r  <sqlalchemy.engine.result.ResultProxy object at 0x00000000025A7748>>>> r.closed  # 只要 r.fetchall() 之后，就会自动关闭 ResultProxy 对象True

Query two tables at a time

>>> s = select([user.c.name, address.c.user_id]).where(user.c.id==address.c.user_id)   # 使用了字段和字段比较的条件>>> s<sqlalchemy.sql.selectable.Select at 0x2f03390; Select object>>>> print sSELECT "user".name, address.user_id FROM "user", address WHERE "user".id = address.user_id

Operator

>>> print user.c.id == address.c.user_id  # 返回一个编译的字符串"user".id = address.user_id>>> print user.c.id == 7"user".id = :id_1   # 编译成为带参数的sql 语句片段字符串>>> print user.c.id != 7"user".id != :id_1>>> print user.c.id > 7"user".id > :id_1>>> print user.c.id == None"user".id IS NULL>>> print user.c.id + address.c.id   # 使用两个整形的变成 +"user".id + address.id>>> print user.c.name + address.c.email  # 使用两个字符串 变成 ||"user".name || address.email

Operation Connection

The connection here refers to the connection of the logical operator when the condition is queried, i.e., and or andnot

>>> print and_(        user.c.name.like(‘j%‘),        user.c.id == address.c.user_id,        or_(            address.c.email == ‘[email protected]‘,            address.c.email == ‘[email protected]‘        ),        

The resulting result is a compiled SQL statement fragment, which looks at a complete example below

>>> se_sql = [(user.c.fullname +", " + address.c.email).label(‘title‘)]>>> wh_sql = and_(              user.c.id == address.c.user_id,              user.c.name.between(‘m‘, ‘z‘),              or_(                  address.c.email.like(‘%@aol.com‘),                  address.c.email.like(‘%@msn.com‘)              )         )>>> print wh_sql"user".id = address.user_id AND "user".name BETWEEN :name_1 AND :name_2 AND (address.email LIKE :email_1 OR address.email LIKE :email_2)>>> s = select(se_sql).where(wh_sql)>>> print sSELECT "user".fullname || :fullname_1 || address.email AS title FROM "user", address WHERE "user".id = address.user_id AND "user".name BETWEEN :name_1 AND :name_2 AND (address.email LIKE :email_1 OR address.email LIKE :email_2)>>> r = conn.execute(s)>>> r.fetchall()

Using RAW SQL

When encountering a responsible SQL statement, you can use the text function below Sqlalchemy.sql. The SQL statement wrapper for the string is compiled into the SQL object required by execute execution. For example:

>>> text_sql = "SELECT id, name, fullname FROM user WHERE id=:id"  # 原始sql语句，参数用（ ：value）表示>>> s = text(text_sql)>>> print sSELECT id, name, fullname FROM user WHERE id=:id>>> s<sqlalchemy.sql.elements.TextClause object at 0x0000000002587668>>>> conn.execute(s, id=3).fetchall()   # id=3 传递：id参数[(3L, u‘Jack‘, u‘Jack Jone‘)]

Connect Join

There are join outejoin two methods connected, join has two parameters, the first is join table, the second is on condition, joing must be select_from used after

  >>> print user.join (address) "User" join address on "user". id = Address.user  _ID # Because the foreign key is turned on, the join can only recognize the on condition >>> print user.join (address, address.c.user_id==user.c.id) # Manually specify the on condition "user" JOIN address on address.user_id = "user" .id>>> s = select ([User.c.name, Address.c.email]). Select_from ( User.join (Address, user.c.id==address.c.user_id) # Jion SQL statements need to be combined with the Select_from method >>> s< Sqlalchemy.sql.selectable.Select at 0x2eb63c8; Select object>>>> Print sselect "user". Name, Address.email from "user" JOIN address on "user". id = address.use R_id>>> Conn.execute (s). Fetchall () [(U ' Hello ', U ' [email protected] '), (U ' hello ', u ' [email  Protected], (U ' hello ', U ' [email protected] '), (U ' hello ', U ' [email protected] '), (U ' Jack ', U ' [email  Protected], (U ' Jack ', U ' [email protected] '), (U ' Jack ', U ' [email protected] '), (U ' Jack ', U ' [email  Protected] ')]  

More complex connections refer to the official documentation.

Sort grouped pagination

Sorting using order_by methods, grouping is group_by , paging is naturally limit 和 offset two method mates

>>> s = select([user.c.name]).order_by(user.c.name)  # order_by>>> print sSELECT "user".name FROM "user" ORDER BY "user".name>>> s = select([user]).order_by(user.c.name.desc())>>> print sSELECT "user".id, "user".name, "user".fullname FROM "user" ORDER BY "user".name DESC>>> s = select([user]).group_by(user.c.name)       # group_by>>> print sSELECT "user".id, "user".name, "user".fullname FROM "user" GROUP BY "user".name>>> s = select([user]).order_by(user.c.name.desc()).limit(1).offset(3)  # limit(1).offset(3)>>> print sSELECT "user".id, "user".name, "user".fullname FROM "user" ORDER BY "user".name DESC LIMIT :param_1 OFFSET :param_2[(4L, u‘jack‘, u‘jack Jone‘)]

Updating update

The front are some queries, update and insert the method is very similar, are the table below the method, the difference is that update one more where method to select the filter

>>> s = user.update()>>> print sUPDATE "user" SET id=:id, name=:name, fullname=:fullname>>> s = user.update().values(fullname=user.c.name)           # values 指定了更新的字段>>> print sUPDATE "user" SET fullname="user".name>>> s = user.update().where(user.c.name == ‘jack‘).values(name=‘ed‘)  # where 进行选择过滤>>> print s UPDATE "user" SET name=:name WHERE "user".name = :name_1>>> r = conn.execute(s)>>> print r.rowcount         # 影响行数3

There is also a high-level usage, that is, a command to execute multiple records of the update, need to use the bindparam method

>>> s = user.update().where(user.c.name==bindparam(‘oldname‘)).values(name=bindparam(‘newname‘))   # oldname 与下面的传入的从拿书进行绑定，newname也一样>>> print sUPDATE "user" SET name=:newname WHERE "user".name = :oldname>>> u = [{‘oldname‘:‘hello‘, ‘newname‘:‘edd‘},{‘oldname‘:‘ed‘, ‘newname‘:‘mary‘},{‘oldname‘:‘tom‘, ‘newname‘:‘jake‘}]>>> r = conn.execute(s, u)>>> r.rowcount5L

Remove Delete

Delete is easier, call delete the method, without the where filter, delete all data, but do not drop the table, equal to empty the data table

>>> r = conn.execute(address.delete()) # 清空表>>> print r<sqlalchemy.engine.result.ResultProxy object at 0x0000000002EAF550>>>> r.rowcount8L>>> r = conn.execute(users.delete().where(users.c.name > ‘m‘)) # 删除记录>>> r.rowcount3L

At this point, the basic usage of the SQLAlchemy SQL expression is complete, and more in-depth reading can be viewed in the official APISQL Statements and Expressions API

Original link: HTTP://WWW.JIANSHU.COM/P/E6BBA189FCBD

SqlAlchemy and additions to SQL statements