python - 在MySQL-Python中使用INSERT INTO语句的语法错误

Question

我正在尝试使用以下SQL语法，使用来自文本文件的每一行的值填充MySQL数据库中的表：

INSERT INTO Table SET
  fieldname1 = "value1",
  fieldname2 = "value2",
  fieldname3 = "value3";

我使用一个嵌套的 for循环来打印一个字符串（名为 SQL3），该字符串在文本文件的每一行中都使用相同的字段名称，试图以正确的方式处理语法。似乎我接近了，但仍然向我抛出SQL语法错误，对于可能发生的事情我似乎视而不见。由于这是第一次尝试通过Python连接到数据库，因此可能还会隐藏更多问题。

希望有人可以帮助找到问题。是否缺少一些重要信息？

这是代码：

import MySQLdb 

fieldNames = """Org_rowNr_countsheet
Orig_row_10604
pt3
pt3_ensembl_id
status
pt2_meth
pt3_meth
pt2_kegg_id
pt2_uniprotID
pt3_kegg_hit
pt3_uniprot
pt2_contig
pt2_start
pt2_stop
pt2_strand
pt3_contig
pt3_start
pt3_stop  
pt3_strand
"""
fieldnames = fieldnames.strip().split("\n")

myconnection = MySQLdb.connect(host = "localhost", user = 'root', passwd = "mock", db="pt3_annot")
mycursor = myconnection.cursor()

infilename = "/home/oaklander114/winshare/mysql_pt3/pt3_annot_ids_reduced.csv"
infile =  open(infilename, 'r')
linenumber = 0 

for line in infile:
    if linenumber > 0:
    line =  line.strip("\n\r")
    fields = line.split(',')
    print 'INSERT INTO ids SET '
    for i, fieldname in enumerate(fieldnames):
        query = []
        if i < 18:
            SQL1 = """
             %s = '%s',
             """ % (fieldname, fields[i])
            query.append(SQL1)
        else:
            SQL2 = """
             %s = '%s';
             """ % (FieldName, Fields[i])
            query.append(SQL2)
        SQL3 = " ".join(query)
        print SQL3
        mycursor.execute(SQL3)

    linenumber += 1
infile.close()
mycursor.close()
myconnection.commit()
myconnection.close()

这是错误消息：

ProgrammingError: (1064, "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 'Org_rowNr_countsheet = '10578',' at line 1")

我认为在错误发生时显示语法的打印内容会很有用，因为脚本似乎正在生成它（看起来对我来说）：

INSERT INTO ids SET
  Org_rowNr_countsheet = '10579',
  Orig_row_10604 = '',
  pt3 = '300002',

等等...但是错误与第一行有关。这是更详细的错误报告：

%run /home/oaklander114/winshare/mysql_pt3/insert_rows_ids.py
INSERT INTO ids SET
Org_rowNr_countsheet='10578',
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
/home/oaklander114/Canopy/appdata/canopy-1.4.1.1975.rh5-x86_64/lib/python2.7/site-   packages/IPython/utils/py3compat.pyc in execfile(fname, *where)
202             else:
203                 filename = fname
--> 204             __builtin__.execfile(filename, *where)

/home/oaklander114/winshare/mysql_pt3/insert_rows_ids.py in <module>()
 52             SQL3 = " ".join(query)
 53             print SQL3
---> 54             MyCursor.execute(SQL3)
 55 
 56     LineNumber += 1

/home/oaklander114/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/MySQL_python-1.2.5-    py2.7-linux-x86_64.egg/MySQLdb/cursors.pyc in execute(self, query, args)
203             del tb
204             self.messages.append((exc, value))
--> 205             self.errorhandler(self, exc, value)
206         self._executed = query
207         if not self._defer_warnings: self._warning_check()

/home/oaklander114/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/MySQL_python-1.2.5-py2.7-linux-x86_64.egg/MySQLdb/connections.pyc in defaulterrorhandler(***failed resolving arguments***)
 34     del cursor
 35     del connection
---> 36     raise errorclass, errorvalue
 37 
 38 re_numeric_part = re.compile(r"^(\d+)")

ProgrammingError: (1064, "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 'Org_rowNr_countsheet='10578',' at line 1") 

这些是 ids表的列：

mysql>  SHOW COLUMNS FROM ids;
+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| Org_rowNr_countsheet | varchar(255) | NO   | PRI | NULL    |       |
| Org_row_10604        | varchar(255) | YES  |     | NULL    |       |
| pt3                  | varchar(255) | YES  |     | NULL    |       |
| pt3_ensembl_id       | varchar(255) | YES  |     | NULL    |       |
| status               | varchar(255) | YES  |     | NULL    |       |
| pt2_meth             | varchar(255) | YES  |     | NULL    |       |
| pt3_meth             | varchar(255) | YES  |     | NULL    |       |
| pt2_kegg_id          | varchar(255) | YES  |     | NULL    |       |
| pt2_uniprotID        | varchar(255) | YES  |     | NULL    |       |
| pt3_kegg_hit         | varchar(255) | YES  |     | NULL    |       |
| pt3_uniprot          | varchar(255) | YES  |     | NULL    |       |
| pt2_contig           | varchar(255) | YES  |     | NULL    |       |
| pt2_start            | varchar(255) | YES  |     | NULL    |       |
| pt2_stop             | varchar(255) | YES  |     | NULL    |       |
| pt2_strand           | varchar(255) | YES  |     | NULL    |       |
| pt3_contig           | varchar(255) | YES  |     | NULL    |       |
| pt3_start            | varchar(255) | YES  |     | NULL    |       |
| pt3_stop             | varchar(255) | YES  |     | NULL    |       |
| pt3_strand           | varchar(255) | YES  |     | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+
19 rows in set (0.00 sec)

Answer 1

2件事。我会把if块排除在循环之外，并确保在前面加上适当的SQL。

SQL3 = 'UPDATE table SET ' + " ".join(query) + ';'  #don't forget WHERE clause as well...

或者如果您要插入...您的有效INSERT语句

您在文件中遇到空白行吗？也许尝试...。

而不是这个（在您的问题中格式不正确）

for line in InFile:
    if LineNumber > 0:
    line =  line.strip("\n\r")
    #print line
    Fields = line.split(',')

尝试这个：

for line in InFile:
    line =  line.strip("\n\r")
    if LineNumber > 0 and line != '':
        #print line
        Fields = line.split(',')
        #....rest of your code...