flask与mysql_连接flask与mysql

数据库

mysql中建立数据库，并建立账号tester，密码tester。

SHOW DATABASES ;

CREATE DATABASE IF NOT EXISTS `flask-test`;

CREATE USER 'tester'@'localhost' IDENTIFIED BY 'tester';

GRANT ALL PRIVILEGES ON `flask-test`.* TO 'tester'@'localhost';

USE `flask-test`;

配置flask

在flask项目目录下编写三个文件

1 config.py

最主要的是URI。

按照mysql+pymysql://:@/[?]编写，配置时要放在引号中。

SQLALCHEMY_ECHO = True会把查询语句打印到console中。

class Config(object):

pass

class ProdConfig(Config):

pass

class DevConfig(Config):

DEBUG = True

SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://tester:tester@localhost:3306/flask-test?charset=utf8'

SQLALCHEMY_ECHO = True

2 main.py

需要安装第三方包：

sqlalchemy

pymysql

flask-script

首先，引入SQLAlchemy包。

然后，用db=SQLAlchemy(app)实例化。

User类中继承db.Model。

__tablename__ = 'user_table_name'设置表名。

id、username和password是字段名。

__init__与__repr__可以不用定义。

from flask import Flask

from config import DevConfig

from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config.from_object(DevConfig)

db = SQLAlchemy(app)

class User(db.Model):

__tablename__ = 'user_table_name'

id = db.Column(db.Integer(), primary_key=True)

username = db.Column(db.String(255))

password = db.Column(db.String(255))

def __init__(self, username):

self.username = username

def __repr__(self):

return "".format(self.username)

3 manager.py

从main中引入db与User

make_shell_context()函数中，return的dict参数中加入db与User。

from flask.ext.script import Manager, Server

from main import app, db, User

manager = Manager(app)

manager.add_command("server", Server())

@manager.shell

def make_shell_context():

return dict(app=app, db=db, User=User)

if __name__ == "__main__":

manager.run()

shell运行

在terminal中运行

$ python manager.py shell

进入shell环境后输入db.creat_all()得到sqlalchemy转变成sql语句的logging。

>>> db.create_all()

2017-02-05 21:28:36,073 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

2017-02-05 21:28:36,073 INFO sqlalchemy.engine.base.Engine {}

2017-02-05 21:28:36,075 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()

2017-02-05 21:28:36,075 INFO sqlalchemy.engine.base.Engine {}

2017-02-05 21:28:36,075 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'

2017-02-05 21:28:36,075 INFO sqlalchemy.engine.base.Engine {}

2017-02-05 21:28:36,077 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1

2017-02-05 21:28:36,077 INFO sqlalchemy.engine.base.Engine {}

2017-02-05 21:28:36,083 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1

2017-02-05 21:28:36,083 INFO sqlalchemy.engine.base.Engine {}

2017-02-05 21:28:36,084 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1

2017-02-05 21:28:36,084 INFO sqlalchemy.engine.base.Engine {}

2017-02-05 21:28:36,085 INFO sqlalchemy.engine.base.Engine DESCRIBE `user_table_name`

2017-02-05 21:28:36,085 INFO sqlalchemy.engine.base.Engine {}

2017-02-05 21:28:36,086 INFO sqlalchemy.engine.base.Engine ROLLBACK

2017-02-05 21:28:36,087 INFO sqlalchemy.engine.base.Engine

CREATE TABLE user_table_name (

id INTEGER NOT NULL AUTO_INCREMENT,

username VARCHAR(255),

password VARCHAR(255),

PRIMARY KEY (id)

)

2017-02-05 21:28:36,087 INFO sqlalchemy.engine.base.Engine {}

2017-02-05 21:28:36,104 INFO sqlalchemy.engine.base.Engine COMMIT

查询mysql数据库。

mysql> show tables;

+----------------------+

| Tables_in_flask-test |

+----------------------+

| user_table_name |

+----------------------+

1 row in set (0.00 sec)

mysql> desc user_table_name;

+----------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| username | varchar(255) | YES | | NULL | |

| password | varchar(255) | YES | | NULL | |

+----------+--------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

在flask中创建mysql的表成功。

*: 参照《深入理解Flask》第2章