CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
hyeogeon
hyeogeon

Reputation: 33

psycopg2 - insert None Using execute_values

insert_bulk

.

def insert_bulk(products: list):
    try:
        sql = """
        INSERT INTO product(
        id, created_date_time, name, expiration_date
        )
        SELECT
        id, NOW() AT TIME ZONE 'Asia/Seoul', name, expiration_date
        FROM (VALUES %s)
        AS products_insert (
            id,
            name,
            expiration_date
        );
        """

        products_insert = [[value for value in product.values()] for product in products]
        # products_insert
        # [['1','apple', None],['2', 'banana', None],['3', 'meat', None], ...]
        with Connector.connect() as connection:
            with connection.cursor() as cursor:
                psycopg2.extras.execute_values(
                    cursor,
                    sql,
                    products_insert,
                    page_size=500,
                )
                affected_row_count = cursor.rowcount
        print(affected_row_count, "products inserted successfully")
    except (Exception, Error) as error:
        print(
            "[product_repository] Error while insert_bulk",
            error,
        )

Connector

import psycopg2
from stock.properties.properties import properties

class Connector:

...

    @staticmethod
    def connect():
        connection = psycopg2.connect(**properties.database)
        return connection

When I try to insert None into expiration_date, the following error occurs.

column "expiration_date" is of type date but expression is of type text
HINT: You will need to rewrite or cast the expression.



I solved the problem by using type casting in update.
(expiration_date = product_update.expiration_date::date)

sql = """
    UPDATE product
    SET
    id = product_update.id,
    name = product_update.name,
    expiration_date = product_update.expiration_date::date
    FROM (VALUES %s)
    AS product_update (
        id,
        name,
        expiration_date
    ) 
    WHERE product_update.id = product.id;
    """

But this way didn't work in insert.

So How can I insert None into date type?

Upvotes: 0

Views: 688

Answers (1)

hyeogeon
hyeogeon

Reputation: 33

template argument is effective

with Connector.connect() as connection:
            with connection.cursor() as cursor:
                psycopg2.extras.execute_values(
                    cursor,
                    sql,
                    products_insert,
                    template='(%s, %s, %s::date)'
                    page_size=500,
                )

Upvotes: 1

Related Questions