|
|
import pymysql
|
|
|
import time
|
|
|
|
|
|
t = time.strftime('%Y-%m-%d %H:%M:%S')
|
|
|
|
|
|
# 连接数据库
|
|
|
def begin():
|
|
|
db = pymysql.connect(host='192.168.137.56',
|
|
|
user='root',
|
|
|
password='123456',
|
|
|
database='znjj',
|
|
|
charset='utf8')
|
|
|
|
|
|
# 使用 cursor() 方法创建一个游标对象 cursor
|
|
|
cursor = db.cursor()
|
|
|
# print("数据库连接成功！")
|
|
|
return cursor, db
|
|
|
|
|
|
# 使用 execute() 方法执行 SQL 查询
|
|
|
# cursor.execute("SELECT VERSION()")
|
|
|
|
|
|
# 使用 fetchone() 方法获取单条数据.
|
|
|
# data = cursor.fetchone()
|
|
|
|
|
|
# 关闭数据库连接
|
|
|
# db.close()
|
|
|
|
|
|
'''
|
|
|
============================================================================================
|
|
|
以上是数据库连接操作
|
|
|
============================================================================================
|
|
|
'''
|
|
|
def rl_insert(num, name):
|
|
|
cursor, db = begin()
|
|
|
try:
|
|
|
sql = "insert into user(id, name) values ('"+num+"','"+name+"')"
|
|
|
print(sql)
|
|
|
# 运行sql语句
|
|
|
cursor.execute(sql)
|
|
|
# 修改
|
|
|
db.commit()
|
|
|
# 关闭游标
|
|
|
cursor.close()
|
|
|
# 关闭连接
|
|
|
db.close()
|
|
|
print("victory!")
|
|
|
except:
|
|
|
print("false")
|
|
|
|
|
|
# rl_insert("8","312313")
|
|
|
# print("12345678910")
|
|
|
|
|
|
def rl_update(id ,information, name):
|
|
|
cursor, db = begin()
|
|
|
try:
|
|
|
sql = "update user set "+ information +" = '"+ name +"' WHERE id = "+ id
|
|
|
|
|
|
# 运行sql语句
|
|
|
cursor.execute(sql)
|
|
|
# 修改
|
|
|
db.commit()
|
|
|
# 关闭游标
|
|
|
cursor.close()
|
|
|
# 关闭连接
|
|
|
db.close()
|
|
|
print("victory!")
|
|
|
except:
|
|
|
print("false")
|
|
|
|
|
|
# rl_update("7", "name", "32131231231")
|
|
|
|
|
|
def rl_select(function):
|
|
|
cursor, db = begin()
|
|
|
# 查询语句
|
|
|
try:
|
|
|
cursor = db.cursor()
|
|
|
sql = "select "+ function +" from user "
|
|
|
cursor.execute(sql)
|
|
|
result = cursor.fetchall()
|
|
|
# for data in result:
|
|
|
# print(data)
|
|
|
except Exception:
|
|
|
print("查询失败")
|
|
|
return result
|
|
|
|
|
|
|
|
|
# 用户表信息删除
|
|
|
def rl_delete(id):
|
|
|
cursor, db = begin()
|
|
|
# SQL 删除语句
|
|
|
sql = "DELETE FROM user WHERE id="+ id
|
|
|
try:
|
|
|
# 执行SQL语句
|
|
|
cursor.execute(sql)
|
|
|
# 向数据库提交
|
|
|
db.commit()
|
|
|
except:
|
|
|
# 发生错误时回滚
|
|
|
db.rollback()
|
|
|
# 关闭连接
|
|
|
db.close()
|
|
|
# 成功提示
|
|
|
print("victory!")
|
|
|
# rl_delete("8")
|
|
|
|
|
|
|
|
|
def rl_Nselect(information, id):
|
|
|
cursor, db = begin()
|
|
|
# 查询语句
|
|
|
try:
|
|
|
cursor = db.cursor()
|
|
|
sql = "select "+ information +" from user where id = " + id
|
|
|
cursor.execute(sql)
|
|
|
result = cursor.fetchall()
|
|
|
# for data in result:
|
|
|
# print(data)
|
|
|
except Exception:
|
|
|
print("查询失败")
|
|
|
return result
|
|
|
|
|
|
# name = rl_Nselect("sex","6")
|
|
|
# print(t+name[0][0])
|
|
|
|
|
|
'''
|
|
|
============================================================================================
|
|
|
以上是居家人士（user）表的操作
|
|
|
============================================================================================
|
|
|
'''
|
|
|
def hcsr_insert(table, id, time, num):
|
|
|
cursor, db = begin()
|
|
|
try:
|
|
|
sql = "insert into "+ table +" (num_id, monitor_time, first_photo) values ('" + id + "','" + time + "','"+ num +"')"
|
|
|
print(sql)
|
|
|
# 运行sql语句
|
|
|
cursor.execute(sql)
|
|
|
# 修改
|
|
|
db.commit()
|
|
|
# 关闭游标
|
|
|
cursor.close()
|
|
|
# 关闭连接
|
|
|
db.close()
|
|
|
print("victory!")
|
|
|
except:
|
|
|
print("false")
|
|
|
|
|
|
t = time.strftime('%Y-%m-%d %H:%M:%S')
|
|
|
# hcsr_insert("hcsr", "8", t, "5")
|
|
|
|
|
|
|
|
|
def hcsr_update(table, information, name, use_id):
|
|
|
cursor, db = begin()
|
|
|
try:
|
|
|
sql = "update "+ table +" set "+ information +" = '"+ name +"' WHERE num_id = "+ use_id
|
|
|
|
|
|
# 运行sql语句
|
|
|
cursor.execute(sql)
|
|
|
# 修改
|
|
|
db.commit()
|
|
|
# 关闭游标
|
|
|
cursor.close()
|
|
|
# 关闭连接
|
|
|
db.close()
|
|
|
print("victory!")
|
|
|
except:
|
|
|
print("false")
|
|
|
|
|
|
# hcsr_update("hcsr", "last_photo", "5", "7")
|
|
|
|
|
|
def hcsr_select(table, function):
|
|
|
cursor, db = begin()
|
|
|
# 查询语句
|
|
|
try:
|
|
|
cursor = db.cursor()
|
|
|
sql = "select "+function+" from "+ table
|
|
|
cursor.execute(sql)
|
|
|
result = cursor.fetchall()
|
|
|
# for data in result:
|
|
|
# print(data)
|
|
|
except Exception:
|
|
|
print("查询失败")
|
|
|
return result
|
|
|
|
|
|
# x = hcsr_select("hcsr", "max(id)")
|
|
|
# print(x[0][0])
|
|
|
|
|
|
'''
|
|
|
============================================================================================
|
|
|
以上是自动监控（user）表的操作和视频监控（m_photo）表的操作
|
|
|
============================================================================================
|
|
|
'''
|
|
|
|
|
|
|
|
|
def check_insert(num, id, name, function, time):
|
|
|
cursor, db = begin()
|
|
|
try:
|
|
|
sql = "insert into check_auth (number, id, name, function_use, time) values ('"+ num +"','" + id + "','" + name + "','" + function +"','"+ time +"')"
|
|
|
print(sql)
|
|
|
# 运行sql语句
|
|
|
cursor.execute(sql)
|
|
|
# 修改
|
|
|
db.commit()
|
|
|
# 关闭游标
|
|
|
cursor.close()
|
|
|
# 关闭连接
|
|
|
db.close()
|
|
|
print("victory!")
|
|
|
except:
|
|
|
print("false")
|
|
|
|
|
|
# check_insert("2","5","常旭光","门锁", t)
|
|
|
|
|
|
def check_select(function):
|
|
|
cursor, db = begin()
|
|
|
# 查询语句
|
|
|
try:
|
|
|
cursor = db.cursor()
|
|
|
sql = "select "+function+" from check_auth"
|
|
|
cursor.execute(sql)
|
|
|
result = cursor.fetchall()
|
|
|
# for data in result:
|
|
|
# print(data)
|
|
|
except Exception:
|
|
|
print("查询失败")
|
|
|
return result
|
|
|
|
|
|
x = check_select("max(number)")
|
|
|
# print(x)
|
|
|
'''
|
|
|
============================================================================================
|
|
|
以上是查看权限操作（check_auth）表的操作
|
|
|
============================================================================================
|
|
|
'''
|
|
|
|
|
|
# 数据库插入信息 温湿度表
|
|
|
def th_insert(number, time, t_number, h_number):
|
|
|
cursor, db = begin()
|
|
|
try:
|
|
|
sql = "insert into th (number, check_time, temperature, humidity) values ('"+number+"','"+time+"','"+t_number+"','"+h_number+"')"
|
|
|
|
|
|
# 运行sql语句
|
|
|
cursor.execute(sql)
|
|
|
# 修改
|
|
|
db.commit()
|
|
|
# 关闭游标
|
|
|
cursor.close()
|
|
|
# 关闭连接
|
|
|
db.close()
|
|
|
print("victory!")
|
|
|
except:
|
|
|
print("false")
|
|
|
|
|
|
# th_insert("2", "2023-04-19 23:44:15","53.0","21.4")
|
|
|
|
|
|
def th_select(function):
|
|
|
cursor, db = begin()
|
|
|
# 查询语句
|
|
|
try:
|
|
|
cursor = db.cursor()
|
|
|
sql = "select "+function+" from th"
|
|
|
cursor.execute(sql)
|
|
|
result = cursor.fetchall()
|
|
|
# for data in result:
|
|
|
# print(data)
|
|
|
except Exception:
|
|
|
print("查询失败")
|
|
|
return result
|
|
|
|
|
|
'''
|
|
|
============================================================================================
|
|
|
以上是温湿度（th）表的操作
|
|
|
============================================================================================
|
|
|
'''
|
|
|
def ms_insert(num, time, state):
|
|
|
cursor, db = begin()
|
|
|
try:
|
|
|
sql = "insert into ms (number, time_open, state) values ('"+num+"','"+time+"','"+state+"')"
|
|
|
|
|
|
# 运行sql语句
|
|
|
cursor.execute(sql)
|
|
|
# 修改
|
|
|
db.commit()
|
|
|
# 关闭游标
|
|
|
cursor.close()
|
|
|
# 关闭连接
|
|
|
db.close()
|
|
|
print("victory!")
|
|
|
except:
|
|
|
print("false")
|
|
|
|
|
|
t = time.strftime('%Y-%m-%d %H:%M:%S')
|
|
|
# ms_insert("1",t,"close")
|
|
|
|
|
|
def ms_update(information, n, number):
|
|
|
cursor, db = begin()
|
|
|
try:
|
|
|
sql = "update ms set "+ information +" = '"+ n +"' WHERE number = "+ number
|
|
|
|
|
|
# 运行sql语句
|
|
|
cursor.execute(sql)
|
|
|
# 修改
|
|
|
db.commit()
|
|
|
# 关闭游标
|
|
|
cursor.close()
|
|
|
# 关闭连接
|
|
|
db.close()
|
|
|
print("victory!")
|
|
|
except:
|
|
|
print("false")
|
|
|
|
|
|
# ms_update("close_state", "Y", "1")
|
|
|
|
|
|
|
|
|
def ms_select(function):
|
|
|
cursor, db = begin()
|
|
|
# 查询语句
|
|
|
try:
|
|
|
cursor = db.cursor()
|
|
|
sql = "select "+function+" from ms"
|
|
|
cursor.execute(sql)
|
|
|
result = cursor.fetchall()
|
|
|
# for data in result:
|
|
|
# print(data)
|
|
|
except Exception:
|
|
|
print("查询失败")
|
|
|
return result
|
|
|
|
|
|
# x = ms_select("max(number)")
|
|
|
# print(x[0][0])
|
|
|
|
|
|
'''
|
|
|
============================================================================================
|
|
|
以上是门锁控制（ms）表的操作
|
|
|
============================================================================================
|
|
|
'''
|
|
|
|
|
|
|
|
|
|
