沒有運算子匹配給定的名稱和引數型別。您可能需要添加顯式型別轉換

我正在使用 PostgreSQL 來存盤用戶名及其相應的加鹽和散列密碼，但它不斷給我這個錯誤。

LINE 1: ...egister where USERNAME = 'siddharth' and PASSWORD = '\x24326...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

我的表名是hassle_free_register其結構如下：

                                       Table "public.hassle_free_register"
  Column  |          Type          | Collation | Nullable |                        Default
---------- ------------------------ ----------- ---------- -------------------------------------------------------
 user_id  | integer                |           | not null | nextval('hassle_free_register_user_id_seq'::regclass)
 username | character varying(255) |           | not null |
 password | character varying(255) |           | not null |
Indexes:
    "hassle_free_register_pkey" PRIMARY KEY, btree (user_id)
    "hassle_free_register_username_key" UNIQUE CONSTRAINT, btree (username)

用于注冊用戶的代碼：-

   @app.route('/register' ,methods =['POST'])
def register():
   try:
      NAME = request.form['USER_NAME']
      PASSWORD = request.form['USER_PASSWORD']
      if(len(NAME)==0):
         return jsonify({"message" :"USERNAME CANNOT BE EMPTY"}),400
      if(len(PASSWORD)<=8):
         return jsonify({"message" :"PASSWORD LENGTH TOO SHORT"}),400
      if(len(PASSWORD)>=30):
         return jsonify({"message" :"PASSWORD LENGTH TOO LONG"}),400
      HASHEDPASS = bcrypt.hashpw(PASSWORD.encode('utf-8'),bcrypt.gensalt())    
      mycursor.execute("insert into hassle_free_register (USERNAME,PASSWORD) values(%s, %s);",(NAME,HASHEDPASS)) 

      #ERROR IS OCCURING IN THIS LINE
      mycursor.execute("select USER_ID from Hassle_Free_Register where USERNAME = %s and PASSWORD = %s;",(NAME,HASHEDPASS)) 

      data = mycursor.fetchone()
      print(data)
      mycursor.execute("create table {TABLENAME} (PASSWORD_ID int SERIAL NOT NULL PRIMARY KEY,APP_NAME varchar(255) NOT NULL, APP_USERNAME varchar(255) NOT NULL , APP_PASSWORD varchar(255) NOT NULL);".format(TABLENAME = NAME   "_"   str(data[0])))
      mydb.commit()
      return jsonify("REGISTERED SUCCESSFULLY") 
   except TypeError as error:
      print(error)
      return jsonify({"message":str(error)}),403
   except ValueError as error:
      print(error)
      return jsonify({"message":str(error)}),403
   except psycopg2.Error as error:
      print(error)
      return jsonify({"message":str(error)}),403

（評論后編輯）我是 Python 和 postgrSQL 的新手。請幫忙 ！

我運行了在 SQL shell (psql) 中給出錯誤的查詢，但它運行成功了。

詢問 ：-

hassle_free=# select USER_ID from hassle_free_register where USERNAME = 'siddharth' and PASSWORD = '\x24326224313224587174764d532e6265334d6654354e47514a6d73674f6d72327a75723966747a42542e5a2e4f48374e74446d6e76355353752e7461';

結果 ：-

 user_id
---------
      39
(1 row)

請幫忙。

uj5u.com熱心網友回復：

要清楚發生了什么：

create table pwd_test(id int, password varchar);

import psycopg2
import bcrypt 
pwd = bcrypt.hashpw('test_pwd'.encode('utf-8'),bcrypt.gensalt()) 

pwd
b'$2b$12$DTSJSsuuhwgyMdSOqLmb0.4RAk.smxQERas/i7WcR3NKTPtLQfoPK'
# Note the b'
# From here [Binary adaptation](https://www.psycopg.org/docs/usage.html#adapt-binary)
# This gets converted to bytea

cur.execute('insert into pwd_test values(%s,%s)', [2, pwd])
-- On the INSERT it gets cast to a varchar
select * from pwd_test where id = 2;
 id |                                                          password                                                          
---- ----------------------------------------------------------------------------------------------------------------------------
  2 | \x243262243132244454534a53737575687767794d64534f714c6d62302e3452416b2e736d7851455261732f6937576352334e4b5450744c51666f504b

# When you do the comparison you are comparing the varchar value in the table to the bytea type that is pwd.

cur.execute('select * from pwd_test where password = %s', [pwd])                                                                                                          
---------------------------------------------------------------------------
UndefinedFunction                         Traceback (most recent call last)
<ipython-input-15-e2bde44ff261> in <module>
----> 1 cur.execute('select * from pwd_test where password = %s', [pwd])

UndefinedFunction: operator does not exist: character varying = bytea
LINE 1: select * from pwd_test where password = '\x24326224313224445...
                                              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

# You need to cast the varchar to bytea
cur.execute('select * from pwd_test where password::bytea = %s', [pwd]) 

cur.fetchone()
 id |                                                          password                                                          
---- ----------------------------------------------------------------------------------------------------------------------------
  2 | \x243262243132244454534a53737575687767794d64534f714c6d62302e3452416b2e736d7851455261732f6937576352334e4b5450744c51666f504b

因此，您的選擇是在查詢中執行passwordtobytea的強制轉換或更改passwordto的列型別bytea。

標籤：Python PostgreSQL psycopg2

上一篇：在讀提交事務中，每個WITH查詢都算作一個單獨的命令嗎？

下一篇：根據postgresql表中的條件洗掉并保留某些行