PyODBC, cursor.execute() не будет вставлять параметры в строку SQL

Я использую pypyodbc для вставки данных в базу данных, и когда я использую команду cursor.execute(), я пытаюсь поместить строку sql и параметры, но я получаю следующую ошибку:

SELECT uid FROM HP_DATA WHERE( hpName = ? AND processID = ? AND ipAddress = ? AND port = ? AND usernameTried = ? AND passwordTried = ? AND fileID = ?);
 INSERT INTO HP_DATA_LOGIN_DETAIL(uid, attackDate, gmtOffset) VALUES(?, CONVERT(DATETIME, ?, 126), ?);
 2016-04-19T05:40:58.000
 ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.')

Это мой код:

# Inserting the info of the file that is read into HP_DATA_LOG
 # This is supposed to allow us to check in the future, what files are read/unread
 print("Inserting File data into HP_DATA_LOG...")
 log_file_date_read = datetime.datetime.today()
 log_file_date_added = datetime.datetime.fromtimestamp(os.path.getctime(path)).strftime("%Y-%m-%d %H:%M:%S.%f")
 file_size = os.path.getsize(path)
 #log_sql = "INSERT INTO HP_DATA_LOG(dateRead, dateAdded, fileName, fileSize) VALUES("
 #log_sql += "'" + str(log_file_date_read) + "', "
 #log_sql += "'" + str(log_file_date_added) + "', "
 #log_sql += "'" + path + "', "
 #log_sql += "" + str(file_size) + ");"
 log_params = (log_file_date_read, log_file_date_added, file_name, file_size)
 log_sql = '''INSERT INTO HP_DATA_LOG(dateRead, dateAdded, fileName, fileSize) VALUES(?, ?, ?, ?);'''
 print(log_sql)
 cursor.execute(log_sql, log_params)
 # Getting the auto-generated fileID from the table
 print("Getting fileID...")
 #get_fileID_sql = "SELECT fileID FROM HP_DATA_LOG WHERE "
 #get_fileID_sql += "(dateRead = '" + str(log_file_date_read) + "'"
 #get_fileID_sql += " AND dateAdded = '" + str(log_file_date_added) + "'"
 #get_fileID_sql += " AND fileName = '" + path + "'"
 #get_fileID_sql += " AND fileSize = '" + str(file_size) + "');"
 fileID_params = (log_file_date_read, log_file_date_added, file_name, file_size)
 get_fileID_sql = '''SELECT fileID FROM HP_DATA_LOG WHERE (dateRead = ? AND dateAdded = ? AND fileName = ? AND fileSize = ?);'''
 print(get_fileID_sql)
 cursor.execute(get_fileID_sql, fileID_params)
 fileID = cursor.fetchone()
 # Logging the attack by Inserting the HoneyPot data into HP_DATA
 hp_name = re.findall('-\d\d:\d\d\s(.*)\ssshd', line)
 pid = re.findall('\ssshd-22\[(\d+)\]', line)
 ip_add = re.findall('\sIP:\s(\d+.\d+.\d+.\d+)\s', line)
 port = re.findall('\s.\d+\sPass(.*)Log\s', line)
 if port == "2222":
 port = '2222'
 else:
 port = '22'
 username = re.findall('\sUsername:\s(.*)\sPas', line)
 password = re.findall('\sPassword:\s(.*)', line)
 #sql = "INSERT INTO HP_DATA(hpName, processID, ipAddress, port, usernameTried, passwordTried, fileID) VALUES("
 #sql += "'" + hp_name[0] + "', "
 #sql += str(int(pid[0])) + ", "
 #sql += "'" + ip_add[0] + "', "
 #sql += str(port) + ", "
 #sql += "'" + username[0] + "', "
 #sql += "'" + password[0] + "', "
 #sql += str(list(fileID)[0]) + ");"
 sql_params = (hp_name[0], pid[0], ip_add[0], port, username[0], password[0], fileID[0])
 sql = '''INSERT INTO HP_DATA(hpName, processID, ipAddress, port, usernameTried, passwordTried, fileID) VALUES(?, ?, ?, ?, ?, ?, ?);'''
 print(sql)
 cursor.execute(sql, sql_params)
 #
 #user_sql = r"SELECT uid FROM HP_DATA WHERE("
 #user_sql += "hpName = '" + hp_name[0] + "' AND "
 #user_sql += "processID = " + str(int(pid[0])) + " AND "
 #user_sql += "ipAddress = '" + ip_add[0] + "' AND "
 #user_sql += "port = " + str(port) + " AND "
 #user_sql += r"usernameTried = '" + username[0] + "' AND "
 #user_sql += r"passwordTried = '" + password[0] + "' AND "
 #user_sql += "fileID = " + str(list(fileID)[0]) + ");"
 user_sql_params = (hp_name[0], pid[0], ip_add[0], port, username[0], password[0], fileID[0])
 user_sql = '''SELECT uid FROM HP_DATA WHERE( hpName = ? AND processID = ? AND ipAddress = ? AND port = ? AND usernameTried = ? AND passwordTried = ? AND fileID = ?);'''
 print(user_sql)
 cursor.execute(user_sql, user_sql_params)
 uid = cursor.fetchone()
 # Inserting date and time information in order to prevent duplicates
 attack_date = re.findall('(\d{4}-\d\d-\d\d)T', line)
 timestamp = re.findall('T(\d\d:\d\d:\d\d.*).*-.*sshd', line)
 attack_datetime = attack_date[0] + "T" + timestamp[0] + ".000"
 gmt_offset = re.findall('\d\d:\d\d:\d\d.*-(\d\d:\d\d)\s', line)
 #hp_detail_sql = r"INSERT INTO HP_DATA_LOGIN_DETAIL(uid, attackDate, attackTime, gmtOffset) VALUES("
 #hp_detail_sql += "" + str(uid[0]) + ", "
 #hp_detail_sql += "'" + attackDate[0] + "', "
 #hp_detail_sql += "'" + timestamp[0] + "', "
 #hp_detail_sql += "'" + gmt_offset[0] + "');"
 hp_detail_sql_params = (uid[0], attack_datetime[0], gmt_offset[0])
 hp_detail_sql = '''INSERT INTO HP_DATA_LOGIN_DETAIL(uid, attackDate, gmtOffset) VALUES(?, ?, ?);'''
 print(hp_detail_sql)
 print(attack_datetime)
 cursor.execute(hp_detail_sql, hp_detail_sql_params)
 print("Executed insert statements")
1 ответ

Используйте datetime.strptime(), чтобы преобразовать значение attack_datetime в datetime перед передачей значения SQL Server.

Например, передача строки с форматированием даты и времени с тем же сообщением об ошибке, которое вы получаете

...
# assumes connection and cursor objects initialized
create_date_str = "2016-06-16T01:23:45.67890"
sql = "select name, create_date from sys.databases where create_date = ?"
rows = cursor.execute(sql, create_date_str).fetchall()

Повышения

Traceback (последний последний вызов): Файл "", строка 1, в  pyodbc.DataError: ('22007', '[22007] [Microsoft] [SQL Server Собственный клиент 11.0] [SQL Server] Ошибка конверсии при преобразовании даты и/или время от символьной строки. (241) (SQLExecDirectW) ')

При преобразовании строки datetime в объект datetime успешно

...
# convert datetime string to object, specifying input format
create_date = datetime.datetime.strptime(create_date_str, '%Y-%m-%dT%H:%M:%S.%f')
rows = cursor.execute(sql, create_date).fetchall()

licensed under cc by-sa 3.0 with attribution.