MySQL和Python选择语句问题 [英] MySQL and Python Select Statement Issues

问题描述

感谢您抽出时间阅读这篇文章。这将是一个很长的职位来解释这个问题。我没有能够在所有常见的来源找到答案。

问题：
我有一个问题，使用select语句与python从mysql数据库中的表中调用数据。

系统和版本：

  Linux ubuntu 2.6.38-14 -generic＃58-Ubuntu SMP Tue Mar 27 20:04:55 UTC 2012 x86_64 x86_64 x86_64 GNU / Linux 
 Python：2.7.1+ 
 MySql：服务器版本：5.1.62-0ubuntu0.11.04。 1（Ubuntu）
  

这是表：

 的MySQL>描述哈希; 
 + ------- + -------------- + ------ + ----- + --------- + ------- + 
 | Field |类型|空|关键|默认|额外| 
 + ------- + -------------- + ------ + ----- + --------- + ------- + 
 | id | varchar（20）| NO | PRI | NULL | | 
 |哈希| varbinary（4）| NO | MUL | NULL | | 
 + ------- + -------------- + ------ + ----- + --------- + ------- + 
  

以下是我通过正常的mysql查询进行的响应：

  mysql> SELECT id FROM hashes WHERE hash ='f'; 
 + ------ + 
 | id | 
 + ------ + 
 | 0x67 | 
 + ------ + 
 
 mysql> SELECT id FROM hashes WHERE hash ='ff'; 
 + -------- + 
 | id | 
 + -------- + 
 | 0x6700 | 
 + -------- + 
  

像以前一样，这些是响应这是预期的，以及我如何设计数据库。

我的代码：

 从数据库导入mysql.connector 
 login_info 
 import sys 
 db = mysql.connector.Connect（** login_info）
 cursor = db.cursor（）
 data ='ff'
 cursor.execute（SELECT 
 * FROM hashes 
 WHERE哈希=％s
（数据））
 
 rows = cursor.fetchall（）
打印行
行中的行：
打印行[0] 
  

这将返回我预期的结果：

  [（u'0x67 '，'f'）] 
 0x67 
  

如果我将数据更改为：
data ='ff'
我收到以下错误：

 追溯（最近的最后一次呼叫）：
文件test.py，第11行，在< module> 
（data））
文件/usr/local/lib/python2.7/dist-packages/mysql_connector_python-0.3.2_devel- py2.7.egg / mysql / connector / cursor.py，行310，执行
字符串格式化期间参数数量错误）
 mysql.connector.errors.ProgrammingError：字符串格式化期间参数数量错误
  

确定。所以，我添加一个字符串格式字符到我的SQL语句如下：

  cursor.execute（SELECT 
 * FROM hashes 
 WHERE哈希=％s％s
（数据））
  

我收到以下回复：

  [（u'0x665aa6'，f'f） ] 
 0x665aa6 
  

，它应该是0x6700。

我知道我应该传递一个％s的数据。这就是我如何构建数据库表，每个变量使用一个％s：

  cursor.execute（
 INSERT INTO hashing（id，hash）
 VALUES（％s，％s），（k，hash））
  

任何想法如何解决这个问题？

谢谢。

解决方案

您的执行语句似乎不正确。我的理解是，它应该遵循模式 cursor.execute（< select statement string>，< tuple>），并且在元组位置只放置一个值其实只是一个字符串。为了使第二个参数是正确的数据类型，您需要在其中输入逗号，因此您的语句将如下所示：

  cursor .execute（SELECT 
 * FROM hashes 
 WHERE hash =％s
（data，））
  

Thanks for taking the time to read this. It's going to be a long post to explain the problem. I haven't been able to find an answer in all the usual sources.

Problem: I am having an issue with using the select statement with python to recall data from a table in a mysql database.

System and versions:

Linux ubuntu 2.6.38-14-generic #58-Ubuntu SMP Tue Mar 27 20:04:55 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
Python: 2.7.1+
MySql: Server version: 5.1.62-0ubuntu0.11.04.1 (Ubuntu)

Here's the table:

mysql> describe hashes;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(20)  | NO   | PRI | NULL    |       |
| hash  | varbinary(4) | NO   | MUL | NULL    |       |
+-------+--------------+------+-----+---------+-------+

Here are responses that I want via a normal mysql query:

mysql> SELECT id FROM hashes WHERE hash='f'; 
+------+
| id   |
+------+
| 0x67 |
+------+

mysql> SELECT id FROM hashes WHERE hash='ff'; 
+--------+
| id     |
+--------+
| 0x6700 |
+--------+

As before, these are the responses that are expected and how I designed the DB.

My code:

import mysql.connector
from database import login_info
import sys
db = mysql.connector.Connect(**login_info)
cursor = db.cursor()
data = 'ff'
cursor.execute("""SELECT
            * FROM hashes
            WHERE hash=%s""",
            (data))

rows = cursor.fetchall()
print rows
for row in rows:
        print row[0]

This returns the result I expect:

[(u'0x67', 'f')]
0x67

If I change data to : data = 'ff' I receive the following error:

Traceback (most recent call last):
 File "test.py", line 11, in <module>
    (data))
  File "/usr/local/lib/python2.7/dist-packages/mysql_connector_python-0.3.2_devel-    py2.7.egg/mysql/connector/cursor.py", line 310, in execute
    "Wrong number of arguments during string formatting")
mysql.connector.errors.ProgrammingError: Wrong number of arguments during string formatting

OK. So, I add a string formatting character to my SQL statement as so:

cursor.execute("""SELECT
            * FROM hashes
            WHERE hash=%s%s""",
            (data))

And I get the following response:

[(u'0x665aa6', "f'f")]
0x665aa6

and it should by 0x6700.

I know that I should be passing the data with one %s character. That is how I built my database table, using one %s per variable:

cursor.execute("""
INSERT INTO hashes (id, hash) 
VALUES (%s, %s)""", (k, hash))

Any ideas how to fix this?

Thanks.

解决方案

Your execute statement doesn't seem quite correct. My understanding is that it should follow the pattern cursor.execute( <select statement string>, <tuple>) and by putting only a single value in the tuple location it is actually just a string. To make the second argument the correct data type you need to put a comma in there, so your statement would look like:

cursor.execute("""SELECT
            * FROM hashes
            WHERE hash=%s""",
            (data, ))

这篇关于MySQL和Python选择语句问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！