python用于写后台接口

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

前提

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

1、数据库相关操作

新建数据库
新建表

2、python操作数据库

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

3、python实现分页

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

4、sql多条件查询

多条件查询

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）；

5、模糊查询

模糊查询

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 '张%';

6、封装插入数据库方法

# 本地数据库插入方法
def insertData(data, table): # data为对应的字典
  db = pymysql.connect(host = 'localhost', port = 3306, user = 'root', passwd = 'xxx', db='xxxx', cursorclass=pymysql.cursors.DictCursor, charset = 'utf8')
  cur = db.cursor()
  # 根据数据库自动配对键值对
  table = table
  keys = ', '.join(data.keys())
  values = ', '.join(['%s'] * len(data))
  sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
  try:
    cur.execute(sql, tuple(data.values()))
    # print('Successful')
    res = 'Successful'
    db.commit()
  except:
    # print('Failed')
    db.rollback()
    res = 'Failed'
  cur.close()
  return res

调用的时候

res = insertData(dict, 'orderlist')