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")
