class Database:
"""Create database from script and handling data input"""
def __init__(self, db_host, db_user, db_passwd, db_name):
"""Initialize database"""
self.db = SimpleMysql(
host=db_host,
db=db_name,
user=db_user,
passwd=db_passwd,
keep_alive=True
)
self.db.query('SET NAMES utf8;')
self.db.query('SET CHARACTER SET utf8;')
self.db.query('SET character_set_connection=utf8;')
try:
self.db.getOne('user_table', ['username'])
print 'Database working'
except Exception:
print 'Create new database'
sql_file = open(
os.path.join(
os.path.dirname(
os.path.abspath(__file__)
),
'sqlscript.sql'
), 'r'
)
sql_query = ''
for line in sql_file:
if line == 'go\n':
self.db.query(sql_query)
sql_query = ''
else:
sql_query = sql_query + line
sql_file.close()
self.register_user('admin', 'admin', 'admin', 'secret')
self.commit()
def commit(self):
"""Commit"""
self.db.conn.commit()
def username_exist(self, username):
"""Validate username"""
return True if self.db.getOne(
'user_table',
['username'],
('username=%s', [username])
) else False
def register_user(self, first_name, last_name, username, raw_password):
"""Register player to the database with username
and encrypted password"""
if self.username_exist(username):
return False
algo = 'sha1'
salt = encryption.get_hexdigest(algo, str(random.random()),
str(random.random()))[:5]
hsh = encryption.get_hexdigest(algo, salt, raw_password)
password = '******'.format(algo, salt, hsh)
self.db.insert('user_table', {
'first_name': first_name,
'last_name': last_name,
'username': username,
'password': password,
'role': 'normal'
})
return True
def login(self, username, password):
"""Check user login data, return true if the
data is matched with database"""
user_password = self.db.getOne(
'user_table',
['password'],
('username=%s', [username])
)
if user_password:
algo, salt, hsh = user_password[0].split('$')
return True if hsh == encryption.get_hexdigest(algo,
salt, password) else False
else:
return False
def get_last_insert_id(self, table):
"""Return last insert id"""
return self.db.query(
'select max(id) from %s' % (table)
).fetchone()[0]
def get_no_of_elements(self, table):
"""Return the number of record in specific table"""
return self.db.query(
'select count(id) from %s' % (table)
).fetchone()[0]
def get_no_of_elements_filter(self, table, condition):
"""Return the number of record in specific table"""
return self.db.query(
'select count(id) from %s where %s' % (table, condition)
).fetchone()[0]
def get_database(self):
return self.db
def input_drug(self):
sql_file = open(
os.path.join(
os.path.dirname(
os.path.abspath(__file__)
),
'drug.txt'
), 'r'
)
for line in sql_file:
li = re.compile("--").split(line)
self.db.insert('drug_table',
{
'name': li[0],
'unit': li[1]
}
)
sql_file.close()
self.commit()
