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:[email protected]: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;

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

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

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

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

3 rows in set (0.00 sec)

在flask中建立mysql的表成功。

*: 參照《深入了解Flask》第2章