[2751] | 1 | #!/usr/bin/env python |
---|
| 2 | # -*- coding: utf-8 -*- |
---|
| 3 | |
---|
[2775] | 4 | # ==================================================================== # |
---|
| 5 | # Author: Sonia Labetoulle # |
---|
| 6 | # Contact: sonia.labetoulle _at_ ipsl.jussieu.fr # |
---|
| 7 | # Created: 2016 # |
---|
| 8 | # History: # |
---|
| 9 | # Modification: # |
---|
| 10 | # ==================================================================== # |
---|
| 11 | |
---|
[2751] | 12 | # =================================================================== # |
---|
| 13 | # ssh readonly@prodiguer-test-db.ipsl.upmc.fr # |
---|
| 14 | # psql -U prodiguer_db_user prodiguer # |
---|
| 15 | # # |
---|
| 16 | # ssh readonly@prodiguer-test-db.ipsl.upmc.fr -L 5432:localhost:5432 # |
---|
| 17 | # =================================================================== # |
---|
| 18 | |
---|
| 19 | |
---|
| 20 | # This must come first |
---|
| 21 | from __future__ import print_function, unicode_literals, division |
---|
| 22 | |
---|
| 23 | # Standard library imports |
---|
| 24 | import psycopg2 |
---|
| 25 | import psycopg2.extras |
---|
| 26 | |
---|
| 27 | # Application library imports |
---|
| 28 | |
---|
| 29 | |
---|
| 30 | ####################################################################### |
---|
[2783] | 31 | def connect_db(db_host, db_name, db_user, db_pwd): |
---|
[2751] | 32 | |
---|
[2783] | 33 | conn_string = "host={} dbname={} user={} password={}".format( |
---|
| 34 | db_host, db_name, db_user, db_pwd |
---|
[2751] | 35 | ) |
---|
| 36 | |
---|
| 37 | # # Print the connection string we will use to connect |
---|
| 38 | # print("Connecting to database\n -> {}".format(conn_string)) |
---|
| 39 | |
---|
| 40 | # Get a connection, if a connect cannot be made an exception will be |
---|
| 41 | # raised here |
---|
| 42 | try : |
---|
| 43 | conn = psycopg2.connect(conn_string) |
---|
| 44 | except Exception as rc: |
---|
| 45 | print("Problem with DB connect:\n{}".format(rc)) |
---|
[2783] | 46 | exit(9) |
---|
[2751] | 47 | |
---|
| 48 | # conn.cursor will return a cursor object, you can use this cursor |
---|
| 49 | # to perform queries |
---|
| 50 | # cursor = conn.cursor() |
---|
| 51 | cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) |
---|
| 52 | print(" => Connected!\n") |
---|
| 53 | |
---|
| 54 | return conn, cursor |
---|
| 55 | |
---|
| 56 | |
---|
| 57 | ####################################################################### |
---|
| 58 | def select_db(cursor, request): |
---|
| 59 | |
---|
| 60 | # Execute query |
---|
| 61 | try: |
---|
| 62 | cursor.execute(request) |
---|
| 63 | except Exception as rc: |
---|
| 64 | print("Problem with select:\n{}".format(rc)) |
---|
[2783] | 65 | exit(9) |
---|
[2751] | 66 | |
---|
| 67 | # # retrieve the records from the database |
---|
| 68 | # records = cursor.fetchall() |
---|
| 69 | |
---|
| 70 | # # print out the records using pretty print |
---|
| 71 | # # note that the NAMES of the columns are not shown, instead just indexes. |
---|
| 72 | # # for most people this isn't very useful so we'll show you how to return |
---|
| 73 | # # columns as a dictionary (hash) in the next example. |
---|
| 74 | # pprint(records) |
---|
| 75 | |
---|
| 76 | |
---|
| 77 | ####################################################################### |
---|
| 78 | def insert_db(cursor, request): |
---|
| 79 | """ |
---|
| 80 | """ |
---|
| 81 | |
---|
| 82 | # Execute query |
---|
| 83 | try: |
---|
| 84 | cursor.execute(request) |
---|
| 85 | except Exception as rc: |
---|
| 86 | print("Problem with insert:\n{}".format(rc)) |
---|
[2783] | 87 | exit(9) |
---|
[2751] | 88 | |
---|
| 89 | |
---|
| 90 | ####################################################################### |
---|
| 91 | def commit_db(conn): |
---|
| 92 | """ |
---|
| 93 | """ |
---|
| 94 | |
---|
| 95 | # Execute query |
---|
| 96 | try: |
---|
| 97 | conn.commit() |
---|
| 98 | except Exception as rc: |
---|
| 99 | print("Problem with commit:\n{}".format(rc)) |
---|
[2783] | 100 | exit(9) |
---|
[2751] | 101 | |
---|
| 102 | |
---|
| 103 | ####################################################################### |
---|
| 104 | def close_db(conn): |
---|
| 105 | """ |
---|
| 106 | """ |
---|
| 107 | |
---|
| 108 | try: |
---|
| 109 | conn.close() |
---|
| 110 | except Exception as rc: |
---|
| 111 | print("Problem with close:\n{}".format(rc)) |
---|
[2783] | 112 | exit(9) |
---|
[2751] | 113 | |
---|
| 114 | |
---|
| 115 | ####################################################################### |
---|
| 116 | if __name__ == "__main__": |
---|
| 117 | pass |
---|