import requests
import sqlsoup
import sqlalchemy
import datetime
import time
from enum import IntEnum
db_server = 'preprod'
database = 'masterdb'
user = 'masteradmin'
password = 'standpassword'
engine = sqlalchemy.create_engine(f"mssql+pyodbc://{user}:{password}@{db_server}/{database}?driver=SQL+Server+Native+Client+11.0", echo=False)
db = sqlsoup.SQLSoup(engine)
def columns(tablename):
sql = "SELECT COLUMN_NAME, DATA_TYPE"\
" FROM INFORMATION_SCHEMA.COLUMNS"\
" WHERE "\
f" TABLE_NAME = '{tablename}'"# AND "\
#f" COLUMN_NAME = '{columnname}'"
return ((column, ctype) for column,ctype in db.execute(sql).fetchall())
def find_datetime_column(columns):
dtime_columns = []
for c in columns: #(column_name, column_type)
if c[1].startswith("datetime"):
dtime_columns.append(c[0])
if len(dtime_columns) == 1:
return dtime_columns[0]
if len(dtime_columns) > 1:
print('first date time columns taken')
return dtime_columns[0]
return None
def generate_delete_script(tablename, dtime_column, from_date, to_date):
sql = f"DELETE FROM {tablename} WHERE {dtime_column} >= {from_date} AND "\
f" {dtime_column} <= {to_date}"
print(sql)
meta = sqlalchemy.schema.MetaData()
meta.reflect(bind=engine)
#for table in meta.tables:
#pass
tablename = 'inner_events'
from_date = "10:10:10"
to_date = "10:10:11"
cs = columns(tablename)
dtime_column = find_datetime_column(cs)
generate_delete_script(tablename, dtime_column, from_date, to_date)