Get or create object in python with psycopg postgresql

Python -- Posted on Nov. 5, 2023

The code you provided is a Python function named get_or_create_obj that is designed to interact with a database. This function takes two arguments: data, which is a dictionary representing the data to be inserted or checked in the database, and db_table, which is the name of the database table in which the operation is to be performed. The function performs the following steps:

  1. It constructs a SQL query to select data from the specified database table based on the key-value pairs in the data dictionary.

  2. It removes the trailing "AND" from the query string to ensure it's a valid SQL query.

  3. It executes the SQL query with the dynamically generated conditions by using the cursor.

  4. It fetches the result of the SQL query using cursor.fetchone().

  5. It checks if the result is None, indicating that no matching data was found in the database.

  6. If no matching data is found, it constructs an SQL insert query to insert the data from the data dictionary into the specified database table. It uses the "ON CONFLICT DO NOTHING" clause, which means that if there is a conflict (e.g., a unique constraint violation), the insertion will be skipped without an error.

  7. It executes the insert query using the cursor and commits the transaction to the database.

This function effectively either retrieves an existing database record based on the provided data or creates a new record if it doesn't exist, while also handling potential conflicts to avoid duplicate records.

Please note that the code assumes that you have already established a database connection and have a valid cursor to execute SQL queries. Additionally, the specific database you are working with may have variations in syntax, so you should ensure that the SQL statements generated by this code are compatible with your database system.

                def get_or_create_obj(data,db_table):
    columns = ', '.join(data.keys())
    values = ', '.join(['%s' for _ in data])
    query = "SELECT * FROM your_table WHERE "
    params = []
    for key, value in data.items():
        query += f"{key} = %s AND "

    # Remove the trailing "AND " from the query
    query = query[:-4]
    # Execute the SQL query with the dynamically generated conditions
    cursor.execute(query, params)
    # Fetch the result
    result = cursor.fetchone()
    if result is None:
        insert_query = f"INSERT INTO {db_table} ({columns}) VALUES ({values}) ON CONFLICT DO NOTHING"
        # Execute the SQL query
        cursor.execute(insert_query, list(data.values()))
        # Commit the transaction

Related Posts