python---pymsql+flask_sqlalchemy连接数据库

pymsql+flask_sqlalchemy连接mysql

pip install pymysql
pip install flask_sqlalchemy

#!/usr/bin/env python
# -*- coding:utf-8 -*-

from flask import Flask
from flask import render_template
from flask import request
from flask import abort
from flask import flash
import logging
from flask_sqlalchemy import SQLAlchemy
import pymysql

import json

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

import time

app = Flask(__name__)

engine = create_engine('mysql+pymysql://root:password@10.16.2.64:11109/database?charset=utf8',
                       convert_unicode=True)
metadata = MetaData()
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()

def init_db():
    metadata.create_all(bind=engine)


@app.teardown_request
def shutdown_session(exception=None):
    db_session.remove()



from sqlalchemy import *

class XingTanRecCard(Base):
    __tablename__ = 'XingTan_RecCard'
    RecCardNo = Column(CHAR(45), primary_key=True)
    UserID = Column(Integer)
    StartTime = Column(Date)
    StopTime = Column(Date)
    UseTime = Column(TIMESTAMP)
    Status = Column(Integer)


    @staticmethod
    def to_json(data):
        return_json = list()
        for i in data:
            var_json = {
                'RecCardNo': i.RecCardNo,
                'UserID': i.UserID,
                'StartTime': str(i.StartTime),
                'StopTime': str(i.StopTime),
                'UseTime': i.UseTime,
                'Status': i.Status

            }

            return_json.append(var_json)
        return json.dumps(return_json)





logging.basicConfig(level=logging.WARNING,
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%a, %d %b %Y %H:%M:%S',
                    filename="log.log", filemode="w")


@app.route('/user', methods=['GET', 'POST'])
def show_user_profile():
    users = XingTanRecCard.query.filter_by(UserID="48873564").limit(10).all()
    return XingTanRecCard.to_json(users)





if __name__ == "__main__":
    app.debug = True
    app.run(host="127.0.0.1", port=9503)