how to insert data in postgres table

#!/usr/bin/python import psycopg2 from config import config def insert_vendor(vendor_name): """ insert a new vendor into the vendors table """ sql = """INSERT INTO vendors(vendor_name) VALUES(%s) RETURNING vendor_id;""" conn = None vendor_id = None try: # read database configuration params = config() # connect to the PostgreSQL database conn = psycopg2.connect(**params) # create a new cursor cur = conn.cursor() # execute the INSERT statement cur.execute(sql, (vendor_name,)) # get the generated id back vendor_id = cur.fetchone()[0] # commit the changes to the database conn.commit() # close communication with the database cur.close() except (Exception, psycopg2.DatabaseError) as error: print(error) finally: if conn is not None: conn.close() return vendor_id

insert record into table postgresql

try:
        connection = psycopg2.connect(**postgres_credentials())
        cursor = connection.cursor()

        records = [(1,'FOO'),(2,'SPAM')]

        placeholders = ','.join(['%s']*len(records)) # => '%s,%s'
        sql = f"""
            INSERT INTO schema.table(id, field)
            VALUES {placeholders}
        """
                       
        # Mogrify helpful to debug command sent to DB bc transforms command into human readable form. 
        # It's not necessary. Could just use executemany, but slower & harder to debug command as SO suggests.
        insert_statement = cursor.mogrify(sql, records)
        # print(insert_statement.decode('utf-8'))

        cursor.execute(insert_statement)
        # cursor.executemany(sql, records) # SLOW bc executes and commits each record one at a time.
        # print(cursor.mogrify(sql, records).decode('utf-8'))

        connection.commit()

    except psycopg2.DatabaseError:
        raise
    finally:
        if not connection.closed:
            cursor.close()
            connection.close()

Posted by: Guest on May-20-2021

Source

Code answers related to "how to insert data in postgres table"

Browse Popular Code Answers by Language

Answers for "how to insert data in postgres table"

Code answers related to "how to insert data in postgres table"

Code answers related to "SQL"

Browse Popular Code Answers by Language

Popular Programming Languages

Advertisements

Company

Compilers

Help

Connect with us