Ejemplo n.º 1
0
class dbgetter:
    def __init__(self, dbchoice=None):
        self.db = DAL('sqlite://storage.db', folder=path.join('../database'))
        self.hasdb = (not dbchoice == None)
        self.mydb = 0
        if self.hasdb:
            if dbchoice == 'trenddates' or dbchoice == 'trends':
                self.mydb = self.db.define_table('trenddates',
                                                 Field('trend', 'text'),
                                                 Field('date', 'date'))
            elif dbchoice == 'words':
                self.mydb = self.db.define_table('words',
                                                 Field('word', 'string'),
                                                 Field('freq', 'integer'),
                                                 Field('dates', 'list:string'))
            else:
                raise NameError(
                    "Your options from dbgetter are 'trends' or 'words'.")
        else:
            raise NameError(
                "You must provide a database choice from dbgetter ('trends' or 'words')."
            )

    def getInfo(self, data):
        toRet = []
        dbdata = self.db(self.mydb).select().as_list()
        for d in dbdata:
            toRet.append(d.get(data))
        return toRet

    def getTable(self):
        return self.db(self.mydb).select()

    def close(self):
        self.db.close()
Ejemplo n.º 2
0
    def testRun(self):
        db = DAL(DEFAULT_URI, check_reserved=['all'])
        dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245',
                                              None)
        self.assertEqual(dt.microsecond, 223245)
        self.assertEqual(dt.hour, 12)

        dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245Z',
                                              None)
        self.assertEqual(dt.microsecond, 223245)
        self.assertEqual(dt.hour, 12)

        dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245-2:0',
                                              None)
        self.assertEqual(dt.microsecond, 223245)
        self.assertEqual(dt.hour, 10)

        dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36+1:0', None)
        self.assertEqual(dt.microsecond, 0)
        self.assertEqual(dt.hour, 13)

        dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.123', None)
        self.assertEqual(dt.microsecond, 123000)

        dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.00123',
                                              None)
        self.assertEqual(dt.microsecond, 1230)

        dt = db._adapter.parsemap['datetime']('2015-09-04t12:33:36.1234567890',
                                              None)
        self.assertEqual(dt.microsecond, 123456)
        db.close()
Ejemplo n.º 3
0
    def testRun(self):
        db = DAL(DEFAULT_URI, check_reserved=['all'])
        dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245', None)
        self.assertEqual(dt.microsecond, 223245)
        self.assertEqual(dt.hour, 12)

        dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245Z', None)
        self.assertEqual(dt.microsecond, 223245)
        self.assertEqual(dt.hour, 12)

        dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.223245-2:0', None)
        self.assertEqual(dt.microsecond, 223245)
        self.assertEqual(dt.hour, 10)

        dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36+1:0', None)
        self.assertEqual(dt.microsecond, 0)
        self.assertEqual(dt.hour, 13)

        dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.123', None)
        self.assertEqual(dt.microsecond, 123000)

        dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.00123', None)
        self.assertEqual(dt.microsecond, 1230)

        dt=db._adapter.parsemap['datetime']('2015-09-04t12:33:36.1234567890', None)
        self.assertEqual(dt.microsecond, 123456)
        db.close()
Ejemplo n.º 4
0
class trenddb:
    def __init__(self):
        self.db = DAL('sqlite://storage.db', folder=path.join("../database"))
        try:
            self.db.define_table('trenddates', Field('trend', 'text'),
                                 Field('date', 'date'))
        except:
            print("Trenddates exists...")
            if len(self.db(self.db.trenddates).select().as_list()) > 0:
                print("It has entries, somehow.")

    def add(self, trends, trenddate):
        if trends is None:
            return
        f = open('runningTrends.txt', 'a')
        d = date(int(trenddate[:4]), int(trenddate[5:7]), int(trenddate[8:]))
        for t in trends:
            self.db.trenddates.insert(trend=t, date=d)
            f.write(str(d) + ": " + t)
            f.write("\n")
        f.close()

    def addParsed(self, trends, trenddate):
        d = date(int(trenddate[:4]), int(trenddate[5:7]), int(trenddate[8:]))
        for t in trends:
            self.db.trenddates.insert(trend=t, date=d)

    def end(self):
        self.db.close()
    def update_database(self):
        db = DAL('sqlite://courses.db', folder='dbs')
        try:
            response = requests.get(
                'http://localhost:5000/api/v1.0/courses/all/2000')
            response.raise_for_status()
            jsonResponse = response.json()

            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'))
            for key in jsonResponse:
                db.courses.insert(class_id=key['class_id'],
                                  class_name=key['class_name'],
                                  date_time=key['date_time'],
                                  descriptive_link=key['descriptive_link'],
                                  enrolled=key['enrolled'],
                                  instructor=key['instructor'],
                                  link_sources=key['link_sources'],
                                  location=key['location'],
                                  status=key['status'])
            rows = db().select(db.courses.ALL)

        finally:
            if db:
                db.close()
Ejemplo n.º 6
0
    def testRun(self):
        db = DAL(DEFAULT_URI, check_reserved=['all'])

        #: skip for adapters that use drivers for datetime parsing
        if db._adapter.parser.registered.get('datetime') is None:
            return

        parse = lambda v: db._adapter.parser.parse(v, 'datetime', 'datetime')

        dt = parse('2015-09-04t12:33:36.223245')
        self.assertEqual(dt.microsecond, 223245)
        self.assertEqual(dt.hour, 12)

        dt = parse('2015-09-04t12:33:36.223245Z')
        self.assertEqual(dt.microsecond, 223245)
        self.assertEqual(dt.hour, 12)

        dt = parse('2015-09-04t12:33:36.223245-2:0')
        self.assertEqual(dt.microsecond, 223245)
        self.assertEqual(dt.hour, 10)

        dt = parse('2015-09-04t12:33:36+1:0')
        self.assertEqual(dt.microsecond, 0)
        self.assertEqual(dt.hour, 13)

        dt = parse('2015-09-04t12:33:36.123')
        self.assertEqual(dt.microsecond, 123000)

        dt = parse('2015-09-04t12:33:36.00123')
        self.assertEqual(dt.microsecond, 1230)

        dt = parse('2015-09-04t12:33:36.1234567890')
        self.assertEqual(dt.microsecond, 123456)
        db.close()
Ejemplo n.º 7
0
 def testRun(self):
     for ref, bigint in [("reference", False), ("big-reference", True)]:
         db = DAL(DEFAULT_URI, check_reserved=["all"], bigint_id=bigint)
         if bigint and "big-id" not in db._adapter.types:
             continue
         db.define_table("tt", Field("vv"))
         db.define_table(
             "ttt",
             Field("vv"),
             Field("tt_id", "%s tt" % ref, unique=True),
             Field("tt_uq", "integer", unique=True),
         )
         id_1 = db.tt.insert(vv="pydal")
         id_2 = db.tt.insert(vv="pydal")
         # Null tt_id
         db.ttt.insert(vv="pydal", tt_uq=1)
         # first insert is OK
         db.ttt.insert(tt_id=id_1, tt_uq=2)
         self.assertRaises(Exception, db.ttt.insert, tt_id=id_1, tt_uq=3)
         self.assertRaises(Exception, db.ttt.insert, tt_id=id_2, tt_uq=2)
         # The following is mandatory for backends as PG to close the aborted transaction
         db.commit()
         drop(db.ttt)
         drop(db.tt)
         db.close()
Ejemplo n.º 8
0
 def testRun(self):
     db = DAL(DEFAULT_URI, check_reserved=["all"])
     db.define_table("tt", Field("vv"))
     vv = "ἀγοραζε"
     id_i = db.tt.insert(vv=vv)
     row = db(db.tt.id == id_i).select().first()
     self.assertEqual(row.vv, vv)
     db.commit()
     drop(db.tt)
     db.close()
Ejemplo n.º 9
0
 def testRun(self):
     db = DAL(DEFAULT_URI, check_reserved=['all'])
     db.define_table('tt', Field('vv'))
     db.define_table('ttt', Field('vv'), Field('tt_id', 'reference tt', notnull=True))
     self.assertRaises(Exception, db.ttt.insert, vv='pydal')
     # The following is mandatory for backends as PG to close the aborted transaction
     db.commit()
     drop(db.ttt)
     drop(db.tt)
     db.close()
Ejemplo n.º 10
0
 def testRun(self):
     db = DAL(DEFAULT_URI, check_reserved=['all'])
     db.define_table('tt', Field('vv'))
     vv = 'ἀγοραζε'
     id_i = db.tt.insert(vv=vv)
     row = db(db.tt.id == id_i).select().first()
     self.assertEqual(row.vv, vv)
     db.commit()
     drop(db.tt)
     db.close()
Ejemplo n.º 11
0
 def testRun(self):
     for ref, bigint in [('reference', False), ('big-reference', True)]:
         db = DAL(DEFAULT_URI, check_reserved=['all'], bigint_id=bigint)
         db.define_table('tt', Field('vv'))
         db.define_table('ttt', Field('vv'),
                         Field('tt_id', '%s tt' % ref, notnull=True))
         self.assertRaises(Exception, db.ttt.insert, vv='pydal')
         # The following is mandatory for backends as PG to close the aborted transaction
         db.commit()
         drop(db.ttt)
         drop(db.tt)
         db.close()
Ejemplo n.º 12
0
 def testRun(self):
     for ref, bigint in [("reference", False), ("big-reference", True)]:
         db = DAL(DEFAULT_URI, check_reserved=["all"], bigint_id=bigint)
         if bigint and "big-id" not in db._adapter.types:
             continue
         db.define_table("tt", Field("vv"))
         db.define_table("ttt", Field("vv"),
                         Field("tt_id", "%s tt" % ref, notnull=True))
         self.assertRaises(Exception, db.ttt.insert, vv="pydal")
         # The following is mandatory for backends as PG to close the aborted transaction
         db.commit()
         drop(db.ttt)
         drop(db.tt)
         db.close()
Ejemplo n.º 13
0
 def testRun(self):
     for ref, bigint in [('reference', False), ('big-reference', True)]:
         db = DAL(DEFAULT_URI, check_reserved=['all'], bigint_id=bigint)
         if bigint and 'big-id' not in db._adapter.types:
             continue
         db.define_table('tt', Field('vv'))
         db.define_table('ttt', Field('vv'), Field('tt_id', '%s tt' % ref,
                                                   notnull=True))
         self.assertRaises(Exception, db.ttt.insert, vv='pydal')
         # The following is mandatory for backends as PG to close the aborted transaction
         db.commit()
         drop(db.ttt)
         drop(db.tt)
         db.close()
Ejemplo n.º 14
0
Archivo: base.py Proyecto: boa-py/pydal
 def testRun(self):
     for ref, bigint in [('reference', False), ('big-reference', True)]:
         db = DAL(DEFAULT_URI, check_reserved=['all'], bigint_id=bigint)
         db.define_table('tt', Field('vv'))
         db.define_table('ttt', Field('vv'), Field('tt_id', '%s tt' % ref,
                                                   unique=True))
         id_i = db.tt.insert(vv='pydal')
         # Null tt_id
         db.ttt.insert(vv='pydal')
         # first insert is OK
         db.ttt.insert(tt_id=id_i)
         self.assertRaises(Exception, db.ttt.insert, tt_id=id_i)
         # The following is mandatory for backends as PG to close the aborted transaction
         db.commit()
         drop(db.ttt)
         drop(db.tt)
         db.close()
Ejemplo n.º 15
0
def index():
    from pydal import DAL, Field
    dbConnect = DAL('mysql://*****:*****@localhost/web2py2')
    try:
        dbConnect.define_table(
            'list_event',
            Field('event_name', type='text'),
            Field('dia_diem', type='text'),
            Field('thoi_gian', type='date'),
        )
        # dbConnect.list_event.insert(event_name='Phao Hoa', dia_diem='Cau rong', thoi_gian='2018/05/06')
        # dbConnect.list_event.insert(event_name='Cau phun lua', dia_diem='Cau rong', thoi_gian='2018/05/07')
        # dbConnect.list_event.insert(event_name='Duong pho', dia_diem='Hai Chau', thoi_gian='2018/05/06')

        getAllEvents = dbConnect().select(dbConnect.list_event.ALL)
    finally:
        if dbConnect:
            dbConnect.close()
    return dict(list=getAllEvents)
Ejemplo n.º 16
0
 def testRun(self):
     for ref, bigint in [('reference', False), ('big-reference', True)]:
         db = DAL(DEFAULT_URI, check_reserved=['all'], bigint_id=bigint)
         if bigint and 'big-id' not in db._adapter.types:
             continue
         db.define_table('tt', Field('vv'))
         db.define_table('ttt', Field('vv'), Field('tt_id', '%s tt' % ref,
                                                   unique=True,
                                                   notnull=True))
         self.assertRaises(Exception, db.ttt.insert, vv='pydal')
         db.commit()
         id_i = db.tt.insert(vv='pydal')
         # first insert is OK
         db.ttt.insert(tt_id=id_i)
         self.assertRaises(Exception, db.ttt.insert, tt_id=id_i)
         # The following is mandatory for backends as PG to close the aborted transaction
         db.commit()
         drop(db.ttt)
         drop(db.tt)
         db.close()
Ejemplo n.º 17
0
    def sqlDump(self, filename):
        try:
            rmtree("./sqldump")
            os.mkdir("./sqldump")
        except OSError as e:
            if e.errno != errno.ENOENT:
                raise
            else:
                os.mkdir("./sqldump")

        db = DAL("sqlite://" + filename, folder="sqldump")

        for klass in (
                Server,
                ExternalEntity,
                Dataflow,
                Datastore,
                Actor,
                Process,
                SetOfProcesses,
                Boundary,
                TM,
                Threat,
                Lambda,
                Data,
                Finding,
        ):
            self.get_table(db, klass)

        for e in TM._threats + TM._data + TM._elements + self.findings + [
                self
        ]:
            table = self.get_table(db, e.__class__)
            row = {}
            for k, v in serialize(e).items():
                if k == "id":
                    k = "SID"
                row[k] = ", ".join(v) if isinstance(v, list) else v
            db[table].bulk_insert([row])

        db.close()
Ejemplo n.º 18
0
class Db:

    def __init__(self):
        self.uri = 'sqlite://database.sqlite'
        self.folder = os.path.join(current_dir, "sqlite")

        self.db = None

    def connect(self):
        if not self.db:
            self.db = DAL(self.uri, folder=self.folder, pool_size=5, lazy_tables=False)
            #, migrate_enabled=False, migrate=False, lazy_tables=True
            self.tables()
        else:
            print("Error: db already open")

    def close(self):
        self.db.close()
        self.db = None

    def tables(self):
        self.tableUsers()


    def tableUsers(self):
        try:
            #Note: id is created automattically if omited
            self.db.define_table('users', 
                Field('username', type='string'), 
                Field('email', type='string'),
                Field('password', type='string'),
                Field('uid', type='integer', default=0),
                Field('userRole', type='string', defaul='user'),#user, admin
                Field('secureKey', type='string', default='0', writable=False, readable=False),#secet key token - should be used if in server side
                Field('dateRegistration', type='datetime', writable=False, readable=False)
                )
        except:
            print('models db.DB() tableUsers Error')

    def tableLogs(self):
        pass


    def insertUser(self, username, email, password, dateRegistration):
        self.connect()
        import secrets#generate url safe token
        secureKey = secrets.token_urlsafe()
        self.db.users.insert(username=username, email=email, password=password, uid=0, userRole="user", secureKey=secureKey, dateRegistration=dateRegistration)
        self.db.commit()
        self.db.close()

    def loginUser(self, username, password):
        self.connect()
        self.db._adapter.reconnect()#gives thread error without
        user = self.db( (self.db.users.username == username) & (self.db.users.password == password) ).select().first()
        self.db.close()
        #if no user, it will return user = None
        return user
Ejemplo n.º 19
0
    def testRun(self):
        db = DAL(DEFAULT_URI, check_reserved=['all'])
        db.define_table('aa',Field('name'))
        db.define_table('bb',Field('aa','reference aa'),Field('name'))
        for k in ('x','y','z'):
            i = db.aa.insert(name=k)
            for j in ('u','v','w'):
                db.bb.insert(aa=i,name=k+j)
        db.commit()
        rows = db(db.aa).select()
        rows.join(db.bb.aa, fields=[db.bb.name], orderby=[db.bb.name])
        self.assertEqual(rows[0].bb[0].name, 'xu')
        self.assertEqual(rows[0].bb[1].name, 'xv')
        self.assertEqual(rows[0].bb[2].name, 'xw')
        self.assertEqual(rows[1].bb[0].name, 'yu')
        self.assertEqual(rows[1].bb[1].name, 'yv')
        self.assertEqual(rows[1].bb[2].name, 'yw')
        self.assertEqual(rows[2].bb[0].name, 'zu')
        self.assertEqual(rows[2].bb[1].name, 'zv')
        self.assertEqual(rows[2].bb[2].name, 'zw')

        rows = db(db.bb).select()
        rows.join(db.aa.id, fields=[db.aa.name])
        
        self.assertEqual(rows[0].aa.name, 'x')
        self.assertEqual(rows[1].aa.name, 'x')
        self.assertEqual(rows[2].aa.name, 'x')
        self.assertEqual(rows[3].aa.name, 'y')
        self.assertEqual(rows[4].aa.name, 'y')
        self.assertEqual(rows[5].aa.name, 'y')
        self.assertEqual(rows[6].aa.name, 'z')
        self.assertEqual(rows[7].aa.name, 'z')
        self.assertEqual(rows[8].aa.name, 'z')

        rows_json = rows.as_json()
        drop(db.bb)
        drop(db.aa)
        db.close()
Ejemplo n.º 20
0
    def testRun(self):
        db = DAL(DEFAULT_URI, check_reserved=["all"])
        db.define_table("aa", Field("name"))
        db.define_table("bb", Field("aa", "reference aa"), Field("name"))
        for k in ("x", "y", "z"):
            i = db.aa.insert(name=k)
            for j in ("u", "v", "w"):
                db.bb.insert(aa=i, name=k + j)
        db.commit()
        rows = db(db.aa).select()
        rows.join(db.bb.aa, fields=[db.bb.name], orderby=[db.bb.name])
        self.assertEqual(rows[0].bb[0].name, "xu")
        self.assertEqual(rows[0].bb[1].name, "xv")
        self.assertEqual(rows[0].bb[2].name, "xw")
        self.assertEqual(rows[1].bb[0].name, "yu")
        self.assertEqual(rows[1].bb[1].name, "yv")
        self.assertEqual(rows[1].bb[2].name, "yw")
        self.assertEqual(rows[2].bb[0].name, "zu")
        self.assertEqual(rows[2].bb[1].name, "zv")
        self.assertEqual(rows[2].bb[2].name, "zw")

        rows = db(db.bb).select()
        rows.join(db.aa.id, fields=[db.aa.name])

        self.assertEqual(rows[0].aa.name, "x")
        self.assertEqual(rows[1].aa.name, "x")
        self.assertEqual(rows[2].aa.name, "x")
        self.assertEqual(rows[3].aa.name, "y")
        self.assertEqual(rows[4].aa.name, "y")
        self.assertEqual(rows[5].aa.name, "y")
        self.assertEqual(rows[6].aa.name, "z")
        self.assertEqual(rows[7].aa.name, "z")
        self.assertEqual(rows[8].aa.name, "z")

        rows_json = rows.as_json()
        drop(db.bb)
        drop(db.aa)
        db.close()
Ejemplo n.º 21
0
 def testRun(self):
     db = DAL(None)
     db.define_table("no_table", Field("aa"))
     self.assertIsInstance(db.no_table.aa, Field)
     self.assertIsInstance(db.no_table["aa"], Field)
     db.close()
Ejemplo n.º 22
0
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()
Ejemplo n.º 23
0
Archivo: base.py Proyecto: web2py/pydal
 def testRun(self):
     db = DAL(None)
     db.define_table('no_table', Field('aa'))
     self.assertIsInstance(db.no_table.aa, Field)
     self.assertIsInstance(db.no_table['aa'], Field)
     db.close()
Ejemplo n.º 24
0
 def testRun(self):
     db = DAL(None)
     db.define_table('no_table', Field('aa'))
     self.assertIsInstance(db.no_table.aa, Field)
     self.assertIsInstance(db.no_table['aa'], Field)
     db.close()
Ejemplo n.º 25
0
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()
Ejemplo n.º 26
0
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)
Ejemplo n.º 27
0
    print("You Want to regist in event!input 1 >> ok 2 >> not ")
    check = int(input())
    while check < 4:
        print("Welcome to festival in Da nang city!")
        print("       ---------------------     ")
        print("You can choose the option!")
        print(
            "You are 1 : member or 2: not member or 3 :find 4 :delete ..> 5to exist"
        )

        position = int(input())

        if position == 1:
            print("--List of festival in Da Nang city---")
            list_festival()
        elif position == 2:
            print("--Please regist member--")
            regist_member()
        elif position == 3:
            print("--List the people have been registed--")
            list_registedById()
        elif position == 4:
            print("--List the people have been registed by Id--")
            delete_registedPerson()
        else:
            check = 5
finally:
    if dbConnect:
        dbConnect.close()
        if l in punctuation:
            checkTrend = checkTrend.replace(l, '')
    if not checkTrend.replace(" ",
                              "").isalnum():  #if the trend is not alphanumeric
        db.words.insert(word=trend, freq=1,
                        trends=[])  #just add it to the db and move on
        failed += 1
        print(trend, "is not alpha or digit")
        continue
    splitTrend = parse(trend)
    splitTrend = [st.lower() for st in splitTrend]

    tdate = str(entry.get('date'))
    for word in splitTrend:
        #print(word)
        trendDates = []
        if word in myWords:  #if this word already exists,
            wordEntry = db(db.words.word == word).select(
                db.words.ALL)[0]  #just update the freq and associated words
            wordEntry.update_record(freq=wordEntry.freq + 1)
            wordEntry.update_record(dates=wordEntry.dates + [tdate])
        else:  #if the word doesn't already exist
            db.words.insert(word=word, freq=1,
                            dates=[tdate])  #add it to the new db
            myWords.append(word)
            count += 1

print("Added " + str(count) + " entries.")
print("Failed with " + str(failed) + " entries.")
db.close()
Ejemplo n.º 29
0
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)
Ejemplo n.º 30
0
class CalDav(object):
  def __init__(self):
    self.db = DAL('sqlite://caldav.db', folder=BaseDirectory.save_data_path('pyhttpserver'))
    self.db.define_table('collections', Field('url', type='string', required=True, unique=True),
                                        Field('displayname', type='string'),
                                        Field('subscribed', type='boolean', default=True))
    self.db.define_table('colitems', Field('href', type='string', required=True),
                                  Field('etag', type='string'),
                                  Field('collection', type='reference collections'),
                                  Field('content', type='blob'),
                                  Field('local_status', type='integer', default=0))
    self.davStorages = {}

  def __del__(self):
    self.db.close()


  def sync_collections(self):
    deletedCollections = set([ x.url for x in self.db(self.db.collections.url != None).select() ])
    cfg = common.get_davconfig()
    for serverCfg in cfg.get('servers', []):
      config = dict(username=serverCfg['username'], password=serverCfg['password'], url=serverCfg['url'])
      config['url'] = requests.get(config['url']).url
      storage = CalDAVStorage(**config)

      # Fetch all locally available collections, then fetch the collections from the server side and add them locally if they are not available yet
      # Afterwards delete all collections from the database that were not available on the server side anymore
      for s in storage.discover(**config):
        subStorage = CalDAVStorage(**s)
        displayname = subStorage.get_meta('displayname')
        deletedCollections.discard(s['url'])
        self.db.collections.update_or_insert(self.db.collections.url==s['url'], url=s['url'], displayname=displayname)
        self.davStorages[s['url']] = subStorage
    for deletedCollection in deletedCollections:
      print("Collection with url={} was deleted on the server side".format(deletedCollection))
      self.db(self.db.collections.url == deletedCollection).delete()
    self.db.commit()


  def sync(self, socketio=None):
    #~ if socketio: socketio.sleep(3)
    self.sync_collections()
    # sync only subsribed collections...
    for collectionRow in self.db(self.db.collections.subscribed == True).select():
      davUrl = collectionRow.url
      davStorage = self.davStorages[davUrl]
      id = collectionRow.id

      # Sync local changes to the server
      self.sync_local_changes(davUrl)

      # get changes from server
      updated = set()
      new = set()
      deleted = set([ x['href']  for x in self.db(self.db.colitems.collection == id).select() ])
      for href,etag in davStorage.list():
        if href in deleted:
          deleted.remove(href)
          if self.db((self.db.colitems.collection == id) & (self.db.colitems.href == href)).select()[0].etag != etag:
            updated.add(href)
        else:
          new.add(href)

      if len(new)>0:     print("New objects at server: ", new)
      if len(updated)>0: print("Updated objects at server: ", updated)
      if len(deleted)>0: print("Deleted objects at server: ", deleted)

      # Delete server deleted items also from local database
      for deletedItem in deleted:
        print("Deleted object with href={} on the server side".format(deletedItem))
        self.db(self.db.colitems.href == deletedItem).delete()
      # Fetch server created/modified items and update in local db
      for href, item, etag in davStorage.get_multi(updated|new):
        self.db.colitems.update_or_insert((self.db.colitems.collection==id) & (self.db.colitems.href==href), href=href, etag=etag, collection=id, content=item.raw, local_status=0)

    for collectionRow in self.db(self.db.collections.subscribed == False).select():
      self.db(self.db.colitems.collection==collectionRow.id).delete();

    self.db.commit()

  def get_collection_id(self, url):
    return self.db(self.db.collections.url == url).select()[0].id


  def sync_local_changes(self, davStorageUrl):
    davStorage = self.davStorages[davStorageUrl]
    collectionId = self.get_collection_id(davStorageUrl)
    for deletedItem in self.db((self.db.colitems.local_status == 2) & (self.db.colitems.collection == collectionId)).select():
      print("Deleting locally removed item with etag={} from server".format(deletedItem.etag))
      davStorage.delete(deletedItem.href, deletedItem.etag)
      deletedItem.delete_record()
    for modifiedItem in self.db((self.db.colitems.local_status == 1) & (self.db.colitems.collection == collectionId)).select():
      print("Updating locally modified item with etag={} at server".format(modifiedItem.etag))
      newEtag = davStorage.update(modifiedItem.href, Item(modifiedItem.content), modifiedItem.etag)
      modifiedItem.update_record(etag=newEtag, local_status=0)
    for newItem in self.db((self.db.colitems.local_status == 3) & (self.db.colitems.collection == collectionId)).select():
      print("Adding a new ical to the server")
      href, etag = davStorage.upload(Item(newItem.content))
      newItem.update_record(etag=etag, href=href, local_status=0)
    self.db.commit()

  def get_subscribed_collections(self):
    return [ {'url': x.url, 'id': x.id, 'name': x.displayname} for x in self.db(self.db.collections.subscribed == True).select() ]

  def get(self, id):
    return vcard21_to_vcard30(vobject.readOne(self.db(self.db.colitems.id == id).select()[0].content))

  def get_structured(self, id):
    vcard = self.get(id)
    ret = {}
    vcardAsDict = vcard.contents
    if 'fn' not in vcardAsDict:
      print("vCard with id={} does not have the field FN set".format(id))
    else:
      if len(vcardAsDict['fn']) > 1: print("vCard with id={} has more than one FN field. Ignoring all but the first".format(id))
      ret['fn'] = vcardAsDict['fn'][0].value
    if 'n' not in vcardAsDict:
      print("vCard with id={} does not have the field N set".format(id))
      ret['fn'] = dict(given="",family="",prefix="",suffix="",additional="")
    else:
      if len(vcardAsDict['n']) > 1: print("vCard with id={} has more than one N field. Ignoring all but the first".format(id))
      ret['n'] = vcardAsDict['n'][0].value.__dict__
    if len(vcardAsDict.get('bday', [])) > 1: print("vCard with id={} has more than one BDAY field. Ignoring all but the first".format(id))
    ret['bday'] = ""
    if 'bday' in vcardAsDict and vcardAsDict['bday'][0].value:
      bday_datetime = parse(vcardAsDict['bday'][0].value).replace(tzinfo=None)
      ret['bday'] = '%02d. %02d. %04d' % (bday_datetime.day, bday_datetime.month, bday_datetime.year)
    for a in ['adr','tel','email']:
      for i in range(len(vcardAsDict.get(a, []))):
        setattr(vcardAsDict[a][i], 'orig_id', i)
    for attr in ['adr', 'tel', 'email']:
      ret[attr] = [ {'value': (x.value if type(x.value)==str else x.value.__dict__),
                     'type': [t.lstrip('x-') for t in x.params.get('TYPE', []) if t.lower() != 'pref'],
                     'pref': (vcard_get_pref_value(x) != 101),
                     'orig_id': x.orig_id
                    } for x in sorted(vcardAsDict.get(attr, []), key=vcard_get_pref_value) ]
    ret['itemid'] = id
    ret['colid'] = self.db(self.db.colitems.id == id).select()[0].collection;
    return ret

  def get_serialized(self, id):
    vcard = self.get_structured(id)
    vcard_formatted = 'Name: {}\n'.format(vcard['fn'])
    vcard_formatted += 'Full name: {} {} {} {} {}\n\n'.format(vcard['n']['prefix'],vcard['n']['given'],vcard['n']['additional'],vcard['n']['family'],vcard['n']['suffix'])
    if vcard['bday']: vcard_formatted += 'Birthday: {}\n\n'.format(vcard['bday'])
    for email in vcard['email']:
      vcard_formatted += 'Email {}{}:\n\t{}\n'.format(",".join(email['type']), " (pref)" if email['pref'] else "", email['value'])
    vcard_formatted += '\n'
    for adr in vcard['adr']:
      vcard_formatted += 'Address {}{}:\n\t{}\n\t{} {}\n\t{}\n\n'.format(",".join(adr['type']), " (pref)" if adr['pref'] else "", adr['value']['street'], adr['value']['code'], adr['value']['city'], adr['value']['country'])
    for tel in vcard['tel']:
      vcard_formatted += 'Telephone {}{}:\n\t{}\n'.format(",".join(tel['type']), " (pref)" if tel['pref'] else "", tel['value'])
    return vcard_formatted

  def get_all_items(self):
    def mk_item(db_item):
      ret = {"id": db_item.id, "colid": db_item.collection}
      vItem = self.get_structured(db_item.id)
      ret['fn'] = vItem['fn']
      ret['email'] = "<br />".join((x['value'] for x in vItem['email']))
      ret['tel'] = "<br />".join((x['value'] for x in vItem['tel']))
      ret['bday'] = vItem['bday']
      ret['bday_diff'] = 400
      if vItem['bday']:
        bday_datetime = datetime.strptime(vItem['bday'], "%d. %m. %Y")
        ret['bday_diff'] = (rrule(YEARLY, bymonth=bday_datetime.month, bymonthday=bday_datetime.day).after(bday_datetime, True) - datetime.today()).days
      return ret
    return ( mk_item(x) for x in self.db(self.db.colitems.local_status != 2).select() )

  def _merge_dict_to_vcard(self, itemDict, vcard):
    def get_vcard_item(vcard, item, append=False, cls=str):
      entry = vcard.add(item) if append else getattr(vcard, item) if hasattr(vcard, item) else vcard.add(item)
      if append: entry.value = cls()
      assert cls == type(entry.value)
      return entry
    get_vcard_item(vcard, 'n', cls=vobject.vcard.Name).value.__dict__.update(itemDict['n'])
    get_vcard_item(vcard, 'fn').value = "{0} {1}".format(itemDict['n']['given'], itemDict['n']['family'])
    if itemDict['bday']:
      get_vcard_item(vcard, 'bday').value = datetime.strptime(itemDict['bday'], "%d. %m. %Y").strftime("%Y%m%d")
      del vcard.contents['bday'][1:]
    elif 'bday' in vcard.contents: del vcard.contents['bday']
    for (attr, cls) in [('adr',vobject.vcard.Address), ('tel',str), ('email',str)]:
      entriesBefore = frozenset(range(len(vcard.contents.get(attr, []))))
      entriesNow = frozenset([ x['orig_id'] for x in itemDict[attr] if 'orig_id' in x ])
      for item in itemDict[attr]:
        orig_id = item.get('orig_id', None)
        entry = vcard.contents[attr][orig_id] if orig_id != None else get_vcard_item(vcard, attr, append="True", cls=cls)
        if (cls == str): entry.value = item['value']
        else: entry.value.__dict__.update(item['value'])
        knownTypes = frozenset(item['type']).intersection(defined_types[attr])
        userTypes = [ 'x-{}'.format(x) for x in frozenset(item['type']).difference(defined_types[attr]) ]
        pref = ['pref'] if item['pref'] else []
        entry.params['TYPE'] = list(knownTypes)+userTypes+pref
        if attr=='adr' and 'LABEL' in entry.params: del entry.params['LABEL']
      for deletedIdx in sorted(entriesBefore.difference(entriesNow), reverse=True): del vcard.contents[attr][deletedIdx]
    d = datetime.now()
    get_vcard_item(vcard, 'rev').value = "%04d%02d%02dT%02d%02d%02d"%(d.year,d.month,d.day,d.hour,d.minute,d.second)
    return vcard

  def update_item(self, id, itemDict, socketio=None):
    self.db(self.db.colitems.id == id).update(content=self._merge_dict_to_vcard(itemDict, self.get(id)).serialize(), local_status=1)
    self.db.commit()
    if socketio: socketio.start_background_task(self.sync, socketio)
    else: self.sync()
    return self.get_structured(id)

  def add_item(self, itemDict, collectionId=1, socketio=None):
    id = self.db.colitems.insert(content=self._merge_dict_to_vcard(itemDict, vobject.vCard()).serialize(), local_status=3, collection=collectionId, href="")
    self.db.commit()
    if socketio: socketio.start_background_task(self.sync, socketio)
    else: self.sync()
    return self.get_structured(id)

  def delete_item(self, id, socketio=None):
    item = self.db(self.db.colitems.id == id).select()[0]
    if item.local_status == 3: item.delete_record()
    else: item.update_record(local_status=2)
    self.db.commit()
    if socketio: socketio.start_background_task(self.sync, socketio)
    else: self.sync()


  def get_collections(self, user, pw, url):
    config = dict(username=user, password=pw, url=url)
    if not pw:
      keyringData = keyring.DbusKeyring().FindItem({'dav-url': url, 'dav-user': user})
      config['password'] = keyringData[1]
    config['url'] = requests.get(config['url']).url # do redirection magic
    storage = CalDAVStorage(**config)
    ret = []
    for s in storage.discover(**config):
      subscribed = False
      subStorage = CalDAVStorage(**s)
      displayname = subStorage.get_meta('displayname')
      dbSet = self.db(self.db.collections.url==s['url'])
      if not dbSet.isempty(): subscribed = dbSet.select()[0].subscribed
      ret.append({'url': s['url'], 'subscribed': subscribed, 'name': displayname})
    return ret

  def update_collection_subscriptions(self, incomingJson):
    for server in incomingJson['servers']:
      for caldav in server['caldav_collections']:
        self.db.collections.update_or_insert(self.db.collections.url==caldav['url'], url=caldav['url'], subscribed=caldav["subscribed"]);