data-manipulation/dm.py

481 lines
19 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

# -*- 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()