class TestSetup(unittest.TestCase): """docstring for TestSetUp""" def setUp(self): #self.driver = webdriver.Firefox() self.driver = webdriver.Remote(command_executor='http://0.0.0.0:4444/wd/hub', desired_capabilities=DesiredCapabilities.FIREFOX) self.submit_button = "//input[@value='Submit']" db_username_postgres = 'postgres' db_password_postgres = '1234' db_postgres_url = 'postgres://' + db_username_postgres + ':' + db_password_postgres + '@localhost/dev' path_to_database = path.join(path.curdir, "../databases") self.db_test = DAL(db_postgres_url, folder=path_to_database) self.db_test.import_table_definitions(path_to_database) def tearDown(self): self.driver.quit() def submit_form(self): self.driver.find_element_by_xpath(self.submit_button).click() def limpa_dados_tabela(self, nome_tabela): self.db_test.executesql('delete from ' + nome_tabela) self.db_test.commit()
def qq(): db = DAL('postgres://*****:*****@localhost/Lean',pool_size=5,check_reserved=['postgres'], folder=None, bigint_id=True ) #fake_migrate_all=True migrate_enabled=True db.executesql('SET search_path TO public, admins, model_gen, history, tasks, trac;') db.define_table('a_tbl', Field('name')) db.define_table('b_tbl', Field('id', 'reference a_tbl') ,Field('name'), primarykey=['id']) db.define_table('c_tbl', Field('id', 'reference b_tbl') ,Field('name'), primarykey=['id'])
class DocDB(object): """Creates instances of DocDB. Each instance will have a db connection. Extracted from web2py docs: --------------------------- Here are examples of connection strings for specific types of supported back-end databases (in all cases, we assume the database is running from localhost on its default port and is named "test"): SQLite sqlite://storage.db MySQL mysql://username:password@localhost/test PostgreSQL postgres://username:password@localhost/test MSSQL mssql://username:password@localhost/test FireBird firebird://username:password@localhost/test Oracle oracle://username/password@test DB2 db2://username:password@test Ingres ingres://username:password@localhost/test Informix informix://username:password@test Google App Engine/SQL google:sql Google App Engine/NoSQL google:datastore Notice that in SQLite the database consists of a single file. If it does not exist, it is created. This file is locked every time it is accessed. In the case of MySQL, PostgreSQL, MSSQL, FireBird, Oracle, DB2, Ingres and Informix the database "test" MUST exist. """ def __init__(self, conn='sqlite://temp.db', pool_size=0, migrate=True): """ Generates a connection with the given DB. @connection: system path to the sqlite file to use or DB connection string. If None given, a default temp.db file will be created. """ if '://' not in conn: print """Connection string needed!\n \ Some examples:\n \ SQLite sqlite://storage.db MySQL mysql://username:password@localhost/test \ PostgreSQL postgres://username:password@localhost/test """ sys.exit(2) self._conn = conn self._db = DAL(conn, folder=PATH, pool_size=pool_size) self._db.define_table('documents', Field('key'), Field('data', 'text'), Field('valid', 'boolean'), migrate = migrate) if not self._db(self._db.documents).count(): try: self._db.executesql('CREATE INDEX keyx ON documents (key)') #CREATE INDEX IF NOT EXISTS except Exception: self._db.rollback() def get(self, key): """ Searches and returns the doc for a given key. """ db = self._db doc = db((db.documents.key==key) & (db.documents.valid==True)).select(db.documents.data).first() if doc: return json.loads(doc['data']) return None def set(self, key, doc): """ Inserts a document (doc) into the DB. @doc: can be of any python data structure (string, number, dict, list, ... """ db = self._db data = json.dumps(doc) db((db.documents.key==key) & (db.documents.valid==True)).update(valid=False) db.documents.insert(key=key, data=data, valid=True) db.commit() return True def mset(self, docs): """ Inserts a set of documents into the DB. Example: >>> l = [('key1', {'key': 'value'}), ('key2', {'key': 'value'}), ('key3', {'key': 'value'})] >>> db.mset(*l) """ db = self._db counter = 0 for doc in docs: key, data = doc data = json.dumps(data) db((db.documents.key==key) & (db.documents.valid==True)).update(valid=False) db.documents.insert(key=key, data=data, valid=True) counter += 1 if counter > 1000: db.commit() counter = 0 db.commit() return True def versions(self, key): """ Lists all the documents related to a key. """ db = self._db results = [] for doc in db(db.documents.key == key).select(): id, data, valid = doc['id'], json.loads(doc['data']), doc['valid'] results.append((id, data, valid)) return results def revert(self, key, version): """ Reverts to a previous version of the document. """ db = self._db vers = self.versions(key) for doc in vers: id, data, valid = doc if id == version: db((db.documents.key==key) & (db.documents.valid==True)).update(valid=False) db(db.documents.id==id).update(valid=True) db.commit() return True return False def info(self): """ Returns a dict with info about the current DB (including db filesize, number of keys, etc.). """ dbsize = -1 db = self._db if "postgres" in self._conn: dbsize = db.executesql("SELECT pg_size_pretty(pg_database_size('%s'));" % self.dbname)[0][0] num_keys = db(db.documents.valid==True).count() return dict(keys=num_keys, dbsize=dbsize) def keys(self): """ Returns a list of ALL the keys in the current DB. """ db = self._db return [doc['key'] for doc in db(db.documents.valid==True).select(db.documents.key)] def flushall(self): """ Deletes ALL the content from the DB. TODO: Use truncate. """ self._db.documents.truncate() self._db.commit() #self.__init__() return True def compact(self, key=None): """ Deletes ALL the versions for a given document or the entire DB. """ db = self._db if key: db((db.documents.key==key) & (db.documents.valid==False)).delete() else: db(db.documents.valid==False).delete() db.commit() return True def __contains__(self, item): if self.get(item): return True return False def __getitem__(self, item): return self.get(item) def __setitem__(self, key, doc): return self.set(key, doc) def __delitem__(self, key): self._db.execute('UPDATE document SET valid == 0 WHERE key == ?', (key,)) self._db.commit() return True
session.comprobado = True # initialize admin user and roles group: useradmin = db(db.auth_user.id == 1).select() if len(useradmin) == 0: db.Sede.insert(name="Sede de pruebas") db.Almacen.insert(name="AlmacenTest") my_crypt = CRYPT(key=auth.settings.hmac_key) crypted_passwd = my_crypt('password_malo')[0] db.commit() # k = db.auth_user.insert(email='*****@*****.**', first_name='Administrator', # password=crypted_passwd, almacen=1) k = db.auth_user.insert(email='*****@*****.**', first_name='Administrator', password=crypted_passwd) if str(k) != '1': db.executesql('update auth_user set id=1 where id=' + str(k)) k = auth.add_group('admins', 'Administradores de la aplicación') if str(k) != '1': db.executesql('update auth_group set id=1 where id=' + str(k)) auth.add_membership(1, 1) auth.add_permission(1, 'admins', db.auth_user) db.commit() # Temporal, para importar datos de bb.dd. anterior ### import importacion locs = db().select(db.poblacion.ALL, limitby=(0, 1)) if not locs: # importacion.rellena_familias() # importacion.rellena_subfamilias()
db = DAL('mysql://*****:*****@localhost/wantu', pool_size=10, db_codec='latin1')#, migrate=False, migrate_enabled=False) db.define_table('urlresource', Field('id', 'id'), Field('url', 'string'), Field('pagetype', 'integer'), Field('referrerurl', 'string'), Field('description', 'string'), Field('savedpath', 'string'), Field('createtime', 'integer'), Field('lastmodtime', 'integer'), Field('isfinished', 'integer', default=0), Field('ispublished', 'integer', default=0))#, #migrate=False) db.executesql("set autocommit=True") def refresh_context(db): ''' change isolation mode to load recent changes ''' #end current active transaction #db.executesql("commit") #change isolation mode to read-committed #db.executesql("set @@tx_isolation='read-uncommitted'") pass def save_url(url, **fields): db.executesql("set autocommit=True") row = db(db.urlresource.url == url).select().first() fields.pop('createtime', None)
class UpdateFromRDB(): """This class update all the tables necesary to the simulation """ def __init__(self, mDatabase, mUid, inDGF=True): """Class initialization function :param mDatabase: local database (MoSiVo). :type mDatabase: DAL connection. :param mUid: Mosivo user id. :type inDGF: Integer. :param inDGF: App is in DGF. :type inDGF: Bool. """ # mosivo database self._db = mDatabase # mosivo app user id self._muid = mUid # DGF connection. Please use read only user user = '******' passw = '' if inDGF: self.rdb = DAL("mssql://%s:%[email protected]/DGF" % (user, passw), migrate_enabled=False, migrate=False) else: self.rdb = DAL('sqlite://dgf_database.db', migrate_enabled=False, migrate=False) def uPlanTmp(self): """Inserta todas las carpetas no nulas y que no esten con bajas en una tabla temporal de MoSiVo (plantmp) """ sql = "SELECT DISTINCT cp.Nro_Carpeta, d.Numero, cp.Cod_Sj, cp.Longitud, cp.Latitud " \ "FROM Carpetas_P cp, Deptos d " \ "WHERE d.Codigo=cp.Cod_Depto AND cp.Baja=0 AND cp.Nro_Carpeta IS NOT NULL AND " \ "cp.Cod_Depto IS NOT NULL AND cp.Cod_Sj IS NOT NULL AND cp.Cod_Sj>0 ORDER BY cp.Nro_Carpeta" rows = self.rdb.executesql(sql) if len(rows) > 0: # Delete all data in table carpeta for this user self._db.executesql("DELETE FROM plantmp WHERE cby=%i" % self._muid) self._db.commit() try: for r in rows: self._db['plantmp'].insert( ncarpeta=r[0], depto=r[1], sj=r[2], lon=r[3], lat=r[4], cby=self._muid, ) self._db.commit() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def uPT2P(self): """Pasa de plantmp a plan """ # Delete all data in table plan for this user self._db.executesql("DELETE FROM plan WHERE cby=%i" % self._muid) sql = "INSERT INTO plan(ncarpeta,sjudicial,lon,lat,cby) " sql += "(SELECT pt.ncarpeta, sj.id, pt.lon, pt.lat, sj.cby " sql += "FROM plantmp pt, seccionjudicial sj " sql += "WHERE pt.depto=sj.departamento AND pt.sj=sj.nombre " sql += "AND pt.cby=%i AND sj.cby=%i" % (self._muid, self._muid) sql += "ORDER BY pt.ncarpeta)" try: rows = self._db.executesql(sql) return True except Exception as e: print "Error: %s" % e return False def uPlan(self): """Actualiza en dos etapas plan desde DGF """ if self.uPlanTmp(): if self.uPT2P(): return True else: return False else: return False def uRodalDTmp(self): """Inserta rodales declarados para cada carpeta """ self._db.executesql("DELETE FROM rodaldtmp WHERE cby=%i" % self._muid) ''' sql = "SELECT cp.Nro_Carpeta, pl.Genero, pl.Especie, p.Ano_Dec, p.Ha_Dec " sql += "FROM Planes p, Planes_Pro pp, Carpetas_P cp, Plantas pl " sql += "WHERE p.CodG_Dec IS NOT NULL AND p.CodE_Dec IS NOT NULL AND " \ "pp.Codigo_Cp=cp.Nro_Carpeta AND pp.Codigo=p.Codigo_Plan_Pro AND " \ "p.Ano_Dec>0 AND p.Ha_Dec>0 AND " \ "pl.CodG=p.CodG_Dec AND pl.CodE=p.CodE_Dec ORDER BY cp.Nro_Carpeta" ''' ##################################################################### # RFPV - Muy importante: # Planes_Pro.Codigo_Cp=Carpetas_P.Nro_Carpeta # Planes_Pro.Codigo=Planes.Codigo_Plan_Pro # RFPV - Muy importante #################################################################### sql = "SELECT cp.Nro_Carpeta, pl.Genero, pl.Especie, p.Anio_Dec, p.Ha_Dec " sql += "FROM Planes_View p, Planes_Pro pp, Carpetas_P cp, Plantas pl " sql += "WHERE p.CodG_Dec IS NOT NULL AND p.CodE_Dec IS NOT NULL AND " \ "pp.Codigo_Cp=cp.Nro_Carpeta AND pp.Codigo=p.Codigo_Plan_Pro AND " \ "p.Anio_Dec>0 AND p.Ha_Dec>0 AND " \ "pl.CodG=p.CodG_Dec AND pl.CodE=p.CodE_Dec ORDER BY cp.Nro_Carpeta" rows = self.rdb.executesql(sql) self._db.commit() try: for r in rows: self._db['rodaldtmp'].insert( ncarpeta=r[0], ngen=r[1].strip(), nesp=r[2].strip(), anioplant=int(r[3]), areaafect=float(r[4]), cby=self._muid, ) self._db.commit() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def uRDT2RD(self): """Pasa de rodaldtmp a rodald """ self._db.commit() try: # Delete all data in table rodald for this user self._db.executesql( "DELETE FROM rodald WHERE plan IN (SELECT id FROM plan WHERE cby=%i)" % self._muid) sql = "INSERT INTO rodald(plan,especie,anioplant,areaafect) " sql += "(SELECT p.id, e.id, rdt.anioplant, rdt.areaafect " sql += "FROM plan p, especie e, genero g, rodaldtmp rdt " sql += "WHERE rdt.ncarpeta=p.ncarpeta AND e.nombre=rdt.nesp AND g.nombre=rdt.ngen AND e.genero=g.id " sql += "AND p.cby=%i AND g.cby=%i AND rdt.cby=%i " % ( self._muid, self._muid, self._muid) sql += "ORDER BY rdt.ncarpeta)" rows = self._db.executesql(sql) self._db.commit() self.__uUbicacionRodalD() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def uRodalD(self): """Actualiza en dos etapas rodald desde DGF """ if self.uRodalDTmp(): if self.uRDT2RD(): return True else: return False else: return False def uGenero(self): """Get genero data from DGF.Plantas """ sql = "SELECT DISTINCT CodG,Genero FROM Plantas WHERE Genero IS NOT NULL ORDER BY Genero" rows = self.rdb.executesql(sql) if len(rows) > 0: # Delete all data in table genero self._db.executesql("DELETE FROM genero WHERE cby=%i" % self._muid) self._db.commit() try: for r in rows: self._db.genero.insert( nombre=str(r[1]).strip(), codigo=str(r[0]).strip(), cby=self._muid, ) self._db.commit() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def uEspecie(self): """Get especie data from DGF.Plantas """ lrows = self._db((self._db['genero']['id'] > 0) & (self._db['genero']['cby'] == self._muid)).select( self._db['genero']['id'], self._db['genero']['nombre'], self._db['genero']['codigo']) if len(lrows) > 0: # Delete all data in table especie # self._db.executesql("DELETE FROM especie WHERE cby=%i" % self._muid) self._db.executesql( "DELETE FROM especie WHERE genero IN (SELECT id FROM genero WHERE cby=%i)" % self._muid) self._db.commit() try: for lr in lrows: id = int(lr['id']) genero = lr['nombre'] sql = 'SELECT CodE, Especie FROM Plantas WHERE Genero LIKE \'' + str( genero) + '%\'' print "UpdateFromRDB.uEspecie.sql: %s" % sql rows = self.rdb.executesql(sql) try: for r in rows: self._db.especie.insert( genero=id, nombre=str(r[1]).strip(), codigo=str(r[0]).strip(), # cby = self._muid, ) except Exception as e: print "Error: %s" % e raise Exception('Especie error') self._db.commit() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def __uUbicacionRodalD(self): """Fill ubicacionrodald (private function - autoused) """ self._db.commit() try: # SQL is short and fast sql1 = "SELECT rd.id, p.sjudicial, p.lon, p.lat FROM rodald rd, plan p " \ "WHERE rd.plan=p.id AND p.cby=%i" \ "ORDER BY rd.id" % self._muid sql = "INSERT INTO ubicacionrodald(rodal,sjudicial,lon,lat) (%s)" % sql1 print "UpdateFromRDB.__uUbicacionRodalD.sql: %s" % sql self._db.executesql(sql) self._db.commit() return True except Exception as e: print "Error: %s!" % e self._db.rollback() return False def uAll(self): """Util function to update all the data from remote database to local database """ # Order is important return [self.uGenero(), self.uEspecie(), self.uPlan(), self.uRodalD()]
class UpdateFromRDB(): """This class update all the tables necesary to the simulation """ def __init__(self, mDatabase, mUid, inDGF=True): """Class initialization function :param mDatabase: local database (MoSiVo). :type mDatabase: DAL connection. :param mUid: Mosivo user id. :type inDGF: Integer. :param inDGF: App is in DGF. :type inDGF: Bool. """ # mosivo database self._db = mDatabase # mosivo app user id self._muid = mUid # DGF connection. Please use read only user user='******' passw='' if inDGF: self.rdb = DAL("mssql://%s:%[email protected]/DGF" % (user, passw), migrate_enabled=False, migrate=False) else: self.rdb = DAL('sqlite://dgf_database.db', migrate_enabled=False, migrate=False) def uPlanTmp(self): """Inserta todas las carpetas no nulas y que no esten con bajas en una tabla temporal de MoSiVo (plantmp) """ sql = "SELECT DISTINCT cp.Nro_Carpeta, d.Numero, cp.Cod_Sj, cp.Longitud, cp.Latitud " \ "FROM Carpetas_P cp, Deptos d " \ "WHERE d.Codigo=cp.Cod_Depto AND cp.Baja=0 AND cp.Nro_Carpeta IS NOT NULL AND " \ "cp.Cod_Depto IS NOT NULL AND cp.Cod_Sj IS NOT NULL AND cp.Cod_Sj>0 ORDER BY cp.Nro_Carpeta" rows = self.rdb.executesql(sql) if len(rows) > 0: # Delete all data in table carpeta for this user self._db.executesql("DELETE FROM plantmp WHERE cby=%i" % self._muid) self._db.commit() try: for r in rows: self._db['plantmp'].insert( ncarpeta=r[0], depto=r[1], sj=r[2], lon = r[3], lat = r[4], cby = self._muid, ) self._db.commit() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def uPT2P(self): """Pasa de plantmp a plan """ # Delete all data in table plan for this user self._db.executesql("DELETE FROM plan WHERE cby=%i" % self._muid) sql = "INSERT INTO plan(ncarpeta,sjudicial,lon,lat,cby) " sql += "(SELECT pt.ncarpeta, sj.id, pt.lon, pt.lat, sj.cby " sql += "FROM plantmp pt, seccionjudicial sj " sql += "WHERE pt.depto=sj.departamento AND pt.sj=sj.nombre " sql += "AND pt.cby=%i AND sj.cby=%i" % (self._muid,self._muid) sql += "ORDER BY pt.ncarpeta)" try: rows = self._db.executesql(sql) return True except Exception as e: print "Error: %s" % e return False def uPlan(self): """Actualiza en dos etapas plan desde DGF """ if self.uPlanTmp(): if self.uPT2P(): return True else: return False else: return False def uRodalDTmp(self): """Inserta rodales declarados para cada carpeta """ self._db.executesql("DELETE FROM rodaldtmp WHERE cby=%i" % self._muid) ''' sql = "SELECT cp.Nro_Carpeta, pl.Genero, pl.Especie, p.Ano_Dec, p.Ha_Dec " sql += "FROM Planes p, Planes_Pro pp, Carpetas_P cp, Plantas pl " sql += "WHERE p.CodG_Dec IS NOT NULL AND p.CodE_Dec IS NOT NULL AND " \ "pp.Codigo_Cp=cp.Nro_Carpeta AND pp.Codigo=p.Codigo_Plan_Pro AND " \ "p.Ano_Dec>0 AND p.Ha_Dec>0 AND " \ "pl.CodG=p.CodG_Dec AND pl.CodE=p.CodE_Dec ORDER BY cp.Nro_Carpeta" ''' ##################################################################### # RFPV - Muy importante: # Planes_Pro.Codigo_Cp=Carpetas_P.Nro_Carpeta # Planes_Pro.Codigo=Planes.Codigo_Plan_Pro # RFPV - Muy importante #################################################################### sql = "SELECT cp.Nro_Carpeta, pl.Genero, pl.Especie, p.Anio_Dec, p.Ha_Dec " sql += "FROM Planes_View p, Planes_Pro pp, Carpetas_P cp, Plantas pl " sql += "WHERE p.CodG_Dec IS NOT NULL AND p.CodE_Dec IS NOT NULL AND " \ "pp.Codigo_Cp=cp.Nro_Carpeta AND pp.Codigo=p.Codigo_Plan_Pro AND " \ "p.Anio_Dec>0 AND p.Ha_Dec>0 AND " \ "pl.CodG=p.CodG_Dec AND pl.CodE=p.CodE_Dec ORDER BY cp.Nro_Carpeta" rows = self.rdb.executesql(sql) self._db.commit() try: for r in rows: self._db['rodaldtmp'].insert( ncarpeta=r[0], ngen=r[1].strip(), nesp=r[2].strip(), anioplant=int(r[3]), areaafect=float(r[4]), cby = self._muid, ) self._db.commit() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def uRDT2RD(self): """Pasa de rodaldtmp a rodald """ self._db.commit() try: # Delete all data in table rodald for this user self._db.executesql("DELETE FROM rodald WHERE plan IN (SELECT id FROM plan WHERE cby=%i)" % self._muid) sql = "INSERT INTO rodald(plan,especie,anioplant,areaafect) " sql += "(SELECT p.id, e.id, rdt.anioplant, rdt.areaafect " sql += "FROM plan p, especie e, genero g, rodaldtmp rdt " sql += "WHERE rdt.ncarpeta=p.ncarpeta AND e.nombre=rdt.nesp AND g.nombre=rdt.ngen AND e.genero=g.id " sql += "AND p.cby=%i AND g.cby=%i AND rdt.cby=%i " % (self._muid, self._muid, self._muid) sql += "ORDER BY rdt.ncarpeta)" rows = self._db.executesql(sql) self._db.commit() self.__uUbicacionRodalD() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def uRodalD(self): """Actualiza en dos etapas rodald desde DGF """ if self.uRodalDTmp(): if self.uRDT2RD(): return True else: return False else: return False def uGenero(self): """Get genero data from DGF.Plantas """ sql = "SELECT DISTINCT CodG,Genero FROM Plantas WHERE Genero IS NOT NULL ORDER BY Genero" rows = self.rdb.executesql(sql) if len(rows) > 0: # Delete all data in table genero self._db.executesql("DELETE FROM genero WHERE cby=%i" % self._muid) self._db.commit() try: for r in rows: self._db.genero.insert( nombre=str(r[1]).strip(), codigo=str(r[0]).strip(), cby = self._muid, ) self._db.commit() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def uEspecie(self): """Get especie data from DGF.Plantas """ lrows = self._db( ( self._db['genero']['id']>0 ) & ( self._db['genero']['cby'] == self._muid ) ).select ( self._db['genero']['id'], self._db['genero']['nombre'], self._db['genero']['codigo'] ) if len(lrows)>0: # Delete all data in table especie # self._db.executesql("DELETE FROM especie WHERE cby=%i" % self._muid) self._db.executesql( "DELETE FROM especie WHERE genero IN (SELECT id FROM genero WHERE cby=%i)" % self._muid ) self._db.commit() try: for lr in lrows: id=int( lr['id'] ) genero=lr['nombre'] sql = 'SELECT CodE, Especie FROM Plantas WHERE Genero LIKE \'' + str(genero) + '%\'' print "UpdateFromRDB.uEspecie.sql: %s" % sql rows=self.rdb.executesql(sql) try: for r in rows: self._db.especie.insert( genero = id, nombre = str(r[1]).strip(), codigo = str(r[0]).strip(), # cby = self._muid, ) except Exception as e: print "Error: %s" % e raise Exception('Especie error') self._db.commit() return True except Exception as e: print "Error: %s" % e self._db.rollback() return False def __uUbicacionRodalD(self): """Fill ubicacionrodald (private function - autoused) """ self._db.commit() try: # SQL is short and fast sql1 = "SELECT rd.id, p.sjudicial, p.lon, p.lat FROM rodald rd, plan p " \ "WHERE rd.plan=p.id AND p.cby=%i" \ "ORDER BY rd.id" % self._muid sql ="INSERT INTO ubicacionrodald(rodal,sjudicial,lon,lat) (%s)" % sql1 print "UpdateFromRDB.__uUbicacionRodalD.sql: %s" % sql self._db.executesql(sql) self._db.commit() return True except Exception as e: print "Error: %s!" % e self._db.rollback() return False def uAll(self): """Util function to update all the data from remote database to local database """ # Order is important return [ self.uGenero(), self.uEspecie(), self.uPlan(), self.uRodalD() ]
def main_0(): migrate=True db = DAL('postgres://*****:*****@localhost/Lean',pool_size=5,check_reserved=['postgres'], folder=None, bigint_id=True ) #fake_migrate_all=True migrate_enabled=True db.executesql('SET search_path TO public, admins, model_gen, history, tasks, trac;') db.define_table('person', Field('id', type = 'id', required = True, ), Field('name', type = 'string', required = True, ), Field('surname', type = 'string', required = True, ), Field('patroname', type = 'string', required = True, ), Field('is_hidden', type = 'boolean', default = False, required = True, ), migrate=False ) band_type = Enum(db, 'band_type', code_field=True, migrate=False) div_type = Enum(db, 'division_type', code_field=True, migrate=False) div_fntype = Enum(db, 'division_fntype', code_field=True, migrate=False) def_PG_table(db, 'band', Field('name'), ref_field(band_type, nocache=True), alt_key=['name', band_type.FK_name], migrate=False) def_PG_table(db, 'a_comp', Field('id', 'reference a_bands'), Field('comp_name'), primarykey=['id'], migrate=False) def_PG_table(db, 'a_dept', Field('id', 'reference a_bands'), Field('dept_name'), primarykey=['id'], migrate=False) def_PG_table(db, 'a_men', Field('name'), migrate=False) Entity.init_cls(db) band_ent = Entity(db.a_bands, D_field=band_type.FK_name, tracking=True) comp_ent = Entity(db.a_comp, parent = band_ent, D_value=1) dept_ent = Entity(db.a_dept, parent = band_ent, D_value=2) men_ent= Entity(db.a_men ) band_ent.def_option('region',Field('name')) band_ent.def_option('brunch', Field('id'), Field('name')) """ dept_ent.update(10, dept_name='вапвап', **{'region.name':'dept_ert', 'brunch.*':[ {'id':14, 'name':''}, ] }) dept_ent.insert(dept_name='вапвап new', **{'region.name':'dept_ert_new', 'brunch.*':[ {'id':14, 'name':'new34'}, ]} ) comp_ent.insert(name='prnt_name_cmp8', comp_name='comp_name_8', **{'brunch.*':[ {'name':'brunch_name6'}, {'name':'brunch_name7'} ] } ) """ band_ent.create_all_views(migrate=True) comp_ent.create_all_views(migrate=True) dept_ent.create_all_views(migrate=True) men_ent.def_option('age', Field('age', 'integer')) men_ent.create_all_views(migrate=True) #men_ent.insert(name='Петя fg', **{'age.age':20} ) band_ent.def_history() band_ent._write_history(1) band_men = Relation([db.a_bands, db.a_men], Field('men_role'), alt_key=['a_bands_id', 'a_men_id']) #band_men.insert(a_bands_id=1, a_men_id=1) #band_men.insert(a_bands_id=1, a_men_id=2 ) #band_men.insert(a_bands_id=2, a_men_id=2 ) r= band_men._select(None, db.a_men_vw_agg.id, json_agg_sql([db.a_men_vw_agg.name, db.a_bands_vw_agg.name], as_alias='ff'), groupby=[db.a_men_vw_agg.id] ) #band_ent.create_view( migrate=True ) #dept_ent.insert(name='prnt_dpt_name', dept_name='dept_name') #enm_c=Enum(db, 'atstc_type', code_field=True, migrate=True) #enm_clr=Enum(db, 'aclr_type', code_field=True, migrate=True) #enm_clr.own.insert(id=1, name='Red', code='#red') #enm_clr.own.insert(id=2, name='Blue', code='#bl') #enm_clr.own.insert(id=3, name='Green', code='#gr') """ db.define_table('a_entst', Field('name'), ref_field(enm_clr, nocache=True), migrate=True) ent= Entity(db.a_entst) ent.def_option('opt', Field('color'), Field('shape') , migrate=True) ent.def_option('plan', Field('start', 'date'), Field('finish', 'date') , migrate=True) ent.def_option('opt_lst', Field('id'), Field('this'), Field('that') , migrate=True) #db.plan._entopt.vw_pref='pln' #db.plan.start._entopt=Storage(vw_name='start_') ent.create_view( migrate=True ) ent.create_view_json( migrate=True) ent.create_view_agg(migrate=True) r = ent.update_many(db.a_entst._id<5, del_insert = True, **{ 'clr':2, 'opt.color':None, 'opt.shape':'', 'opt_lst.*': [dict(id=30, this='', that=''), dict(id=30, this='df0', that='that30')] }) #ent.update(7, **{'name':'Val', 'opt_lst.*': [dict(id=2 , this='', that='') # ]}) #ent.insert(**{'opt.color':'red'}) """ db.commit() rows = db().select(comp_ent.view_join_parent.ALL) print rows.as_list() #db.person._entopt=Storage(vw_pref='prsn') #db.person.name._entopt=Storage(vw_name='name') #g=Entity._vw_opt_fld_name(db.person.surname) pass