python用于写后台接口

这里只是作为一些博客简单的拼接

mySQL安装
还有一个可视化工具的安装，我用的Navicat

新建数据库
新建表

python连接数据库

import pymysql
 
# 打开数据库连接(加入默认db，否则会找不到对应的db)
conn = pymysql.connect(host='localhost',
                       port=3306,
                       user='root',
                       passwd='xxx',
                       db='xxx',
                       charset = 'utf8'
                       )
                
# 使用 cursor() 方法创建一个游标对象 cursor                      
cursor = conn.cursor()
 
# 使用 execute()  方法执行 SQL 查询
cursor.execute("show databases;")
sqlNum = "select stuNum from userdb where stuNum='xxx'"
res = cursor.execute(sqlNum)
print(res, 'res')
cursor.execute("show databases;")

# cursor.execute("use database_name;")
# cursor.execute("show tables;")
# cursor.execute("select * from tables_name")
 
# 使用 fetchone() 方法获取单条数据;使用 fetchall() 方法获取所有数据
data = cursor.fetchall()
for item in data:
    print(item)
     
# 关闭数据库连接
cursor.close()

利用python对数据库增删改查
python写接口
python写接口2
这里记录两个获取数据库结构的方法

# 本地数据库方法
def Msqldb(sql):
    db = pymysql.connect(host = 'localhost', port = 3306, user = 'root', passwd = 'xxx', db='xxx', charset = 'utf8')
    cur = db.cursor()
    cur.execute(sql)
    # 判断sql语句是否select开头
    if sql.strip()[:6].upper() == 'SELECT':
        res = cur.fetchall()
    else:
        db.commit()
        res = 'OK'
    cur.close()
    db.close()
    return res
 
 # 本地数据库方法,返回字典(cursorclass=pymysql.cursors.DictCursor)
def MsqldbObject(sql):
    db = pymysql.connect(host = 'localhost', port = 3306, user = 'root', passwd = 'xxx', db='xxx',cursorclass=pymysql.cursors.DictCursor, charset = 'utf8')
    cur = db.cursor()
    cur.execute(sql)
    # 判断sql语句是否select开头
    if sql.strip()[:6].upper() == 'SELECT':
        res = cur.fetchall()
        #执行结果转化为dataframe
        # df = pd.DataFrame(list(res))
    else:
        db.commit()
        res = 'OK'
    cur.close()
    db.close()
    return res

django安装
python实现分页
paginator分页器

server.route('/meetingRoom/list', methods=['post'])
def meetingRoomList():
    # 获取接口传入的参数的值
    # content = flask.request.json
    # print(content, 'content') # {'meetingRoomName': '', 'meetingRoomBuildingNum': '00', 'state': '00', 'orderTime': '2022-05-20', 'size': 10, 'page': 1}
    meetingRoomName = flask.request.json.get('meetingRoomName')
    meetingRoomBuildingNum = flask.request.json.get('meetingRoomBuildingNum')
    state = flask.request.json.get('state')
    orderTime = flask.request.json.get('orderTime')
    size = flask.request.json.get('size')
    page = flask.request.json.get('page')
    # sqlList语句
    sqlList = "select * from meetingroomdb"

    # 数据库的值
    meetingRoomList = MsqldbObject(sqlList)
    print(meetingRoomList, 'meetingRoomList')
    print(len(meetingRoomList), 'meetingRoomList')
    # paginator.count：返回所有记录的总数量
    # paginator.num_pages：返回分页后的总页数
    # paginator.page_range：返回分页后的页码范围，一个range对象
    paginator = Paginator(meetingRoomList, size)
    current_page_num = int(page)
    page_obj = paginator.page(current_page_num)
    currentList = page_obj.object_list

多条件查询

SELECt *
FROM table_name 
WHERe 条件1 and 条件2； 

SELECt *
FROM table_name 
WHERe 条件1 and 条件2 or 条件；

SELECt *
FROM table_name 
WHERe （字段名1，字段名2）=（值1，值2）；

模糊查询

sql = "SELECt * FROM table_test WHERe value LIKE '%%%%%s%%%%'" % test_value

拼接后的语句

SELECt * FROM table_test WHERe value LIKE '%%public%%'

模糊查询2

select * from Person where cname like '张%';