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