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)
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()
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
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
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
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()
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()
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()
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
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
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()
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)
]
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,
"""
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.
"""
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.
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)
''')
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()
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",
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):
"""
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.
"""