时间戳

首页 > 解决方案 > Insert into table with type casting and condition in INSERT statement

sql - Insert into table with type casting and condition in INSERT statement

问题描述

Based on this previous question

I am trying to compose an insert statement in Python using psycopg2 with some data. The query works beautifully in postgresql console but when I try it from psycopg2 I get an error.

Here's the table i want to insert to

create_test_table = """CREATE TABLE persons(
   name           VARCHAR,
   age            INT,
   dob            DATE,
   sex            VARCHAR);"""

Here's the data I want to insert

d_ = {"name":"Gino", "age": "", "dob": "na", "sex": "M"}

And here's my query:

cur.execute("""INSERT INTO persons (age,dob,sex,name) 
               VALUES (CASE WHEN cast(%(age)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(age)s END,
                       CASE WHEN cast(%(dob)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(dob)s END,
                       CASE WHEN cast(%(sex)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(sex)s END,
                       CASE WHEN cast(%(name)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(name)s END)""", d_)

Error message:

----> 5  CASE WHEN cast(%(name)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE %(name)s END)""", d_)

ProgrammingError: column "age" is of type integer but expression is of type text
LINE 2:                VALUES (CASE WHEN cast('' as text) IN ('', '#...
                               ^
HINT:  You will need to rewrite or cast the expression.

Maybe it is not possible to do this in psycopg2. Any help would be appreciated :))

标签： sqlpostgresqlinsertpsycopg2

解决方案

您的参数替换不正确。例如，而不是这个：

%(CASE WHEN cast(age as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE age END)s

你应该有：

(CASE WHEN cast(%(age)s as text) IN ('', '#', '-', '--', '??') THEN NULL ELSE age END)

推荐阅读