问如何使用pyodbc在插入sql语句的循环中增加变量的值
EN

Stack Overflow用户

提问于 2019-06-18 11:30:26

回答 1查看 384关注 0票数 0

我试图从API中提取employee timecard数据，然后将其插入到sql数据库中。我希望代码查看数据，为特定的日期隔离输入和输出的穿孔，然后将它们插入sql数据库。唯一的问题是，数据库需要一个序列号"SEQ_NO“来指示当天的哪一次冲头，以便计算休息时间、午餐时间等，所以我希望它循环增加序列号1，而不多次为同一个员工执行头插入(最终，"kelly”和"080098“将被替换为变量，这样就可以对数据集中的每个员工执行。

我已经尝试过在堆栈溢出中找到的几乎每一个变量增量方法，但我无法让它工作。对于循环，而循环如果，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()

我认为这将增加'sequence_number‘到2，并执行下一部分。一旦我完成了这个部分，我打算增加更多的陈述。

编辑:这是'timepunches.json‘中的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}]

python

python-2.7

pyodbc

语音识别特惠，低至14.9元！

提供业界非常具有性价比的语音识别服务，超高识别准确率，适用多场景

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-06-18 13:29:11

考虑使用enumerate()循环对序列号进行以下调整，更重要的是使用参数化而不是字符串内插。

# 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()