pgconsole/database.py
author Radek Brich <brich.radek@ifortuna.cz>
Tue, 06 May 2014 18:34:38 +0200
changeset 99 245646538743
parent 76 3a41b351b122
permissions -rw-r--r--
Update runquery tool: Add --one-query-per-line parameter.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
10
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     1
import psycopg2
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     2
import psycopg2.extensions
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     3
import psycopg2.extras
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     4
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     5
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     6
class DatabaseError(Exception):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     7
    def __init__(self, msg, query=None):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     8
        self.query = query
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
     9
        Exception.__init__(self, msg)
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    10
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    11
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    12
class BadConnectionError(Exception):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    13
    pass
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    14
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    15
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    16
class Row(dict):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    17
    def __getattr__(self, key):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    18
        return self[key]
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    19
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    20
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    21
class Database:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    22
    def __init__(self):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    23
        # pool of database connections
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    24
        # indexed by conninfo, items are lists of connections
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    25
        self.pool = {}
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    26
        # number of unused connections per conninfo to keep open
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    27
        self.pool_keep_open = 1
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    28
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    29
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    30
    def __del__(self):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    31
        for conninfo in self.pool.keys():
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    32
            for conn in self.pool[conninfo]:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    33
                conn.close()
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    34
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    35
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    36
    def connect(self, conninfo):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    37
        try:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    38
            conn = psycopg2.connect(conninfo, async=1)
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    39
            psycopg2.extras.wait_select(conn)
76
3a41b351b122 Port pgconsole to Python3 + GTK3.
Radek Brich <radek.brich@devl.cz>
parents: 10
diff changeset
    40
        except psycopg2.DatabaseError as e:
10
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    41
            raise DatabaseError(str(e))
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    42
        return conn
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    43
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    44
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    45
    def get_conn(self, conninfo):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    46
        if not conninfo in self.pool:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    47
            self.pool[conninfo] = []
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    48
            return self.connect(conninfo)
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    49
        else:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    50
            conn = None
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    51
            while len(self.pool[conninfo]) and conn is None:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    52
                conn = self.pool[conninfo].pop()
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    53
                if conn.closed:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    54
                    conn = None
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    55
            if conn is None:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    56
                return self.connect(conninfo)
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    57
        return conn
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    58
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    59
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    60
    def put_conn(self, conninfo, conn):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    61
        if len(self.pool[conninfo]) >= self.pool_keep_open:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    62
            conn.close()
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    63
        else:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    64
            self.pool[conninfo].append(conn)
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    65
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    66
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    67
    def execute(self, q, args=[]):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    68
        conn = self.get_conn()
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    69
        try:
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    70
            curs = conn.cursor()
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    71
            curs.execute(q, args)
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    72
            psycopg2.extras.wait_select(curs.connection)
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    73
#            conn.commit()
76
3a41b351b122 Port pgconsole to Python3 + GTK3.
Radek Brich <radek.brich@devl.cz>
parents: 10
diff changeset
    74
        except psycopg2.OperationalError as e:
10
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    75
            # disconnected?
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    76
#            conn.rollback()
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    77
            conn.close()
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    78
            raise BadConnectionError(str(e))
76
3a41b351b122 Port pgconsole to Python3 + GTK3.
Radek Brich <radek.brich@devl.cz>
parents: 10
diff changeset
    79
        except psycopg2.DatabaseError as e:
10
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    80
#            conn.rollback()
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    81
            raise DatabaseError(str(e), curs.query)
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    82
        return curs
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    83
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    84
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    85
    def finish(self, curs):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    86
        self.put_conn(curs.connection)
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    87
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    88
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    89
    def row(self, curs, row):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    90
        return Row(zip([x[0] for x in curs.description], row))
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    91
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    92
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    93
    def fetchone(self, curs):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    94
        return self.row(curs, curs.fetchone())
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    95
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    96
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    97
    def fetchall(self, curs):
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    98
        rows = curs.fetchall()
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
    99
        return [self.row(curs, row) for row in rows]
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   100
f3a1b9792cc9 Added pgconsole. It is my older project, a GUI query console. It uses GTK+ and asynchronous queries.
Radek Brich <radek.brich@devl.cz>
parents:
diff changeset
   101