def import_table_data(edel_connection, query, table_name, fields_list,
schema_name):
result = []
# print (query)
cur = edel_connection.cursor()
cur.execute(query) #pd.read_sql(query, edel_connection)
for row in cur.fetchall():
if not row:
return None
vals = ""
for i in range(len(fields_list)):
vals += defs.value_str(row[i])
result.append("insert into " + schema_name + "." + table_name + " (" +
defs.fields_str(fields_list) + ") values(" + vals[:-1] +
")")
# print(4)
cur.close()
return result
def select_for_update_query(last_update):
return "select " + defs.fields_str(FIELD_NAMES,
"ROOM") + " from Admin.ROOM"
def select_for_update_query(last_update):
return "select " + defs.fields_str(FIELD_NAMES, 'RESERVE') + " from Admin." + 'RESERVE' + \
" where RESERVE.CREATEDTIME < '" + last_update.strftime('%Y/%m/%d %H:%M:%S') + \
"' and RESERVE.UPDATEDTIME > '" + last_update.strftime('%Y/%m/%d %H:%M:%S') + "'"
def select_for_update_query(last_update):
return "select " + defs.fields_str(FIELD_NAMES, 'PERSON') + " from Admin." + 'PERSON' + \
" where Admin.PERSON.RESERVID in (select Admin.RESERVE.ID from Admin.RESERVE" + \
" where RESERVE.CREATEDTIME < '" + last_update.strftime('%Y/%m/%d %H:%M:%S') + \
"' and RESERVE.UPDATEDTIME > '" + last_update.strftime('%Y/%m/%d %H:%M:%S') + "')"
def select_for_insert_query(last_update):
return "select " + defs.fields_str(FIELD_NAMES,
'ROOMTYPE') + " from Admin.ROOMTYPE"
def insert_update_Reserves(CWD, currentTIME, mssql, edel, last_insert,
last_update, FIELD_NAMES, fullUpdate):
try:
T_Reserves = "Reserves"
T_ReservesByDays = "ReservesByDays"
T_Reserves_upd = T_Reserves + sUPD
T_ReservesByDays_upd = T_ReservesByDays + sUPD
low_fields = []
for fld in defs.MS_Reserves_FIELDS.keys():
low_fields.append(fld.lower())
if fullUpdate:
try:
mssql.cursor().execute("DROP TABLE %s.%s" %
(mssql_SCHEMA, T_Reserves))
except Exception as E:
pass
try:
mssql.cursor().execute("DROP TABLE %s.%s" %
(mssql_SCHEMA, T_ReservesByDays))
except Exception as E:
pass
try:
mssql.cursor().execute(
"CREATE TABLE %s.%s (%s)" %
(mssql_SCHEMA, T_Reserves,
defs.fields_types_str(defs.MS_Reserves_FIELDS)))
mssql.commit()
except Exception as E:
pass
try:
mssql.cursor().execute(
"CREATE TABLE %s.%s (%s)" %
(mssql_SCHEMA, T_ReservesByDays,
defs.fields_types_str(defs.MS_ReservesByDays_FIELDS)))
mssql.commit()
except Exception as E:
pass
# добавление
edel_data = pd.read_sql(
defs.select_for_insert_query(last_insert),
edel) #, index_col=ReservesByDays.MS_ReservesByDays_FIELDS)
# Брони
edel_data.to_csv(path_or_buf=bcpFILE.format(CWD, T_Reserves),
index=False,
sep=bcpSEP,
header=False,
columns=low_fields)
print("%d records written to %s" %
(len(edel_data), bcpFILE.format(CWD, T_Reserves)))
# БрониПоДням
if not write_ReservesByDays_to_bcp(edel_data, T_ReservesByDays):
raise Exception("file not written")
cmd = str(bcpCMD).format(mssql_SCHEMA, T_Reserves,
bcpFILE.format(CWD, T_Reserves),
mssql_DATABASE, mssql_SERVER, bcpSEP,
mssql_USERNAME, mssql_PASSWORD)
p = subprocess.run(
cmd
) #, shell=True, stdin=PIPE, stdout=PIPE, stderr=subprocess.STDOUT, close_fds=True)
print(cmd)
cmd = str(bcpCMD).format(mssql_SCHEMA, T_ReservesByDays,
bcpFILE.format(CWD, T_ReservesByDays),
mssql_DATABASE, mssql_SERVER, bcpSEP,
mssql_USERNAME, mssql_PASSWORD)
p = subprocess.run(
cmd
) #, shell=True, stdin=PIPE, stdout=PIPE, stderr=subprocess.STDOUT, close_fds=True)
print(cmd)
mssql.cursor().execute(
"update %s.%s set last_insert = '%s' where tbl_name = '%s'" %
(mssql_SCHEMA, mssql_TABLE_UPDATES,
currentTIME.strftime('%Y/%m/%d %H:%M:%S'), T_Reserves))
mssql.cursor().execute(
"update %s.%s set last_insert = '%s' where tbl_name = '%s'" %
(mssql_SCHEMA, mssql_TABLE_UPDATES,
currentTIME.strftime('%Y/%m/%d %H:%M:%S'), T_ReservesByDays))
if fullUpdate:
mssql.cursor().execute(
"update %s.%s set last_update = '%s' where tbl_name = '%s'" %
(mssql_SCHEMA, mssql_TABLE_UPDATES,
currentTIME.strftime('%Y/%m/%d %H:%M:%S'), T_Reserves))
mssql.cursor().execute(
"update %s.%s set last_update = '%s' where tbl_name = '%s'" %
(mssql_SCHEMA, mssql_TABLE_UPDATES,
currentTIME.strftime('%Y/%m/%d %H:%M:%S'), T_ReservesByDays))
print("%s, %s updated" % (T_Reserves, T_ReservesByDays))
mssql.commit()
print("{:d} records inserted to {:s}\n".format(len(edel_data),
T_Reserves))
if fullUpdate:
return
# обновление
edel_data = pd.read_sql(defs.select_for_update_query(last_update),
edel)
# Брони
edel_data.to_csv(path_or_buf=bcpFILE.format(CWD, T_Reserves_upd),
index=False,
sep=bcpSEP,
header=False,
columns=low_fields)
print("%d records written to %s" %
(len(edel_data), bcpFILE.format(CWD, T_Reserves_upd)))
# БрониПоДням
if not write_ReservesByDays_to_bcp(edel_data, T_ReservesByDays_upd):
raise Exception("file not written")
try:
mssql.cursor().execute("drop table %s.%s" %
(mssql_SCHEMA, T_Reserves_upd))
except Exception as E:
pass
try:
mssql.cursor().execute("drop table %s.%s" %
(mssql_SCHEMA, T_ReservesByDays_upd))
except Exception as E:
pass
mssql.cursor().execute(
"select * into {0}.{1} from {0}.{2} where 0 = 1".format(
mssql_SCHEMA, T_Reserves_upd, T_Reserves))
mssql.cursor().execute(
"select * into {0}.{1} from {0}.{2} where 0 = 1".format(
mssql_SCHEMA, T_ReservesByDays_upd, T_ReservesByDays))
mssql.commit()
cmd = str(bcpCMD).format(mssql_SCHEMA, T_Reserves_upd,
bcpFILE.format(CWD, T_Reserves_upd),
mssql_DATABASE, mssql_SERVER, bcpSEP,
mssql_USERNAME, mssql_PASSWORD)
p = subprocess.run(cmd)
print(cmd)
cmd = str(bcpCMD).format(mssql_SCHEMA, T_ReservesByDays_upd,
bcpFILE.format(CWD, T_ReservesByDays_upd),
mssql_DATABASE, mssql_SERVER, bcpSEP,
mssql_USERNAME, mssql_PASSWORD)
p = subprocess.run(cmd)
print(cmd)
res_id_lst = ""
for idx in range(len(edel_data.values)):
res_id_lst += str(edel_data.loc[idx,
defs.F_ReservId.lower()]) + ","
fupdstr1 = defs.fields_upd_str(defs.MS_Reserves_FIELDS, T_Reserves,
T_Reserves_upd)
# fupdstr2 = defs.fields_upd_str(defs.MS_ReservesByDays_FIELDS, T_ReservesByDays, T_ReservesByDays_upd)
mssql.cursor().execute(
"UPDATE {0}.{1} SET {3} FROM {0}.{2} WHERE {0}.{1}.{4} = {0}.{2}.{4} "
.format(mssql_SCHEMA, T_Reserves, T_Reserves_upd, fupdstr1,
defs.F_ReservId))
mssql.cursor().execute("DELETE FROM {0}.{1} WHERE {2} IN ({3})".format(
mssql_SCHEMA, T_ReservesByDays, defs.F_ReservId, res_id_lst[:-1]))
mssql.cursor().execute(
"INSERT INTO {0}.{1} ({3}) SELECT {3} FROM {0}.{2}".format(
mssql_SCHEMA, T_ReservesByDays, T_ReservesByDays_upd,
defs.fields_str(defs.MS_ReservesByDays_FIELDS.keys())))
mssql.cursor().execute(
"DELETE FROM {0}.{1} WHERE {2} IN (SELECT {2} FROM {0}.{3} WHERE {4} = 1)"
.format(mssql_SCHEMA, T_ReservesByDays, defs.F_ReservId,
T_Reserves, defs.F_IsDeleted))
mssql.cursor().execute("DELETE FROM {0}.{1} WHERE {2} = 1".format(
mssql_SCHEMA, T_Reserves, defs.F_IsDeleted))
mssql.cursor().execute(
"UPDATE {0}.{1} SET last_update = '{2}' where tbl_name = '{3}'".
format(mssql_SCHEMA, mssql_TABLE_UPDATES,
currentTIME.strftime('%Y/%m/%d %H:%M:%S'), T_Reserves))
mssql.cursor().execute("DROP TABLE %s.%s" %
(mssql_SCHEMA, T_Reserves_upd))
mssql.cursor().execute("DROP TABLE %s.%s" %
(mssql_SCHEMA, T_ReservesByDays_upd))
mssql.commit()
print("{:d} records updated in {:s}\n".format(len(edel_data),
T_Reserves))
except Exception as E:
print('error on update %s: %s\n' % (T_Reserves, E), file=sys.stderr)