-
Notifications
You must be signed in to change notification settings - Fork 21
Expand file tree
/
Copy pathMySQL_test02.py
More file actions
77 lines (63 loc) · 2.05 KB
/
Copy pathMySQL_test02.py
File metadata and controls
77 lines (63 loc) · 2.05 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
# coding:utf-8
import os
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
import pymysql
basedir = os.path.abspath(os.path.dirname(__file__))
app = Flask(__name__)
app.debug = True
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost/myTestSQL_db'
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)
class Role(db.Model):
__tablename__ = 'roles'
id = db.Column(db.Integer, primary_key = True)
name = db.Column(db.String(64), unique = True)
users = db.relationship('User', backref = 'role')
def __repr__(self):
return '<Role %r>' % self.name
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key = True)
username = db.Column(db.String(64), unique = True, index = True)
role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))
def __repr__(self):
return '<User %r>' % self.username
db.drop_all()
db.create_all()
admin_role = Role(name='Admin')
mod_role = Role(name='Moderator')
user_role = Role(name='User')
user_john = User(username='john', role=admin_role)
user_susan = User(username='susan', role=user_role)
user_david = User(username='david', role=user_role)
print(admin_role.id)
print(mod_role.id)
print(user_role.id)
# 数据库会话（事务）
db.session.add(admin_role)
db.session.add(mod_role)
db.session.add(user_role)
db.session.add(user_john)
db.session.add(user_susan)
db.session.add(user_david)
# db.session.add_all([admin_role, mod_role, user_role, user_john, user_susan, user_david])
db.session.commit()
print(admin_role.id)
print(mod_role.id)
print(user_role.id)
##### modify
print('before modify:' + admin_role.name)
admin_role.name = 'Administrator'
db.session.add(admin_role)
db.session.commit()
print('after modify:' + admin_role.name)
#### delete
db.session.delete(mod_role)
db.session.commit()
#### Query
print(Role.query.all())
print(User.query.all())
print(User.query.filter_by(role=user_role).all())
print(str(User.query.filter_by(role=user_role)))