postgresql - PostgreSQL 查询 - 类型转换
问题描述
执行 PostgreSQL 查询
从两个列表中搜索数据库中的匹配项时,我收到以下错误:
ProgrammingError: operator does not exist: character varying ~~ text[]
LINE 1: ...FROM public."Phosphosite_table" WHERE "GENE_NAME" LIKE ARRAY...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
我的代码如下:
start = time.time()
input_file = "az20.tsv"
names = []
residuelist = []
kinase = []
fclist = []
with open(input_file, "r") as data:
data = csv.reader(data, delimiter='\t')
next(data, None)
for row in data:
data = row[0:6]
if "(" in data[0]:
name = data[0].split("(")[0]
residue1 = data[0].split("(")[1]
residue = residue1.split(")")[0]
fc = data[3]
else:
pass
if "_" in name:
name = name.split("_")[0]
if residue != "None":
names.append(str(name))
residuelist.append(str(residue))
fclist.append(fc)
genename = names
location = residuelist
connection = pg.connect(HAHAHA)
cur = connection.cursor()
cur.execute('SELECT "KINASE_NAME" FROM public."Phosphosite_table" WHERE "GENE_NAME" LIKE %s and "RESIDUE" LIKE %s',\
(genename, location))
query = cur.fetchall()
print query
connection.close()
end = time.time()
print str((end - start)/60) + " Minutes"
我做了一些研究,似乎 PostgreSQL 不执行任何类型转换。但是,我认为这将是一个字符串与一个字符串的比较,我在添加到我的列表之前对其进行了更改。有人有建议吗?
康纳
解决方案
但是,我认为这将是一个字符串与一个字符串的比较
错误消息的一部分character varying ~~ text[]
告诉您,您正在将字符串(“字符变”)与数组(“文本 []”)进行比较。
如果要将单个值与数组的所有元素进行比较,则需要使用ANY
运算符:
WHERE "GENE_NAME" LIKE any(%s)
假设%s
从您的代码中作为本机 Postgres 数组传递(这似乎是给定错误消息的情况)。