SQLAlchemy

介绍

SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

安装：

pip install sqlalchemy

组成部分：

• Engine，框架的引擎
• Connection Pooling ，数据库连接池
• Dialect，选择连接数据库的DB API种类
• Schema/Types，架构和类型
• SQL Exprression Language，SQL表达式语言

 SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

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:pass@host:port/dbname[?key=value&key=value...]
    
更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html

使用

1.执行原生SQL

import time
import threading
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.engine.base import Engine
 
engine = create_engine(
    "mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8",
    max_overflow=0,  # 超过连接池大小外最多创建的连接
    pool_size=5,  # 连接池大小
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
)
 
 
def task(arg):
    conn = engine.raw_connection()
    cursor = conn.cursor()
    cursor.execute(
        "select * from t1"
    )
    result = cursor.fetchall()
    cursor.close()
    conn.close()
 
 
for i in range(20):
    t = threading.Thread(target=task, args=(i,))
    t.start()

2.ORM

创建表

import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, UniqueConstraint, Index

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'  # 表名
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    }

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=False)
    email = Column(String(32), unique=True)
    ctime = Column(DateTime, default=datetime.datetime.now)    

    # __table_args__ = (
        # UniqueConstraint('id', 'name', name='uq_id_name'),  # 联合唯一
        # Index('') # 索引
    # )

engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/day145?charset=utf8",
        max_overflow=0,  # 超过连接池大小后最多再创建的连接数
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 设置连接池中没有线程后最多等待的时间
        pool_recycle=-1,  # 多久之后对连接池中的线程进行一次连接的回收
    )


def init_db():
    '''
    根据类创建数据库表
    :return:
    '''
    Base.metadata.create_all(engine)


def drop_db():
    '''
    根据类删除数据库表
    :return:
    '''
    Base.metadata.drop_all(engine)


if __name__ == '__main__':
    init_db()

#!/usr/bin/env python
# -*- coding: utf-8 -*-
__author__ = 'Wu'

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

Base = declarative_base()


class Hobby(Base):
    __tablename__ = 'hobby'

    id = Column(Integer, primary_key=True)
    caption = Column(String(32), default='羽毛球')


class Person(Base):
    __tablename__ = 'person'
    __mapper_args__ = {'column_prefix': 'ch_'}  # 为列名设置前缀

    id = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    hobby_id = Column(Integer, ForeignKey('hobby.id'))

    hobby = relationship('hobby', backref='person')


engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/day145?charset=utf8",
        max_overflow=0,  # 超过连接池大小后最多再创建的连接数
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 设置连接池中没有线程后最多等待的时间
        pool_recycle=-1,  # 多久之后对连接池中的线程进行一次连接的回收
    )


def init_db():
    Base.metadata.create_all(engine)


def drop_db():
    Base.metadata.drop_all(engine)


init_db()

#!/usr/bin/env python
# -*- coding: utf-8 -*-
__author__ = 'Wu'

class Server2Group(Base):
    __tablename__ = 'server2group'
    id = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))


class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)

    # 与生成表结构无关，仅用于查询方便
    servers = relationship('Server', secondary='server2group', backref='groups')


class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False


engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/day145?charset=utf8",
        max_overflow=0,  # 超过连接池大小后最多再创建的连接数
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 设置连接池中没有线程后最多等待的时间
        pool_recycle=-1,  # 多久之后对连接池中的线程进行一次连接的回收
    )


def init_db():
    Base.metadata.create_all(engine)


def drop_db():
    Base.metadata.drop_all(engine)


init_db()

查询