SQLAlchemy DB Python SQL
2014-09-20
例子：搜索用户表 user 中字段 phone 包含 520 的行。
SQL
和 pymysql 等库一样的用：
keyword = '520'
conn.execute('select * from user where phone like "%%%s%%";' % keyword)
conn.execute('select * from user where phone like "%%%s%%";', keyword)
.like 方法
q = session.query(model.User.id, model.User.phone).filter(model.User.phone.like(f'%{keyword}%'))
qs = qs.all()
# print(qs.statement)
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE "user".phone LIKE :phone_1
对应的大小写不敏感方法有 ilike (lower("user".phone) LIKE lower(:phone_1))
还有：not_like, not_ilike
.contains 方法
print(session.query(model.User.id, model.User.phone).filter(model.User.phone.contains(keyword)).statement)
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE ("user".phone LIKE '%' || :phone_1 || '%')
.regexp_match 方法（1.4 新增）
对应的是 MySQL 支持的 REGEXP 操作符。
print(session.query(model.User.id, model.User.phone).filter(model.User.phone.regexp_match(keyword)).statement)
session.query(model.User.id, model.User.phone).filter(model.User.phone.regexp_match(keyword)).all()
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE "user".phone <regexp> :phone_1
.startswith 和 .endswith
print(session.query(model.User.id, model.User.phone).filter(model.User.phone.startswith(keyword)).statement)
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE ("user".phone LIKE :phone_1 || '%')
print(session.query(model.User.id, model.User.phone).filter(model.User.phone.endswith(keyword)).statement)
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE ("user".phone LIKE '%' || :phone_1)
.match 方法
对应的是数据库的 MATCH (col1,col2,...) AGAINST (expr [search_modifier]) 全文索引方法。
对单字段同样可用，不过需要先建立 FULLTEXT 索引。
print(session.query(model.User.id, model.User.phone).filter(model.User.phone.match(keyword)).statement)
session.query(model.User.id, model.User.phone).filter(model.User.phone.match(keyword)).all()
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE "user".phone MATCH :phone_1
参考资料与拓展阅读
DB MySQL
2014-08-20
顾名思义，就是集合类型。
set('a', 'b', ...)
每个字段可以是指定选项中的若干个（包含 0 个）。
DB MySQL
2014-03-06
几种形式
- 字符串，比如：
varchar(14)，存：yyyymmddHHMMSS 格式varchar(19)，存：yyyy-mm-dd HH:MM:SS 格式varchar(23)，存：yyyy-mm-dd HH:MM:SS.fff 格式varchar(26)，存：yyyy-mm-dd HH:MM:SS.ffffff 格式varchar(24)，存：yyyy-mm-ddTHH:MM:SS+0800 或 yyyy-mm-dd HH:MM:SS 0800 格式- 整形数
int 1970 - 2038time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime((1 << 31) - 1))
# 2038-01-19 03:14:07
- int unsigned 1970 - 2106time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime((1 << 32) - 1))
# 2106-02-07 06:28:15
- bigint 可以用到世界毁灭- 注意：也可以任意指定一个时间为起点，比如
2020-01-01 00:00:00
TIMESTAMP 类型- 相当于上面的
int 类型，2038 问题
DATETIME 类型
PS: DATE, TIME, YEAR 三种类型：
比较
1. 存储效率
2. 时间比较
3. 是否方便索引
DB MySQL
2014-03-03
优点
Timestamp 是按 UTC 时间进行存储。
2038 问题
MySQL 的 timestamp 类型挺好的，不过有个严重的 2038 问题，我不知道到时候这个类型会如何处理。
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
(1 << 31) / (3600 * 24 * 365)
根据时间范围，这应该是用的一个有符号的 32 位整型数实现的，为什么不用无符号数呢，不是又可以往后续 68 年么？
我预测，MySQL 的某一个新版本，将会增加一个 TIMESTAMP64 类型，采用 64 位有符号整形数存时间戳。
或直接将 TIMESTAMP 拓展到 64 位，先支持配置和命令行参数开启这个特性，然后后续版本将其视作默认设置。
参考资料与拓展阅读
DB MySQL
2013-09-17
MySQL 出现了一个问题，插入一个长字符串（二十几KB）结果导致被截断。
Python MySQLdb DB
2013-09-15
用 MySQLdb 操作数据库，插入数据之后发现数据库中依然为空，不知原因为何。
开启 mysqld 的 log 设置项之后发现日志文档中更有执行 sql 语句，直接复制语句在客户端中执行也没有问题，那么为什么通过 MySQLdb 的插入全部没有结果呢？
SQLite DB
2013-07-12
简介
- 嵌入式数据库
- 第一版 2000 年发布
- 公有领域
- 实现了大多数 SQL-92 标准，包括事务（ACID）
- 使用最广泛的 DB 引擎
命令
.help
.databases
.tables
.shema <tableName> # show sql
.fullschema
.quit
.mode list # 默认，没有头部，竖线隔开
.mode tabs # tab 隔开
.mode column # 可读性强些
.mode line # 像 MySQL \G
sqlite> .show
echo: off
eqp: off
explain: auto
headers: off
mode: list
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
stats: off
width:
filename: /tmp/history.db
sqlite> .dbinfo main
database page size: 4096
write format: 2
read format: 2
reserved bytes: 0
file change counter: 3110
database page count: 6669
freelist page count: 0
schema cookie: 7
schema format: 4
default cache size: 0
autovacuum top root: 0
incremental vacuum: 0
text encoding: 1 (utf8)
user version: 2
application id: 0
software version: 3033000
number of tables: 4
number of indexes: 6
number of triggers: 0
number of views: 0
schema size: 785
data version 2
常用操作
查看表结构
.schema tablename
pragma dbname.table_info(tablename)
pragma dbname.table_xinfo(tablename) # 包含虚拟表中的隐藏列
SELECT * FROM sqlite_master WHERE tbl_name = 'tablename';
工具
- SQLite Database Browser
- heidisql
- 浏览器拓展
- Navicat for SQLite
附：sqlite help
sqlite> .help
.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.connection [close] [#] Open or close an auxiliary database connection
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dump ?OBJECTS? Render database content as SQL
.echo on|off Turn command echo on or off
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of next command in spreadsheet
.exit ?CODE? Exit this program with return-code CODE
.expert EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
.filectrl CMD ... Run various sqlite3_file_control() operations
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help ?-all? ?PATTERN? Show help text for PATTERN
.import FILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of indexes
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.lint OPTIONS Report potential schema issues.
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE? Set output mode
.nonce STRING Disable safe mode for one command if the nonce matches
.nullvalue STRING Use STRING in place of NULL values
.once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE? Close existing database and reopen FILE
.output ?FILE? Send output to FILE or stdout if FILE is omitted
.parameter CMD ... Manage SQL parameter bindings
.print STRING... Print literal STRING
.progress N Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILE Read input from FILE
.recover Recover as much data as possible from corrupt db.
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.save FILE Write in-memory database into FILE
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.selftest ?OPTIONS? Run tests defined in the SELFTEST table
.separator COL ?ROW? Change the column and row separators
.session ?NAME? CMD ... Create or control sessions
.sha3sum ... Compute a SHA3 hash of database content
.shell CMD ARGS... Run CMD ARGS... in a system shell
.show Show the current values for various settings
.stats ?ARG? Show stats or turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
.testcase NAME Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ... Run various sqlite3_test_control() operations
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace ?OPTIONS? Output each SQL statement as it is run
.vfsinfo ?AUX? Information about the top-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set minimum column widths for columnar output