1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
|
#coding:utf-8
import os
import sys
import sunhpc
import threading
import subprocess
import sqlalchemy
import sqlalchemy.exc
from sqlalchemy import create_engine
from sunhpc.db.mappings.base import *
threadlocal = threading.local()
class Database(object):
"""
This class should proxy all the connection to the database.
There are two main internal objects inside this class which come from
sqlalchemy:
- session: this is used by the ORM layer
- connection: this is used by the execute statement, so every time
you use pure sql
These two objects have two separate DB connections, which means
that DB status can be different when queried through them.
Usage Example::
db = Database()
db.setVerbose()
db.connect()
"""
def __init__(self):
self.conn = None
self.engine = None
self.verbose = False
self.results = False
self._dbPath = "/opt/sunhpc/data"
self._dbFile = "sunhpc.db"
self._dbLock = ".database"
self._datafile = os.path.join(self._dbPath, self._dbFile)
def setVerbose(self, verbose):
"""
If the verbose is true all the sql will be printed to
stdout. This function must be called before the connect
:type verbose: bool
:param verbose: if verbose should be set to True
"""
self.verbose = verbose
def getDBFile(self):
return self._datafile
def connect(self):
"""
It start the connection to the DB and create all the internal
data structure
"""
if 'SUNHPCDEBUG' in os.environ:
self.setVerbose(True)
url = 'sqlite:///' + self._dbPath + '/' + self._dbFile
if self.verbose:
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
if self.verbose:
print ("Database connection URL: ", url)
self.engine = create_engine(url)
self.conn = self.engine.connect()
def reconnect(self):
self.engine.dispose()
self.conn = self.engine.connect()
def getSession(self):
session = getattr(threadlocal, "session", None)
if session:
return session
elif self.engine:
Session = sqlalchemy.orm.sessionmaker(bind=self.engine)
session = Session()
setattr(threadlocal, "session", session)
return session
else:
return None
def closeSession(self):
session = getattr(threadlocal, "session", None)
if session:
session.close()
setattr(threadlocal, "session", None)
return
return
def commit(self):
"""
Commit the current session if it existsi. *It does not touch the
connection.*
"""
session = self.getSession()
if session:
session.commit()
else:
pass
def search(self, command):
self.execute(command)
rows = self.fetchall()
self.execute(command)
return len(rows)
def execute(self, command):
if self.conn:
if '%' in command:
command = command.replace('%', '%%')
try:
self.results = self.conn.execute(command)
except sqlalchemy.exc.OperationalError as e:
self.renewConnection()
self.results = self.conn.execute(command)
return self.results.rowcount
else:
return None
def fetchone(self):
"""
Fetch one row from the results of the previous query
:rtype: tuple
:return: a tuple containing the values of the fetched row.
It really returns a :class:`sqlalchemy.engine.result.RowProxy`
but it can be treated as a tuple
"""
if self.results:
return self.results.fetchone()
return ()
def fetchall(self):
"""
Fetch all rows from the results of the previous query
:rtype: list
:return: a list of tuples containing the values of the fetched rows
"""
if self.results:
return self.results.fetchall()
return ()
def close(self):
"""
It closes the connection only. You also need to close the
session, if you want to release all the DB resources
:meth:`closeSession`
"""
if self.results:
self.results.close()
self.results = None
if self.conn:
self.conn.close()
def renewConnection(self):
"""
It renews the connection, if inactive for few hours mysql
closes down the connection, so you might need to renew it.
"""
self.close()
self.conn = self.engine.connect()
if __name__ == "__main__":
d = Database()
d.connect()
conn = d.getSession()
print (conn)
qry = conn.query(Node)
print (qry.all())
|
