Subversion Repositories SE.SVN

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
35 7u83 1 
"""
2 
Functions and classes to handle databases
3 
"""
4 
 
5 
from psycopg2 import connect,threadsafety,paramstyle
6 
 
7 
import psycopg2 as pgdb
8 
 
9 
#from pyPgSQL.PgSQL import concnect,threadsafety,paramstyle
10 
 
11 
import threading
12 
import random
13 
from . import config
14 
 
15 
#def TimestampFromTicks(ticks):
16 
#    return pgdb.TimestampFromTicks(ticks)
17 
 
18 
 
19 
class ConnectionPool:
20 
    """
21 
    Implements a db connection pool
22 
    """
23 
 
24 
    def __init__(self,**args):
25 
        self.pool = []
26 
        self.glock = threading.Lock()
27 
        self.rlock = threading.Lock()
28 
        self.notifier = threading.Condition()
29 
        self.cursormap = {}
30 
        self.args=args
31 
 
32 
    def connect(self):
33 
        return  pgdb.connect(**self.args)
34 
 
35 
 
36 
    def get_conn(self):
37 
        """
38 
        get a database connection
39 
        """
40 
 
41 
        self.glock.acquire()
42 
 
43 
        while len(self.pool)==0:
44 
            self.notifier.acquire()
45 
            self.notifier.wait()
46 
            self.notifier.release()
47 
 
48 
        r = self.pool.pop()
49 
        self.glock.release()
50 
 
51 
        # Hack for pgdb
52 
        try:
53 
            r.rollback()
54 
        except:
55 
            pass
56 
 
57 
        return r
58 
 
59 
 
60 
    def free_conn(self,c):
61 
        """
62 
        free a connection aquired by get_con()
63 
        """
64 
        try:
65 
            c.rollback()
66 
        except:
67 
            pass
68 
 
69 
        self.rlock.acquire()
70 
        self.notifier.acquire()
71 
        self.pool.append(c)
72 
        self.notifier.notify()
73 
        self.notifier.release()
74 
        self.rlock.release()
75 
 
76 
 
77 
    def get_cursor(self):
78 
 
79 
        db = self.get_conn()
80 
 
81 
        cursor = db.cursor()
82 
        self.cursormap[cursor]=db
83 
        return cursor
84 
 
85 
    def free_cursor(self,cursor,commit=True):
86 
        db = self.cursormap[cursor]
87 
        del self.cursormap[cursor]
88 
        cursor.close()
89 
        if commit:
90 
            db.commit()
91 
        else:
92 
            db.rollback()
93 
        self.free_conn(db)
94 
 
95 
    def create(self,poolsize):
96 
        for i in range(0,poolsize):
97 
            c = self.connect ()
98 
            self.pool.append(c)
99 
 
100 
 
101 
    def check_conn(self,conn):
102 
        """
103 
        Check if a connection is alive
104 
        Inputs the connection object
105 
        Returns true if the connection is alive otherwise False
106 
        """
107 
        try:
108 
            cursor=conn.cursor()
109 
        except Exception as e:
110 
            if type(e) != pgdb.InterfaceError:
111 
                return False
112 
 
113 
        try:
114 
            cursor.execute("SELECT 1")
115 
            cursor.close()
116 
 
117 
        except Exception as e:
118 
            if type(e) != pgdb.OperationalError:
119 
                return False
120 
            raise
121 
 
122 
        return True
123 
 
124 
 
125 
 
126 
cursormap = {}
127 
 
128 
def get_cursor():
129 
    db = get_conn()
130 
    cursor = db.cursor()
131 
    cursormap[cursor]=db
132 
    return cursor
133 
 
134 
def free_cursor(cursor,commit=True):
135 
    db = cursormap[cursor]
136 
    del cursormap[cursor]
137 
    cursor.close()
138 
    if commit:
139 
        db.commit()
140 
    else:
141 
        db.rollback()
142 
    free_conn(db)
143 
 
144 
 
145 
 
146 
 
147 
def row_to_dict(cursor,row):
148 
    """
149 
    Converts a row, fetched from a database to, a dictionary
150 
 
151 
    Example:
152 
        result = cursor.fetchone()
153 
        dictresult = row_to_dict(cursor,reult)
154 
    """
155 
    i=0
156 
    r={}
157 
    if row == None:
158 
        return
159 
 
160 
    for d in cursor.description:
161 
        r[d[0]]=row[i]
162 
        i=i+1
163 
 
164 
    return r
165 
 
166 
 
167 
def make_random_id( len=32 ):
168 
    str=''
169 
    for i in range(0,len):
170 
        h = random.randint(0,9)
171 
        str += repr(h)
172 
    return str
173 
 
174 
 
175 
def create_connection_pool(conffile,conf_section):
176 
    """
177 
    Create a connection pool
178 
    """
179 
    cfg = config.Cfg(conffile)
180 
 
181 
    connpool = ConnectionPool(
182 
        user=cfg.get(conf_section,'dbuser'),
183 
        password=cfg.get(conf_section,'dbpassword'),
184 
        host=cfg.get(conf_section,'dbhost'),
185 
        database=cfg.get(conf_section,'dbdatabase'),
186 
        port=cfg.get(conf_section,'dbport')
187 
        )
188 
 
189 
    connpool.create(int(cfg.get(conf_section,'poolsize')))
190 
    return connpool
191 
 
192 
 
193 
 
194