此查詢在 SQL（PostgreSQL 資料庫）中運行沒有問題：
SELECT
number_column
FROM
number_table
WHERE
number_column ILIKE ANY (array[ 'W244048699%',
'0310003808%',
')0030480768%',
')0070002130%',
')0100059345%',]
我正在嘗試將其翻譯成 Python，如下所示：
numbers = ['W244048699%',
'0310003808%',
')0030480768%',
')0070002130%',
')0100059345%',]
sql_query = """
SELECT
number_column
FROM
number_table
WHERE
number_column ILIKE ANY (array[%(numbers)s]
"""
df = pd.read_sql(sql_query, engine, params = {'numbers':tuple(numbers)})
但是，我收到以下錯誤：
(psycopg2.errors.UndefinedFunction) operator does not exist: character varying ~~* record
LINE 19: where number ilike any (array[('%...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
有誰知道如何實作上述以使查詢在 Python 中運行？
謝謝！
uj5u.com熱心網友回復：
根據psycopg2檔案串列改編，串列適用于陣列。所以你把事情復雜化了。
嘗試 ：
number_column ILIKE ANY (%(numbers)s)
和論點：
{'numbers': numbers}
uj5u.com熱心網友回復：
用這個：
import psycopg2
#establishing the connection
conn = psycopg2.connect(
database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)
#Setting auto commit false
conn.autocommit = True
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
numbers = ['W244048699%',
'0310003808%',
')0030480768%',
')0070002130%',
')0100059345%',]
number_tuple=tuple(numbers)
sql_query = "SELECT number_column
FROM
number_table
WHERE
number_column ILIKE ANY {}".format(number_tuple)
cursor.execute(sql_query )
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/432602.html
標籤：Python 熊猫 PostgreSQL 心理咨询师2