I am developing a small application that depends on SQL Server and development in Python
, this takes data of Sp
of SQL Server
that it has by default of name:
xp_readerrorlog 6, 1, N'login', N'failed'
As you see, send results where the login fails through python in the following way:
# -*- encoding: utf-8 -*-
#Libreias
#import pymssql
from time import sleep
import os,sys
from pprint import pprint
import pymssql
reload(sys) sys.setdefaultencoding('utf8')
#-------------------------------------------------------------
#atributos de conexión
server = "XP-AX"
user = "olis"
password = "Monitor"
base = "StoragePython"
#Conexion almacena la consulta de la BD de StoragePython
Conection = "select se.servidor, se.[user], se.Pass, sc.query, sc.tabla, ses.idq from servers se left join ServerScript ses on se.Servidor = ses.IdServidor left join scripts sc on ses.idq = sc.idq where ses.status = 1 and se.status = 1 order by se.servidor, ses.idq "
validacion = "XPSATURNO"
#crea una función con los parametros (server, user, password, base) a fin de poder utilizarlos dentro de la misma
def ejecutaSp(server, user, password, base):
#Para evitar que se levante la excepción y se detenga la ejecución del programa se crea un bloque de ejecución try-except
try:
#Se almacena la información de EXEC en la variable qeury
query = "EXEC sys.xp_readerrorlog 6, 1, N'login', N'failed'"
#Coneccion a sql
con = pymssql.connect(server, user, password, base)
cursor = con.cursor() #Crea un cursor
cursor.execute(query)#Ejecuta la consulta
for row in cursor.fetchall():
resultado = row[0], row[1], row[2]
print resultado
insert = "INSERT INTO prueba(fechahora,descripcion) VALUES(" + '"' + str(row[0]) + '"' + "," + '"' + (row[2] or '') + '"' + ")"
print insert
try:
cursor.execute(insert)
con.commit()
except Exception as error:
print('Ocurrió un problema al insertar: ' + str(error))
exit(0)
except Exception as error:
print('Ocurrió un problema en la conexión con el servidor principal: ' + server + ' e usuario: ' + user + ' - ' + str(error))
The result of the print is as follows and next to the error is this:
(datetime.datetime (2017, 3, 3, 17, 6, 9, 880000), u'Logon ', or "Login failed for user' GRUPOXCARET \ edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69] ") INSERT INTO test (date, description) VALUES ("2017-03-03 17: 06: 09.880000", "Login failed for user 'GRUPOXCARET \ edejesus'. Reason: Could not find a login matching the name provided. [CLIENT: 10.248.56.69] ") There was a problem inserting: (207, "Invalid column name '2017-03-03 17: 06: 09.880000'.DB-Lib error message 20018, severity 16: \ nGeneral SQL Server error: Check messages from the SQL Server \ nDB-Lib error message 20018, severity 16: \ nGeneral SQL Server error: Check messages from the SQL Server \ n ")
How funny that when copying and inserting the insert that is up in the SQl Server, it works: (