archwhite
2/6/2018 - 1:06 PM

SQL. Print columns and Generate Delete Script

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)