def get_course_names(self, prefix=None):
db = DAL('sqlite://courses.db', folder='dbs')
db.define_table('courses', Field('class_id', type='integer'),
Field('class_name'), Field('date_time'),
Field('descriptive_link'), Field('enrolled'),
Field('instructor'), Field('link_sources'),
Field('location'), Field('status'))
if (prefix != None):
return db.executesql(
'SELECT DISTINCT class_name FROM courses WHERE class_name LIKE '
+ prefix + '%')
else:
return db.executesql('SELECT DISTINCT class_name FROM courses')
def indexx():
#response.flash = T("Hello World")
#response.menu += [
# (T('My Sites'), False, URL('admin', 'default', 'site'))
#]
import os.path
# x=os.getcwd()+'\..\models\database_registry.py.bak'
# x=os.getcwd()+'\models\database_registry.py.bak'
x = os.getcwd(
) + '\\applications\\' + request.application + '\models\database_registry.py.bak'
outfile = os.getcwd(
) + '\\applications\\' + request.application + '\models\database_registry.py.out'
# y=x + request.application
# return 'ZZZ \>' + y + str(os.path.isfile(y)) + '\\' + request.application + ' \< ZZZ'
#print(x)
#return [os.path.dirname(os.path.abspath(__file__)), " <".join(os.getcwd()).join(">> "), os.path.isfile(os.getcwd().join('/../models/database_registry.py.bak'))]
# return [os.getcwd(), os.path.isfile(fname)]
from pydal import DAL, Field
# DAL()
db = DAL('mssql4://BuildDbAdmin:Alt0ids76@localhost/master')
results = db.executesql('select * from sys.databases')
with open(outfile, 'w') as f:
for row in results:
# print row.name
# f.write("%s\n" % str(row.name))
# register('ApplicationConfiguration', 'mssql4://BuildDbAdmin:Alt0ids76@localhost/ApplicationConfiguration')
registerx(row.name,
'mssql4://BuildDbAdmin:Alt0ids76@localhost/' + row.name)
# return 'ZZZ \>' + x + str(os.path.isfile(x)) + '\\' + request.application + ' \< ZZZ'
return DBREG
def index():
existingfile = os.getcwd(
) + '\\applications\\' + request.application + '\models\database_registry.py.bak'
outfile = os.getcwd(
) + '\\applications\\' + request.application + '\models\database_registry.py.out'
db = DAL('mssql4://BuildDbAdmin:Alt0ids76@localhost/master')
results = db.executesql('select * from sys.databases')
with open(outfile, 'w') as f:
for row in results:
# f.write("%s\n" % str(row.name))
register(row.name,
'mssql4://BuildDbAdmin:Alt0ids76@localhost/' + row.name)
print("XXXXXXXXXXXXXXXXXRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR")
print(DBREG)
print("XXXXXXXXXXXXXXXXXRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR")
return DBREG
def setUp():
import os.path
# x=os.getcwd()+'\..\models\database_registry.py.bak'
# x=os.getcwd()+'\models\database_registry.py.bak'
x=os.getcwd()+ '\\applications\\' +request.application+'\models\database_registry.py.bak'
outfile=os.getcwd()+ '\\applications\\' +request.application+'\models\database_registry.py.out'
# y=x + request.application
# return 'ZZZ \>' + y + str(os.path.isfile(y)) + '\\' + request.application + ' \< ZZZ'
#print(x)
#return [os.path.dirname(os.path.abspath(__file__)), " <".join(os.getcwd()).join(">> "), os.path.isfile(os.getcwd().join('/../models/database_registry.py.bak'))]
# return [os.getcwd(), os.path.isfile(fname)]
from pydal import DAL, Field
# DAL()
db=DAL('mssql4://BuildDbAdmin:Alt0ids76@localhost/master')
results=db.executesql('select * from sys.databases')
with open(outfile, 'w') as f:
for row in results:
register(row.name, 'mssql4://BuildDbAdmin:Alt0ids76@localhost/' + row.name)
# print row.name
# f.write("%s\n" % str(row.name))
# register('ApplicationConfiguration', 'mssql4://BuildDbAdmin:Alt0ids76@localhost/ApplicationConfiguration')
return None
def index():
# storedProcedures={"A":"AAAA", "B":"BBBB"}
# return locals
db = DAL('mssql4://BuildDbAdmin:Alt0ids76@localhost/master')
results = db.executesql('select name from sys.databases')
appdbs = []
sysdbs = ['master', 'tempdb', 'model', 'msdb']
for i in range(len(results)):
results[i] = results[i].name.encode('ascii', 'ignore')
if results[i] not in sysdbs:
appdbs.append(results[i])
# with open(outfile, 'w') as f:
# for row in results:
## f.write("%s\n" % str(row.name))
# register(row.name, 'mssql4://BuildDbAdmin:Alt0ids76@localhost/' + row.name)
# return results
# return DBREG
# return storedProcedures
# import pyodbc
procName = []
for i in range(len(appdbs)):
db = appdbs[i]
cnxn = pyodbc.connect(
r'Driver={SQL Server};Server=localhost;Database=' + db +
r';Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.execute(
"SELECT * FROM information_schema.routines WHERE ROUTINE_TYPE = 'PROCEDURE'"
)
while 1:
row = cursor.fetchone()
if not row:
break
procName.append(db + "::" +
row.ROUTINE_NAME.encode('ascii', 'ignore'))
return locals()
class dataSource:
db = "" # Conector Base de datos
# constructor
def __init__(self, host, user, passw, database, port, tipo_bd):
if tipo_bd == "sqlite":
self.db = DAL("sqlite://" + database + ".db")
elif tipo_bd == "mysql":
self.db = DAL("mysql://" + user + ":" + passw + "@" + host + "/" +
database + "")
elif tipo_bd == "postgres":
self.db = DAL("postgres://" + user + ":" + passw + "@" + host +
"/" + database + "")
elif tipo_bd == "sqlserver":
self.db = DAL("mssql4://" + user + ":" + passw + "@" + host + "/" +
database + "")
elif tipo_bd == "firebird":
self.db = DAL("firebird://" + user + ":" + passw + "@" + host +
"/" + database + "")
elif tipo_bd == "oracle":
self.db = DAL("oracle://" + user + ":" + passw + "@" + host + "/" +
database + "")
elif tipo_bd == "db2":
self.db = DAL("db2://" + user + ":" + passw + "@" + database + "")
"""
Ingres ingres://usuario:contraseña@localhost/nombrebd
Sybase sybase://usuario:contraseña@localhost/nombrebd
Informix informix://usuario:contraseña@nombrebd
Teradata teradata://DSN=dsn;UID=usuario;PWD=contraseña;DATABASE=nombrebd
Cubrid cubrid://usuario:contraseña@localhost/nombrebd
SAPDB sapdb://usuario:contraseña@localhost/nombrebd
IMAP imap://user:contraseña@server:port
MongoDB mongodb://usuario:contraseña@localhost/nombrebd
"""
# Vincular a una tabla 1 preexistente.
self.db.define_table(
"bienes",
# Indicarle a pyDAL cuál es la clave principal.
Field("id_bienes", type="id"),
Field("identificacion", type="integer"),
Field("tipo"),
Field("serie"),
Field("marca"),
Field("codigo", type="integer"),
Field("nombre"),
Field("ficha_formacion", type="integer"),
# Desactivar migraciones.
migrate=False)
# Vincular a una tabla 2 preexistente.
self.db.define_table(
"controles",
# Indicarle a pyDAL cuál es la clave principal.
Field("id_control", type="id"),
Field("identificacion", type="integer"),
Field("fechahe", type="datetime"),
Field("fechahs", type="datetime"),
Field("control"),
# Desactivar migraciones.
migrate=False)
# Vincular a una tabla 3 preexistente.
self.db.define_table(
"personal",
# Indicarle a pyDAL cuál es la clave principal.
Field("id_personal", type="id"),
Field("nombre"),
Field("identificacion", type="integer"),
Field("telefono", type="integer"),
Field("correo"),
Field("ficha_formacion", type="integer"),
Field("rol"),
# Desactivar migraciones.
migrate=False)
# Vincular a una table preexistente.
self.db.define_table(
"usuario",
# Indicarle a pyDAL cuál es la clave principal.
Field("id_usuario", type="id"),
Field("user"),
Field("clave"),
Field("rol"),
# Desactivar migraciones.
migrate=False)
""" Tipos de datos
string text blob boolean
integer double decimal(n, m) date
time datetime password upload
reference <tabla> list:string list:integer
list:reference <tabla> json bigint
big-id big-reference
"""
def query(self, sql):
try:
self.db.executesql(sql)
self.db.commit()
return True
except:
return False
def getData(self, sql):
q = self.db.executesql(sql)
self.db.commit()
return q
class DBClient:
log = customlogger(globalconfig.logging_level)
def __init__(self, uri_db):
self.log.info("creating instance of DBClient: {0}".format(uri_db))
self.db = DAL(uri_db, migrate_enabled=False)
self.wells = self.db.define_table('wells',
Field('uuid'),
Field('project_uuid'),
Field('well_name'),
Field('uwi'),
Field('created_at'),
Field('modified_at'),
primarykey=['uuid'])
self.clients = self.db.define_table('clients',
Field('uuid'),
Field('company_name'),
Field('created_at'),
Field('modified_at'),
primarykey=['uuid'])
self.projects = self.db.define_table('projects',
Field('uuid'),
Field('client_uuid'),
Field('name'),
Field('created_at'),
Field('modified_at'),
Field('basin'),
Field('shapefile'),
primarykey=['uuid'])
@add_post_func_delay(0.5)
def insert_well(self, *args):
self.log.info("inserting well into DB..")
self.wells.insert(uuid=uuid.uuid4(),
well_name=args[0],
uwi=args[1],
created_at=datetime.datetime.now(),
modified_at=datetime.datetime.now())
self.db.commit()
@add_post_func_delay(0.5)
def insert_client(self, *args):
self.log.info("inserting client into DB..")
self.clients.insert(uuid=uuid.uuid4(),
company_name=args[0],
created_at=datetime.datetime.now(),
modified_at=datetime.datetime.now())
self.db.commit()
# TODO insert project has been modified on the front end such that there is no field called project type on DB table
# def insert_project(self, *args):
# self.insert_client(args[0])
# c_uuid = self.clients(company_name=args[0])['uuid']
# # self.db.projects.insert(uuid=uuid.uuid4(), name=args[1], client_uuid=c_uuid, basin='Midland', created_at=datetime.datetime.now(), modified_at=datetime.datetime.now())
# self.projects.insert(uuid=uuid.uuid4(), name=args[1], client_uuid=c_uuid, basin='midland', created_at=datetime.datetime.now(), modified_at=datetime.datetime.now())
# self.db.commit()
@add_post_func_delay(0.5)
def delete_table(self, tablename):
self.log.info("deleteing table {0}".format(tablename))
table = self.db.get(tablename)
table.truncate(mode='CASCADE')
self.db.commit()
def execute_sql(self, sql):
self.log.info("executing sql: '{0}'".format(sql))
return self.db.executesql(sql)
def close(self):
self.log.info("closing DB instance..")
self.db.close()
from pydal import DAL, Field
from datetime import datetime
db = DAL('sqlite://download.db')
market = db.define_table(
'market', Field('name'), Field('ask', type='double'),
Field('timestamp', type='datetime', default=datetime.now))
db.executesql('CREATE INDEX IF NOT EXISTS tidx ON market (timestamp);')
db.executesql('CREATE INDEX IF NOT EXISTS m_n_idx ON market (name);')
buy = db.define_table(
'buy',
Field('market'),
Field('purchase_price', type='double'),
Field('selling_price', type='double'),
Field('amount', type='double'),
)
db.executesql('CREATE INDEX IF NOT EXISTS sidx ON buy (selling_price);')
picks = db.define_table(
'picks', Field('market'), Field('old_price', type='double'),
Field('new_price', type='double'), Field('gain', type='double'),
Field('timestamp', type='datetime', default=datetime.now))
class DataBase:
def __init__(self,
username,
password,
host='localhost',
dbname='postgres',
port=5432,
pool_size=5):
self.schema = 'soad'
self.username = username
self.host = host
self.port = port
self.dbname = dbname
self.folder = 'Resources' + os.sep + 'database'
os.environ["PGPASSWORD"] = password
self.password = password
self.dbinfo = 'postgres://' + username + ':' + password + '@' + host + ':' + str(
port) + '/' + self.dbname
self.db = DAL(self.dbinfo,
folder=self.folder,
pool_size=pool_size,
migrate=False,
attempts=1)
self.connection = None
self.threadpool = QThreadPool()
def busca_registro(self,
nome_tabela,
coluna,
valor='',
operador='=',
filtro=None):
if filtro == '' or filtro is None:
filtro = '1=1'
sql = "select * from " + self.schema + ".fnc_buscar_registro(" \
+ "p_tabela=>" + "'" + nome_tabela + "'" \
+ ", p_coluna=>" + "'" + coluna + "'" \
+ ", p_valor=>" + "'" + valor + "'" \
+ ", p_operador=>" + "'" + operador + "'" \
+ ", p_filtro=>" + "'" + filtro + "'" \
+ ");"
return self.execute_sql(sql)
def get_registro(self, fnc, campo, valor):
sql = "select * from " + self.schema + "." + fnc + "(" \
+ "p_" + campo + "=>" + "'" + str(valor) + "'" \
+ ");"
return self.execute_sql(sql)
def call_procedure(self, schema='soad', params=None):
if not params:
return
# Remove parametros vazios
vazio = []
for param in params["params"].items():
if param[1] == '':
vazio.append(param[0])
logging.info('[DataBase] Parâmetros vazios: ' + str(vazio))
for i in range(len(vazio)):
del params["params"][vazio[i]]
params = json.dumps(params, ensure_ascii=False)
sql = "select * from " + schema + ".fnc_chamada_de_metodo(" \
+ "p_json_params=>" + "'" + params + "'" \
+ ");"
return self.execute_sql(sql)
def execute_sql(self, sql, as_dict=True):
retorno = list()
try:
retorno = self.db.executesql(query=sql, as_dict=as_dict)
self.db.commit()
logging.debug('[DataBase] status=' + str(True))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] retorno=' + str(retorno))
prc = True, retorno, str(self.db._lastsql)
except Exception as e:
self.db.rollback()
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception=' + str(e))
retorno.append(e)
prc = False, retorno, str(sql)
except:
e = 'Exceção não tratada'
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception2=' + str(e))
retorno.append(e)
prc = False, e, str(sql)
return prc
def __conectar_banco__(self, progress_callback):
try:
self.connection = self.db.__call__()
#progress_callback.emit(100)
except Exception as e:
logging.debug('[DataBase] ' + str(e))
os.environ["PGPASSWORD"] = ''
pass
#progress_callback.emit(0)
return self
def definir_schema(self, schema):
self.schema = schema
self.execute_sql("SET search_path TO " + self.schema, as_dict=False)
def fechar_conexao(self):
self.db.close()
def progress_fn(self, n):
print("%d%% done" % n)
def retorno_conexao(self, s):
self.connection = s
def thread_complete(self):
logging.debug('[DataBase] Thread Completed')
def abrir_conexao(self):
# Pass the function to execute
worker = Worker(
self.db.__call__
) # Any other args, kwargs are passed to the run function
worker.signals.result.connect(self.retorno_conexao)
worker.signals.finished.connect(self.thread_complete)
#worker.signals.progress.connect(self.progress_fn)
# Execute
self.threadpool.start(worker)
from pydal import DAL, Field
# db = DAL('mssql4://BuildDbAdmin:Alt0ids76@localhost/PyOdbcDb')
db = DAL(
'mssql://Driver={SQL Server};Server=localhost;Database=master;Trusted_Connection=yes;'
)
results = db.executesql("select name from sys.databases")
print(results)
except DecompressionBombError as e:
print(f"DecompressionBomb, Skipping image {image} (#{n})")
else:
image_ids.append(int(image_id_re.match(image)[1]))
img_array = keras.preprocessing.image.img_to_array(img)
img_array = tf.expand_dims(img_array, 0) # Create batch axis
images.append(img_array)
scores = handle_predictions_batch(n, images, model, scores)
db = DAL('sqlite://flower_storage.db', folder='./data')
define_tables(db)
db(db.images.id > 0).update(is_live=False)
db.commit()
is_non_flower_indexes = scores[scores > 0.5].index
db.executesql(
"UPDATE images SET is_live='T', is_non_flower='T' WHERE id IN({})".format(
','.join(list(is_non_flower_indexes.astype('str')))))
db.commit()
is_flower_indexes = scores[scores <= 0.5].index
db.executesql(
"UPDATE images SET is_live='T', is_non_flower='F' WHERE id IN({})".format(
','.join(list(is_flower_indexes.astype('str')))))
db.commit()
# %%
','.join(['1', '2', '3'])
#]
import os.path
# x=os.getcwd()+'\..\models\database_registry.py.bak'
# x=os.getcwd()+'\models\database_registry.py.bak'
x = os.getcwd(
) + '\\applications\\' + request.application + '\models\database_registry.py.bak'
outfile = os.getcwd(
) + '\\applications\\' + request.application + '\models\database_registry.py.out'
# y=x + request.application
# return 'ZZZ \>' + y + str(os.path.isfile(y)) + '\\' + request.application + ' \< ZZZ'
#print(x)
#return [os.path.dirname(os.path.abspath(__file__)), " <".join(os.getcwd()).join(">> "), os.path.isfile(os.getcwd().join('/../models/database_registry.py.bak'))]
# return [os.getcwd(), os.path.isfile(fname)]
from pydal import DAL, Field
# DAL()
db = DAL('mssql4://BuildDbAdmin:Alt0ids76@localhost/master')
results = db.executesql('select * from sys.databases')
with open(outfile, 'w') as f:
for row in results:
# print row.name
# f.write("%s\n" % str(row.name))
# register('ApplicationConfiguration', 'mssql4://BuildDbAdmin:Alt0ids76@localhost/ApplicationConfiguration')
register(row.name,
'mssql4://BuildDbAdmin:Alt0ids76@localhost/' + row.name)
# return 'ZZZ \>' + x + str(os.path.isfile(x)) + '\\' + request.application + ' \< ZZZ'
# return DBREG
# return dict()
class KeyValueStore(object):
def __init__(self):
self.db = DAL('sqlite://keyvalstore.db', folder=get_config_dir())
self.db.define_table(
'store', Field('key', type='string', required=True, unique=True),
Field('value', type='blob'),
Field('version', type='integer', default=0))
def __del__(self):
self.db.close()
def set(self, key, value):
# We have to execute pure SQL statements because pydal does not support blobs as it seems
res = self.db.executesql('SELECT value from store where key=?',
placeholders=[key])
if len(res) > 0:
self.db.executesql('UPDATE store SET value=? where key=?',
placeholders=[pickle.dumps(value), key])
else:
self.db.executesql(
'INSERT INTO "store"("key","value") VALUES (?, ?)',
placeholders=[key, pickle.dumps(value)])
self.db.commit()
def get(self, key, default=""):
res = self.db.executesql('SELECT value from store where key=?',
placeholders=[key])
return default if len(res) == 0 else pickle.loads(res[0][0])
def set_versioned(self, key, value, version):
response = requests.post(
'https://dibser.vserverli.de/php/keyvaluestore.php?key={}&version={}'
.format(key, version),
data=pickle.dumps(value),
headers={'Content-Type': 'application/octet-stream'})
response.raise_for_status()
res = self.db.executesql('SELECT value from store where key=?',
placeholders=[key])
if len(res) > 0:
self.db.executesql(
'UPDATE store SET value=?, version=? where key=?',
placeholders=[pickle.dumps(value), version, key])
else:
self.db.executesql(
'INSERT INTO "store"("key","value", "version") VALUES (?, ?, ?)',
placeholders=[key, pickle.dumps(value), version])
self.db.commit()
def get_versioned(self, key, default=""):
res = self.db.executesql('SELECT value,version from store where key=?',
placeholders=[key])
value = None
if len(res) > 0:
newest_version = local_version = res[0][1]
if not local_version: local_version = 1
value = res[0][0]
# check remote version and update if remote is newer (status code == 200 if a newer version is available, otherwise status code 404 if not available or 304 if older/same version)
response = requests.get(
'https://dibser.vserverli.de/php/keyvaluestore.php?key={}&version={}'
.format(key, local_version))
if response.status_code == 200:
newest_version = int(response.headers['X-Keyvalstore-Version'])
value = response.content
print(
"Remote version is newer for key={}. Local version={} remote version={}"
.format(key, local_version, newest_version))
return (default, -1) if value == None else (pickle.loads(value),
newest_version)
class DataBase:
def __init__(self, dbname='postgres', schema='Acervo', pool_size=5):
load_dotenv()
self.dbname = dbname
self.schema = schema
self.username = os.getenv("DBUSERNAME")
self.password = os.getenv("DBPASS")
self.host = os.getenv("DBHOST")
self.port = os.getenv("DBPORT")
self.folder = 'Resources' + os.sep + 'database'
self.dbinfo = \
'postgres://' + str(self.username) + ':' + str(self.password) + '@' \
+ str(self.host) + ':' + str(self.port) + '/' + str(self.dbname)
self.db = DAL(
self.dbinfo,
folder=self.folder,
pool_size=pool_size,
migrate=False,
attempts=1
)
self.connection = None
def execute_sql(self, sql, as_dict=True):
retorno = list()
try:
retorno = self.db.executesql(query=sql, as_dict=as_dict)
self.db.commit()
logging.debug('[DataBase] status=' + str(True))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] retorno=' + str(retorno))
prc = True, retorno, str(self.db._lastsql)
except Exception as e:
self.db.rollback()
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception=' + str(e))
retorno.append(e)
prc = False, retorno, str(sql)
except:
e = 'Exceção não tratada'
logging.debug('[DataBase] status=' + str(False))
logging.debug('[DataBase] sql=' + str(sql))
logging.debug('[DataBase] exception2=' + str(e))
retorno.append(e)
prc = False, e, str(sql)
return prc
def __conectar_banco__(self):
try:
self.connection = self.db.__call__()
except Exception as e:
logging.debug('[DataBase] ' + str(e))
return self
def definir_schema(self, schema):
self.schema = schema
self.execute_sql("SET search_path TO " + self.schema, as_dict=False)
def fechar_conexao(self):
self.db.close()
