Valid column name SQL Server-Python

0

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: (

    
asked by Santiago Huh 23.06.2017 в 19:03
source

1 answer

1

I imagine it has something to do with 'and'.

Try this

insert = "INSERT INTO prueba(fechahora,descripcion) VALUES('" str(row[0]) + "','" + (row[2].replace('\'','\'\'')) + "')"
    
answered by 23.06.2017 / 19:23
source