1. 数据库

[toc]

mysql
SQLAlchemy

1.1. mysql

1.1.1. 常用函数

字符串截取：

length(str)：返回str的长度。
left(str, length)：从左边开始截取，length是截取的长度。
right(str,length)：同上，不过方向相反
substring(str, pos, [length]) 或 substr(str, pos, [length])：截取子字符串，注意 pos 是从1开始，而不是0。
locate(sub_str, str)：查找子串在str中的位置。找不到返回0。如果是substr(str, pos, [length])函数，我可能要使用查找字符串中某个字符的位置问题。所有就要使用该函数。

1.1.2. 字符集

CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

设置字符集为utf-8，

COLLATE utf8_general_ci:数据库校对规则。该三部分分别为数据库字符集、解释不明白、区分大小写。

ci是case insensitive的缩写，意思是大小写不敏感；相对的是cs，即case sensitive。

还有一种是utf8_bin，是将字符串中的每一个字符用二进制数据存储，区分大小写。

1.1.3. mysql编程

drop PROCEDURE if EXISTS hggg;

CREATE PROCEDURE hggg(date int, count_ int)
BEGIN
  declare i int default 0;

    WHILE i<count_ DO
        INSERT INTO biz_12_daily_interest (date, market, ccy, account_id, amount, interest) 
        VALUES (date+i, 5, 'USD', 10294938, 10000, 0.1);
        set i = i+1;
    END WHILE;
END;

call hggg(20191101,20);

要删除procedure，不然下次再运行时会出错。

1.2. SQLAlchemy

1.2.1. 根据数据表自动生成model

Python可以利用 sqlacodegen ( sqlalchemy code generate)，根据数据库中的数据表，自动生成 SQLAlchemy model。

工具安装： pip install sqlacodegen

将db数据库中的数据表t1，t2（直接用逗号隔开），生成models.py。

sqlacodegen mysql+pymysql://user:pass@ip:port/db --tables t1,t2 > models.py
# or
sqlacodegen mysql+pymysql://user:pass@ip:port/db --tables t1,t2 --outfile models.py

不使用--tables选项时，默认生成数据库中所有的表。

1.2.2. filter和filter_by的区别

语法细节：filter用类名.属性名，比较用==，filter_by直接用属性名，比较用=。

filter不支持组合查询，只能连续调用filter来变相实现。filter_by的参数是**kwargs，直接支持组合查询。

q = sess.query(IS).filter(IS.node == node).filter(IS.password == password).all()
 = sess.query(IS).filter_by(node=node, password=password).all()

1.2.3. 批量插入数据

copy：http://www.manongjc.com/detail/8-fjqznbdwcmthgru.html

方式1：每次添加1个对象，然后commit

first_time = datetime.utcnow()
for i in range(10000):
    user = User(username=username + str(i), password=password)
    db.session.add(user)
    db.session.commit()
second_time = datetime.utcnow()
print((second_time - first_time).total_seconds())

# 38.14347s

方式2：bulk_save_objects

second_time = datetime.utcnow()
db.session.bulk_save_objects(
    [
        User(username=username + str(i), password=password)
        for i in range(10000)
    ]
)
db.session.commit()
third_time = datetime.utcnow()
print((third_time - second_time).total_seconds())

# 2.121589s

方式3：bulk_insert_mappings

third_time = datetime.utcnow()
db.session.bulk_insert_mappings(User,
    [ dict(username="NAME INSERT " + str(i), password=password)
      for i in range(10000) ]
)
db.session.commit()
fourth_time = datetime.utcnow()
print((fourth_time - third_time).total_seconds())

# 1.13548s

方式4：execute

fourth_time = datetime.utcnow()
db.session.execute(
    User.__table__.insert(),
    [{"username": 'Execute NAME ' + str(i), "password": password} for i in range(10000)]
)
db.session.commit()
five_time = datetime.utcnow()
print((five_time - fourth_time).total_seconds())

# 0.888822s

1.2.4. synchronize_session参数

该参数用于删除或者更新对象时：

session.query(User).filter(User.name == 'hg').delete()

session.query(User).filter(User.name == 'hg').update(values={}, synchronize_session='evaluate')

更新-值选项：

False：不要同步会话。这个选项是最有效的，并且在会话过期后是可靠的，这通常发生在commit（）之后，或者显式地使用expire_all（）。在到期之前，更新的对象可能仍然保留在会话中，其属性上的值已过时，这可能导致混淆结果。
'fetch' ：在更新之前执行select查询，以查找与更新查询匹配的对象。更新的属性在匹配的对象上过期。
'evaluate' ：在Python中直接对会话中的对象评估查询条件。如果未实现对条件的评估，则会引发异常。表达式计算器目前不考虑数据库和python之间不同的字符串排序规则。

删除-值选项

False -不要同步会话。这个选项是最有效的，并且在会话过期后是可靠的，这通常发生在commit（）之后，或者显式地使用expire_all（）。在过期之前，对象可能仍然保留在会话中，而实际上已被删除，如果通过get（）或已加载的集合访问这些对象，则可能导致混淆结果。
'fetch' -在删除之前执行选择查询，以查找与删除查询匹配且需要从会话中删除的对象。匹配的对象将从会话中删除。
'evaluate' -直接在会话中的对象上用python计算查询的条件。如果未实现对条件的评估，则会引发错误。表达式计算器目前不考虑数据库和python之间不同的字符串排序规则。

1.2.5. 输出方法对应的sql语句

方法1：直接输出

q = session.query(User).filter(User.name == 'hg')
print(q)   # or print(str(q))

不足：参数不会直接被替换成'hg'，而是：

WHERE user.name = %(name_1)s

方法2：可以进行参数替换

from sqlalchemy.dialects import mysql

q = session.query(User).filter(User.name == 'hg')

print(q.statement.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True}))

这种方式可以打印包含参数的执行语句，但是参数只包括数字和字符串等基本类型。其中dialects表示需要的数据库方言，我这里用的mysql。

1.2.6. 自定义排序

from sqlalchemy import case, text

q = session.query(User)
name_case = case(value=User.name, whens={'name_1': 1, 'name_2': 2, 'name_3': 3, 'name_4': 4})

symbol_text = text("LEFT(symbol,1)")
q = q.order_by(name_case).order_by(symbol_text)

# 不同字段同时比较
case_ = case( ( (User.name=='name_1', 1), (User.name=='name_2', 2), (User.updated==True, 3) ) )
# 注：没有出现 在排序规则里面的会排在最前面。如User.name中如果有不是name_1或name_2的字段，那么它会排在最前面。

首先通过特定的名字顺序进行排序，然后根据symbol属性的第一个字符进行排序。

1.2.7. 反射

通过反射获取数据库中某张数据表的类。

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base

engine = create_engine('mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(user, pwd, ip, port, dbname), pool_size=30, pool_recycle=300, pool_pre_ping=True, echo=False)
Base = automap_base()
Base.prepare(engine, reflect=True)
model_class = getattr(Base.classes, 'table_name')

数据库