481 lines
19 KiB
Python
481 lines
19 KiB
Python
# -*- coding: utf-8 -*-
|
||
import json, os, configparser, shutil, re
|
||
from datetime import datetime, date, time
|
||
|
||
def firstInit():
|
||
# Инициализация переменных создание конфигруационного файла, копирование шаблонов
|
||
global db_type, db_hostname, db_user, db_password, db_name, template_file, db_type, template_dir
|
||
config = configparser.RawConfigParser()
|
||
# проверяем тип ОС
|
||
if os.name == "nt":
|
||
cfg_dir = os.path.join(os.getenv("USERPROFILE"), ".dm")
|
||
elif os.name == "posix":
|
||
cfg_dir = os.path.join(os.getenv("HOME"), ".dm")
|
||
# определим каталог для конфигруции и создадим если его нет
|
||
if os.path.isdir(cfg_dir):
|
||
print(cfg_dir + " already exists")
|
||
else:
|
||
os.mkdir(cfg_dir)
|
||
cfg_file = os.path.join(cfg_dir, 'dm.cfg')
|
||
# создадим файл конфигурации
|
||
config.add_section('DataBase')
|
||
config.set('DataBase', 'db_type', 'sqlite')
|
||
config.set('DataBase', 'db_hostname', 'localhost')
|
||
config.set('DataBase', 'db_name', 'dm')
|
||
config.set('DataBase', 'db_user', 'dm')
|
||
config.set('DataBase', 'db_password', 'password')
|
||
config.add_section('Directory')
|
||
config.set('Directory', 'work_dir', cfg_dir)
|
||
config.set('Directory', 'template_dir', os.path.join(cfg_dir, 'db_template'))
|
||
|
||
# Если конфиг уже есть читаем его если нет, создаем и потом читаем
|
||
if os.path.isfile(cfg_file):
|
||
config.read(cfg_file)
|
||
else:
|
||
# Запись конфигурации в файл 'example.cfg'
|
||
with open(cfg_file, 'w') as configfile:
|
||
config.write(configfile)
|
||
configfile.close()
|
||
config.read(cfg_file)
|
||
|
||
work_dir = config.get('Directory', 'work_dir')
|
||
global template_dir
|
||
template_dir = config.get('Directory', 'template_dir')
|
||
db_type = config.get('DataBase', 'db_type')
|
||
db_hostname = config.get('DataBase', 'db_hostname')
|
||
if db_type == 'sqlite':
|
||
db_name = os.path.join(work_dir, config.get('DataBase', 'db_name'))
|
||
else:
|
||
db_name = config.get('DataBase', 'db_name')
|
||
db_user = config.get('DataBase', 'db_user')
|
||
db_password = config.get('DataBase', 'db_password')
|
||
|
||
# Создаём нужные каталоги
|
||
if os.path.isdir(work_dir):
|
||
print(work_dir + " already exists")
|
||
else:
|
||
os.mkdir(work_dir)
|
||
if os.path.isdir(template_dir):
|
||
print(template_dir + " already exists")
|
||
else:
|
||
os.mkdir(template_dir)
|
||
# копируем и читаем файл шаблон БД
|
||
template = config.get('DataBase', 'db_name') + '.json'
|
||
|
||
template_file = os.path.join(template_dir, template)
|
||
if os.path.isfile(template_file):
|
||
print("Template file already exists " + template_file)
|
||
else:
|
||
shutil.copy('db_template/dm.json', template_file)
|
||
|
||
|
||
def dbConnect():
|
||
global c, db_type, db_hostname, db_user, db_password, db_name, conn
|
||
if db_type == "mysql":
|
||
import pymysql
|
||
conn = pymysql.connect(
|
||
db=db_name,
|
||
user=db_user,
|
||
passwd=db_password,
|
||
host=db_hostname,
|
||
charset='utf8')
|
||
conn.commit()
|
||
elif db_type == "sqlite":
|
||
import sqlite3
|
||
conn = sqlite3.connect(db_name)
|
||
else:
|
||
print(db_type + " database type does`t support")
|
||
exit()
|
||
#print(conn)
|
||
c = conn.cursor()
|
||
return c
|
||
|
||
def createTables(tbl_list):
|
||
global dbTablesDescriptionList, dbTablesStructList, c, db_type
|
||
i = 0
|
||
dbTablesNamesList = []
|
||
dbTablesDescriptionList = []
|
||
dbTablesStructList = []
|
||
|
||
while i < len(tbl_list):
|
||
one_Table_descr = []
|
||
one_Table_struct = []
|
||
one_Table_relation = []
|
||
tbl_descr = tbl_list[i]["tableDescription"]
|
||
tbl_name = tbl_list[i]["tableName"]
|
||
#field_list = tbl_list[i]["fieldList"][i].keys()
|
||
dbTablesNamesList.append([tbl_name, tbl_descr])
|
||
one_Table_descr.append(tbl_name)
|
||
one_Table_struct.append(tbl_name)
|
||
|
||
x = 0
|
||
# список всех полей таблицы
|
||
qwery_create = "CREATE TABLE IF NOT EXISTS " + tbl_name + " ("
|
||
index = ""
|
||
field_names_list = []
|
||
struct_fields_list = []
|
||
# формируем запрос в зависимости от типа БД
|
||
if db_type == 'mysql':
|
||
while x < len(tbl_list[i]["fieldList"]):
|
||
if tbl_list[i]["fieldList"][x]["autoIncrement"] == "yes":
|
||
auto_increment = " AUTO_INCREMENT, "
|
||
else:
|
||
auto_increment = ", "
|
||
qwery_create = qwery_create + tbl_list[i]["fieldList"][x]["fName"] + " " + \
|
||
tbl_list[i]["fieldList"][x]["fType"] + auto_increment
|
||
if tbl_list[i]["fieldList"][x]["index"] == "PRIMARY KEY":
|
||
index = ", PRIMARY KEY (" + tbl_list[i]["fieldList"][x]["fName"] + ")"
|
||
field_names_list.append(
|
||
[tbl_list[i]["fieldList"][x]["fName"], tbl_list[i]["fieldList"][x]["fDescription"]])
|
||
# struct_fields_list.append(tbl_list[i]["fieldList"][x]["fName"])
|
||
struct_fields_list.append(
|
||
[tbl_list[i]["fieldList"][x]["fName"], fieldTypeConvert(tbl_list[i]["fieldList"][x]["fType"]), auto_increment.strip(', ')])
|
||
if tbl_list[i]["fieldList"][x]["relation"]:
|
||
relation_list = [tbl_list[i]["fieldList"][x]["fName"]]
|
||
relation_list.append(tbl_list[i]["fieldList"][x]["relation"])
|
||
|
||
one_Table_relation.append(relation_list)
|
||
x = x + 1
|
||
qwery_create = qwery_create.strip(', ') + index + ');'
|
||
#qwery_create = qwery_create + index + ');'
|
||
#print(qwery_create)
|
||
elif db_type == 'sqlite':
|
||
while x < len(tbl_list[i]["fieldList"]):
|
||
if tbl_list[i]["fieldList"][x]["autoIncrement"] == "yes":
|
||
auto_increment = " AUTOINCREMENT,"
|
||
else:
|
||
auto_increment = ", "
|
||
if tbl_list[i]["fieldList"][x]["index"] == "PRIMARY KEY":
|
||
index = " PRIMARY KEY"
|
||
else:
|
||
index = ''
|
||
qwery_create = qwery_create + tbl_list[i]["fieldList"][x]["fName"] + ' ' + \
|
||
tbl_list[i]["fieldList"][x]["fType"] + index + auto_increment
|
||
|
||
field_names_list.append([tbl_list[i]["fieldList"][x]["fName"], tbl_list[i]["fieldList"][x]["fDescription"]])
|
||
# struct_fields_list.append(tbl_list[i]["fieldList"][x]["fName"])
|
||
struct_fields_list.append(
|
||
[tbl_list[i]["fieldList"][x]["fName"], fieldTypeConvert(tbl_list[i]["fieldList"][x]["fType"]), auto_increment.strip(', ')])
|
||
if tbl_list[i]["fieldList"][x]["relation"]:
|
||
relation_list = [tbl_list[i]["fieldList"][x]["fName"]]
|
||
relation_list.append(tbl_list[i]["fieldList"][x]["relation"])
|
||
|
||
one_Table_relation.append(relation_list)
|
||
x = x + 1
|
||
|
||
qwery_create = qwery_create.strip(', ') + ");"
|
||
one_Table_struct.append(struct_fields_list)
|
||
one_Table_struct.append(one_Table_relation)
|
||
one_Table_descr.append(field_names_list)
|
||
i = i + 1
|
||
dbTablesDescriptionList.append(one_Table_descr)
|
||
dbTablesStructList.append(one_Table_struct)
|
||
print(qwery_create)
|
||
c.execute(qwery_create)
|
||
return dbTablesNamesList
|
||
|
||
def initDBstructure():
|
||
global dbTablesDescriptionList, template_file, tblNamesList
|
||
table_list = open(template_file, "r", encoding="utf-8")
|
||
# data = json.load(table_list, encoding="utf-8")
|
||
data = json.load(table_list)
|
||
#tbl_list = data["tables"]
|
||
#tblNamesList = createTables(tbl_list)
|
||
tblNamesList = createTables(data["tables"])
|
||
return tblNamesList
|
||
|
||
# выборка данных из заданной таблицы
|
||
def selectData(tbl):
|
||
global dbTablesStructList, c, db_type
|
||
# если юольше 1 поля добавить CONCAT
|
||
qwery = "SELECT "
|
||
subqwery = ""
|
||
#print(dbTablesStructList)
|
||
for item in dbTablesStructList:
|
||
if item[0] == tbl:
|
||
for field in item[1]:
|
||
field = field[0]
|
||
qwery = qwery + field + ","
|
||
for rel in item[2]:
|
||
field = rel[0]
|
||
field_rel = rel[1][0]
|
||
field_replace = rel[1][1]
|
||
# определяем название таблицы для вложенного запроса
|
||
table1 = field_rel.split('.')[0]
|
||
field1 = field_rel.split('.')[1]
|
||
if table1 == tbl:
|
||
table1 = table1 + "_1"
|
||
subqwery = tbl + " AS " +table1
|
||
else:
|
||
subqwery = table1
|
||
# составляем подзапрос и подменяем им поле в запросе
|
||
print(subqwery)
|
||
|
||
if db_type == "mysql":
|
||
field_replace = field_replace.replace(",", ",' ',")
|
||
subqwery = "(SELECT CONCAT('>', {}.{}, '<', {}) FROM {} WHERE {}.{}={}.{}) AS {}"\
|
||
.format(tbl,field,field_replace,subqwery,table1,field1,tbl,field,field)
|
||
elif db_type == "sqlite":
|
||
field_replace = field_replace.replace(",", " || ' ' ||")
|
||
subqwery = "(SELECT ({}) FROM {} WHERE {}.{}={}.{}) AS {}" \
|
||
.format(field_replace, subqwery, table1, field1, tbl, field, field)
|
||
qwery = qwery.replace(field, subqwery)
|
||
qwery = '{} FROM {} LIMIT 10000'.format(qwery.rstrip(','), tbl)
|
||
print(qwery)
|
||
c.execute(qwery)
|
||
return c.fetchall()
|
||
|
||
# получаем на вход имя таблицы и возвращаем список заголовков полей
|
||
def getTableStructure(tbl):
|
||
global dbTablesDescriptionList, dbTablesStructList
|
||
for item in dbTablesDescriptionList:
|
||
if item[0] == tbl:
|
||
return item[1]
|
||
|
||
# Получаем список названий полей и типов для заданной таблицы
|
||
def getFields(tbl):
|
||
global dbTablesDescriptionList, dbTablesStructList
|
||
for item in dbTablesStructList:
|
||
if item[0] == tbl:
|
||
#print(item[1])
|
||
return item[1]
|
||
# Ищем описание поля по его названию и возвращаем.
|
||
def getFieldDescription(tbl, field):
|
||
global dbTablesDescriptionList, dbTablesStructList
|
||
for item in dbTablesDescriptionList:
|
||
if item[0] == tbl:
|
||
for i in item[1]:
|
||
if i[0] == field:
|
||
fName = i[1]
|
||
return fName
|
||
return "null"
|
||
|
||
def fieldTypeConvert(ftype):
|
||
# разбираем строку на тип и длину
|
||
ftype = str.lower(ftype)
|
||
line = re.search("(.+?)\(([0-9]+)\)", ftype)
|
||
if line:
|
||
fType = line.groups()[0]
|
||
fLength = line.groups()[1]
|
||
if fType == 'int' or fType == 'integer':
|
||
fType = 'integer'
|
||
elif fType == 'char' or fType == 'varchar':
|
||
fType = 'character'
|
||
else:
|
||
fType = ftype
|
||
#print(fType)
|
||
return fType
|
||
|
||
def getFieldType(tbl, field):
|
||
global dbTablesDescriptionList, dbTablesStructList
|
||
for item in dbTablesStructList:
|
||
if item[0] == tbl:
|
||
for i in item[1]:
|
||
if i[0] == field:
|
||
fType = i[1]
|
||
return fType
|
||
|
||
def getRelationsForField(tblSearch, fieldName):
|
||
global dbTablesStructList, c, db_type
|
||
dataList = []
|
||
for item in dbTablesStructList:
|
||
#print(item)
|
||
tblName = item[0]
|
||
for field in item[1]:
|
||
field = field[0]
|
||
for rel in item[2]:
|
||
if tblName == tblSearch:
|
||
# выдергиваем из списка название и поле нужной нам таблицы
|
||
if rel[0] == fieldName:
|
||
l = rel[1][0].split('.')
|
||
relTable = l[0]
|
||
relField = l[1]
|
||
replaceFields = rel[1][1]
|
||
return [relTable, relField, replaceFields]
|
||
return "q"
|
||
|
||
# добавление записи в БД на вход принимает список вида:
|
||
# [table [['field_1', 'значение'], ['field_2', 'значение'],....., ['field_n', 'значение']]]
|
||
def insertDataIntoBD(dataList):
|
||
global c, conn
|
||
dbConnect()
|
||
tableName = dataList[0]
|
||
qwery = 'INSERT INTO {} ('.format(tableName)
|
||
qweryData = ''
|
||
qweryField = ''
|
||
for item in dataList[1]:
|
||
fType = getFieldType(tableName, item[0])
|
||
#print(fType)
|
||
# проверяем если значение поля пустое то в запрос оно не включается
|
||
if item[1] != '':
|
||
qweryField = '{}{},'.format(qweryField,item[0])
|
||
if fType == 'integer':
|
||
qweryData = '{}{},'.format(qweryData,item[1])
|
||
elif fType == 'datetime':
|
||
# преобразуем дату всяко разно
|
||
dt = datetime.strptime(item[1], "%d.%m.%y %H:%M")
|
||
item[1] = str(dt)
|
||
qweryData = "{}'{}',".format(qweryData,item[1])
|
||
elif fType == 'date':
|
||
d = item[1].split('.')
|
||
myDate = '{}-{}-{}'.format(d[2], d[1], d[1])
|
||
item[1] = str(myDate)
|
||
qweryData = "{}'{}',".format(qweryData, item[1])
|
||
else:
|
||
qweryData = "{}'{}',".format(qweryData, item[1])
|
||
|
||
|
||
qwery = '{}{}) VALUES ({});'.format(qwery, qweryField.rstrip(','), qweryData.rstrip(','))
|
||
|
||
print(qwery)
|
||
c.execute(qwery)
|
||
conn.commit()
|
||
#c.close()
|
||
return
|
||
|
||
# Выборка данных по значению
|
||
# принимает на вход название таблицы, назване поля, значение этого поля
|
||
def selectDataFromDB(tblName, fieldName, fieldValue):
|
||
global dbTablesStructList, c, db_type
|
||
qwery = "SELECT "
|
||
subqwery = ""
|
||
|
||
for item in dbTablesStructList:
|
||
if item[0] == tblName:
|
||
for field in item[1]:
|
||
field = field[0]
|
||
qwery = qwery + field + ","
|
||
for rel in item[2]:
|
||
field = rel[0]
|
||
fieldRel = rel[1][0]
|
||
fieldReplace = rel[1][1]
|
||
# определяем название таблицы для вложенного запроса
|
||
table1 = fieldRel.split('.')[0]
|
||
field1 = fieldRel.split('.')[1]
|
||
if table1 == tblName:
|
||
table1 = table1 + "_1"
|
||
subqwery = tblName + " AS " +table1
|
||
else:
|
||
subqwery = table1
|
||
# составляем подзапрос и подменяем им поле в запросе
|
||
if db_type == "mysql":
|
||
fieldReplace = fieldReplace.replace(",", ",' ',")
|
||
subqwery = '(SELECT CONCAT({}) FROM {} WHERE {}.{}={}.{}) AS {}'\
|
||
.format(fieldReplace, subqwery, table1, field1, tblName, field, field)
|
||
elif db_type == "sqlite":
|
||
fieldReplace = fieldReplace.replace(",", " || ' ' ||")
|
||
subqwery = '(SELECT ({}) FROM {} WHERE {}.{}={}.{}) AS {}'\
|
||
.format(fieldReplace, subqwery, table1, field1, tblName, field, field)
|
||
qwery = qwery.replace(field, subqwery)
|
||
qwery = '{} FROM {} WHERE {}={}'.format(qwery.rstrip(','), tblName, fieldName, fieldValue)
|
||
|
||
|
||
print(qwery)
|
||
c.execute(qwery)
|
||
return c.fetchall()
|
||
|
||
def selectRelationsDataFromDB(tblSearch, fieldName, fieldValue):
|
||
global dbTablesStructList, c, db_type
|
||
searchField = tblSearch + '.' + fieldName
|
||
dataList = []
|
||
for item in dbTablesStructList:
|
||
tblName = item[0]
|
||
for field in item[1]:
|
||
field = field[0]
|
||
for rel in item[2]:
|
||
if rel[1][0] == searchField:
|
||
data = selectDataFromDB(tblName, rel[0], fieldValue)
|
||
dataList.append([tblName, data])
|
||
return dataList
|
||
|
||
# Ищем название таблицы по её описанию
|
||
def getTablesNameOfDescription(tblDescr):
|
||
global tblNamesList
|
||
for i in tblNamesList:
|
||
if i[1] == tblDescr:
|
||
return(i[0])
|
||
|
||
# Ищем описание таблицы по её названию
|
||
def getTablesDescriptionOfName(tblName):
|
||
global tblNamesList
|
||
for i in tblNamesList:
|
||
if i[0] == tblName:
|
||
return(i[1])
|
||
|
||
# Удаление записи из БД
|
||
def deleteRecordsFromDB(tableName, valueList):
|
||
global c, db_type, conn
|
||
qwery = 'DELETE FROM {} WHERE '.format(tableName)
|
||
#print(valueList)
|
||
subQwery=''
|
||
i = 0
|
||
for item in getFields(tableName):
|
||
#print(item[0], item[1])
|
||
if item[1] == 'integer':
|
||
#subQwery = subQwery + item[0] + '=' + valueList[i] + ' AND '
|
||
subQwery = '{}{}={} AND '.format(subQwery, item[0], valueList[i])
|
||
else:
|
||
#subQwery = subQwery + item[0] + '=\'' + valueList[i] + '\' AND '
|
||
subQwery = "{}{}='{}' AND ".format(subQwery, item[0], valueList[i])
|
||
i += 1
|
||
qwery += subQwery
|
||
qwery = qwery.rstrip('AND ').replace("='None'", " IS NULL")
|
||
qwery = qwery.rstrip('AND ').replace("=None", " IS NULL")
|
||
qwery = qwery.rstrip('AND ').replace("=NULL", " IS NULL")
|
||
print(qwery)
|
||
c.execute(qwery)
|
||
conn.commit()
|
||
return
|
||
|
||
# Редактирование записи в БД
|
||
def editDataIntoBD(dataList):
|
||
global c, conn
|
||
dbConnect()
|
||
tableName = dataList[0]
|
||
qwery = 'UPDATE {} SET '.format(tableName)
|
||
#qweryData = ''
|
||
qweryField = ''
|
||
qweryWhere = ''
|
||
for item in dataList[1]:
|
||
fType = getFieldType(tableName, item[0])
|
||
# проверяем если значение поля пустое то в запрос оно не включается
|
||
if item[1] != '':
|
||
qweryField = '{}{}='.format(qweryField,item[0])
|
||
|
||
if fType == 'integer':
|
||
qweryField = '{}{},'.format(qweryField,item[1])
|
||
elif fType == 'datetime':
|
||
# преобразуем дату всяко разно
|
||
dt = datetime.strptime(item[1], "%d.%m.%y %H:%M")
|
||
item[1] = str(dt)
|
||
qweryField = "{}'{}',".format(qweryField,item[1])
|
||
elif fType == 'date':
|
||
d = item[1].split('.')
|
||
myDate = '{}-{}-{}'.format(d[2], d[1], d[1])
|
||
item[1] = str(myDate)
|
||
qweryField = "{}'{}',".format(qweryField, item[1])
|
||
else:
|
||
qweryField = "{}'{}',".format(qweryField, item[1])
|
||
qwery = '{}{} WHERE {};'.format(qwery, qweryField.rstrip(','), qweryWhere)
|
||
|
||
print(qwery)
|
||
#c.execute(qwery)
|
||
#conn.commit()
|
||
#c.close()
|
||
return
|
||
# Вывод структуры таблицы из базы данных
|
||
def getTableStructureFromDB(tableName):
|
||
global c, db_type, conn
|
||
|
||
if db_type == 'sqlite':
|
||
qwery = 'PRAGMA table_info({})'.format(tableName)
|
||
fieldsList = c.execute(qwery).fetchall()
|
||
#for row in fieldsList:
|
||
# print(row)
|
||
return(fieldsList)
|
||
|
||
|
||
#initDBstructure()
|
||
firstInit()
|
||
dbConnect()
|