def open(self) -> bool:
if not self.check_for_dsn(self.dsn):
return False
self.logger.info("Attempting to connect to the dsn '%s'." % s)
if self.integrated == 'Y':
try:
self.connection = pyodbc.connect(
'DSN=%s;Trusted_Connection=yes' % self.dsn
, timeout=5 # allow 5 seconds to connect
)
self.logger.info(
'connection to ' + self.dsn + ' opened at ' + timestr())
return True
except Exception as e:
self.logger.error('Unable to connect to ' + self.dsn + '.')
self.logger.error(str(e))
self.status = str(e)
return False
else:
# self.password = getpass.getpass(
# 'Please enter your password for %s: \n' % self.dsn)
try:
self.connection = pyodbc.connect(
'DSN=%s;PWD=%s' % (self.dsn, self.password)
, timeout=5 # allow 5 seconds to connect
)
self.logger.info(
'connection to ' + self.dsn + ' opened at ' + timestr())
return True
except Exception as e:
self.logger.error('Unable to connect to ' + self.dsn + '.')
self.logger.error(str(e))
self.status = str(e)
return False
def close(self) -> bool:
try:
msg = timestr() + 'connection to' + self.dsn + 'closed.'
self.logger.info(msg)
self.connection.close()
return True
except Exception as e:
self.logger.debug('close() failed: ' + str(e))
return False
def export_query_results(self, parent, name, tbl, sql, pw):
"""
Writes a sql query to a csv.
"""
start_datetime = timestr()
start_time = just_time()
logger = logging.getLogger(
'main.sql_exporter.ExportSql.export_query_results')
logger.debug('Pulling query ' + name)
fso = FSO()
fso.make_dir('output')
csv_path = 'output\\' + name + '_' + start_datetime + '.csv'
con = Connection(table=tbl, password=pw)
def result_iter(cursor, chunksize=1000):
while True:
results = cursor.fetchmany(chunksize)
if not results:
break
for result in results:
yield result
def call_grandfather(status, done=False):
if done == True:
finish = just_time()
else:
finish = '...'
parent.parent.callback(
name
, start_time
, finish
, status
)
call_grandfather(status='Connecting')
if con.open():
cursor = con.connection.cursor()
call_grandfather(status='Executing query')
try:
cursor.execute(sql)
with open(csv_path, 'w', newline='') as csv_file:
call_grandfather(status='Writing csv')
writer = csv.writer(csv_file, quoting=csv.QUOTE_ALL)
writer.writerow([i[0] for i in cursor.description]) # header
for r, row in enumerate(result_iter(cursor, 1000)):
if r > 100000:
break
if r % 1000 == 0:
logger.info('Writing row ' + str(r))
writer.writerow(list(row) + ['', '', '', ''])
call_grandfather(status='Great Success!', done=True)
fso.open_file(csv_path)
except Exception as e:
err = str(e)
logger.error(err)
call_grandfather(status=str(e), done=True)
finally:
con.close()
parent.call_dad()
def open_file(path):
print(timestr(), 'opening', path)
os.startfile(path)
def write_text(text, name='output.txt'):
FSO().make_dir('output')
path = 'output\\' + name + '_' + timestr() + '.txt'
with open(path, "w") as text_file:
text_file.write(text)
return path
