class OctoPrintSchema:
"""
Provide methods for OctoPrint profile management
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
self.create_octoprint_table()
def __del__(self):
self.conn.close()
def create_octoprint_table(self):
"""
Init octoprint table
:return: void
"""
query = """
CREATE TABLE IF NOT EXISTS octoprint (
ip CHAR(22) NOT NULL UNIQUE,
host VARCHAR(255) NOT NULL,
x_api_key CHAR(32) NOT NULL UNIQUE PRIMARY KEY
);
"""
self.curs.execute(query)
self.conn.commit()
class OctoPrintModel:
"""
Provide methods for OctoPrint profile management into the DB
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
def create(self, ip, host, x_api_key):
"""
Create new OctoPrint profile
:param ip: IP of the device with OctoPrint
:param host: The domain on which to make requests
:param x_api_key: X_API_KEY of OctoPrint
:return: True | False
"""
query = """
INSERT INTO octoprint(ip, host, x_api_key)
VALUES (%s, %s, %s)
"""
try:
self.curs.execute(query, (ip, host, x_api_key))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("octoprint_model -> create")
return False
def delete(self, x_api_key):
"""
Delete an OctoPrint profile by x_api_key
:param x_api_key: X_API_KEY of OctoPrint
:return: True | False
"""
query = """
DELETE FROM octoprint
WHERE x_api_key=%s
"""
try:
self.curs.execute(query, (x_api_key, ))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("octoprint_model -> delete")
return False
def user(self, firstname=None, lastname=None,
address=None, email=None, phone=None,
password=None, password2=None):
"""Returns a page to edit a user's own information.
If new information was passed on to it, it verifies it and
edits accordingly.
"""
template = self.lookup.get_template('user.mako')
(u, c) = getUserInfo()
conn = Database()
session = conn.get()
user = session.query(User).filter(User.user_name == u).one()
fail = False
# make sure password was entered correctly
if password or password2:
if password == password2:
user.password = password
else:
fail = True
if firstname and not fail:
user.person.first_name = firstname
if lastname and not fail:
user.person.last_name = lastname
if address and not fail:
user.person.address = address
if email and not fail:
user.person.email = email
if phone and not fail:
user.person.phone = phone
if firstname or lastname or address or email or phone or password:
if not fail:
conn.commit()
user = session.query(User).filter(User.user_name == u).one()
oldinfo = []
oldinfo.append(user.person.first_name)
oldinfo.append(user.person.last_name)
oldinfo.append(user.person.address)
oldinfo.append(user.person.email)
oldinfo.append(user.person.phone)
if firstname or lastname or address or email or phone or password:
if fail:
conn.close()
return template.render(
username=u, classtype=c, oldinfo=oldinfo, action="nomatch")
else:
conn.close()
return template.render(
username=u, classtype=c, oldinfo=oldinfo, action="success")
else:
conn.close()
return template.render(username=u, classtype=c, oldinfo=oldinfo)
def updateIdEndereco(self, endereco, idEndereco):
try:
conn = Database().conexao()
cur = conn.cursor()
sql = "UPDATE contato\
SET idEndereco = ?\
WHERE id = ?;"
cur.execute(sql, (idEndereco, endereco.getIdContato()))
#aux = cur.lastrowid
conn.commit()
except expression as identifier:
pass
finally:
pass
def sendData():
r = requests.get("https://www.amfiindia.com/spages/NAVAll.txt")
db = Database("test")
data = r.text.split('\n')
amc = ""
for row in data:
row = row.replace('\r', '')
if row in amclist:
amc = row
elif amc:
if row != "":
# db.insert_data(row+';'+amc)
db.update_data(row + ';' + amc)
db.commit()
class DeviceSchema:
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
self.create_device_table()
self.create_power_strip_table()
def __del__(self):
self.conn.close()
def create_device_table(self):
query = """
CREATE TABLE IF NOT EXISTS device (
id CHAR(40) NOT NULL UNIQUE PRIMARY KEY,
ip CHAR(22) NOT NULL,
type VARCHAR(255) NOT NULL,
path VARCHAR(10)
)
"""
self.curs.execute(query)
self.conn.commit()
def create_power_strip_table(self):
"""
Init power_strip table
:return: void
"""
query = """
CREATE TABLE IF NOT EXISTS power_strip (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
device_id CHAR(40),
name VARCHAR(255) NOT NULL,
switch_number INT NOT NULL,
switch_name VARCHAR(255) NOT NULL,
FOREIGN KEY (device_id)
REFERENCES device(id)
ON DELETE CASCADE
);
"""
self.curs.execute(query)
self.conn.commit()
def insere(self, endereco):
try:
conn = Database().conexao()
cur = conn.cursor()
sql = 'INSERT INTO endereco (idContato, cep, logradouro, bairro, numero, complemento, uf)\
values(?,?,?,?,?,?,?)'
cur.execute(sql, (endereco.idContato, endereco.cep,
endereco.logradouro, endereco.bairro, endereco.numero, endereco.complemento, endereco.uf ))
aux = cur.lastrowid
conn.commit()
except Exception as e:
print("Erro ao inserir CONTATO - "+e.__str__())
finally:
cur.close()
conn.close()
return aux
def insere(self, contato):
try:
conn = Database().conexao()
cur = conn.cursor()
sql = "INSERT INTO contato (nome, celular, telefone, email, idEndereco)\
values(?, ?, ?, ?, ?)"
cur.execute(sql, (contato.getNome(), contato.getCelular(),
contato.getTelefone(), contato.getEmail(), 0))
aux = cur.lastrowid
conn.commit()
except Exception as e:
print("Erro ao inserir CONTATO - "+e.__str__())
finally:
cur.close()
conn.close()
return aux
class UserSchema:
"""
Provide methods for user data management
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
self.create_user_table()
self.create_otp_table()
def __del__(self):
"""
Body of destructor
:return: void
"""
self.conn.close()
def create_user_table(self):
"""
Init user table
:return: void
"""
query = """
CREATE TABLE IF NOT EXISTS user (
name VARCHAR(50) NOT NULL,
surname VARCHAR(50) NOT NULL,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
api_key CHAR(64) NOT NULL UNIQUE PRIMARY KEY,
admin TINYINT NOT NULL DEFAULT 0,
created_on DATE DEFAULT (CURRENT_DATE),
otp_requests INT DEFAULT 0,
last_otp_timestamp TIMESTAMP NULL
);
"""
self.curs.execute(query)
self.conn.commit()
def create_otp_table(self):
"""
Init otp table
:return: void
"""
query = """
CREATE TABLE IF NOT EXISTS otp (
api_key CHAR(64) NOT NULL UNIQUE PRIMARY KEY,
otp_code INT(6) NOT NULL,
otp_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
FOREIGN KEY (api_key)
REFERENCES user(api_key)
ON DELETE CASCADE
);
"""
self.curs.execute(query)
self.conn.commit()
class UserModel:
"""
Provide methods for user management into the DB
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
def create(self, name: str, surname: str, username: str, email: str,
api_key: str):
"""
Create new user.
:param name:
:param surname:
:param username:
:param email:
:param api_key:
:return: api_key | False
"""
query = """
INSERT INTO user(name, surname, username, email, api_key)
VALUES (%s, %s, %s, %s, %s)
"""
try:
self.curs.execute(query, (name, surname, username, email, api_key))
self.conn.commit()
return api_key
except mariadb.Error:
logger.exception("user_model -> create")
return False
# TODO
def delete(self):
pass
def update_otp(self, otp_requests, otp_timestamp, api_key):
query = """
UPDATE user
SET otp_requests=%s, last_otp_timestamp=%s
WHERE api_key=%s
"""
try:
self.curs.execute(query, (otp_requests, otp_timestamp, api_key))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("user_model -> update_otp")
return False
def reset_otp_requests(self, api_key):
query = """
UPDATE user
SET otp_requests=0
WHERE api_key=%s
"""
try:
self.curs.execute(query, (api_key, ))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("user_model -> reset_otp_requests")
return False
def get_otp_requests(self, api_key):
query = """
SELECT otp_requests
FROM user
WHERE api_key=%s
"""
try:
self.curs.execute(query, (api_key, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mariadb.Error:
logger.exception("user_model -> get_otp_requests")
return False
def check_user(self, email: str, api_key: str):
"""
Find user by:
:param email:
:param api_key:
:returns tuple(email, api_key) | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM user
WHERE email=%s AND api_key=%s
)
"""
try:
self.curs.execute(query, (email, api_key))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mariadb.Error:
logger.exception("user_model -> check_user")
return False
def check_username(self, username: str):
"""
Check if username already exists
:param username:
:return: True | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM user
WHERE username=%s
)
"""
try:
self.curs.execute(query, (username, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mariadb.Error:
logger.exception("user_model -> check_username")
return False
def check_api_key(self, api_key: str):
"""
Find user by:
:param api_key:
:return True | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM user
WHERE api_key=%s
)
"""
try:
self.curs.execute(query, (api_key, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mariadb.Error:
logger.exception("user_model -> check_api_key")
return False
def get_user_with_otp(self, email, api_key, otp):
"""
Find user by:
:param email:
:param api_key:
:param otp:
:return: True | False
"""
# NOTE: "SELECT EXISTS" -> Not work
query = """
SELECT user.email, user.api_key, otp.otp_code
FROM user INNER JOIN otp ON otp.api_key = user.api_key
WHERE user.email=%s AND user.api_key=%s AND otp.otp_code=%s
AND otp.otp_timestamp >= NOW() - INTERVAL 5 MINUTE
"""
try:
self.curs.execute(query, (email, api_key, otp))
res = self.curs.fetchone()
self.conn.commit()
if res is not None:
return True
return False
except mariadb.Error:
logger.exception("user_model -> get_user_with_otp")
return False
def check_user_otp_timestamp(self, api_key):
"""
Check if the last OTP was sent at least 10 minutes ago
:param api_key:
:return: True | False
"""
query = """
SELECT EXISTS (
SELECT name
FROM user
WHERE api_key=%s AND last_otp_timestamp <= NOW() - INTERVAL 10 MINUTE
)
"""
try:
self.curs.execute(query, (api_key, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mariadb.Error:
logger.exception("user_model -> check_user_otp_timestamp")
return False
def create_otp(self, api_key: str, otp_code: int):
"""
Store new otp code.
:param api_key:
:param otp_code:
:return: True | False
"""
query = """
REPLACE INTO otp(api_key, otp_code)
VALUES (%s, %s)
"""
try:
self.curs.execute(query, (api_key, otp_code))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("user_model -> create_otp")
return False
def delete_otp(self, api_key: str, otp_code: int):
"""
Store new otp code.
:param api_key:
:param otp_code:
:return: True | False
"""
query = """
DELETE FROM otp
WHERE api_key=%s AND otp_code=%s
"""
try:
self.curs.execute(query, (api_key, otp_code))
self.conn.commit()
return True
except mariadb.Error:
logger.exception("user_model -> delete_otp")
return False
def get_otp_timestamp(self, api_key):
query = """
SELECT otp_timestamp
FROM otp
WHERE api_key=%s
"""
try:
self.curs.execute(query, (api_key, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mariadb.Error:
logger.exception("user_model -> get_otp_info")
return False
default=False,
help='Verbose output')
parser.add_argument('-i',
'--id',
type=str,
required=True,
help='Stock or index id')
parser.add_argument('file',
type=str,
default=None,
help='CSV file to import')
args = parser.parse_args()
database = Database(args.database)
if args.database == ':memory:':
database.create()
data = pd.read_csv(args.file, header=0)
for i in range(len(data)):
row = data.ix[i]
if row['Low'] != 'null' and row['High'] != 'null':
entry = StockEntry(to_date(row['Date']), args.id.lower(),
(float(row['High']) + float(row['Low'])) / 2)
database.add(entry)
database.commit()
class Interface:
def __init__(self, TOKEN, SHARD, sqlName='market', reset=False):
self.info = Info(TOKEN, SHARD)
self.database = Database(sqlName, reset)
def insertMarketInfo(self):
ret = self.info.marketInfo()
for _ in range(len(ret)):
try:
ret[_]['resource'] = ret[_]['_id']
del ret[_]['_id']
except:
pass
self.database.insertMarketInfo(**ret[_])
self.database.commit()
def insertResourceOrder(self, resource):
ret = self.info.resourceOrder(resource)
activeIDList = [elem['_id'] for elem in ret]
self.database.deleteFinishedDeal(resource, activeIDList)
for _ in range(len(ret)):
try:
ret[_]['orderType'] = ret[_]['type']
del ret[_]['type']
except:
pass
self.database.insertDealInfo(resource=resource, **ret[_])
self.database.commit()
def insertAllResourceOrder(self):
resources = self.database.getAllResourceType()
for resource in resources:
self.insertResourceOrder(resource)
def insertCreditRecord(self, command=''):
"""
Accepted Params:: 'all','recent','' and number or string-number.
default: ""
"""
if command == "recent" or command == "":
command = "0"
elif type(command) == int:
command = str(command)
elif not command == "all":
raise ValueError(
"Unexpected Value from (command,{})".format(command))
ret = self.info.getRecentBalance(command)
if type(ret[1]) == bool:
ret = ret[0]
for _ in range(len(ret)):
if ret[_]["shard"] != self.info.SHARD or ret[_][
"type"] == 'market.fee':
continue
ret[_]["orderType"] = ret[_]["type"]
ret[_]["resource"] = ret[_]["market"]["resourceType"]
ret[_]["room"] = ret[_]["market"]["roomName"]
ret[_]["targetRoom"] = ret[_]["market"]["targetRoomName"]
ret[_]["npc"] = ret[_]["market"]["npc"]
ret[_]["amount"] = ret[_]["market"]["amount"]
del ret[_]['user']
del ret[_]["type"]
del ret[_]["market"]
del ret[_]["shard"]
print(ret[_])
self.database.insertRecordInfo(**ret[_])
self.database.commit()
def clearInactiveOrder(self):
ret = self.info.myOrder()
for _ in range(len(ret)):
if ret[_]['active'] == False:
self.info.cancelOrder(ret[_]['_id'])
sys.path.append('.')
from database.database import Database
# init database
cursor = Database.cursor()
cursor.execute('''create table if not exists pictures (
id integer primary key auto_increment,
url text not null,
status char(10) not null,
collection_id integer not null,
filename text,
foreign key (collection_id) references collections(id)
)'''
)
Database.commit()
def get_html(url_target, socket_timeout):
data = urllib2.urlopen(url = url_target, timeout = socket_timeout)
return data.read()
def get_pictures(html):
pictures_content_reg = re.compile('<div class="postContent">.+?</div>', re.S)
picture_content = re.findall(pictures_content_reg, html)
if (len(picture_content) != 1):
raise Exception('No post content found')
pictures_reg = re.compile('src="(.+?)"')
pictures = re.findall(pictures_reg, picture_content[0])
return pictures
cursor.execute('select * from collections where status = "%s" and site = "%s"' % ('new', 'meizitu.com'))
class DeviceModel:
"""
Provide methods for device data management into the DB
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
def create(self, id, ip, type, path):
"""
Create new device
:param id:
:param ip:
:param type:
:return: True | False
"""
query = """
INSERT INTO device (id, ip, type, path)
VALUES (%s, %s, %s, %s)
"""
try:
self.curs.execute(query, (id, ip, type, path))
self.conn.commit()
return True
except mysql.Error:
logger.exception("device_model -> create_device")
return False
def delete(self, id):
"""
Delete device by:
:param id:
:return: True | False
"""
query = """
DELETE FROM device
WHERE id=%s
"""
try:
self.curs.execute(query, (id, ))
self.conn.commit()
return True
except mysql.Error:
logger.exception("device_model -> delete_device")
return False
def check_id(self, id):
"""
Check if id exists into the DB
:param id:
:return: True | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM device
WHERE id=%s
)
"""
try:
self.curs.execute(query, (id, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mysql.Error:
logger.exception("device_model -> check_id")
return False
def get_ip(self, id):
query = """
SELECT ip
FROM device
WHERE id=%s
"""
try:
self.curs.execute(query, (id, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mysql.Error:
logger.exception("device_model -> get_ip")
return False
def get_path(self, id):
query = """
SELECT path
FROM device
WHERE id=%s
"""
try:
self.curs.execute(query, (id, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mysql.Error:
logger.exception("device_model -> get_path")
return False
class PowerStripModel:
"""
Provide methods for power_strip data management into the DB
"""
def __init__(self):
self.conn = Database().get_conn()
self.curs = self.conn.cursor()
def create(self, device_id, name, switch_number, switch_name):
"""
Create new power strip
:param device_id:
:param name: The name of the power strip
:param switch_number:
:param switch_name:
:return: True | False
"""
query = """
INSERT INTO power_strip(device_id, name, switch_number, switch_name)
VALUES (%s, %s, %s, %s)
"""
try:
self.curs.execute(query,
(device_id, name, switch_number, switch_name))
self.conn.commit()
return True
except mysql.Error:
logger.exception("power_strip_model -> create")
return False
def delete(self, device_id):
"""
Delete power strip by device_id
:param device_id:
:return: True | False
"""
query = """
DELETE FROM power_strip
WHERE device_id=%s
"""
try:
self.curs.execute(query, (device_id, ))
self.conn.commit()
return True
except mysql.Error:
logger.exception("power_strip_model -> delete")
return False
def update_name(self, id, new_name):
"""
Update name of a specific power strip
:param id: ID of device
:param new_name:
:return: True | False
"""
query = """
UPDATE power_strip
SET name=%s
WHERE device_id=%s
"""
try:
self.curs.execute(query, (new_name, id))
self.conn.commit()
return True
except mysql.Error:
logger.exception("power_strip_model -> update_name")
return False
def update_switch_name(self, id, switch_number, new_name):
"""
Update name of a specific switch of specific power strip
:param id: ID of device
:param switch_number:
:param new_name:
:return: True | False
"""
query = """
UPDATE power_strip
SET switch_name=%s
WHERE device_id=%s AND switch_number=%s
"""
try:
self.curs.execute(query, (new_name, id, switch_number))
self.conn.commit()
return True
except mysql.Error:
logger.exception("power_strip_model -> update_switch_name")
return False
def check_name(self, name):
"""
Check if name exists into the DB
:param name:
:return: True | False
"""
query = """
SELECT EXISTS (
SELECT *
FROM power_strip
WHERE name=%s
)
"""
try:
self.curs.execute(query, (name, ))
res = self.curs.fetchone()
self.conn.commit()
return bool(res[0])
except mysql.Error:
logger.exception("power_strip_model -> check_name")
return False
def get_switch_number(self, id):
"""
Count how many switch have a specific power strip
:param id: ID of device
:return: Number of switchs | False
"""
query = """
SELECT COUNT(*)
FROM power_strip
WHERE device_id=%s
"""
try:
self.curs.execute(query, (id, ))
res = self.curs.fetchone()
self.conn.commit()
return res[0]
except mysql.Error:
logger.exception("power_strip_model -> get_switch_number")
return False
