def writejson(resp): try: tmp = json.dumps(resp.dict(), cls=db.JsonOrmEncoder) response.write(tmp) except ValueError: raise bc.Error(1, "JSON ValueError for " + resp.dict()) except TypeError: raise bc.Error(1, "JSON TypeError for " + resp.dict())
def count(self, query): sql = "select count(*) from %s" % (query.table()) sql2, values = query.toSql() sql += sql2 self.execute(sql, values) try: row = self.cursor.fetchone() if row is None: raise bc.Error( 1, 'Cannot query for count(*) in %s' % (query.table())) rows = int(row['count(*)']) except mysql.connector.Error as err: raise bc.Error(err.errno, str(err)) return rows
def count(self, query): sql = "select count(*) from %s" % (query.table()) sql2, values = query.toSql() sql += sql2 self.execute(sql, values) try: row = self.cursor.fetchone() if row is None: raise bc.Error( 1, 'Cannot query for count(*) in %s' % (query.table())) data = int(row[0]) except psycopg2.DatabaseError as e: raise bc.Error(1, str(e)) return data
def count(self, query): sql = "select count(*) from %s" % (query.table()) sql2, values = query.toSql() sql += sql2 self.execute(sql, values) try: row = self.cursor.fetchone() if row is not None: key = 'count(*)' rows = int(row[key]) else: raise bc.Error( 1, 'Cannot query for count(*) in %s' % (query.table())) except sqlite3.Error as e: raise bc.Error(1, e.args[0]) return rows
def execute(self, sql, values=None, commit=False): """ Execute a query, if error try to reconnect and redo the query to handle timeouts """ for i in range(0, 2): if self.dbconnection is None: self.connect() if self.debug & bc.DEBUG_SQL: self.log.debug('SQL=%s, values=%s' % (sql, values)) try: if values is not None: self.cursor.execute(sql, values) else: self.cursor.execute(sql) if commit: self.dbconnection.commit() return except mysql.connector.Error as err: if self.dbconnection is not None: try: self.dbconnection.commit() except mysql.connector.Error as err: pass if i == 1: raise bc.Error(err.errno, str(err)) self.disconnect()
def connect(self): try: self.dbconnection = sqlite3.connect(self.dbconf.database, check_same_thread=False) self.dbconnection.row_factory = sqlite3.Row # return querys as dictionaries self.cursor = self.dbconnection.cursor() except sqlite3.Error as e: raise bc.Error(1, e.args[0])
def count(self, query_): if isinstance(query_, basium_model.Model): query = Query(query_) elif isinstance(query_, Query): query = query_ else: raise bc.Error(1, "Fatal: incorrect object type in count") return self.driver.count(query)
def delete(self, query): """ delete a row from a table "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) returns number of rows deleted """ sql = "DELETE FROM %s" % query.table() sql2, values = query.toSql() if sql2 == '': raise bc.Error( 1, 'Missing query on delete(), empty query is not accepted') sql += sql2.replace("%s", "?") self.execute(sql, values) try: data = self.cursor.rowcount except sqlite3.Error as e: raise bc.Error(1, e.args[0]) return data
def connect(self): try: if not self.dbconf.port: self.dbconf.port = 5432 self.dbconnection = psycopg2.connect(host=self.dbconf.host, port=self.dbconf.port, user=self.dbconf.username, password=self.dbconf.password, dbname=self.dbconf.database) self.cursor = self.dbconnection.cursor( cursor_factory=psycopg2.extras.DictCursor) except psycopg2.DatabaseError as e: raise bc.Error(1, str(e))
def load(self, query_): """ Fetch one or multiple rows from table, each stored in a object If no query is specified, the default is to fetch one object identified with the object._id Query can be either An instance of Model() An instance of Query() Driver returns an object that can be iterated over one row at a time or throws DriverError Note: when loading a single object, an error is returned if not found. Workaround is to use a query instead """ one = False if isinstance(query_, basium_model.Model): query = Query().filter(query_.q._id, EQ, query_._id) one = True elif isinstance(query_, Query): query = query_ else: raise bc.Error(1, "Fatal: incorrect object type") data = [] for row in self.driver.select(query): newobj = query._model.__class__() for colname, column in newobj._iterNameColumn(): try: newobj._values[colname] = column.toPython(row[colname]) except (KeyError, ValueError): pass data.append(newobj) if one and len(data) < 1: raise bc.Error( 1, "Unknown ID %s in table %s" % (query_._id, query_._table)) return data
def isTable(self, tableName): """ Returns True if the table exist """ if not self.tables: self.tables = {} sql = "SELECT name FROM sqlite_master WHERE type='table'" self.execute(sql) try: for row in self.cursor.fetchall(): self.tables[row[0]] = 1 except sqlite3.Error as e: raise bc.Error(1, e.args[0]) return tableName in self.tables
def isDatabase(self, dbName): """ Returns True if the database exist """ sql = "select * from pg_database where datname=%s" # % dbName values = (dbName, ) self.execute(sql, values) try: row = self.cursor.fetchone() if row and len(row) > 0: exist = row[0] == dbName except psycopg2.DatabaseError as e: raise bc.Error(1, str(e)) return exist
def delete(self, query): """ delete a row from a table "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20) refuses to delete all rows in a table (empty query) returns number of rows deleted """ sql = "DELETE FROM %s" % query.table() sql2, values = query.toSql() if sql2 == '': raise bc.Error(1, 'delete() with empty query not accepted') sql += sql2 self.execute(sql, values, commit=True) return self.cursor.rowcount
def isDatabase(self, dbName): """ Returns True if the database exist """ sql = "SELECT IF(EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '%s'), 'Yes','No')" % dbName exist = False self.execute(sql) try: row = self.cursor.fetchone() key = list(row.keys())[0] exist = row[key] == 'Yes' except mysql.connector.Error as err: raise bc.Error(err.errno, str(err)) return exist
def delete(self, query): """ delete a row from a table "DELETE FROM EMPLOYEE WHERE AGE > '%s'", (20, ) returns number of rows deleted """ sql = "DELETE FROM %s" % query.table() sql2, values = query.toSql() if sql2 == '': raise bc.Error( 1, 'Missing query on delete(), empty query is not accepted') sql += sql2 self.execute(sql, values, commit=True) return self.cursor.rowcount
def isTable(self, tableName): """ Returns True if the table exist """ if not self.tables: self.tables = {} sql = "SELECT table_name FROM information_schema.tables WHERE table_schema='public'" values = (tableName, ) self.execute(sql, values) try: for row in self.cursor.fetchall(): self.tables[row[0]] = 1 except psycopg2.DatabaseError as e: raise bc.Error(1, str(e)) return tableName in self.tables
def isTable(self, tableName): """ Returns True if the table exist """ if not self.tables: # Read all tables and cache locally self.tables = {} sql = "show tables like %s" self.execute(sql, (tableName, )) try: for row in self.cursor.fetchall(): value = list(row.values())[0] self.tables[value] = 1 except mysql.connector.Error as err: raise bc.Error(err.errno, str(err)) return tableName in self.tables
def modifyTable(self, obj, actions): """ Update table to latest definition of class actions is the result from verifytable todo: sqlite only support a subset of functionality in "ALTER TABLE...", so we work around this by copying the table to a new one """ if len(actions) == 0: if self.debug & bc.DEBUG_TABLE_MGMT: self.log.debug(" Nothing to do") return self.log.debug("Actions that needs to be done:") askForConfirmation = False for action in actions: if self.debug & bc.DEBUG_TABLE_MGMT: self.log.debug(" %s" % action.msg) self.log.debug(" SQL: %s" % action.sqlcmd) if not action.unattended: askForConfirmation = True if askForConfirmation: self.log.debug( "WARNING: removal of columns can lead to data loss.") a = input('Are you sure (yes/No)? ') if a != 'yes': raise bc.Error(1, "Aborted!") # we first remove columns, so we dont get into conflicts # with the new columns, for example changing primary key (there can only be one primary key) for action in actions: if 'DROP' in action.sqlcmd: if self.debug & bc.DEBUG_TABLE_MGMT: self.log.debug("Fixing %s" % action.msg) self.log.debug(" Cmd: %s" % action.sqlcmd) self.cursor.execute(action.sqlcmd) for action in actions: if 'DROP' not in action.sqlcmd: self.log.debug("Fixing %s" % action.msg) self.log.debug(" Cmd: %s" % action.sqlcmd) self.cursor.execute(action.sqlcmd) self.dbconnection.commit()
def connect(self): try: if not self.dbconf.port: self.dbconf.port = 3306 self.dbconnection = mysql.connector.connect( host=self.dbconf.host, port=int(self.dbconf.port), user=self.dbconf.username, passwd=self.dbconf.password, db=self.dbconf.database) self.cursor = self.dbconnection.cursor( cursor_class=MySQLCursorDict) sql = "set autocommit=1;" if self.debug & bc.DEBUG_SQL: self.log.debug('SQL=%s' % sql) self.cursor.execute(sql) if self.dbconnection: self.dbconnection.commit() except mysql.connector.Error as err: raise bc.Error(err.errno, str(err))
def insert(self, table, values): """ Insert a row in the table value is a dictionary with columns, excluding primary key """ parms = [] holder = [] vals = [] for key, val in values.items(): if key != '_id': parms.append('"' + key + '"') holder.append("%s") vals.append(val) sql = "INSERT INTO %s ( %s ) VALUES ( %s ) RETURNING _id" % ( table, ",".join(parms), ",".join(holder)) self.execute(sql, vals, commit=True) try: data = self.cursor.fetchone()[0] except psycopg2.DatabaseError as e: raise bc.Error(1, str(e)) return data
def delete(self, query_): """ Delete objects in the table. query_ can be either An instance of Model() An instance of Query() If instance of model, that instance will be deleted If query, the objects matching the query is deleted """ one = False if isinstance(query_, basium_model.Model): query = Query().filter(query_.q._id, EQ, query_._id) one = True elif isinstance(query_, Query): query = query_ else: raise bc.Error(1, "Fatal: incorrect object type passed") rowcount = self.driver.delete(query) if one: query_._id = -1 return rowcount
def execute(self, sql, values=None, commit=False): """ Execute a query If error try to reconnect and redo the query to handle timeouts """ for i in range(0, 2): if self.dbconnection is None: self.connect() try: if self.debug & bc.DEBUG_SQL: self.log.debug(self.cursor.mogrify(sql, values)) if values is not None: self.cursor.execute(sql, values) else: self.cursor.execute(sql) if commit: self.dbconnection.commit() return except psycopg2.DatabaseError as e: if i == 1: raise bc.Error(1, str(e)) self.disconnect()
def execute(self, method=None, url=None, data=None, decode=False): if self.debug & bc.DEBUG_SQL: self.log.debug('Method=%s URL=%s Data=%s' % (method, url, data)) respdata = None req = RequestWithMethod(url, method=method) if self.dbconf.username is not None: auth = '%s:%s' % (self.dbconf.username, self.dbconf.password) auth = auth.encode("utf-8") req.add_header(b"Authorization", b"Basic " + base64.b64encode(auth)) try: if data: resp = urllib.request.urlopen( req, urllib.parse.urlencode(data, encoding="utf-8").encode("ascii")) else: resp = urllib.request.urlopen(req) except urllib.error.HTTPError as e: raise bc.Error(1, "HTTPerror %s" % e) except urllib.error.URLError as e: raise bc.Error(1, "URLerror %s" % e) if decode: encoding = resp.headers.get_content_charset() if encoding is None: encoding = "utf-8" try: tmp = resp.read().decode(encoding) res = json.loads(tmp) resp.close() except ValueError: raise bc.Error(1, "JSON ValueError for " + tmp) except TypeError: raise bc.Error(1, "JSON TypeError for " + tmp) try: if res['errno'] != 0: raise bc.Error(res['errno'], res['errmsg']) respdata = res["data"] except KeyError: raise bc.Error(1, "Result keyerror, missing errno/errmsg") return respdata, resp
def execute(self, sql, values=None, commit=True): """ Execute a query, if error try to reconnect and redo the query to handle timeouts """ for i in range(0, 2): if self.dbconnection is None: self.connect() try: if self.debug & bc.DEBUG_SQL: self.log.debug('SQL=%s' % sql) if values: self.log.debug(' =%s' % values) if values is not None: self.cursor.execute(sql, values) else: self.cursor.execute(sql) if commit: self.dbconnection.commit() return except sqlite3.Error as e: if i == 1: raise bc.Error(1, e.args[0])
def delete(self, query): raise bc.Error(1, 'Not implemented')
def count(self, query): raise bc.Error(1, 'Not implemented')
def select(self, query): raise bc.Error(1, "Not implemented")
def insert(self, table, values): raise bc.Error(1, 'Not implemented')
""" import datetime import decimal import basium_common as bc import basium_driver err = None try: import mysql.connector except ImportError: err = "Can't find the mysql.connector python module" if err: raise bc.Error(1, err) class BooleanCol(basium_driver.Column): """ Stores boolean as number: 0 or 1 """ def typeToSql(self): sql = "tinyint(1)" if self.nullable: sql += " null" else: sql += " not null" if self.default is not None: if self.default: sql += " default 1"
def update(self, table, values): raise bc.Error(1, 'Not implemented')