42000您的SQL语法有错误;查看与MySQL服务器对应的手册

I'm having trouble with my Python odbc code. I don't get this following code to work:

我的Python odbc代码有问题。我没有得到以下代码:

temp=process_query("SELECT FName, LName FROM Employee WHERE SSN='%s'" %i)

known_hours=process_query("SELECT DISTINCT Coalesce(Hours,0) 
FROM Works_On WHERE ESSN='%s'" %i)

 temp.append(known_hours)

where process_query takes the form:

process_query采用以下形式:

def process_query(query):
    cursor1.execute(str(query))

(process_query continues some more but that is merely for printing purposes, when I've searched the web for my problem it seems that the problem lies within how I call the execute function so I omitted the rest of the function here).

(process_query继续了一些,但仅仅是为了打印目的,当我在网上搜索我的问题时,问题似乎在于我如何调用execute函数,所以我省略了函数的其余部分)。

The error I receive when I'm trying to execute this program is:

我在尝试执行此程序时收到的错误是:

pyodbc.ProgrammingError: ('42000', "[42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.66-0+squeeze1-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'John', [Decimal('32.5'), Decimal('7.5')], 'Yes']'' at line 1 (1064) (SQLExecDirectW)")

pyodbc.ProgrammingError:('42000',“[42000] [MySQL] [ODBC 5.1驱动程序] [mysqld-5.1.66-0 + squeeze1-log]您的SQL语法有错误;请查看与您的语法对应的手册MySQL服务器版本,用于在'John'附近使用正确的语法,[Decimal('32 .5'),Decimal('7.5')],'Yes']''在第1行(1064)(SQLExecDirectW)“)

PS. If someone knows how to omit the "Decimal" when I'm printing and instead just have for instance 32.5 that would be appreciated as well to get help with.

PS。如果有人知道如何在我打印时省略“十进制”,而只是例如32.5,那么也可以获得帮助。

Also I know that it has been several topics regarding this, but I am yet to see and understand the problem I have for a select statement.

另外我知道有关于此的几个主题,但我还没有看到并理解我对select语句的问题。

Edit

Regarding how "i" is implemented it is as following:

关于“i”的实施方式如下:

I have an initial list called theList which contains all relevant social security numbers, SSN, which I then "loop" through like this:

我有一个名为theList的初始列表,其中包含所有相关的社会安全号码SSN,然后我将其“循环”通过,如下所示:

for i in theList:

    temp=process_query("SELECT FName, LName FROM Employee WHERE SSN='%s'" %i)

    known_hours=process_query("SELECT DISTINCT SUM(Coalesce(Hours,0)) FROM Works_On WHERE ESSN='%s'" %i)

    temp.append(known_hours)
    unknown_hours=process_query("SELECT Distinct COUNT(*) FROM Works_On WHERE ISNULL(Hours) AND ESSN='%s'" %i)

    temp.append(unknown_hours)

Edit

I've changed it as beargle suggested. However I now run into another error namely that since I loop through i (the social security numbers) in theList I have to define these before.

我已经改变了,因为熊掌建议。但是我现在遇到另一个错误,即因为我在列表中循环通过i(社会安全号码),我必须先定义它们。

Hence I use the line

因此我使用该线

theList=process_query('SELECT DISTINCT SSN FROM Employee', None)

Where I've updated my process_query to:

我将process_query更新为:

def process_query(query, parameters):

    if(parameters is None):

        cursor1.execute(query)

    else:

        cursor1.execute(query, parameters)  (*)

    n=0

    lista = []

    while 1:

        row = cursor1.fetchone()        

        if not row:

            break

        lista.append(row[0])

        n = n+1

    if n==0:

        print "No tuples matching the given query were found."

    return lista    

The trouble now is that the program complains at the second cursor1.execute (marked with a asterix, *) that ('The SQL contains 1 parameter markers, but 4 parameters were supplied', 'HY000') which I believe stems from that i is a social secuirity number and thus is not a single digit integer, but I cannot understand how to fix this issue.

麻烦现在是程序抱怨第二个cursor1.execute(标有星号,*)('SQL包含1个参数标记,但提供了4个参数','HY000')我认为这源于我是一个社会性的数字,因此不是一个数字整数,但我无法理解如何解决这个问题。

I now call my as:

我现在打电话给我:

temp=process_query('SELECT FName, LName FROM Employee WHERE SSN= ?', i)

known_hours=process_query('SELECT DISTINCT SUM(Coalesce(Hours,0)) FROM Works_On WHERE ESSN=?', i)

the i's in theList is identified before the loop defining temp and such as follows:

在定义temp的循环之前标识了List中的i,如下所示:

theList=process_query('SELECT DISTINCT SSN FROM Employee', None)

which removed all other errors but got me a new one as stated previously. Also, I tried to convert the "i" to int(i) and map(int,i) without getting a release from my error.

它删除了所有其他错误,但如前所述让我得到了一个新错误。此外,我试图将“i”转换为int(i)和map(int,i)而不从我的错误中获取释放。

1 个解决方案

#1

Use query parameters in the cursor.execute() call of your process_query function. This will handle any escaping issues (protecting your code against SQL injection) and promotes statement preparation.

在process_query函数的cursor.execute()调用中使用查询参数。这将处理任何转义问题(保护您的代码免受SQL注入)并促进语句准备。

Change the process_query function to accept two parameters, one for the SQL string (containing parameter value placeholder) and one for parameter values:

更改process_query函数以接受两个参数,一个用于SQL字符串(包含参数值占位符),另一个用于参数值:

def process_query(sql, params):
    cursor1.execute(sql, params)

The for loop would then change to:

然后for循环将更改为:

for i in theList:
    temp=process_query('SELECT FName, LName FROM Employee WHERE SSN=?', i)
    known_hours=process_query('SELECT DISTINCT SUM(Coalesce(Hours,0)) FROM Works_On WHERE ESSN=?', i)
    temp.append(known_hours)
    unknown_hours=process_query('SELECT Distinct COUNT(*) FROM Works_On WHERE ISNULL(Hours) AND ESSN=?', i)
    temp.append(unknown_hours)

If this doesn't resolve the syntax problem, update your question with the query string causing the problem.

如果这不能解决语法问题,请使用导致问题的查询字符串更新您的问题。