mysql/connector python查询在mysql中有效，但在python中不起作用 [英] mysql/connector python query works in mysql but not python
问题描述
我已经看到以下两个答案:
I've seen the following two answers:
- python - mysql query not working
- SQL query works in console but not in python
这两种解决方案都不适合我.我正在运行下面的代码，该代码每次都完整无误地完成，但是根本不会修改mysql表Compounds，并且我已经验证了输入文件的格式与我的mysql表的格式完全匹配，并且它已正确定界且不为空.
And neither of the solutions work for me. I'm running the code below which completes without any errors every single time yet does not at all modify the mysql table Compounds, and I've verified that the format of the input file matches exactly the format of my mysql table and that it is correctly delimited and not empty.
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 lib可能无法运行实际查询是否有原因，或者我缺少某些函数调用?
However if I print this query to terminal and then run it in mysql then it works just fine. Is there a reason the mysql lib might fail to run the actual query or am I missing some function call?
推荐答案
MySQL不接受标识符的绑定占位符.在这种情况下，表的名称.那必须是SQL文本的一部分.您只能使用绑定占位符提供值.
MySQL doesn't accept a bind placeholder for an identifier. In this case, the name of the table. That has to be part of the SQL text. You can only supply values using bind placeholders.
您可以执行以下操作:
SELECT t.id FROM mytable t WHERE t.foo = :val
^^^^
但这是无效:
SELECT t.id FROM :mytable t WHERE t.foo = 'bar'
^^^^^^^^
作为上一步，您可以执行sprintf类型的操作，以生成包含实际表名的SQL文本(字符串)，然后将该SQL文本(字符串)提交给MySQL.
You could do a an sprintf-type operation as a prior step, to generate SQL text (a string) that contains the actual table name, and then submit that SQL text (string) to MySQL.
到达该cursor.execute时，您需要SQL文本来包括表名作为字符串的一部分. (很可能文件名也必须指定为文字，不能作为占位符，但是我不确定.我确定的是表名.)
When you get to that cursor.execute, you need the SQL text to include the table name as part of the string. (It's also likely that the name of the file has to be specified as a literal, and can't be a placeholder, but I'm not sure about that. What I am sure about is the table name.)
作为测试，请尝试对表名"Compounds"进行硬编码，然后将文件名硬编码为SQL文本...
As a test, try hardcoding the table name "Compounds" and hardcoding the filename into the SQL text...
cursor.execute = (
"LOAD DATA LOCAL INFILE '/opt/data/compounds/parsed/fullData.csv'"
" REPLACE"
" INTO TABLE `Compounds`"
这篇关于mysql/connector python查询在mysql中有效，但在python中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
