Esempio n. 1
0
def test_pd_field():

    db = SqliteDatabase(":memory:")

    class TestModel(Model):
        pf = PartialDateField(null=True)

        class Meta:
            database = db

    TestModel.create_table()
    TestModel(pf=PartialDate()).save()
    TestModel(pf=None).save()
    res = [r[0] for r in db.execute_sql("SELECT pf FROM testmodel").fetchall()]
    assert res[0] is None and res[1] is None
    TestModel(pf=PartialDate(1997)).save()
    TestModel(pf=PartialDate(1996, 4)).save()
    TestModel(pf=PartialDate(1995, 5, 13)).save()
    res = [r[0] for r in db.execute_sql("SELECT pf FROM testmodel").fetchall()]
    assert '1995-05-13' in res
    assert '1996-04-**' in res
    assert '1997-**-**' in res

    res = [r.pf for r in TestModel.select().order_by(TestModel.pf)]
    assert res[0] is None
    assert res[1] is None
    assert res[2] == PartialDate(1995, 5, 13)
    assert res[3] == PartialDate(1996, 4)
    assert res[4] == PartialDate(1997)
Esempio n. 2
0
def loader():
    database = SqliteDatabase(dbname)

    names = 0
    donations = 1

    try:
        database.connect()
        database.execute_sql('PRAGMA foreign_keys = ON;')
        for entry in raw_input:
            with database.transaction():
                new_donor = Donor.create(name=entry[names])
                # new_donor.save()
                new_donation = Donation.create(gift_id=uuid.uuid4(),
                                               value=entry[donations],
                                               donated_by=entry[names],
                                               gift_num=1)
                # new_donation.save()
                logging.info('Database add successful')

    # except Exception as e:
    #     logging.info(e)

    finally:
        logging.info('database closes')
        database.close()
def add_customer(customer_id, name, lastname, home_address, phone_number,
                 email_address, status, credit_limit):
    """ This function will add a new customer to the customers.db database """
    logger.info('In add_customer().')

    database = SqliteDatabase('customers.db')
    try:
        database.connect()
        database.execute_sql('PRAGMA foreign_keys = ON;')
        with database.transaction():
            new_customer = Customer.create(customer_id=customer_id,
                                           name=name,
                                           lastname=lastname,
                                           home_address=home_address,
                                           phone_number=phone_number,
                                           email_address=email_address,
                                           status=status,
                                           credit_limit=credit_limit)

            new_customer.save()
            logger.info('Add new customer to Customer database successfully.')
    except Exception as e:
        logger.info(f'Error creating {name} customer record.')
        logger.info(e)

    finally:
        logger.info('database closes.')
        database.close()
def create_database(db):
    if not os.path.isfile(db):
        database = SqliteDatabase(db)
        database.connect()
        database.execute_sql('PRAGMA foreign_keys = ON;')

        database.create_tables([Customer])
        database.close()
Esempio n. 5
0
def init_database():
    """
    Initialize the database.
    """
    logging.info("Initializing database...")

    database = SqliteDatabase("customers.db")
    database.connect()
    database.execute_sql("PRAGMA foreign_keys = ON;")

    logging.info("Database initialized successfully.")

    return database
Esempio n. 6
0
def load_tags_db():
    '''
    load user tags data from uploaded db file

    Args:
        file: io.BufferedRandom -> uploaded db file stream
    '''
    db_name = get_data_path('uploaded.db')
    try:
        db_upload = SqliteDatabase(db_name)
        db_upload.get_tables()
    except DatabaseError:
        raise DBError()
    db_is_old = False
    tag_data = []
    missed_fanhaos = []
    tag_file_added = 0
    sql_old = '''select item_rate.rate_value, item.fanhao
                from item_rate inner
                join item on item_rate.item_id = item.id
                where item_rate.rate_type=1 '''

    sql_new = '''select item_rate.rate_value, item.fanhao
                from item_rate inner
                join item on item_rate.item_id = item.fanhao
                where item_rate.rate_type=1 '''
    cursor = db_upload.execute_sql(sql_old)
    res = cursor.fetchone()
    if res:
        db_is_old = True
    if db_is_old:
        cursor = db_upload.execute_sql(sql_old)
    else:
        cursor = db_upload.execute_sql(sql_new)

    for row in cursor.fetchall():
        tag_data.append(row)
    with db_upload.atomic():
        for rate_value, fanhao in tag_data:
            item_rate = ItemRate.saveit(RATE_TYPE.USER_RATE, rate_value,
                                        fanhao)
            if item_rate:
                tag_file_added += 1
            if not Item.get_by_fanhao(fanhao):
                # add to get from spider
                missed_fanhaos.append(fanhao)
    logger.debug(tag_data)
    logger.info(f'added user tag rate: {tag_file_added}')
    logger.info(f'added fanhao to download: {len(missed_fanhaos)}')
    return tag_file_added, missed_fanhaos
Esempio n. 7
0
def test_task_type_field(mocker):

    db = SqliteDatabase(":memory:")

    class TestModel(Model):
        tt = TaskTypeField(null=True)

        class Meta:
            database = db

    exception = mocker.patch.object(app.logger, "exception")
    TestModel.create_table()
    TestModel.create(tt=None)
    for v in TaskType:
        TestModel.create(tt=v)
        TestModel.create(tt=str(v.value))
        TestModel.create(tt=v.value)
        TestModel.create(tt=v.name)
    TestModel.create(tt=dict())
    exception.assert_called_once()
    res = {
        r[0]: r[1]
        for r in db.execute_sql(
            "SELECT tt, count(*) AS rc FROM testmodel GROUP BY tt ORDER BY 1").
        fetchall()
    }
    assert all(res[v.value] == 4 for v in TaskType)
    assert res[None] == 2
Esempio n. 8
0
def report():
    database = SqliteDatabase('mailroom.db')
    database.connect()
    print('Last Name____Title__Total Donations__Number of Donations')
    donor_table = database.execute_sql('select * from donor;')
    for donor in donor_table:
        print(donor)
    database.close()
Esempio n. 9
0
def exportSQLite32Excel(dbname, tblname, file):
    try:
        db = SqliteDatabase(dbname)
        sql =  "select * from {0}".format(tblname,)
        result = db.execute_sql(sql)
        utils.writeExcel(result, file, 6, 2)
        utils.writeCreateTime(file, 2, 4)
    except Exception as e:
        logging.debug(e)
    finally:
        db.close() 
Esempio n. 10
0
def initialDates(dbname, startDate, endDate):
    try:
        db = SqliteDatabase(dbname)
        days = (endDate - startDate).days
        db.execute_sql("drop table if exists t_date_temp")
        db.execute_sql("create table t_date_temp (mydate text, week text, weekday text)")
        db.execute_sql("insert into t_date_temp values( ?, ?, ? )", (startDate.strftime('%Y-%m-%d'),startDate.isoweekday(),''))
        for i in range(days):
            db.execute_sql("insert into t_date_temp  select date(max(mydate), '+1 day'),  strftime('%w', date(max(mydate), '+1 day')), '' from t_date_temp")
    except Exception as e:
        logging.debug(e)
    finally:
        db.close()
Esempio n. 11
0
def checkTables(dbname, tables):
    db = SqliteDatabase(dbname)
    if db:
        param = "','".join(tables)
        param = "'" + param + "'"
        sql = "SELECT count(*) FROM sqlite_master WHERE type='table' and tbl_name in ({0})".format(param,)    
        result = db.execute_sql(sql)
        for e in result:
            count = e[0]
        if count != len(tables):
            return False
        else:
            return True
    else:
        return False
Esempio n. 12
0
class ProxiesWorker(object):

	_proxies_db = None
	def __init__(self, https = False, anonymous = False):
		super(object, self).__init__()

		self.create_connection()

		self._https = https
		self._anonymous = anonymous
	
	def create_connection(self) -> SqliteDatabase:
		"""
		create a database connection
		:rtype: SqliteDatabase
		"""
		if self._proxies_db:
			return self._proxies_db
		else: 
			# logger.debug('create new db connection')
			dbpath = get_abspath(get_config('db_path', './scylla.db'))
			if(not os.path.exists(dbpath)):
				raise SqliteDbFilenotFoundError("Sqlit db file not exists")

			self._proxies_db = SqliteDatabase(dbpath)
			return self._proxies_db

	
	def _get_valid_proxies_query(self):
		query = "SELECT ip, port, is_https FROM proxy_ips where latency > 0 and latency < 9999 and is_valid = '1'" 
		query = query + " and is_https = '" + ("1" if self._https else "0") + "'"
		query = query + " and is_anonymous = '" + ("1" if self._anonymous else "0") + "'"

		cursor = self._proxies_db.execute_sql(query)
		return [(i[0], i[1], i[2]) for i in cursor]
		
	#@pysnooper.snoop()
	def get_proxies(self) -> tuple:
		proxies_list = self._get_valid_proxies_query()		
		import random
		r = random.choice(proxies_list)
		proxy : tuple = r
		proxy_str = "{}://{}:{}".format("https" if proxy[2] == "1" else "http", proxy[0], proxy[1]) 
		proxies = {"https": proxy_str, "http": proxy_str}

		# logger.debug('change proxies successfully,{} ...'.format(proxies))
		return proxies
Esempio n. 13
0
class LoadToDB:
    def __init__(self, idbname):
        logging.info("Link sqlite path to database")
        self.database = SqliteDatabase(idbname)

    def db_con(self):
        logging.info("Establishing connection to database")
        self.database.connect()
        # execute SQL directly
        self.database.execute_sql('PRAGMA foreign_keys = ON;')

    def db_discon(self):
        logging.info("Database closed")
        self.database.close()

    def check_unique(self, iname):
        try:
            Donor.get(Donor.name == iname)
            logging.info(f"{iname} is on record")
            return False
        except Exception as e:
            logging.info(f"{iname} is not on record")
            logging.info(e)
            return True

    def add(self, iname, idonation=0.0):
        # gift count to account for entry that is name only, or zero donation specified
        if idonation == 0:
            gift_count = 0
        else:
            gift_count = 1
        self.db_con()
        if self.check_unique(iname):
            logging.info(
                f"Adding new donor {iname}, with a donation of ${idonation}")
            with self.database.transaction():
                new_person = Donor.create(name=iname)
                new_donation = Donation.create(gift_num=gift_count,
                                               value=idonation,
                                               donated_by=iname,
                                               gift_id=uuid.uuid4())
                # new_person.save()
                # new_donation.save()
                logging.info('Database add successful')
                print(f"{iname} has been added to the Database")
        else:
            logging.info(
                f"Donor {iname} already on record, adding ${idonation} to existing funds."
            )
            old_d = Donation.get(Donation.donated_by == iname).value
            old_g = Donation.get(Donation.donated_by == iname).gift_num
            # only try to add to Donation primary key if donation amount dont not equal zero
            if gift_count > 0:
                with self.database.transaction():
                    new_donation = Donation.create(gift_id=uuid.uuid4(),
                                                   gift_num=(old_g + 1),
                                                   value=(old_d + idonation),
                                                   donated_by=iname)
                    # new_donation.save()
                    logging.info('Database add successful')
                    print(
                        f"{iname} with a donation of ${idonation:.2f} has been added to the Database"
                    )
        self.db_discon()

    def remove(self, iname):
        self.db_con()
        if self.check_unique(iname):
            print("Name is not on record")
        else:
            query = Donor.get(Donor.name == iname)
            query.delete_instance()
            query = Donation.get(Donation.donated_by == iname)
            query.delete_instance()
            print(f"{iname} removed from Database")
        self.db_discon()

    def remove_donation(self, iname, igift):
        self.db_con()

        try:
            query = Donation.get(Donation.donated_by == iname,
                                 Donation.gift_num == igift)
        except Exception as e:
            logging.info(
                f"{iname} with {igift} is not on record of Donation DB")
            logging.info(e)
        else:
            if int(igift) >= 1:
                query.delete_instance()
                print(f"{iname} gift number {igift} removed from Database")
        self.db_discon()
Esempio n. 14
0
class DatabaseManager:
    logger = logging.getLogger(__name__)

    def __init__(self, channel: EFBChannel):
        base_path = utils.get_data_path(channel.channel_id)

        self.db = SqliteDatabase(base_path + '/tgdata.db')

        self.db.connect()

        class BaseModel(Model):
            class Meta:
                database = self.db

        class ChatAssoc(BaseModel):
            master_uid = TextField()
            slave_uid = TextField()

        class MsgLog(BaseModel):
            master_msg_id = TextField(unique=True, primary_key=True)
            slave_message_id = TextField()
            text = TextField()
            slave_origin_uid = TextField()
            slave_origin_display_name = TextField(null=True)
            slave_member_uid = TextField(null=True)
            slave_member_display_name = TextField(null=True)
            msg_type = TextField()
            sent_to = TextField()
            time = DateTimeField(default=datetime.datetime.now, null=True)

        class SlaveChatInfo(BaseModel):
            slave_channel_id = TextField()
            slave_channel_emoji = CharField()
            slave_chat_uid = TextField()
            slave_chat_name = TextField()
            slave_chat_alias = TextField(null=True)
            slave_chat_type = CharField()

        self.BaseModel = BaseModel
        self.ChatAssoc = ChatAssoc
        self.MsgLog = MsgLog
        self.SlaveChatInfo = SlaveChatInfo

        if not ChatAssoc.table_exists():
            self._create()

    def _create(self):
        """
        Initializing tables.
        """
        self.db.execute_sql("PRAGMA journal_mode = OFF")
        self.db.create_tables(
            [self.ChatAssoc, self.MsgLog, self.SlaveChatInfo])

    def _migrate(self, i):
        """
        Run migrations.

        Args:
            i: Migration ID

        Returns:
            False: when migration ID is not found
        """
        # migrator = SqliteMigrator(db)
        # if i == 0:
        #     # Migration 0: Added Time column in MsgLog table.
        #     # 2016JUN15
        #     migrate(migrator.add_column("msglog", "time", DateTimeField(default=datetime.datetime.now, null=True)))
        # elif i == 1:
        #     # Migration 1:
        #     # Add table: SlaveChatInfo
        #     # 2017FEB25
        #     SlaveChatInfo.create_table()
        #     migrate(migrator.add_column("msglog", "slave_message_id", CharField(default="__none__")))
        #
        # else:
        return False

    def add_chat_assoc(self, master_uid, slave_uid, multiple_slave=False):
        """
        Add chat associations (chat links).
        One Master channel with many Slave channel.

        Args:
            master_uid (str): Master channel UID ("%(chat_id)s")
            slave_uid (str): Slave channel UID ("%(channel_id)s.%(chat_id)s")
        """
        if not multiple_slave:
            self.remove_chat_assoc(master_uid=master_uid)
        self.remove_chat_assoc(slave_uid=slave_uid)
        return self.ChatAssoc.create(master_uid=master_uid,
                                     slave_uid=slave_uid)

    def remove_chat_assoc(self, master_uid=None, slave_uid=None):
        """
        Remove chat associations (chat links).
        Only one parameter is to be provided.

        Args:
            master_uid (str): Master channel UID ("%(chat_id)s")
            slave_uid (str): Slave channel UID ("%(channel_id)s.%(chat_id)s")
        """
        try:
            if bool(master_uid) == bool(slave_uid):
                raise ValueError("Only one parameter is to be provided.")
            elif master_uid:
                return self.ChatAssoc.delete().where(
                    self.ChatAssoc.master_uid == master_uid).execute()
            elif slave_uid:
                return self.ChatAssoc.delete().where(
                    self.ChatAssoc.slave_uid == slave_uid).execute()
        except DoesNotExist:
            return 0

    def get_chat_assoc(self,
                       master_uid: str = None,
                       slave_uid: str = None) -> List[str]:
        """
        Get chat association (chat link) information.
        Only one parameter is to be provided.

        Args:
            master_uid (str): Master channel UID ("%(chat_id)s")
            slave_uid (str): Slave channel UID ("%(channel_id)s.%(chat_id)s")

        Returns:
            list: The counterpart ID.
        """
        try:
            if bool(master_uid) == bool(slave_uid):
                raise ValueError("Only one parameter is to be provided.")
            elif master_uid:
                slaves = self.ChatAssoc.select().where(
                    self.ChatAssoc.master_uid == master_uid)
                if len(slaves) > 0:
                    return [i.slave_uid for i in slaves]
                else:
                    return []
            elif slave_uid:
                masters = self.ChatAssoc.select().where(
                    self.ChatAssoc.slave_uid == slave_uid)
                if len(masters) > 0:
                    return [i.master_uid for i in masters]
                else:
                    return []
        except DoesNotExist:
            return []

    def get_last_msg_from_chat(self, chat_id):
        """Get last message from the selected chat from Telegram

        Args:
            chat_id (int|str): Telegram chat ID

        Returns:
            MsgLog: The last message from the chat
        """
        try:
            return self.MsgLog.select().where(
                self.MsgLog.master_msg_id.startswith(
                    "%s." % chat_id)).order_by(
                        self.MsgLog.time.desc()).first()
        except DoesNotExist:
            return None

    def add_msg_log(self, **kwargs):
        """
        Add an entry to message log.

        Display name is defined as `alias or name`.

        Args:
            master_msg_id (str): Telegram message ID ("%(chat_id)s.%(msg_id)s")
            text (str): String representation of the message
            slave_origin_uid (str): Slave chat ID ("%(channel_id)s.%(chat_id)s")
            msg_type (str): String of the message type.
            sent_to (str): "master" or "slave"
            slave_origin_display_name (str): Display name of slave chat.
            slave_member_uid (str|None):
                User ID of the slave chat member (sender of the message, for group chat only).
                ("%(channel_id)s.%(chat_id)s"), None if not available.
            slave_member_display_name (str|None):
                Display name of the member, None if not available.
            update (bool): Update a previous record. Default: False.
            slave_message_id (str): the corresponding message uid from slave channel.

        Returns:
            MsgLog: The added/updated entry.
        """
        master_msg_id = kwargs.get('master_msg_id')
        text = kwargs.get('text')
        slave_origin_uid = kwargs.get('slave_origin_uid')
        msg_type = kwargs.get('msg_type')
        sent_to = kwargs.get('sent_to')
        slave_origin_display_name = kwargs.get('slave_origin_display_name',
                                               None)
        slave_member_uid = kwargs.get('slave_member_uid', None)
        slave_member_display_name = kwargs.get('slave_member_display_name',
                                               None)
        slave_message_id = kwargs.get('slave_message_id')
        update = kwargs.get('update', False)
        if update:
            msg_log = self.MsgLog.get(
                self.MsgLog.master_msg_id == master_msg_id)
            msg_log.text = text or msg_log.text
            msg_log.msg_type = msg_type or msg_log.msg_type
            msg_log.sent_to = sent_to or msg_log.sent_to
            msg_log.slave_origin_uid = slave_origin_uid or msg_log.slave_origin_uid
            msg_log.slave_origin_display_name = slave_origin_display_name or msg_log.slave_origin_display_name
            msg_log.slave_member_uid = slave_member_uid or msg_log.slave_member_uid
            msg_log.slave_member_display_name = slave_member_display_name or msg_log.slave_member_display_name
            msg_log.slave_message_id = slave_message_id or msg_log.slave_message_id
            msg_log.save()
            return msg_log
        else:
            return self.MsgLog.create(
                master_msg_id=master_msg_id,
                slave_message_id=slave_message_id,
                text=text,
                slave_origin_uid=slave_origin_uid,
                msg_type=msg_type,
                sent_to=sent_to,
                slave_origin_display_name=slave_origin_display_name,
                slave_member_uid=slave_member_uid,
                slave_member_display_name=slave_member_display_name)

    def get_msg_log(
            self,
            master_msg_id: Optional[str] = None,
            slave_msg_id: Optional[str] = None,
            slave_origin_uid: Optional[str] = None) -> Optional['MsgLog']:
        """Get message log by message ID.

        Args:
            master_msg_id: Telegram message ID in string
            slave_msg_id: Slave message identifier in string
            slave_origin_uid: Slave chat identifier in string

        Returns:
            MsgLog|None: The queried entry, None if not exist.
        """
        if (master_msg_id and (slave_msg_id or slave_origin_uid)) \
                or not (master_msg_id or (slave_msg_id or slave_origin_uid)):
            raise ValueError(
                'master_msg_id and slave_msg_id is mutual exclusive')
        if not master_msg_id and not (slave_msg_id and slave_origin_uid):
            raise ValueError(
                'slave_msg_id and slave_origin_uid must exists together.')
        try:
            if master_msg_id:
                return self.MsgLog.select().where(self.MsgLog.master_msg_id == master_msg_id) \
                    .order_by(self.MsgLog.time.desc()).first()
            else:
                return self.MsgLog.select().where(
                    self.MsgLog.slave_message_id == slave_msg_id and
                    self.MsgLog.slave_origin_uid == slave_origin_uid).order_by(
                        self.MsgLog.time.desc()).first()
        except DoesNotExist:
            return None

    def delete_msg_log(self,
                       master_msg_id: Optional[str] = None,
                       slave_msg_id: Optional[str] = None,
                       slave_origin_uid: Optional[str] = None):
        """Remove a message log by message ID.

        Args:
            master_msg_id: Telegram message ID in string
            slave_msg_id: Slave message identifier in string
            slave_origin_uid: Slave chat identifier in string
        """
        if (master_msg_id and (slave_msg_id or slave_origin_uid)) \
                or not (master_msg_id or (slave_msg_id or slave_origin_uid)):
            raise ValueError(
                'master_msg_id and slave_msg_id is mutual exclusive')
        if not master_msg_id and not (slave_msg_id and slave_origin_uid):
            raise ValueError(
                'slave_msg_id and slave_origin_uid must exists together.')
        try:
            if master_msg_id:
                self.MsgLog.delete().where(
                    self.MsgLog.master_msg_id == master_msg_id).execute()
            else:
                self.MsgLog.delete().where(
                    self.MsgLog.slave_message_id == slave_msg_id
                    and self.MsgLog.slave_origin_uid
                    == slave_origin_uid).execute()
        except DoesNotExist:
            return

    def get_slave_chat_info(self,
                            slave_channel_id=None,
                            slave_chat_uid=None) -> Optional['SlaveChatInfo']:
        """
        Get cached slave chat info from database.

        Returns:
            SlaveChatInfo|None: The matching slave chat info, None if not exist.
        """
        if slave_channel_id is None or slave_chat_uid is None:
            raise ValueError(
                "Both slave_channel_id and slave_chat_id should be provided.")
        try:
            return self.SlaveChatInfo.select().where(
                self.SlaveChatInfo.slave_channel_id == slave_channel_id,
                self.SlaveChatInfo.slave_chat_uid == slave_chat_uid).first()
        except DoesNotExist:
            return None

    def set_slave_chat_info(self,
                            slave_channel_id=None,
                            slave_channel_name=None,
                            slave_channel_emoji=None,
                            slave_chat_uid=None,
                            slave_chat_name=None,
                            slave_chat_alias="",
                            slave_chat_type=None):
        """
        Insert or update slave chat info entry

        Args:
            slave_channel_id (str): Slave channel ID
            slave_channel_name (str): Slave channel name
            slave_channel_emoji (str): Slave channel emoji
            slave_chat_uid (str): Slave chat UID
            slave_chat_name (str): Slave chat name
            slave_chat_alias (str): Slave chat alias, "" (empty string) if not available
            slave_chat_type (channel.ChatType): Slave chat type

        Returns:
            SlaveChatInfo: The inserted or updated row
        """
        if self.get_slave_chat_info(slave_channel_id=slave_channel_id,
                                    slave_chat_uid=slave_chat_uid):
            chat_info = self.SlaveChatInfo.get(
                self.SlaveChatInfo.slave_channel_id == slave_channel_id,
                self.SlaveChatInfo.slave_chat_uid == slave_chat_uid)
            chat_info.slave_channel_name = slave_channel_name
            chat_info.slave_channel_emoji = slave_channel_emoji
            chat_info.slave_chat_name = slave_chat_name
            chat_info.slave_chat_alias = slave_chat_alias
            chat_info.slave_chat_type = slave_chat_type.value
            chat_info.save()
            return chat_info
        else:
            return self.SlaveChatInfo.create(
                slave_channel_id=slave_channel_id,
                slave_channel_name=slave_channel_name,
                slave_channel_emoji=slave_channel_emoji,
                slave_chat_uid=slave_chat_uid,
                slave_chat_name=slave_chat_name,
                slave_chat_alias=slave_chat_alias,
                slave_chat_type=slave_chat_type.value)

    def delete_slave_chat_info(self, slave_channel_id, slave_chat_uid):
        return self.SlaveChatInfo.delete().where(
            self.SlaveChatInfo.slave_channel_id == slave_channel_id
            and self.SlaveChatInfo.slave_chat_uid == slave_chat_uid).execute()

    def get_recent_slave_chats(self, master_chat_id, limit=5):
        return [
            i.slave_origin_uid for i in
            self.MsgLog.select(self.MsgLog.slave_origin_uid).distinct().where(
                self.MsgLog.master_msg_id.startswith("%s." % master_chat_id)
            ).order_by(self.MsgLog.time.desc()).limit(limit)
        ]
Esempio n. 15
0
from peewee import SqliteDatabase

db = SqliteDatabase('level1.db')

db.execute_sql('''
    DROP TABLE IF EXISTS user
''')
db.execute_sql('''
    DROP TABLE IF EXISTS sessions
''')
db.execute_sql('''
    DROP TABLE IF EXISTS blogs
''')
db.execute_sql('''
    DROP TABLE IF EXISTS comments
''')

db.execute_sql('''
    CREATE TABLE user (username TEXT PRIMARY KEY, phash TEXT NOT NULL)
''')

db.execute_sql('''
    CREATE TABLE sessions (username TEXT PRIMARY KEY, sessionKey TEXT NOT NULL, FOREIGN KEY(username) REFERENCES user(username))
''')

db.execute_sql('''
    CREATE TABLE blogs (id INTEGER PRIMARY KEY, author TEXT NOT NULL, title TEXT NOT NULL, privacy INTERGER NOT NULL, content TEXT, FOREIGN KEY(author) REFERENCES user(username))
''')

db.execute_sql('''
    CREATE TABLE comments (id INTEGER PRIMARY KEY, author TEXT NOT NULL, post INTEGER NOT NULL,
Esempio n. 16
0
"""
    Simple database example with Peewee ORM, sqlite and Python
    Here we define the schema
    Use logging for messages so they can be turned off
"""
import logging
from peewee import Model, CharField, DecimalField, BooleanField, SqliteDatabase, DoesNotExist

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


database = SqliteDatabase('customers.db')
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')


class BaseModel(Model):
    class Meta:
        database = database


class Customers(BaseModel):
    """
        This class defines Person, which maintains details of someone
        for whom we want to research career to date.
    """
    logger.info('Must be a unique identifier for each person')
    customer_id = CharField(primary_key=True, max_length=30)
    first_name = CharField(max_length=30)
    last_name = CharField(max_length=40)
# Stella Kim
# Assignment 4: Iterables, Iterators & Generators
"""Customer schema using Peewee ORM, SQLite and Python"""

import logging
from peewee import (Model, SqliteDatabase, IntegerField, CharField,
                    BooleanField, DecimalField)

logging.basicConfig(level=logging.INFO)
LOGGER = logging.getLogger(__name__)

CUSTOMER_DB = SqliteDatabase('customers.db')
CUSTOMER_DB.connect()
CUSTOMER_DB.execute_sql('PRAGMA foreign_keys = ON;')


class BaseModel(Model):
    """Peewee base model"""
    class Meta:
        """Peewee meta class"""
        database = CUSTOMER_DB


class Customer(BaseModel):
    """Class to define customer information for HP Norton"""
    customer_id = IntegerField(primary_key=True)
    first_name = CharField(max_length=50)
    last_name = CharField(max_length=50)
    home_address = CharField(max_length=100)
    phone = CharField(max_length=10)
    email = CharField(max_length=50)
 def setUp(self):
     """Set up database"""
     testdb = SqliteDatabase('test.db')
     testdb.connect()
     testdb.execute_sql('PRAGMA foreign_keys = ON;')
     cm.DB = testdb
    3. As a manager at HP Norton I need to be able to produce monthly counts of
       the total number of active customers so that I can assess if the business
       is growing or shrinking.
"""

# pylint: disable=too-few-public-methods

import logging
from peewee import SqliteDatabase, Model, AutoField, CharField, DecimalField, BooleanField, DoubleField

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

db = SqliteDatabase('customers.db')
db.connect()
db.execute_sql('PRAGMA foreign_keys = ON;')


class BaseModel(Model):
    """ Base model for Customer """
    class Meta:
        """ Meta class for the model """
        database = db


class Customer(BaseModel):
    """
        This class defines Customer, which maintains
        the details of the customer's contact information.
    """
    customer_id = AutoField()  # Auto-incrementing primary key.
Esempio n. 20
0
"""
Store customer data from HP Norton in a relational database (sqlite3).
"""
import logging
from peewee import SqliteDatabase, Model, IntegerField, CharField, BooleanField, DecimalField

database = SqliteDatabase('customer.db')
database.connect()
database.execute_sql('Pragma foreign_keys = ON;')


class BaseModel(Model):
    """ Set up database """
    class Meta:
        """ Establish database """
        database = database


class Customer(BaseModel):
    """
        This class defines a customer, which maintains the details of
        a customer's information.
    """

    customer_id = IntegerField(primary_key=True)
    first_name = CharField()
    last_name = CharField()
    home_address = CharField()
    phone_number = IntegerField()
    email_address = CharField()
    status = BooleanField()
from peewee import CharField
from peewee import DateField
from peewee import DecimalField
from peewee import ForeignKeyField

# pylint: disable=too-few-public-methods

logging.basicConfig(level=logging.INFO)
LOGGER = logging.getLogger(__name__)

LOGGER.info('Naming and connecting to database...')

DATABASE = SqliteDatabase('personjob.db')
DATABASE.connect()
DATABASE.execute_sql('PRAGMA foreign_keys = ON;')

LOGGER.info('Defining data schema...')


class BaseModel(Model):
    """ The Base Model """
    class Meta:
        """ The Meta class """
        database = DATABASE


class Person(BaseModel):
    """
    This class defines Person, which maintains details of someone
    for whom we want to research career to date.
Esempio n. 22
0
from peewee import SqliteDatabase

db = SqliteDatabase('level2.db')

db.execute_sql('''
    DROP TABLE IF EXISTS user
''')
db.execute_sql('''
    DROP TABLE IF EXISTS sessions
''')
db.execute_sql('''
    DROP TABLE IF EXISTS flights
''')

db.execute_sql('''
    CREATE TABLE user (username TEXT PRIMARY KEY, phash TEXT NOT NULL)
''')

db.execute_sql('''
    CREATE TABLE sessions (username TEXT PRIMARY KEY, sessionKey TEXT NOT NULL, FOREIGN KEY(username) REFERENCES user(username))
''')

db.execute_sql('''
    CREATE TABLE flights (code TEXT PRIMARY KEY, airline TEXT NOT NULL, departsFrom TEXT NOT NULL, goingTo TEXT NOT NULL, departTime DATE NOT NULL, arriveTime DATE NOT NULL)
''')
Esempio n. 23
0
db = SqliteDatabase('level1.db')

# flags:
# flag0 private blog from some user - path inj
# flag1 editing another's blog - non ath
# flag2 logging on as another user -sqli
# flag3 obtaining pword hash of another user -sqli
# flag4 stored xss - stored xss

flags = ['HDOQTY', 'PDJVLW', 'MDOPKG', 'QSPMEP', 'QNZKPQ']
other_users = ['richard', 'admin', 'Joe']

if __name__ == "__main__":
    db.execute_sql(
        '''
        INSERT INTO blogs (author, title, privacy, content) VALUES ('richard', 'Super Secret Stuff', 1, ?)
    ''', ("FLAG_0_" + flags[0], ))

    db.execute_sql('''
        INSERT INTO blogs (author, title, privacy, content) VALUES ('Joe', 'No swearing', 0, '<h1>I will personally report anyone who swears in a blog!</h1>')
    ''')
    db.execute_sql('''
        INSERT INTO blogs (author, title, privacy, content) VALUES ('Joe', 'Don''t Read This', 0, 'Oh no!!')
    ''')
    db.execute_sql('''
        INSERT INTO blogs (author, title, privacy, content) VALUES ('admin', 'Helloe', 0, 'Welcome to Zoom Blogs! We are super secure, scripts will not run in blog posts so no XSS!')
    ''')

    db.execute_sql(
        '''
        INSERT INTO user (username, phash) VALUES ('admin', ?)
"""
import logging
from peewee import Model, CharField, BooleanField, DecimalField, SqliteDatabase

logging.basicConfig(level=logging.INFO)
LOGGER = logging.getLogger(__name__)

LOGGER.info('Here we define our data (the schema)')
LOGGER.info('First name and connect to a database (sqlite here)')

LOGGER.info('The next 3 lines of code are the only database specific code')

#To create db in the same folder as the basic_operation.py file.
DATABASE = SqliteDatabase('./codes/customer.db')
DATABASE.connect()
DATABASE.execute_sql('PRAGMA foreign_keys = ON;')  # needed for sqlite only

LOGGER.info('Enable the Peewee magic! This base class does it all')


class BaseModel(Model):
    """This is to setup Pewee and database"""
    class Meta:
        """this is to set up meta"""
        database = DATABASE


class Customer(BaseModel):
    """
        This class defines Customer, which maintains details of customer
        for whom we want to search detail customer information.
"""
customer_model.py
Assignment 4
Joli Umetsu
PY220
"""
from peewee import SqliteDatabase, Model, IntegerField, CharField, BooleanField, DecimalField

DB = SqliteDatabase('customers.db')
DB.connect()
DB.execute_sql('PRAGMA foreign_keys = ON')


class BaseModel(Model):
    """ Establishes/defines database """
    class Meta:
        """ Defines model-specific configuration """
        database = DB


class Customer(BaseModel):
    """ Defines customer database table """

    customer_id = IntegerField(primary_key=True)
    name = CharField(max_length=20, null=False)
    lastname = CharField(max_length=20, null=False)
    home_address = CharField(max_length=40)
    phone_number = IntegerField()
    email_address = CharField(max_length=40, null=False)
    status = BooleanField()
    credit_limit = DecimalField()
Esempio n. 26
0
db = SqliteDatabase('level2.db')

# flags:
# flag0 XSS on search
# flag1 SSTI on search
# flag2 adminPassword
# flag3 yaml vulnerability

flags = ['SLKDGJ', 'PXTWAS', 'carlos420', 'YGTHSW']
other_users = ['davo', 'admin0', 'denice']

if __name__ == "__main__":

    db.execute_sql(
        '''
        INSERT INTO user (username, phash) VALUES ('admin0', ?)
    ''', (md5(flags[2].encode('utf-8')).hexdigest(), ))

    db.execute_sql('''
        INSERT INTO user (username, phash) VALUES ('davo', 'flag2 is FLAG_2_ + admin0 password')
    ''')

    db.execute_sql('''
        INSERT INTO user (username, phash) VALUES ('denice', 'this is md5, super secure! no salt required')
    ''')

    flightsList = [[
        "QA587", "Qantas", "PER", "SYD", "14-04-2020 02:41", "14-04-2020 06:53"
    ],
                   [
                       "VA846", "Virgin Australia", "ADL", "MEL",
Esempio n. 27
0
    Here we define the schema
    Use logging for messages so they can be turned off

"""
from peewee import Model, CharField, BooleanField, DecimalField, SqliteDatabase
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

logger.info('Here we define our data (the schema)')
logger.info('The next 3 lines of code are the only database specific code')

database = SqliteDatabase('customer.db')
database.connect()
database.execute_sql('PRAGMA foreign_keys = ON;')  # needed for sqlite only

logger.info('This means we can easily switch to a different database')
logger.info('Enable the Peewee magic! This base class does it all')


class BaseModel(Model):
    class Meta:
        database = database


logger.info(
    'By inheritance only we keep our model (almost) technology neutral')


class Customer(BaseModel):
Esempio n. 28
0
"""
The schema of the Customers DB (customer.db)
"""

import datetime

from peewee import SqliteDatabase
from peewee import Model

from peewee import CharField
from peewee import DecimalField
from peewee import DateTimeField

DATABASE = SqliteDatabase("customers.db")
DATABASE.connect()
DATABASE.execute_sql("PRAGMA foreign_keys = ON;")


class BaseModel(Model):
    """ The base model for the customer database """
    class Meta:
        """ The meta class """

        database = DATABASE


class Customers(BaseModel):
    """
    The model representing the customers table.
    """