Database_connection_Normal_punch_card.py
import pymysql
class DB_Connect_Normal:
##上班打卡，下班打卡def db_connect_normal_card(self, attendance_date, started_at, end_at):# 指定数据库地址、用户、密码、端口，使用connect()方法声明一个Mysql连接对象dbself.db = pymysql.connect(host='192.168.0.229', user='backyard', password='123456', port=3306)# 调用cursor()方法获得Mysql的操作游标，利用游标来执行SQL语句。self.cursor = self.db.cursor()# 直接用execute()方法执行，第一句用于获得Mysql版本，然后调用fetchone()方法获得第一条数据#attendance_date 考勤日期' "2019-11-20"#`started_at` '上班打卡时间' '2019-11-20 08:31:15.909'#`end_at` '下班打卡时间' 2019-11-20 12:31:15.909sql_daka = "INSERT INTO backyard.staff_work_attendance" \"(id, staff_info_id, organization_id, organization_type, attendance_date, shift_start, shift_end," \" working_day, started_at, started_state, started_staff_lat, started_staff_lng, started_store_id, " \"started_store_lng, started_store_lat, started_clientid, started_clientid_num, started_equipment_type, " \"started_os, started_path, started_bucket, started_remark, end_at, end_state, end_staff_lat," \" end_staff_lng, end_store_id, end_store_lng, end_store_lat, end_clientid, end_clientid_num, " \"end_equipment_type, end_os, end_path, end_bucket, end_remark , created_at, updated_at) " \"VALUES (NULL, 22344, 'TH01010101', 1, \"%s\", NULL, NULL, 0, \"%s\", 1," \" 40.03013680, 116.41032670, 'TH01010101', 116.41037800, 40.03036699, '862122049073801460078013055338', 1, " \"'3', 'android', 'workAttendanceSource/1573806618-b3537b8829bb49949375b93a1ced1d5f.jpg', 'fle-staging-asset-internal'," \" NULL, \"%s\", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL," \" '2019-11-20 08:31:15.913', '2019-11-20 08:32:52.845')"%(str(attendance_date), str(started_at), str(end_at))#self.cursor.execute(sql)# 执行SQL，并返回收影响行数effect_row = self.cursor.execute(sql_daka)# 提交，不然无法保存新建或者修改的数据self.db.commit()# self.data = self.cursor.fetchone()#打印影响的行数print('database version:', effect_row)# 关闭游标self.cursor.close()# 关闭连接self.db.close()##上班打卡，下班打卡def db_connect_normal_card_22344(self, attendance_date, started_at, end_at):# 指定数据库地址、用户、密码、端口，使用connect()方法声明一个Mysql连接对象dbself.db = pymysql.connect(host='192.168.0.229', user='backyard', password='123456', port=3306)# 调用cursor()方法获得Mysql的操作游标，利用游标来执行SQL语句。self.cursor = self.db.cursor()# 直接用execute()方法执行，第一句用于获得Mysql版本，然后调用fetchone()方法获得第一条数据# attendance_date 考勤日期' "2019-11-20"# `started_at` '上班打卡时间' '2019-11-20 08:31:15.909'# `end_at` '下班打卡时间' 2019-11-20 12:31:15.909sql_daka = "INSERT INTO backyard.staff_work_attendance" \"(id, staff_info_id, organization_id, organization_type, attendance_date, shift_start, shift_end," \" working_day, started_at, started_state, started_staff_lat, started_staff_lng, started_store_id, " \"started_store_lng, started_store_lat, started_clientid, started_clientid_num, started_equipment_type, " \"started_os, started_path, started_bucket, started_remark, end_at, end_state, end_staff_lat," \" end_staff_lng, end_store_id, end_store_lng, end_store_lat, end_clientid, end_clientid_num, " \"end_equipment_type, end_os, end_path, end_bucket, end_remark , created_at, updated_at) " \"VALUES (NULL, 22344, 'TH01010101', 1, \"%s\", NULL, NULL, 0, \"%s\", 1," \" 40.03013680, 116.41032670, 'TH01010101', 116.41037800, 40.03036699, '862122049073801460078013055338', 1, " \"'3', 'android', 'workAttendanceSource/1573806618-b3537b8829bb49949375b93a1ced1d5f.jpg', 'fle-staging-asset-internal'," \" NULL, \"%s\", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL," \" '2019-11-20 08:31:15.913', '2019-11-20 08:32:52.845')"%(str(attendance_date), str(started_at), str(end_at))# self.cursor.execute(sql)# 执行SQL，并返回收影响行数effect_row = self.cursor.execute(sql_daka)# 提交，不然无法保存新建或者修改的数据self.db.commit()# self.data = self.cursor.fetchone()# 打印影响的行数print('database version:', effect_row)# 关闭游标self.cursor.close()# 关闭连接self.db.close()#只有上班打卡，下班没有打卡def db_connect_normal_card_end_at_null(self,attendance_date, started_at):# 指定数据库地址、用户、密码、端口，使用connect()方法声明一个Mysql连接对象dbself.db = pymysql.connect(host='192.168.0.229', user='backyard', password='123456', port=3306)# 调用cursor()方法获得Mysql的操作游标，利用游标来执行SQL语句。self.cursor = self.db.cursor()# 直接用execute()方法执行，第一句用于获得Mysql版本，然后调用fetchone()方法获得第一条数据# attendance_date 考勤日期' "2019-11-20"# `started_at` '上班打卡时间' '2019-11-20 08:31:15.909'# sql_end_at_null = "INSERT INTO backyard.staff_work_attendance" \# "(id, staff_info_id, organization_id, organization_type, attendance_date, shift_start, shift_end," \# " working_day, started_at, started_state, started_staff_lat, started_staff_lng, started_store_id, " \# "started_store_lng, started_store_lat, started_clientid, started_clientid_num, started_equipment_type, " \# "started_os, started_path, started_bucket, started_remark, end_at, end_state, end_staff_lat," \# " end_staff_lng, end_store_id, end_store_lng, end_store_lat, end_clientid, end_clientid_num, " \# "end_equipment_type, end_os, end_path, end_bucket, end_remark , created_at, updated_at) " \# "VALUES (NULL, 22344, 'TH01010101', 1, \"%s\", NULL, NULL, 0, \"%s\", 1," \# " 40.03013680, 116.41032670, 'TH01010101', 116.41037800, 40.03036699, '862122049073801460078013055338', 1, " \# "'3', 'android', 'workAttendanceSource/1573806618-b3537b8829bb49949375b93a1ced1d5f.jpg', 'fle-staging-asset-internal'," \# " NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL," \# " '2019-11-20 08:31:15.913', '2019-11-20 08:32:52.845')" % (# str(attendance_date), str(started_at))sql_end_at_null = """INSERT INTO backyard.staff_work_attendance (id,staff_info_id,organization_id,organization_type,attendance_date,shift_start,shift_end,working_day,started_at,started_state,started_staff_lat,started_staff_lng,started_store_id,started_store_lng,started_store_lat,started_clientid,started_clientid_num,started_equipment_type,started_os,started_path,started_bucket,started_remark,end_at,end_state,end_staff_lat,end_staff_lng,end_store_id,end_store_lng,end_store_lat,end_clientid,end_clientid_num,end_equipment_type,end_os,end_path,end_bucket,end_remark,created_at,updated_at) \VALUES (1623655,22344,'TH01010101',1,'2019-11-26',NULL,NULL,0,'2019-11-26 02:00:00.909',1,40.03013680,116.41032670,'TH01010101',116.41037800,40.03036699,'862122049073801460078013055338',1,'3','android','workAttendanceSource/1573806618-b3537b8829bb49949375b93a1ced1d5f.jpg','fle-staging-asset-internal',NULL,'2019-11-26 12:55:00.909',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2019-11-26 02:00:15.913','2019-11-26 12:55:00.845')"""# self.cursor.execute(sql)# 执行SQL，并返回收影响行数# sqlString = "SELECT * FROM backyard.staff_work_attendance"# effect_row = self.cursor.execute(sqlString)try:effect_row = self.cursor.execute(sql_end_at_null)print('database version:', effect_row)# 提交，不然无法保存新建或者修改的数据self.db.commit()except Exception as e:#捕获数据库执行语句异常print(e)# self.data = self.cursor.fetchone()# 打印影响的行数# print('database version:', effect_row)# 关闭游标self.cursor.close()# 关闭连接self.db.close()# 只有上班打卡，下班没有打卡def db_connect_normal_card_end_at_null_22344(self, attendance_date, started_at):# 指定数据库地址、用户、密码、端口，使用connect()方法声明一个Mysql连接对象dbself.db = pymysql.connect(host='192.168.0.229', user='backyard', password='123456', port=3306)# 调用cursor()方法获得Mysql的操作游标，利用游标来执行SQL语句。self.cursor = self.db.cursor()# 直接用execute()方法执行，第一句用于获得Mysql版本，然后调用fetchone()方法获得第一条数据# attendance_date 考勤日期' "2019-11-20"# `started_at` '上班打卡时间' '2019-11-20 08:31:15.909'sql_end_at_null = "INSERT INTO backyard.staff_work_attendance" \"(id, staff_info_id, organization_id, organization_type, attendance_date, shift_start, shift_end," \" working_day, started_at, started_state, started_staff_lat, started_staff_lng, started_store_id, " \"started_store_lng, started_store_lat, started_clientid, started_clientid_num, started_equipment_type, " \"started_os, started_path, started_bucket, started_remark, end_at, end_state, end_staff_lat," \" end_staff_lng, end_store_id, end_store_lng, end_store_lat, end_clientid, end_clientid_num, " \"end_equipment_type, end_os, end_path, end_bucket, end_remark , created_at, updated_at) " \"VALUES (NULL, 22344, 'TH01010101', 1, \"%s\", NULL, NULL, 0, \"%s\", 1," \" 40.03013680, 116.41032670, 'TH01010101', 116.41037800, 40.03036699, '862122049073801460078013055338', 1, " \"'3', 'android', 'workAttendanceSource/1573806618-b3537b8829bb49949375b93a1ced1d5f.jpg', 'fle-staging-asset-internal'," \" NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL," \" '2019-11-20 08:31:15.913', '2019-11-20 08:32:52.845')" % (str(attendance_date), str(started_at))# self.cursor.execute(sql)# 执行SQL，并返回收影响行数effect_row = self.cursor.execute(sql_end_at_null)# 提交，不然无法保存新建或者修改的数据self.db.commit()# self.data = self.cursor.fetchone()# 打印影响的行数print('database version:', effect_row)# 关闭游标self.cursor.close()# 关闭连接self.db.close()
if name == ‘main‘:
db = DB_Connect_Normal()
# db.db_connect_normal_card(“2019-11-20”, “2019-11-20 08:31:15.909”, “2019-11-20 12:31:15.909”)
db.db_connect_normal_card_end_at_null(“2019-11-20”, “2019-11-20 08:31:15.909”)
若有收获，就点个赞吧
0 人点赞
