insert into postgres python

1
2        
3            
4        
5     #!/usr/bin/python
6
7import psycopg2
8from config import config
9
10
11def insert_vendor(vendor_name):
12    """ insert a new vendor into the vendors table """
13    sql = """INSERT INTO vendors(vendor_name)
14             VALUES(%s) RETURNING vendor_id;"""
15    conn = None
16    vendor_id = None
17    try:
18        # read database configuration
19        params = config()
20        # connect to the PostgreSQL database
21        conn = psycopg2.connect(**params)
22        # create a new cursor
23        cur = conn.cursor()
24        # execute the INSERT statement
25        cur.execute(sql, (vendor_name,))
26        # get the generated id back
27        vendor_id = cur.fetchone()[0]
28        # commit the changes to the database
29        conn.commit()
30        # close communication with the database
31        cur.close()
32    except (Exception, psycopg2.DatabaseError) as error:
33        print(error)
34    finally:
35        if conn is not None:
36            conn.close()
37
38    return vendor_id

1
2        
3            
4        
5     def insert_vendor_list(vendor_list):
6    """ insert multiple vendors into the vendors table  """
7    sql = "INSERT INTO vendors(vendor_name) VALUES(%s)"
8    conn = None
9    try:
10        # read database configuration
11        params = config()
12        # connect to the PostgreSQL database
13        conn = psycopg2.connect(**params)
14        # create a new cursor
15        cur = conn.cursor()
16        # execute the INSERT statement
17        cur.executemany(sql,vendor_list)
18        # commit the changes to the database
19        conn.commit()
20        # close communication with the database
21        cur.close()
22    except (Exception, psycopg2.DatabaseError) as error:
23        print(error)
24    finally:
25        if conn is not None:
26            conn.close()