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  # 多久之后对线程池中的线程进行一次连接的回收（重置）

)

conn = engine.raw_connection()

cursor = conn.cursor()   # pymysql的对象

cursor.execute("select * from t1")

result = cursor.fetchall()

cursor.close()

conn.close()

#################
或：

cur = engine.execute("select * from t1")

result = cur.fetchall()

cur.close()

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...]

链接字符串

import datetime

from sqlalchemy import create_engine

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index

Base = declarative_base()

class Users(Base):

    __tablename__ = 'users'

    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)  # default可以写一个callable对象

    # extra = Column(Text, nullable=True)

    __table_args__ = (

        # UniqueConstraint('id', 'name', name='uix_id_name'),  # 约束

        # Index('ix_id_name', 'name', 'email'),

    )

    __table_args__ = {

        'mysql_engine': 'InnoDB',  # 用InnoDB

        'mysql_charset': 'utf8'

    }

association_table = Table('association', Base.metadata,

    Column('left_id', Integer, ForeignKey('left.id')),

    Column('right_id', Integer, ForeignKey('right.id'))

)

# 多对一
class Hobby(Base):

    __tablename__ = 'hobby'

    id = Column(Integer, primary_key=True)

    caption = Column(String(50), default='篮球')

class Person(Base):

    __tablename__ = 'person'

    nid = Column(Integer, primary_key=True)

    name = Column(String(32), index=True, nullable=True)

    hobby_id = Column(Integer, ForeignKey("hobby.id"),nullable=False)  # 这个尽量别让他为空，保证数据不会误删除

    # 与生成表结构无关，仅用于查询方便

    hobby = relationship("Hobby", backref='pers')   # backref在有一个relationship的时候用
　　 # back_populates有同样的效果但是两个表都需要relationship字段
　　 # 例如：
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="parent")

class Child(Base):

    __tablename__ = 'child'

    id = Column(Integer, primary_key=True)

    parent_id = Column(Integer, ForeignKey('parent.id'))

    parent = relationship("Parent", back_populates="children")

# 关于backref ，主要目的是用来反向关联用的。如果两张表都有relationship字段就不要写backref属性。
# backref本质是用来替代另一个表中的relationship字段，如果仅仅是反查名称直接backref="字符串"就可以;
# 如果需要相关参数可以：backref=backref=backref("articles",cascade="all"),这样相当于在另一张表中写articles = relationship("Article",cascade='all')

# 多对多

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)

# 使用table定义中间表

article_tag = Table(
　　"article_tag",
　　Base.metadata,
　　Column("article_id",Integer,ForeignKey("article.id"),primary_key=True),
　　Column("tag_id",Integer,ForeignKey("tag.id"),primary_key=True)
)

class Article(Base):
　　__tablename__ = 'article'
　　id = Column(Integer,primary_key=True,autoincrement=True)
　　title = Column(String(50),nullable=False)

# tags = relationship("Tag",backref="articles",secondary=article_tag) 有一个模型定义了就好

　　def __repr__(self):
　　　　return "<Article(title:%s)>" % self.title

class Tag(Base):
　　__tablename__ = 'tag'
　　id = Column(Integer, primary_key=True, autoincrement=True)
　　name = Column(String(50), nullable=False)

　　articles = relationship("Article",backref="tags",secondary=article_tag)

　　def __repr__(self):
　　　　return "<Tag(name:%s)>" % self.name

# 一对一，通过uselist=true来决定# business_id=Column(Integer,ForeignKey('business.id'),uselist=True)
更多参见：http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-one

1. save-update：默认选项。在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中。这种行为就是save-update属性影响的。

2. delete：表示当删除某一个模型中的数据的时候，是否也删掉使用relationship和他关联的数据。

3. delete-orphan：表示当对一个ORM对象解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，不能用在多对多以及多对一上。并且还需要在子模型中的relationship中，增加一个single_parent=True的参数。 # single_parent代表只有一个关联的父表，但是实际上多个父表也能用。  delete-orphan只能用在多对一上

4. merge：默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作。

5. expunge：移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据库中删除。

6. all：是对save-update, merge, refresh-expire, expunge, delete几种的缩写。

cascade的选项

# sqlalchemy本身不支持需要插件：pip3 install sqlalchemy-utils

class Choose(base):

    type_choices = (

        (1, ..)

        (2, ...)

        )

    name = Column(ChoiceType(types_choices,Integer()))

choicetype选项

Base = declarative_base()

class Users(Base):

    __tablename__ = 'users'

engine = create_engine(

        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",

        max_overflow=0,  # 超过连接池大小外最多创建的连接

        pool_size=5,  # 连接池大小

        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错

        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）

    )

Base.metadata.create_all(engine)   # 创建继承base的所有的表

engine = create_engine(

        "mysql+pymysql://root:123@127.0.0.1:3306/s6?charset=utf8",

        max_overflow=0,  # 超过连接池大小外最多创建的连接

        pool_size=5,  # 连接池大小

        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错

        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）

    )

Base.metadata.drop_all(engine)   # 删除所有继承base的表

from sqlalchemy.orm import sessionmaker

Session=sessionmaker(bind=egine)

session=Session()

session.add(row_obj)

session.add_all([

    Dep(dname='技术'),

    Dep(dname='运营'),

])

# session.add对应的操作是session.expunge(对象) 仅仅是从session删除掉，不修改数据库
# session.merge

session.commit()

session.add_all([

    Emp(ename='fa',dep_id=1),
　　　　.....

    Emp(ename='iuo',dep_id=2),

])

session.commit()

# 依靠relationship插
person = Emp(name='张九', dep=Dep(dname='产品部'))  # 如果设置了relationship可以这么用
session.add(person)

hd = Dep(dname='产品部')
hd.emp = [Emp(name='fa'), Person(name='sa')]
session.add(hb)
session.commit()
session.close()  # session一定要关闭

# 多对多利用append

# article1 = Article(title="article1")
# article2 = Article(title="article2")
#
# tag1 = Tag(name='tag1')
# tag2 = Tag(name='tag2')
#
# article1.tags.append(tag1)
# article1.tags.append(tag2)
#
# article2.tags.append(tag1)
# article2.tags.append(tag2)
#
# session.add(article1)
# session.add(article2)

# 传统插法

session.add_all([

    Server(hostname='c1.com'),

    Server(hostname='c2.com'),

    Group(name='A组'),

    Group(name='B组'),

])

session.commit()

s2g = Server2Group(server_id=1, group_id=1)

session.add(s2g)

session.commit()

# 利用relationship

gp = Group(name='C组')

gp.servers = [Server(hostname='c3.com'),Server(hostname='c4.com')]

session.add(gp)

session.commit()

多对多添加

session.query(Users).filter(Users.id > 2).delete()

session.commit()

# foreignkey删除

# 默认是restrict 模式，在数据库层面是不能随便删除的，但是在orm层面会通过把null=true，让外键关联变为null，然后删除被关联的外键。

# e.g.

class User(Base):

    __tablename__ = 'user'

    id = Column(Integer,primary_key=True,autoincrement=True)

    username = Column(String(50),nullable=False)

class Article(Base):

    __tablename__ = 'article'

    id = Column(Integer, primary_key=True, autoincrement=True)

    title = Column(String(50),nullable=False)

    uid = Column(Integer,ForeignKey("user.id"),nullable=False) # 注意这个nullable

    author = relationship("User",backref='articles')

user = session.query(User).first()

session.delete(user)   # 如果article的uid的nullable为false这里才会报错，否则会成功删除

session.commit()

外键删除

session.query(Users).filter(Users.id > 0).update({"name" : ""})

session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + ""}, synchronize_session=False)

session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")

session.commit()

用于query在进行delete or update操作时，对session的同步策略。

false代表直接同步或删除，还有"fetch","evaluate"选项

r1 = session.query(Users).all()

r2 = session.query(Users.name.label('xx'), Users.age).all()

r3 = session.query(Users).filter(Users.name == "alex").all()

r4 = session.query(Users).filter_by(name='alex').all()  # 这个方法只能是=

r5 = session.query(Users).filter_by(name='alex').first()  # first找第一个

r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()  #条件，填充，防注入

r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()

基本查询

# 正查

v = session.query(Person).first()

print(v.name)

print(v.hobby.caption)

# 反查

v = session.query(Hobby).first()

print(v.caption)

print(v.pers)

foreignKey

# 模糊匹配

ret = session.query(Users).filter(Users.name.like('e%')).all()

ret = session.query(Users).filter(~Users.name.like('e%')).all()

# limit

ret = session.query(Users)[1:2]

# order by

ret = session.query(Users).order_by(Users.name.desc()).all()

ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

#group by + having

from sqlalchemy.sql import func

ret = session.query(

    func.max(Users.id),

    func.sum(Users.id),

    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

# union

q1 = session.query(Users.name).filter(Users.id > 2)

q2 = session.query(Favor.caption).filter(Favor.nid < 2)

ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)

q2 = session.query(Favor.caption).filter(Favor.nid < 2)

ret = q1.union_all(q2).all()

复杂查询条件

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all() # 只能左查，如果右查将Person和Favor互换

连表查询

from sqlalchemy import and_, or_

ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()

ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()

# 正常filter用,隔开就是and

and 和 or

ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()

ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()

ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all() # ~代表非

ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()

in和between操作

cursor = session.execute('select * from users')

result = cursor.fetchall()

cursor = session.execute('insert into users(name) values(:value)',params={"value":'wupeiqi'})

session.commit()

执行原生sql

subqry = session.query(func.count(Server.id).label("sid")).filter(Server.id == Group.id).correlate(Group).as_scalar()

result = session.query(Group.name, subqry)

"""

SELECT `group`.name AS group_name, (SELECT count(server.id) AS sid

FROM server

WHERE server.id = `group`.id) AS anon_1

FROM `group`

"""

关联子查询

from sqlalchemy.orm import sessionmaker

from sqlalchemy import create_engine

from sqlalchemy.orm import scoped_session

from models import Users

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/s6", max_overflow=0, pool_size=5)

Session = sessionmaker(bind=engine)

session = scoped_session(Session)

obj1 = Users(name="alex1")

session.add(obj1)

session.commit()

session.close()

用scoped_session保证线程安全