python - mysql/connector python 查询在 mysql 中有效，但在 python 中无效

Question

我看到了以下两个答案:

• python - mysql query not working
• SQL query works in console but not in python

这两种解决方案都不适合我。我正在运行下面的代码，每次都不会出现任何错误，但根本不会修改 mysql 表 Compounds，并且我已经验证输入文件的格式与我的 mysql 表，它被正确分隔并且不为空。

import os, sys
import mysql.connector
from mysql.connector import errorcode
from mysql.connector.constants import ClientFlag

TABLE_NAME = "Compounds"
fullDataFile  = "data/compounds/parsed/fullData.csv"
cnx = mysql.connector.connect(user='kharland', passwd='password', db='mydb', client_flags=[ClientFlag.LOCAL_FILES])
cursor = cnx.cursor()
print "loading data file: %s/%s" % (os.getcwd(), fullDataFile)
try:
  cursor.execute = (
    "LOAD DATA LOCAL INFILE '%s/%s'"
    " REPLACE"
    " INTO TABLE `%s`"
    " FIELDS TERMINATED BY '^'"
    " LINES TERMINATED BY '\n'"
    " (PUBCHEM_COMPOUND_CID,"
    " PUBCHEM_COMPOUND_CANONICALIZED,"
    " PUBCHEM_CACTVS_COMPLEXITY,"
    " PUBCHEM_CACTVS_HBOND_ACCEPTOR,"
    " PUBCHEM_CACTVS_HBOND_DONOR,"
    " PUBCHEM_CACTVS_ROTATABLE_BOND,"
    " PUBCHEM_CACTVS_SUBSKEYS,"
    " PUBCHEM_IUPAC_INCHI,"
    " PUBCHEM_IUPAC_INCHIKEY,"
    " PUBCHEM_EXACT_MASS,"
    " PUBCHEM_MOLECULAR_FORMULA,"
    " PUBCHEM_MOLECULAR_WEIGHT,"
    " PUBCHEM_OPENEYE_CAN_SMILES,"
    " PUBCHEM_OPENEYE_ISO_SMILES,"
    " PUBCHEM_CACTVS_TPSA,"
    " PUBCHEM_MONOISOTOPIC_WEIGHT,"
    " PUBCHEM_TOTAL_CHARGE,"
    " PUBCHEM_HEAVY_ATOM_COUNT,"
    " PUBCHEM_ATOM_DEF_STEREO_COUNT,"
    " PUBCHEM_ATOM_UDEF_STEREO_COUNT,"  
    " PUBCHEM_BOND_DEF_STEREO_COUNT,"
    " PUBCHEM_BOND_UDEF_STEREO_COUNT,"
    " PUBCHEM_ISOTOPIC_ATOM_COUNT,"
    " PUBCHEM_COMPONENT_COUNT,"
    " PUBCHEM_CACTVS_TAUTO_COUNT);"  %
    (os.getcwd(), fullDataFile, TABLE_NAME))
  cnx.commit()
except mysql.connector.Error as e:
  sys.stderr.write("x Failed loading data: {}\n".format(e))

但是，如果我将此查询打印到终端，然后在 mysql 中运行它，那么它就可以正常工作。 mysql 库是否有可能无法运行实际查询的原因，或者我是否缺少某些函数调用？

Answer 1

MySQL 不接受标识符的绑定(bind)占位符。在本例中为表的名称。这必须是 SQL 文本的一部分。您只能使用绑定(bind)占位符提供值。

你可以这样做:

SELECT t.id FROM mytable t WHERE t.foo = :val
                                         ^^^^

但这无效:

SELECT t.id FROM :mytable t WHERE t.foo = 'bar'
                 ^^^^^^^^ 

您可以在前面的步骤中执行 sprintf 类型的操作，以生成包含实际表名称的 SQL 文本(字符串)，然后将该 SQL 文本(字符串)提交到 MySQL .

当您到达 cursor.execute 时，您需要 SQL 文本来包含表名称作为字符串的一部分。 (文件名也可能必须指定为文字，并且不能是占位符，但我不确定这一点。我确定的是表名。)

<小时/>

作为测试，尝试将表名“Compounds”硬编码并将文件名硬编码到 SQL 文本中...

cursor.execute = (
    "LOAD DATA LOCAL INFILE '/opt/data/compounds/parsed/fullData.csv'"
    " REPLACE"
    " INTO TABLE `Compounds`"