def main():
"""
Velh-IA's Main function.
Load environment variables, check requirements and connections before to start the game
"""
try:
root_dir = os.path.dirname(
os.path.abspath(__file__)).replace('\\', '/')
try:
file_name = f'{datetime.now()}.log'
logging.basicConfig(filename=f'{root_dir}/logs/{file_name}', filemode='w',
format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
level=logging.DEBUG)
logging.info(f'log file {file_name} was created')
except:
if not os.path.exists(f'{root_dir}/logs/app.log'):
open(f'{root_dir}/logs/app.log', 'x')
logging.basicConfig(filename=f'{root_dir}/logs/app.log', filemode='w',
format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
level=logging.DEBUG)
logging.info(f'log file app.log was created')
logging.info('Check all requirements to starting Velh-IA Game')
response = request('GET', os.getenv('API_ADDRESS'))
logging.info("Connected with Velhia's API")
match_db = Database(os.getenv('API_ADDRESS'), 'v1', 'matchs')
family_db = Database(os.getenv('API_ADDRESS'), 'v1', 'families')
education_db = Database(os.getenv('API_ADDRESS'), 'v1', 'educations')
religion_db = Database(os.getenv('API_ADDRESS'), 'v1', 'religions')
algorithm_db = Database(os.getenv('API_ADDRESS'), 'v1', 'algorithms')
logging.info('Databases objects was created!')
global vlh
vlh = Velhia(match_db, family_db, education_db,
religion_db, algorithm_db)
logging.info('Velhia object was created!')
del match_db, algorithm_db, family_db, education_db, religion_db
del response, file_name, root_dir
logging.info('Unnecessary datas was deleted!')
logging.info('Starting Velh-IA Game')
while True:
play()
except exceptions.ConnectionError:
print("Can't connect with Velh-IA API")
logging.exception("Can't connect with Velh-IA API")
except:
print('Error (main.py): ', sys.exc_info())
logging.exception('Exception occurred')
def __init__(self):
self.root = Tk()
self.root.title("Authorization")
self.root.geometry("800x600")
self.root.configure(bg='#%02x%02x%02x' % color.aquamarine2)
log_in_btn = Button(text="Log in",
background="#555",
foreground="#ccc",
padx="20",
pady="8",
font="16",
command=self.log_in)
log_in_btn.place(relx=.605,
rely=.6,
anchor="c",
height=30,
width=130,
bordermode=OUTSIDE)
sign_in_btn = Button(text="Sign in",
background="#555",
foreground="#ccc",
padx="20",
pady="8",
font="16",
command=self.sign_in)
sign_in_btn.place(relx=.395,
rely=.6,
anchor="c",
height=30,
width=130,
bordermode=OUTSIDE)
self.login = StringVar()
self.password = StringVar()
entry_login = Entry(textvariable=self.login)
entry_login.place(relx=.5, rely=.4, anchor="c", height=25, width=300)
entry_password = Entry(textvariable=self.password, show='*')
entry_password.place(relx=.5,
rely=.5,
anchor="c",
height=25,
width=300)
self.root.protocol("WM_DELETE_WINDOW", lambda: exit())
self.db = Database("database", "user_data")
self.smile = Smile()
self.do = True
self.start()
def selectAll(self, ):
"""
Devuelve todos los empleados en la DB
"""
try:
database = Database()
connection = database.getConnection()
query = 'SELECT id, dni, nombre, apellido FROM empleados'
connection['cursor'].execute(query)
empleados = connection['cursor'].fetchall()
connection['db'].commit()
return empleados
finally:
database.closeConnection()
def insert(self, dni, nombre, apellido, clave):
"""
Inserta un empleado de la DB
"""
try:
database = Database()
connection = database.getConnection()
query = """INSERT INTO empleados VALUES (NULL, %s, %s, %s, %s)"""
values = (str(dni), nombre, apellido, clave)
connection['cursor'].execute(query, values)
connection['db'].commit()
return True
except Exception as ex:
print(ex)
finally:
database.closeConnection()
def deleteByDni(self, dni):
"""
Elimina un empleado de la DB seleccionado por DNI
"""
try:
database = Database()
connection = database.getConnection()
query = """DELETE FROM empleados WHERE dni = %s"""
values = (str(dni), )
connection['cursor'].execute(query, values)
connection['db'].commit()
return True
except Exception as ex:
print(str(ex))
finally:
database.closeConnection()
def updateByDni(self, dni, param, value):
"""
Actualiza un empleado de la DB seleccionado por DNI
"""
try:
database = Database()
connection = database.getConnection()
query = """UPDATE empleados SET %s=%s WHERE dni=%s"""
values = (str(dni), )
connection['cursor'].execute(query, values)
connection['db'].commit()
return True
except Exception as ex:
print(str(ex))
finally:
database.closeConnection()
def selectAll(self, ):
"""
Devuelve todos los movimientos en la DB
"""
try:
database = Database()
connection = database.getConnection()
query = 'SELECT * FROM movimientos'
connection['cursor'].execute(query)
movimientos = connection['cursor'].fetchall()
connection['db'].commit()
return movimientos
except Exception as ex:
print(str(ex))
finally:
database.closeConnection()
def selectByDni(self, dni):
"""
Devuelve un empleado de la DB
"""
try:
database = Database()
connection = database.getConnection()
query = """SELECT * FROM empleados WHERE dni = %s"""
values = (str(dni), )
connection['cursor'].execute(query, values)
empleado = connection['cursor'].fetchone()
return empleado
except Exception as ex:
print(ex)
finally:
database.closeConnection()
def log_in(self):
if not self.password.get() and not self.login.get():
messagebox.showinfo("Warning", "Empty data")
elif not self.login.get():
messagebox.showinfo("Warning", "Empty login")
elif not self.password.get():
messagebox.showinfo("Warning", "Empty password")
elif self.db.find_user(self.login.get(),
self.hash_password(self.password.get())):
self.smile.stop()
self.cur_db = Database(self.login.get(), "settings")
self.cur_db.load_settings()
self.do = False
else:
messagebox.showinfo("Warning", "Wrong data")
#Authorization()
def insert(self, id_empleado, tipo, fecha, hora):
"""
Agrega un movimento en la DB
Recibe id_empleado, tipo, fecha, hora
Devuelve True si fue realizado con exito
"""
try:
database = Database()
connection = database.getConnection()
query = 'INSERT INTO movimientos (empleado_id, tipo, fecha, hora) VALUES (%s, %s, %s, %s)'
values = (id_empleado, tipo, fecha, hora)
connection['cursor'].execute(query, values)
connection['db'].commit()
return True
except Exception as ex:
print(str(ex))
finally:
database.closeConnection()
def selectByFechaAndEmpleado(self, fecha, id_empleado):
"""
Devuelve los movimientos de un empleado en una fecha determinada
"""
try:
database = Database()
connection = database.getConnection()
query = """SELECT * FROM movimientos WHERE (fecha = %s) AND (empleado_id = %s)"""
values = (
str(fecha),
str(id_empleado),
)
connection['cursor'].execute(query, values)
movimientos = connection['cursor'].fetchall()
return movimientos
except Exception as ex:
print(str(ex))
finally:
database.closeConnection()
def checked(self, payload):
if not Config.save_checked():
return
sql = """
INSERT INTO repositories(
name
)
VALUES(?)
"""
data = (payload['name'], )
with self.connection as connection:
cur = connection.cursor()
cur.execute(sql, data)
self.repo_id = cur.lastrowid
class TestDatabase:
db = Database('v1', 'algorithms')
obj_id = ''
obj = {}
def test_create(self):
obj = {
"birth":
"Thu Jan 05 2017 22:12:46 GMT-0100 (CET)",
"memory": [{
"isLearner":
True,
"choices": [{
"dateRequest": "Thu Jan 05 2017 22:12:46 GMT-0100 (CET)",
"gameStatus": [1, -1, 0],
"timeToAct": 30,
"action": 2
}],
"environmentReaction":
"DRAW"
}],
"matchs":
1,
"victories":
0,
"defeats":
0,
"draw":
1
}
alg = self.db.create(json.dumps(obj))
self.obj_id = alg.json()['_id']
self.obj = alg.json()
assert alg.status_code == 200
def test_get_one(self):
alg = self.db.get_one(self.obj_id)
assert alg.status_code == 200
def test_get_last(self):
limit = 1
alg = self.db.get_last(limit)
assert alg.text != '[]'
def test_update(self):
alg = self.db.update(object_id=self.obj_id, obj=json.dumps(self.obj))
assert alg.status_code == 200
def init_db(app=None):
from app import create_app
db = Database(app=app or create_app('settings.develop'))
db.connect_db()
from app.models import MODELS
for m in MODELS:
try:
if m.table_exists():
m.drop_table(cascade=True)
except ProgrammingError:
db.close_db(None)
db.connect_db()
m.create_table()
print('Created %s' % m.__name__)
def found(self, payload):
if not Config.save_found():
return
sql = """
INSERT INTO credentials(
repository_id,
match,
name,
credentials
)
VALUES(?, ?, ?, ?)
"""
data = (self.repo_id, payload['match'], payload['name'],
payload['credentials'])
with self.connection as connection:
cur = connection.cursor()
cur.execute(sql, data)
def __init__(self):
if not self.instance:
self.instance = sqlite3.connect(Config.database())
self.initalize()
class Authorization:
def __init__(self):
self.root = Tk()
self.root.title("Authorization")
self.root.geometry("800x600")
self.root.configure(bg='#%02x%02x%02x' % color.aquamarine2)
log_in_btn = Button(text="Log in",
background="#555",
foreground="#ccc",
padx="20",
pady="8",
font="16",
command=self.log_in)
log_in_btn.place(relx=.605,
rely=.6,
anchor="c",
height=30,
width=130,
bordermode=OUTSIDE)
sign_in_btn = Button(text="Sign in",
background="#555",
foreground="#ccc",
padx="20",
pady="8",
font="16",
command=self.sign_in)
sign_in_btn.place(relx=.395,
rely=.6,
anchor="c",
height=30,
width=130,
bordermode=OUTSIDE)
self.login = StringVar()
self.password = StringVar()
entry_login = Entry(textvariable=self.login)
entry_login.place(relx=.5, rely=.4, anchor="c", height=25, width=300)
entry_password = Entry(textvariable=self.password, show='*')
entry_password.place(relx=.5,
rely=.5,
anchor="c",
height=25,
width=300)
self.root.protocol("WM_DELETE_WINDOW", lambda: exit())
self.db = Database("database", "user_data")
self.smile = Smile()
self.do = True
self.start()
#self.root.mainloop()
def start(self):
while self.do is True and self.smile.activated is True:
self.smile.check_face()
self.root.update()
self.root.destroy()
@staticmethod
def hash_password(password):
h = hashlib.sha512(password.encode('utf-8'))
return h.hexdigest()
def sign_in(self):
if not self.password.get() and not self.login.get():
messagebox.showinfo("Warning", "Empty data")
elif not self.login.get():
messagebox.showinfo("Warning", "Empty login")
elif not self.password.get():
messagebox.showinfo("Warning", "Empty password")
elif len(self.password.get()) < 8:
messagebox.showinfo("Warning", "Too short password")
elif self.db.find_login(self.login.get()):
messagebox.showinfo("Warning", "This user is already exists")
else:
self.db.add_user(self.login.get(),
self.hash_password(self.password.get()))
messagebox.showinfo("OK", "Done")
def log_in(self):
if not self.password.get() and not self.login.get():
messagebox.showinfo("Warning", "Empty data")
elif not self.login.get():
messagebox.showinfo("Warning", "Empty login")
elif not self.password.get():
messagebox.showinfo("Warning", "Empty password")
elif self.db.find_user(self.login.get(),
self.hash_password(self.password.get())):
self.smile.stop()
self.cur_db = Database(self.login.get(), "settings")
self.cur_db.load_settings()
self.do = False
else:
messagebox.showinfo("Warning", "Wrong data")
#Authorization()
class Mysql(object):
def __init__(self, write=None):
self.db = Database()
if (write):
self.cnn = self.db.getMysqlCnnW()
return
self.cnn = self.db.getMysqlCnn()
# 获取连接信息
def getCnn(self):
self.cnn = self.db.getMysqlCnn()
return self.cnn
def getCnnW(self):
self.cnn = self.db.getMysqlCnnW()
return self.cnn
# 获取json（dict）的基本结构
def getGeomJson(self, table, where=None):
pass
# 获取表的所有字段名称,注意表名是不带模式的
def getTableColumns(self, tablename):
sql = "SELECT a.attname as field FROM pg_class as c,pg_attribute as a inner " \
"join pg_type on pg_type.oid = a.atttypid where c.relname = '" \
+ tablename + "' and a.attrelid = c.oid and a.attnum>0"
dbRows = self.exec(sql)
return dbRows.T.values[0]
# 给表增加字段
def addTableColumn(self, table, column):
sql = 'ALTER TABLE ' + table + ' ADD ' + column + ' double precision '
return self.ex(sql)
# 获取所有队了geom字段的值，转为json（dict）
def getProperties(self, table, where=None):
if where == None:
whereClause = ''
else:
whereClause = ' where ' + where
columns = self.getTableColumns(table)
columns.pop()
colexcept = ",".join(columns)
sql = 'select row_to_json(t) from (select ' + colexcept + ' from ' + table + whereClause + ') as t'
return self.exec(sql)
# 执行原生的SQL并返回dataframe结果
def exec(self, sql):
cur = self.cnn.cursor()
cur.execute(sql)
res = cur.fetchall()
return res
# return pd.DataFrame(res)
# def exec(self, sql):
# cur = self.cnn.cursor()
# self.cnn.commit()
# cur.execute(sql)
# res = cur.fetchall()
# return pd.DataFrame(res)
# 数据表内容有更新，必须使用到该语句
def insert(self, table, columns, values):
tableStr = table + '(' + ','.join(columns) + ')'
valStr = []
for col in columns:
valStr.append('%s')
sql = "INSERT INTO " + tableStr + " VALUES (" + ','.join(valStr) + ")"
cur = self.cnn.cursor()
cur.executemany(sql, values)
self.cnn.commit()
def sql(self, sql):
cur = self.cnn.cursor()
cur.execute(sql)
self.cnn.commit()
return True
# 执行原生的SQL，没有实际数据，只执行命令，返回成功结果
def ex(self, sql):
return self.sql(sql)
# 执行sql获取返回一个值的内容
def execValue(self, sql):
res = self.exec(sql)
if len(res) > 0:
return res[0][0]
else:
return None
# 获取engine,使用这个引擎
def engine(self):
# engine = create_engine('postgresql://' + self.dbconfig['db_user'] + ':123456@' + self.dbconfig['db_host'] + ':' + str(self.dbconfig['db_port'])
# + '/' + self.dbconfig['db_database'])
engine = create_engine(
"mysql://*****:*****@[email protected]:3306/securitycode",
max_overflow=5)
# create_engine("mysql+pymysql://user:passwd@host:port/db?charset=utf8")
# engine = create_engine('mysql://*****:*****@localhost:3306/test?charset=utf8mb4')
return engine
def myEngine(self):
engine = create_engine(
"mysql://wdkj@dev:wdkj@[email protected]:3306/yjgl",
max_overflow=5)
return engine
def isTableExist(self, table):
sql = "select to_regclass('" + table + "')"
table_name = self.execValue(sql)
if table_name is None:
return False
else:
return True
def clearTable(self, table):
sql = 'TRUNCATE ' + table
return self.ex(sql)
def dropTable(self, tableArr: list):
for table in tableArr:
if self.isTableExist('gegu."' + table + '"'):
sql = 'drop table gegu."' + table + '"'
self.ex(sql)
print('删除表成功：' + table)
print()
return True
def getValue(self, key):
sql = 'select value from kv ' + "where key = '" + key + "'"
return self.execValue(sql)
def setValue(self, key, value):
sql = 'select key from kv ' + "where key = '" + key + "'"
skey = self.execValue(sql)
exSql = ''
if skey is None:
exSql = "insert into kv(key,value) values('" + key + "','" + value + "')"
else:
exSql = "update kv set value = '" + value + "' " + " where key='" + key + "'"
self.ex(exSql)
return True
def delValue(self, key):
sql = 'delete from kv ' + "where key = '" + key + "'"
return self.ex(sql)
def deleteSQL(self, sql):
pass
def __init__(self, write=None):
self.db = Database()
if (write):
self.cnn = self.db.getMysqlCnnW()
return
self.cnn = self.db.getMysqlCnn()
items = {'location': location, 'key': 'TIGBZ-ZOM36-COSSO-E2D5M-7MIGS-HTFFI'}
res = requests.get('https://apis.map.qq.com/ws/geocoder/v1/', params=items)
info = res.json()
return info
def get_admin_region_gov(lat, lon):
items = {'lon': lon, 'lat': lat, 'zoom': 4}
headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/'
'537.36 (KHTML, like Gecko) Chrome/77.0.3865.90 Safari/537.36'} # a User-Agent required
res = requests.get('http://ditu.zjzwfw.gov.cn/ime-server/rest/xzqh_zj/division/rgeo', params=items, headers=headers)
info = res.json()
return info
db = Database()
db2 = Database()
conn = db.getMysqlCnn()
conn2 = db2.getMysqlCnn()
cur = conn.cursor()
cur2 = conn2.cursor()
# sql = 'select * from lyr_user where lon is not null and town*region is null'
sql = 'select AdcdId,Lat,Lng from adcdinfo where city is null'
cur.execute(sql)
while True:
start = time.time()
row = cur.fetchone()
if not row:
break
# admin_info = get_admin_region_tx(row[24], row[23])
# admin_info = get_admin_region_gov(row[24], row[23])
def __init__(self):
self.session = Database.get_mysql_session()
from flask import Flask, request, url_for, session, redirect, render_template
from markupsafe import escape
from random import randint
from flask_mysqldb import MySQL
from application.requests.onlineshop_request import OnlineshopRequest
from config.database import Database
app = Flask(__name__)
app.secret_key = b'_5#y2L"F4Q8z\n\xec]/'
configDB = Database(app)
myDB = MySQL(configDB.config)
@app.route("/", methods=['GET'])
def index():
cur = myDB.connection.cursor()
cur.execute("SELECT * FROM online_shop ORDER BY id DESC LIMIT 20")
data = cur.fetchall()
return render_template('index.html', onlineshop=data)
@app.route("/create", methods=['GET'])
def create():
return render_template('create.html')
@app.route("/store", methods=['POST'])
def store():
data = request.form
value = OnlineshopRequest(data)
cur = myDB.connection.cursor()
