cjklib.dbconnector

29 """ 30 A DatabaseConnector provides simple read access to a SQL database. 31 32 On initialisation it connects to a given database. Once connected it's 33 select methods can be used to quickly access the database content. 34 35 DatabaseConnector supports a wide range of database systems through 36 I{SQLalchemy}. 37 38 As only standard select commands are issued further systems should be easy 39 to incorporate. 40 41 For selecting entries there are for different methods given: 42 1. C{selectRows()}: the most general select method 43 2. C{selectScalars()}: returns entries for only one column 44 3. C{selectRow()}: returns only one entry 45 4. C{selectScalar()}: returns one single value 46 """ 47 dbconnectInst = None 48 """ 49 Instance of a L{DatabaseConnector} used for all connections to SQL server. 50 """ 51 databaseUrl = None 52 """ 53 Database url used to create the connector instance. 54 """ 55 56 @classmethod

57 - def getDBConnector(cls, databaseUrl=None):

58 """ 59 Returns a shared L{DatabaseConnector} instance. 60 61 @type databaseUrl: str 62 @param databaseUrl: database url passed to the L{DatabaseConnector} 63 """ 64 if cls.databaseUrl and databaseUrl \ 65 and cls.databaseUrl != databaseUrl: 66 cls.dbconnectInst = None 67 68 if not cls.dbconnectInst: 69 # get database settings and connect to database 70 # if no settings given read from config or assume default 71 if not databaseUrl: 72 # try to read from config 73 databaseSettings = DatabaseConnector.getConfigSettings('cjklib') 74 if 'databaseUrl' in databaseSettings: 75 databaseUrl = databaseSettings['databaseUrl'] 76 else: 77 # default 78 databaseUrl = 'sqlite:///cjklib.db' 79 cls.dbconnectInst = DatabaseConnector(databaseUrl) 80 cls.databaseUrl = databaseUrl 81 return cls.dbconnectInst

82 83 @staticmethod

84 - def getConfigSettings(projectName):

85 """ 86 Gets the SQL connection parameter from a config file. 87 88 @type projectName: str 89 @param projectName: name of project which will be used as name of the 90 config file 91 @rtype: dict 92 @return: configuration settings for the given project 93 """ 94 try: 95 databaseSettings = {} 96 import ConfigParser 97 import os 98 import os.path 99 config = ConfigParser.SafeConfigParser() 100 config.read([os.path.join(os.path.expanduser('~'), '.' \ 101 + projectName + '.conf'), 102 os.path.join('/', 'etc', projectName + '.conf')]) 103 104 try: 105 databaseSettings['databaseUrl'] = config.get('General', 106 'databaseUrl') 107 except ConfigParser.NoOptionError: 108 pass 109 110 return databaseSettings 111 112 except ConfigParser.NoSectionError: 113 return {}

114

115 - def __init__(self, databaseUrl):

116 """ 117 Constructs the DatabaseConnector object and connects to the database 118 specified by the options given in databaseSettings. 119 120 @type databaseUrl: str 121 @param databaseUrl: database connection setting in the format 122 C{driver://user:pass@host/database}. 123 """ 124 self.databaseUrl = databaseUrl 125 # connect to database 126 self.engine = create_engine(databaseUrl, echo=False) 127 # create connection 128 self.connection = self.engine.connect() 129 # parse table information 130 self.metadata = MetaData(bind=self.connection, reflect=True) 131 # short cut 132 self.tables = self.metadata.tables 133 134 self._registerViews()

135

136 - def _registerViews(self):

137 """ 138 Registers all views and makes them accessible through the same methods 139 as tables in SQLalchemy. 140 141 @attention: Currently only works for MySQL and SQLite. 142 """ 143 if self.engine.name == 'mysql': 144 viewList = self.execute( 145 text("""SELECT table_name FROM Information_schema.view 146 WHERE table_schema = :dbName"""), 147 dbName=self.dbName).fetchall() 148 elif self.engine.name == 'sqlite': 149 viewList = self.execute( 150 text("SELECT name FROM sqlite_master WHERE type IN ('view')"))\ 151 .fetchall() 152 else: 153 import logging 154 logging.warning("Don't know how to get all views from database. Unable to register. Views will not show up in list of available tables.") 155 return 156 157 for viewName, in viewList: 158 # add views that are currently not (well) supported by SQLalchemy 159 # http://www.sqlalchemy.org/trac/ticket/812 160 Table(viewName, self.metadata, autoload=True)

161

162 - def execute(self, *options, **keywords):

163 """ 164 Executes a request on the given database. 165 """ 166 return self.connection.execute(*options, **keywords)

167 168 # select commands 169

170 - def selectScalar(self, request):

171 """ 172 Executes a select query and returns a single variable. 173 174 @param request: SQL request 175 @return: a scalar 176 """ 177 result = self.connection.execute(request) 178 assert result.rowcount <= 1 179 firstRow = result.fetchone() 180 assert not firstRow or len(firstRow) == 1 181 if firstRow: 182 return firstRow[0]

183

184 - def selectScalars(self, request):

185 """ 186 Executes a select query and returns a list of scalars. 187 188 @param request: SQL request 189 @return: a list of scalars 190 """ 191 result = self.connection.execute(request) 192 return [row[0] for row in result.fetchall()]

193

194 - def selectRow(self, request):

195 """ 196 Executes a select query and returns a single table row. 197 198 @param request: SQL request 199 @return: a list of scalars 200 """ 201 result = self.connection.execute(request) 202 assert result.rowcount <= 1 203 firstRow = result.fetchone() 204 if firstRow: 205 return tuple(firstRow)

206

207 - def selectRows(self, request):

208 """ 209 Executes a select query and returns a list of table rows. 210 211 @param request: SQL request 212 @return: a list of scalars 213 """ 214 result = self.connection.execute(request) 215 return [tuple(row) for row in result.fetchall()]

Source Code for Module cjklib.dbconnector