from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)

#设置连接数据库的URL
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqldb://root:root@127.0.0.1:3306/test'

#设置每次请求结束后会自动提交数据库中的改动
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
#查询时会显示原始SQL语句
app.config['SQLALCHEMY_ECHO'] = 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)
    us = db.relationship('User', backref='role')


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True, index=True)
    email = db.Column(db.String(64),unique=True)
    pswd = db.Column(db.String(64))
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))
class City(db.Model):
    #表模型
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    provincename = db.Column(db.String(255))
    cityname = db.Column(db.String(255))
    usernumber = db.Column(db.Integer)


db.create_all()
ro1 = City(name='admin')
ro2 = Role(name='user')
db.session.add_all([ro1, ro2])
db.session.commit()
us1 = City(provincename='hubei', cityname='wuhan',  usernumber=ro1.id)
us2 = City(provincename='hubei', cityname='yichang',  usernumber=ro2.id)


db.session.add_all([us1, us2])
db.session.commit()

@app.route('/')
def index():
    cityList = City.query.order_by(City.id.desc()).all()
    return render_template("index.html", city_list=cityList)

app.run(debug=True)