def process_all(): myconn = ConnectionHelper().get_named_connection("current") cursor = myconn.cursor() cursor.execute("select etl_file_id from etl_file order by etl_file_id") for row in cursor: etl_file_id = row[0] logging.info("about to process %s", etl_file_id) process(etl_file_id)
from pdsutil.DbUtil import ConnectionHelper connection = ConnectionHelper().get_named_connection("it") cursor = connection.cursor() cursor.execute( "select distinct ship_to_cust_id from etl_sale order by ship_to_cust_id") sale_ship_to_ids = cursor.fetchall() print(sale_ship_to_ids) cursor.execute( "select distinct ship_to_cust_id from etl_customer order by ship_to_cust_id" ) cust_ship_to_ids = cursor.fetchall() print(cust_ship_to_ids) for sale_id_t, cust_id_t in zip(sale_ship_to_ids, cust_ship_to_ids): binds = {"to_id": sale_id_t[0], "cust_id": cust_id_t[0]} print("cust_id %s to_id %s" % (sale_id_t[0], cust_id_t[0])) cursor.execute( "update etl_customer set ship_to_cust_id = %(to_id)s where ship_to_cust_id = %(cust_id)s", binds) connection.commit()
from pdsutil.DbUtil import ConnectionHelper, CursorHelper import sys import csv if __name__ == "__main__": connection_name = sys.argv[0] connection = ConnectionHelper().get_named_connection(connection_name) cursor = CursorHelper(connection.cursor()) rows = cursor.execute(sql,binds) quoting_types = [csv.QUOTE_NONNUMERIC, csv.QUOTE_ALL, csv.QUOTE_MINIMAL, csv.QUOTE_NONE] def to_csv(self, file, emit_headers: bool = True, dialect: str = "excel", delimiter: str = ",", quotechar: str = "'", quoting: str = csv.QUOTE_NONNUMERIC): writer = csv.writer(file, dialect="excel", delimiter=',', quotechar='"', quoting=csv.QUOTE_NONNUMERIC) if emit_headers: writer.writerow(self.column_names) for row in self.rows: writer.writerow(row)
class isql: def __init__(self): self.statements = SqlStatements.from_statement_list([]) print(self.statements) self.statement = None self.connection = None self.cursor = None self.binds = {} def dump_statements(self): print("about to dump") print(yaml.dump(self.statements)) def connect(self, name: str): self.connection = ConnectionHelper().get_named_connection(name) self.cursor = CursorHelper(self.connection.cursor()) def load(self, filename: str): self.statements = SqlStatements.from_yaml(filename).statements self.list_statements() def list_statements(self, verbose=False): for i, v in enumerate(self.statements): print("%s %s" % (i, v)) # for k in self.statements: # print (k) def bind_date(self, name, year, month, day): self.binds[name] = datetime.datetime(year, month, day) def use(self, number: int): for i, k in enumerate(self.statements): if i == number: self.statement = self.statements[k] def use_statement(self, name: str): self.statement = name def run(self, index=None): if index == None: print("stmt: %s" % self.statement) else: for i, k in enumerate(self.statements): if i == index: self.statement = self.statements[k] print("k: %s sql: %s" % (k, self.statements["sql"])) rows = self.cursor.execute(self.statement["sql"], self.binds) for row in rows: print(row) def bind(self, name, value): self.binds[name] = value def execute(self, statement_name: str = None): if self.connection is None: print("use connect before use") if statement_name: sql = self.statements[statement_name] elif self.statement is None: sql = self.statements[statement_name] else: print("pass statement or call use_statement() before execute") result = self.cursor.execute(sql, self.binds) import sys def to_csv(self, headers=True, outfile=sys.stdout): import csv rows = self.execute() column_names = [i for i[0] in self.cursor.description] print(column_names) writer = csv.writer(outfile, dialect="excel", delimiter=',', quotechar='"', quoting=csv.QUOTE_NONNUMERIC) if headers: writer.writerow(column_names) for row in rows: writer.writerow(row) @staticmethod def help(): print("connect()") print("list_statements()") print("execute(statement_name)") print("bind_date(name,yr,month,day)") print("load('filename')") print("use statement(statement_name") print("use")