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')
