如何使用pyodb为insert sql语句增加循环中变量的值

2024-10-02 18:17:06 发布

您现在位置：Python中文网/ 问答频道 /正文
8536 3

我试图从API中提取员工考勤卡数据，然后将其插入sql数据库。我希望代码能够查看数据，隔离特定日期的输入和输出，然后将它们插入到sql数据库中。唯一需要注意的是，数据库需要一个序列号“SEQ\ u NO”来指示当天的哪一个打卡时间是用来计算休息时间的，午餐等，所以我希望它循环增加序列号1，而不是对同一个雇员执行多次头插入（最终“kelly walton”和“080098”将被替换为变量，这样就可以对数据集中的每个雇员执行。你知道吗

我已经尝试了几乎所有我在堆栈溢出上找到的变量递增方法，但我无法让它工作。for循环，while循环if，elif语句，如果你说出它的名字，我就什么都做不到了。这只是代码的一个版本。要澄清的问题是，无论我尝试使用哪种类型的循环，它都会尝试复制一些值，要么是头查询，要么是冲压数据，从而导致sql数据库中的错误。你知道吗

today = ('2019-06-05')
with open('timepunches.json', 'r') as f:
    timepunches_dict = json.load(f)
for punch in timepunches_dict:
    punch_in = punch['PunchInDateTime']
    punch_out = punch['PunchOutDateTime']
    punch_in_sql = punch_in.replace('T', ' ')
    punch_out_sql = punch_out.replace('T', ' ')
    if today in punch_in_sql:
        #print(punch_in_sql, punch_out_sql)
        cnx = pyodbc.connect('driver={SQL Server};server=MY_server;database=my_db;uid=****;pwd=****')
        cursor = cnx.cursor()
        sequence_number = 1
            if sequence_number == 1:
                sql_insert_header_query = ''' INSERT INTO my_db.dbo.SY_TIMCRD_HDR
                    (STR_ID, USR_ID, TIMCRD_DAT, USR_NAM) VALUES ('M-MAC', '080098', '2019-06-05 00:00:00.000', 'Kelly Walton')'''
                sql_timecard_data = '''INSERT INTO my_db.dbo.SY_TIMCRD_LIN
                    (STR_ID, USR_ID, TIMCRD_DAT, SEQ_NO, CLCK_IN_TIM) VALUES ('M-MAC', '080098', [dbo].[fnDateOnly]('%s'), '%s', [dbo].[fnTimeOnly]('%s'))'''
                cursor.execute(sql_insert_header_query)
                cursor.execute(sql_timecard_data % (today, sequence_number, punch_in_sql))
                sequence_number+=1
            elif sequence_number == 2:
                sql_timecard_data = '''INSERT INTO my_db.dbo.SY_TIMCRD_LIN
                    (STR_ID, USR_ID, TIMCRD_DAT, SEQ_NO, CLCK_OUT_TIM) VALUES ('M-MAC', '080098', [dbo].[fnDateOnly]('%s'), '%s', [dbo].[fnTimeOnly]('%s'))'''
                cursor.execute(sql_insert_header_query)
                cursor.execute(sql_timecard_data % (today, sequence_number, punch_out_sql))
                sequence_number+=1            
        cnx.commit()
        cnx.close()

我以为这会把“序列号”增加到2，然后执行下一部分。我打算在这部分工作完成后再补充一些内容。你知道吗

编辑：这是'时间戳.json“这可能会有帮助。你知道吗

[{"JobCodeId": null, 
    "PunchInApprovalStatusId": 4, 
    "PunchInImageUrl": "", 
    "LocationName": "", 
    "PunchInNotes": "", 
    "PunchOutLongitude": null, 
    "PunchOutLatitude": null, 
    "Employee": 
        {"Username": "starlord", 
        "EmployeeId":"080097", 
        "FirstName": "Peter", 
        "LastName": "Quill", 
        "ProfileMiniImageUrl": "https://buddypunchapp.blob.core.windows.net/profileminipics/new_employee_face2.jpg", 
        "Email": "starlord@email.com", 
        "FullName": "Peter Quill", 
        "Id": 346968, 
        "IsActive": true}, 
    "PunchOutDateTime": "2019-06-10T10:00:00", 
    "BreakMinutes": 0, 
    "Hours": 2.0, 
    "PunchOutApprovalStatusName": "Changed By Manager", 
    "OverTimeHours": 0.0, 
    "DoubleTimeHours": 0.0, 
    "JobCodeName": "", 
    "PunchInIpAddress": "", 
    "PunchInApprovalStatusName": "Changed By Manager", 
    "PTOEarningCodeAbbr": "", 
    "PunchOutIpAddress": "", 
    "PunchOutImageUrl": "", 
    "PunchInLongitude": null, 
    "BreakApprovalStatusId": null, 
    "BreakApprovalStatusName": null, 
    "PTOHours": null, 
    "PunchOutApprovalStatusId": 4, 
    "PunchInDateTime": "2019-06-10T08:00:00", 
    "PunchOutNotes": "", 
    "PTOEarningCodeId": null, 
    "Id": 12971600, 
    "PunchInLatitude": null, 
    "LocationId": null, 
    "Duration": "02:00:00", 
    "RegularHours": 2.0}, 

    {"JobCodeId": null, 
    "PunchInApprovalStatusId": 4, 
    "PunchInImageUrl": "", 
    "LocationName": "", 
    "PunchInNotes": "", 
    "PunchOutLongitude": null, 
    "PunchOutLatitude": null, 
    "Employee": {
        "Username": "starlord", 
        "EmployeeId": "080097", 
        "FirstName": "Peter", 
        "LastName": "Quill", 
        "ProfileMiniImageUrl": "https://buddypunchapp.blob.core.windows.net/profileminipics/new_employee_face2.jpg", 
        "Email": "starlord@email.com", 
        "FullName": "Peter Quill", 
        "Id": 346968, 
        "IsActive": true}, 
    "PunchOutDateTime": "2019-06-10T12:00:00", 
    "BreakMinutes": 0, 
    "Hours": 1.75, 
    "PunchOutApprovalStatusName": "Changed By Manager", 
    "OverTimeHours": 0.0, 
    "DoubleTimeHours": 0.0, 
    "JobCodeName": "", 
    "PunchInIpAddress": "", 
    "PunchInApprovalStatusName": "Changed By Manager", 
    "PTOEarningCodeAbbr": "", 
    "PunchOutIpAddress": ", 
    "PunchOutImageUrl": "", 
    "PunchInLongitude": null, 
    "BreakApprovalStatusId": null, 
    "BreakApprovalStatusName": null, 
    "PTOHours": null, 
    "PunchOutApprovalStatusId": 4, 
    "PunchInDateTime": "2019-06-10T10:15:00", 
    "PunchOutNotes": "", 
    "PTOEarningCodeId": null, 
    "Id": 12971609, 
    "PunchInLatitude": null, 
    "LocationId": null, 
    "Duration": "01:45:00", 
    "RegularHours": 1.75}, 

    {"JobCodeId": null, 
    "PunchInApprovalStatusId": 4, 
    "PunchInImageUrl": "", 
    "LocationName": "", 
    "PunchInNotes": "", 
    "PunchOutLongitude": null, 
    "PunchOutLatitude": null, 
    "Employee": {
        "Username": "starlord", 
        "EmployeeId": "080097", 
        "FirstName": "Peter", 
        "LastName": "Quill", 
        "ProfileMiniImageUrl": "https://buddypunchapp.blob.core.windows.net/profileminipics/new_employee_face2.jpg", 
        "Email": "starlord@email.com", 
        "FullName": "Peter Quill", 
        "Id": 346968, 
        "IsActive": true}, 
    "PunchOutDateTime": "2019-06-10T15:00:00", 
    "BreakMinutes": 0, 
    "Hours": 2.0, 
    "PunchOutApprovalStatusName": "Changed By Manager", 
    "OverTimeHours": 0.0, 
    "DoubleTimeHours": 0.0, 
    "JobCodeName": "", 
    "PunchInIpAddress": "", 
    "PunchInApprovalStatusName": "Changed By Manager", 
    "PTOEarningCodeAbbr": "", 
    "PunchOutIpAddress": "", 
    "PunchOutImageUrl": "", 
    "PunchInLongitude": null, 
    "BreakApprovalStatusId": null, 
    "BreakApprovalStatusName": null, 
    "PTOHours": null, 
    "PunchOutApprovalStatusId": 4, 
    "PunchInDateTime": "2019-06-10T13:00:00", 
    "PunchOutNotes": "", 
    "PTOEarningCodeId": null, 
    "Id": 12971618, 
    "PunchInLatitude": null, 
    "LocationId": null, 
    "Duration": "02:00:00", 
    "RegularHours": 2.0}, 

    {"JobCodeId": null, 
    "PunchInApprovalStatusId": 4, 
    "PunchInImageUrl": "", 
    "LocationName": "", 
    "PunchInNotes": "", 
    "PunchOutLongitude": null, 
    "PunchOutLatitude": null, 
    "Employee": {
        "Username": "starlord", 
        "EmployeeId": "080097", 
        "FirstName": "Peter", 
        "LastName": "Quill", 
        "ProfileMiniImageUrl": "https://buddypunchapp.blob.core.windows.net/profileminipics/new_employee_face2.jpg", 
        "Email": "starlord@email.com", 
        "FullName": "Peter Quill", 
        "Id": 346968, 
        "IsActive": true}, 
    "PunchOutDateTime": "2019-06-10T17:00:00", 
    "BreakMinutes": 0, 
    "Hours": 1.75, 
    "PunchOutApprovalStatusName": "Changed By Manager", 
    "OverTimeHours": 0.0, 
    "DoubleTimeHours": 0.0, 
    "JobCodeName": "", 
    "PunchInIpAddress": "", 
    "PunchInApprovalStatusName": "Changed By Manager", 
    "PTOEarningCodeAbbr": "", 
    "PunchOutIpAddress": "", 
    "PunchOutImageUrl": "", 
    "PunchInLongitude": null, 
    "BreakApprovalStatusId": null, 
    "BreakApprovalStatusName": null, 
    "PTOHours": null, 
    "PunchOutApprovalStatusId": 4, 
    "PunchInDateTime": "2019-06-10T15:15:00", 
    "PunchOutNotes": "", 
    "PTOEarningCodeId": null, 
    "Id": 12971630, 
    "PunchInLatitude": null, 
    "LocationId": null, 
    "Duration": "01:45:00", 
    "RegularHours": 1.75}]

Tags： inidnumbersqlbymanagernullcursor
1条回答
网友
1楼 · 发布于 2024-10-02 18:17:06

考虑以下对进程的调整：在序列号的循环期间使用enumerate()，重要的是使用parameterization而不是字符串插值。你知道吗

# VARIABLES
today = '2019-06-05'

# OPEN CONNECTION AND CURSOR
cnx = pyodbc.connect('driver={SQL Server};server=MY_server;database=my_db;uid=****;pwd=****')
cursor = cnx.cursor()

# PREPARED STATEMENTS
sql_insert_header_query = '''INSERT INTO my_db.dbo.SY_TIMCRD_HDR (STR_ID, USR_ID, TIMCRD_DAT, USR_NAM) 
                             VALUES ('M-MAC', ?, ?, ?)
                          '''

sql_timecard_data = '''INSERT INTO my_db.dbo.SY_TIMCRD_LIN (STR_ID, USR_ID, TIMCRD_DAT, SEQ_NO, CLCK_IN_TIM) 
                       VALUES ('M-MAC', ?, [dbo].[fnDateOnly](?), ?, [dbo].[fnTimeOnly](?))
                    '''

with open('timepunches.json', 'r') as f:
    timepunches_dict = json.load(f)

for i, punch in enumerate(timepunches_dict):
    punch_in = punch['PunchInDateTime']
    punch_out = punch['PunchOutDateTime']
    punch_in_sql = punch_in.replace('T', ' ')
    punch_out_sql = punch_out.replace('T', ' ')

    emp_id = punch['Employee']['EmployeeId']
    emp_name = punch['Employee']['FullName']

    if today in punch_in_sql:
        #print(punch_in_sql, punch_out_sql)

        if i == 0:
            # ONLY RUN FOR FIRST ITERATION 
            cursor.execute(sql_insert_header_query, (emp_id, today, emp_name))
            cnx.commit()     

        # RUN FOR ALL ITERATIONS
        cursor.execute(sql_timecard_data, (emp_id, today, i+1, punch_in_sql))        
        cnx.commit()

cursor.close()
cnx.close()

Rextester Demo（减去数据库对象）

相关问题 更多 >

    编程相关推荐