一个值得参考的Flask-SQLAlchemy查询案例

account = db.session.query(
    Account.account_id, Account.account_item,
    Account.account_money, AccountType.type_text,
    Account.account_date, Account.account_addition,
    User.user_email).outerjoin(
    AccountType, Account.account_type == AccountType.type_id).outerjoin(
    User, Account.account_user == User.user_id).order_by(
    Account.account_date.desc()).offset((page-1)*perpage).limit(perpage).all()

在这个例子中,我们查询的是:

按日期降序排列,每页perpage条记录,返回第page页的账目数据,将账目数据中的账目类型编号替换成类型名称,将账目数据中的用户编号替换成用户邮箱。

修改Docker中的系统时间

默认在Docker中无法使用常见的命令修改系统时间,会显示没有权限,但测试程序往往需要修改系统时间,可以通过如下方法解决该问题:

root@d26965edacdd:/# TZ=Etc/GMT
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 07:39:26 GMT 2017
root@d26965edacdd:/# TZ=Etc/GMT-1
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 08:39:35 +01 2017
root@d26965edacdd:/# TZ=Etc/GMT-2
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 09:39:40 +02 2017
root@d26965edacdd:/# TZ=Etc/GMT-3
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 10:39:45 +03 2017
root@d26965edacdd:/# TZ=Etc/GMT-4
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 11:42:57 +04 2017
root@d26965edacdd:/# TZ=Etc/GMT-5
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 12:43:05 +05 2017
root@d26965edacdd:/# TZ=Etc/GMT-6
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 13:43:10 +06 2017
root@d26965edacdd:/# TZ=Etc/GMT-7
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 14:43:15 +07 2017
root@d26965edacdd:/# TZ=Etc/GMT-8
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 15:43:20 +08 2017
root@d26965edacdd:/# TZ=Etc/GMT-9
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 16:43:25 +09 2017
root@d26965edacdd:/# TZ=Etc/GMT-10
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 17:43:30 +10 2017
root@d26965edacdd:/# TZ=Etc/GMT-11
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 18:43:35 +11 2017
root@d26965edacdd:/# TZ=Etc/GMT-12
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 19:43:39 +12 2017
root@d26965edacdd:/# TZ=Etc/GMT-13
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 20:43:43 +13 2017
root@d26965edacdd:/# TZ=Etc/GMT-14
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 21:43:50 +14 2017

root@d26965edacdd:/# TZ=Etc/GMT+1
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 06:44:57 -01 2017
root@d26965edacdd:/# TZ=Etc/GMT+2
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 05:45:01 -02 2017
root@d26965edacdd:/# TZ=Etc/GMT+3
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 04:45:05 -03 2017
root@d26965edacdd:/# TZ=Etc/GMT+4
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 03:45:09 -04 2017
root@d26965edacdd:/# TZ=Etc/GMT+5
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 02:45:13 -05 2017
root@d26965edacdd:/# TZ=Etc/GMT+6
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 01:45:17 -06 2017
root@d26965edacdd:/# TZ=Etc/GMT+7
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Tue Jan 31 00:45:24 -07 2017
root@d26965edacdd:/# TZ=Etc/GMT+8
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Mon Jan 30 23:45:28 -08 2017
root@d26965edacdd:/# TZ=Etc/GMT+9
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Mon Jan 30 22:45:32 -09 2017
root@d26965edacdd:/# TZ=Etc/GMT+10
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Mon Jan 30 21:45:37 -10 2017
root@d26965edacdd:/# TZ=Etc/GMT+11
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Mon Jan 30 20:45:53 -11 2017
root@d26965edacdd:/# TZ=Etc/GMT+12
root@d26965edacdd:/# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/# date
Mon Jan 30 19:46:00 -12 2017

我们测试一下实际效果:

root@d26965edacdd:/usr/development# cat test.py
import time
print(time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))
root@d26965edacdd:/usr/development# TZ=Etc/GMT
root@d26965edacdd:/usr/development# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/usr/development# python test.py
2017-01-31 07:50:13
root@d26965edacdd:/usr/development# TZ=Etc/GMT-8
root@d26965edacdd:/usr/development# ln -snf /usr/share/zoneinfo/$TZ /etc/localtime  && echo $TZ > /etc/timezone
root@d26965edacdd:/usr/development# python test.py
2017-01-31 15:50:19

注意

本方法是通过修改时区来“曲线救国”的。

Docker中Emacs支持退格

默认在Docker中,Emacs使用退格无法删除光标前的字符,而是显示帮助信息,这让人有些费解,可以采用如下方法解决:

emacs ~/.emacs
(global-set-key "\C-h" 'backward-delete-char-untabify)
(global-set-key "\d" 'delete-char)
C-x C-s
C-x C-c

这时再用emacs命令打开文件,就可以正常使用退格键删除光标前的字符了。

Flask-Sqlalchemy连接Mysql并支持中文

按照文章 https://codinglonglong.github.io/posts/dockerzhong-pei-zhi-flask-sqlalchemyhe-mysql/ 配置好数据库,在默认使用Flask-Sqlalchemy连接Mysql时,如果插入的数据是中文,会保存成问号。如果要正常保存中文,需要注意以下四点:

1、 .py文件本身是UTF-8的编码,这可以在开发工具的“文件编码”功能中进行查看。

2、 .py文件开头加上 # -*-coding:utf-8-*-

3、 连接字符串采用如下格式,注意最后的部分:

dbconnection = 'mysql+mysqlconnector://username:password@host:port/dbname?charset=utf8'

4、 默认Mysql是latin-1的编码,如下:

mysql> status           12
Current database:
Current user:.14 Distribroot@localhostebian-linux-gnu (x86_64) using readline 6.3
SSL:                    Not in use
Current pager:          ''dout
Using delimiter:        5.5.54-0ubuntu0.14.04.1 (Ubuntu)
Protocol version:       Localhost via UNIX socket
Server characterset:    latin1
Client characterset:    latin1
UNIX socket:cterset:    3 min 58 secqld/mysqld.sock
Uptime:
Threads: 3  Questions: 324  Slow queries: 0  Opens: 45  Flush tables: 1  Open tables: 31  Queries per second avg: 1.361
--------------

要通过修改配置文件,改成utf-8编码。

emacs /etc/mysql/my.cnf
[mysqld]
character_set_server = utf8
[client]
default-character-set = utf8
[mysql]
default-character-set = utf8

service mysql stop
mysqld

重启服务之后,可以看到:

mysql> status           1
Current database:
Current user:.14 Distribroot@localhostebian-linux-gnu (x86_64) using readline 6.3
SSL:                    Not in use
Current pager:          ''dout
Using delimiter:        5.5.54-0ubuntu0.14.04.1 (Ubuntu)
Protocol version:       Localhost via UNIX socket
Server characterset:    utf8
Client characterset:    utf8
UNIX socket:cterset:    6 secrun/mysqld/mysqld.sock
Uptime:
Threads: 1  Questions: 5  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.833
--------------

满足上述四点之后,删除掉原来包含问号的数据库,然后重新创建数据库,运行程序,可以看到中文被正常处理了。

https://github.com/longlongpicture/myblogpicture/raw/master/mysqlchinese.PNG

Docker中配置Flask-Sqlalchemy和Mysql

继文章 https://codinglonglong.github.io/posts/dockerpei-zhi-kai-fa-jing-xiang/ 之后,我们进一步配置数据库的部分。

1、Docker安装Mysql。

apt install mysql-server mysql-client

2、Docker下载安装mysql-connector-python。

apt install curl
curl -O https://cdn.mysql.com/Downloads/Connector-Python/mysql-connector-python-2.1.5.tar.gz
tar zxvf mysql-connector-python-2.1.5.tar.gz
cd mysql-connector-python-2.1.5
python setup.py install

3、Docker安装Flask-Sqlalchemy和Flask-Script。

pip install flask-sqlalchemy
pip install flask-script

4、为已有容器添加端口。

docker commit mypc newpc
docker stop mypc
docker run --name mynewpc -i -t -p 13306:3306 -p 8080:8080 -v ~/share/:/usr/development/  newpc /bin/bash

5、Docker登录Mysql数据库。

mysql -u root -p
create database test;
grant all privileges on *.* to root@'192.168.99.1' identified by 'root' with grant option;
flush privileges;
exit

6、Docker修改配置文件,我习惯用emacs,也可以用vim。

apt install emacs
apt-get install -f
emacs /etc/mysql/my.cnf
bind-address            = 0.0.0.0
C-x C-s
C-x C-c

7、重启mysql服务。

sudo service mysql stop
mysqld

8、主系统编辑测试网站main.py。

from flask import Flask
from flask.ext.script import Manager
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqlconnector://root:root@127.0.0.1:3306/test?charset=utf8'

db = SQLAlchemy(app)
manager = Manager(app)

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(320), unique=True)
    password = db.Column(db.String(32), nullable=False)

if __name__ == '__main__':
    db.create_all()
    manager.run()

9、开一个新的Docker terminal,启动测试网站。

cd /usr/development
python main.py runserver

10、主系统上打开MySQL Workbench,访问数据库。Hostname:192.168.99.100,Port:13306,Password:root。

https://github.com/longlongpicture/myblogpicture/raw/master/mysqlworkbench.PNG

注意

修改bind-address = 0.0.0.0非常重要,否则主系统访问不到Docker中的数据库。