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
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.
如果这不能解决语法问题,请使用导致问题的查询字符串更新您的问题。
#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.
如果这不能解决语法问题,请使用导致问题的查询字符串更新您的问题。