· 6 years ago · Dec 18, 2019, 02:40 PM
1#!/usr/bin/env python3
2# -*- coding: iso-8859-1 -*-
3###############################################################################
4# nzlib.py #
5###############################################################################
6# Version 0.1, Jens Janzen, Karstadt IT Org
7# Datum: 2018-03-02
8# Description:
9# Library contains helper functions for Netezza database access
10import os
11import re
12import shlex
13import subprocess
14import time
15import ibm_db
16
17standard_encoding = 'iso8859_15'
18
19"""Formating select query"""
20def format_result(command):
21 ret = []
22 result = ibm_db.fetch_assoc(command)
23 while result:
24 ret.append(result)
25 result = ibm_db.fetch_assoc(command)
26 return ret
27
28"""Printing select query using fetch_assoc and returning object for other statements"""
29def print_select(query, output):
30 if query.lower().startswith('select'):
31 return format_result(output)
32 return output
33
34def connect_to_database(credentials):
35 if len(credentials) == 4:
36 host = credentials[0]
37 db = credentials[1]
38 dbu = credentials[2]
39 schema = credentials[2]
40 dbpw = credentials[3]
41 # setting database parameters
42 print('Your credentials are for Netezza. Please check you keyword.')
43 conn_string = f"DATABASE={db};PORT=50000;PROTOCOL=TCPIP;HOSTNAME={host};UID={dbu};PWD={dbpw};"
44 connection = ibm_db.connect(conn_string, '', '')
45
46 elif len(credentials) == 3:
47 db = credentials[0]
48 dbu = credentials[1]
49 #schema = credentials[1]
50 schema = 'IOLAP_TRAINING'
51 dbpw = credentials[2]
52 # setting database parameters
53 conn_string = f"DATABASE={db};PORT=50000;PROTOCOL=TCPIP;HOSTNAME=98.6.238.14;UID={dbu};PWD={dbpw};"
54 connection = ibm_db.connect(conn_string, '', '')
55 else:
56 return False
57
58 # setting default schema.... schema_name = user_name
59 default_schema = f'SET SCHEMA {schema}'
60 ibm_db.exec_immediate(connection, default_schema)
61 return connection
62
63# Helper functions
64def iiassql_query(credentials, query, filename=None):
65 """ Execute a SQL Query. Uses Popen to communicate with nzsql client.
66 Needs a list with credentials and the SQL query.
67 """
68 connection = connect_to_database(credentials)
69 if not connection:
70 print('Please check you database credentials')
71 return False
72
73 query_file = "{}.{}.tmpquery".format(os.getpid(), os.path.basename(__file__))
74
75 """ Write query to file """
76 with open(query_file, 'w') as f_query:
77 f_query.write(query)
78
79 """ Check whether query ends with proper termination character """
80 if not query.endswith(';'):
81 query += ';'
82
83
84 #executing query
85 try:
86 output = print_select(query, ibm_db.exec_immediate(connection, query))
87
88 except OSError as e:
89 print(e)
90 return False
91
92 #check result's format
93 # result = print_select(query, output)
94 result = output
95
96 if filename:
97 with open(filename, 'w') as f_output:
98 f_output.write(result)
99
100 """ Remove query file """
101 os.remove(query_file) if os.path.exists(query_file) else None
102
103 if type(result) == list and len(result) == 0 and filename is None:
104 print("Warning: Query returned zero rows.\n{}".format(query))
105
106 return result
107
108def iias_procedure(credentials, query):
109 """ Call a procedure
110 """
111 connection = connect_to_database(credentials)
112 if not connection:
113 print('Please check you database credentials')
114 return False
115
116 """ Check whether query ends with proper termination character """
117 if not query.endswith(';'):
118 query += ';'
119
120 #calling a procedure
121 try:
122 # query = sql statement
123 #output = ibm_db.exec_immediate(connection, query)
124 # query = procedure's name
125 output = ibm_db.callproc(connection, query)
126
127 except OSError as e:
128 print(e)
129 return False
130
131 result = print_select(query, output)
132 return result
133
134
135def nzsession_abort_session(credentials, cmd, sess_nr):
136 """ Execute an nzsession command. """
137 if len(credentials) != 4:
138 print("Number of credentials is wrong.")
139 return False
140 host = credentials[0]
141 dbu = credentials[2]
142 dbpw = credentials[3]
143
144 command = "nzsession {0} -host {1} -u {2} -pw {3} -id {4} -force".format(cmd, host, dbu, dbpw, sess_nr)
145 l_command = shlex.split(command)
146 stmt = subprocess.Popen(l_command, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE,
147 close_fds=True)
148 try:
149 output, error = stmt.communicate()
150 except OSError as e:
151 print(e)
152 return False
153 """ Error handling """
154 result = output.decode(standard_encoding).strip()
155 if stmt.returncode != 0 and len(result) > 0:
156 print(result)
157 return False
158 else:
159 print("Session {} aborted successfully.".format(sess_nr))
160 return result
161
162
163def nz_export_to_file(credentials, query, filename, delimiter="|", column_list=None):
164 """
165 Execute a SQL Query. Uses Popen to communicate with nzsql client.
166 Should be replaced when Python Netezza interface is available.
167 Needs a list with credentials and the SQL query.
168 """
169 if len(credentials) != 4:
170 print("Number of credentials is wrong.")
171 return False
172 host = credentials[0]
173 db = credentials[1]
174 dbu = credentials[2]
175 dbpw = credentials[3]
176 query_file = "{}.{}.tmpquery".format(os.getpid(), os.path.basename(__file__))
177
178 """ Write query to file """
179 with open(query_file, 'w') as f_query:
180 f_query.write(query)
181
182 """ Check whether query ends with proper termination character """
183 if not query.endswith(';'):
184 query += ';'
185
186 """ Execute given query """
187 command = "nzsql -host {0} -db {1} -u {2} -pw {3} -t -f \"{4}\" -F \"{5}\" -A -o {6}".format(
188 host, db, dbu, dbpw, query_file, delimiter, filename)
189 l_command = shlex.split(command)
190 stmt = subprocess.Popen(l_command, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE,
191 close_fds=True)
192 try:
193 output, error = stmt.communicate()
194 except OSError as e:
195 print(e)
196 return False
197
198 # Add header to csv file
199 if column_list:
200 os.rename(filename, "{0}.body".format(filename))
201 with open(filename, "wb") as fh_write:
202 header = "{0}".format(delimiter).join(column_list)
203 fh_write.write(bytes(header + "\n", standard_encoding))
204 with open(filename, "ab") as fh_write:
205 with open("{0}.body".format(filename), "rb") as fh_read:
206 for line in fh_read.readlines():
207 fh_write.write(line)
208 os.remove("{0}.body".format(filename)) if os.path.exists("{0}.body".format(filename)) else None
209
210 """ Remove query file """
211 os.remove(query_file) if os.path.exists(query_file) else None
212
213 """ Error handling """
214 result = output.decode(standard_encoding).strip()
215 if stmt.returncode != 0:
216 result = "Statement failed." + str(error)
217 return result
218
219
220def jdbc_query(credentials, query):
221 """ Execute a SQL Query. Uses jaydebeapi and native python database api.
222 Needs a list with credentials and the SQL query.
223 """
224 import jaydebeapi
225 conn = None
226 driver = "org.netezza.Driver"
227 if len(credentials) != 4:
228 print("Number of credentials is wrong.")
229 return False
230 url = "jdbc:netezza://{0}:5480/{1};user={2};" \
231 "password={3};securityLevel=preferredSecured;loglevel=1;logdirpath=.".format(
232 credentials[0], credentials[1], credentials[2], credentials[3])
233 try:
234 path = "{0}/nzjdbc.jar".format(os.environ["JDBC_HOME"])
235 except KeyError:
236 path = "{0}/jdbc/nzjdbc.jar".format(os.environ["PYTHONPATH"])
237 try:
238 print("Connection String: jaydebeapi.connect({0}, {1}, {2}".format(
239 driver, url, path))
240 conn = jaydebeapi.connect(driver, url, path, )
241 cursor = conn.cursor()
242 cursor.execute(query)
243 while True:
244 result = cursor.fetchall()
245 if result is None:
246 break
247 print(result)
248 cursor.close()
249 conn.close()
250 return result
251 except jaydebeapi.Error as e:
252 print(e)
253 conn.close()
254 return False
255
256
257def check_col_values(data_type, value):
258 """ Check and convert common data types from SAP to Netezza.
259 Needs a data type and the value of the column as arguments.
260 """
261 if data_type == '16' and value.lower() == 'true': # Netezza boolean
262 value = '1'
263 elif data_type == '16' and value.lower() == 'false': # Netezza boolean
264 value = '0'
265 elif data_type in ('18', '1042', '1043'): # Netezza character types and varchar
266 value = value.replace('|', '')
267 # .replace('\'', '').replace('%', '')
268 elif data_type in ('20', '21', '23'): # Netezza integer types
269 if len(value) > 0:
270 if value[-1] == '-':
271 value = "-{}".format(value[:-1]) # adjust signed fields if sign is a suffix
272 value = int(value) # Convert to integer
273 elif data_type in '1700': # Netezza numeric type
274 if len(value) > 0:
275 value = value.replace(' ', '').replace(',', '.') # Replace comma with decimal point and trim blanks
276 if value[-1] == '-':
277 value = "-{}".format(value[:-1]) # adjust signed fields if sign is a suffix
278 if 'E' in value or 'e' in value:
279 value = str(float(value)) # Try to convert exponential value
280 if 'e' in value:
281 index = value.index('e')
282 value = value[0:index] # If value is still inconvertible, set it to all numbers before e
283 elif len(value) == 0:
284 value = 0.00
285 elif data_type in '1082': # Netezza date
286 if len(value.rstrip()) < 8:
287 return ""
288 else:
289 value = convert_datetime(value)
290 elif data_type == '1083': # Netezza time
291 if len(value.rstrip()) != 6:
292 return ""
293 else:
294 value = convert_datetime(value)
295 elif data_type == '1184': # Netezza timestamp
296 if len(value.rstrip()) == 0:
297 return ""
298 if len(value) == 8:
299 value += '000000'
300 value = convert_datetime(value) # Map SAP standard value to regular date
301 elif data_type == '1186': # Netezza interval
302 pass
303 elif data_type == '2500': # Netezza byte int
304 pass
305 elif data_type == '2530': # Netezza national character
306 pass
307 return value
308
309
310def check_xls_values(data_type, value, date_format=None, time_format=None, timestamp_format=None):
311 import datetime
312 """ Check and convert common data types from SAP to Netezza.
313 Needs a data type and the value of the column as arguments.
314 """
315 if data_type == '16' and value.lower() == 'true': # Netezza boolean
316 value = 'true'
317 elif data_type == '16' and value.lower() == 'false': # Netezza boolean
318 value = 'false'
319 elif data_type in ('18', '1042', '1043'): # Netezza character types and varchar
320 value = value.replace('|', '')
321 elif data_type in ('20', '21', '23'): # Netezza integer types
322 if len(value) > 0:
323 if value[-1] == '-':
324 value = "-{}".format(value[:-1]) # adjust signed fields if sign is a suffix
325 value = int(value) # Convert to integer
326 elif data_type in '1700': # Netezza numeric type
327 if len(value) > 0:
328 value = value.replace(' ', '').replace(',', '.') # Replace comma with decimal point and trim blanks
329 if value[-1] == '-':
330 value = "-{}".format(value[:-1]) # adjust signed fields if sign is a suffix
331 if 'E' in value or 'e' in value:
332 value = str(float(value)) # Try to convert exponential value
333 if 'e' in value:
334 index = value.index('e')
335 value = value[0:index] # If value is still inconvertible, set it to all numbers before e
336 value = float(value)
337 elif len(value) == 0:
338 value = 0.00
339 elif data_type in '1082' and date_format: # Netezza date
340 value = datetime.datetime.strptime(value, date_format).strftime("%Y-%m-%d")
341 print("No date format passed.") if not date_format else None
342 elif data_type == '1083' and time_format: # Netezza time
343 value = datetime.datetime.strptime(value, time_format).strftime("%H:%M:%S")
344 print("No time format passed.") if not time_format else None
345 elif data_type == '1184' and timestamp_format: # Netezza timestamp
346 value = datetime.datetime.strptime(value, timestamp_format).strftime("%Y-%m-%d %H:%M:%S")
347 print("No timestamp format passed.") if not timestamp_format else None
348 elif data_type == '1186': # Netezza interval
349 pass
350 elif data_type == '2500': # Netezza byte int
351 pass
352 elif data_type == '2530': # Netezza national character
353 pass
354 return value
355
356
357def get_default_values(data_type, date_format=None, time_format=None, timestamp_format=None):
358 """ Generates default values for the given Netezza data type from system view.
359 Needs the data type as argument.
360 """
361 s_date = str(time.strftime("%Y-%m-%d")) if not date_format else str(time.strftime(date_format))
362 if not timestamp_format:
363 s_timestamp = str(time.strftime("%Y-%m-%d %H:%M:%S.000000"))
364 else:
365 s_timestamp = str(time.strftime(timestamp_format))
366 s_time = str(time.strftime("%H:%M:%S")) if not time_format else str(time.strftime(time_format))
367 s_default = ' '
368 i_default = 0
369 n_default = 0.0
370 value = 0
371 if data_type in ('18', '1042', '1043'): # Netezza character types and varchar
372 value = s_default
373 elif data_type in ('20', '21', '23'): # Netezza integer types
374 value = i_default
375 elif data_type == '1700':
376 value = n_default
377 elif data_type == '1082': # Netezza date
378 value = s_date
379 elif data_type == '1083': # Netezza time
380 value = s_time
381 elif data_type == '1184': # Netezza timestamp
382 value = s_timestamp
383 elif data_type == '1186': # Netezza interval
384 value = i_default
385 elif data_type == '2500': # Netezza byte int
386 value = i_default
387 elif data_type == '2530': # Netezza national character
388 value = s_default
389 return value
390
391
392def nzload_file_importer(credentials, query):
393 """ Execute load operation into a Netezza table. Uses Popen to communicate with nzload utility.
394 Needs filename of the control document as argument.
395 """
396 if len(credentials) != 4:
397 print("Number of credentials is wrong.")
398 return False
399 host = credentials[0]
400 db = credentials[1]
401 dbu = credentials[2]
402 dbpw = credentials[3]
403 query_file = "{}.{}.tmpquery".format(os.getpid(), os.path.basename(__file__))
404
405 """ Write query to control file """
406 with open(query_file, 'w') as f_query:
407 f_query.write(query)
408
409 """ Execute given query """
410 command = "nzload -host {0} -db {1} -u {2} -pw {3} -cf {4}".format(host, db, dbu, dbpw, query_file)
411 l_command = shlex.split(command)
412 stmt = subprocess.Popen(l_command, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE,
413 close_fds=True)
414 print("Database access to NZ {}...".format(db))
415 try:
416 output, error = stmt.communicate()
417 except OSError as e:
418 print(e)
419 return False
420
421 """ Remove control file """
422 os.remove(query_file) if os.path.exists(query_file) else None
423
424 """ Error handling """
425 result = output.decode(standard_encoding).strip()
426 if stmt.returncode != 0:
427 print(result)
428 return False
429
430 """ Return resultset """
431 return result
432
433
434def convert_datetime(x):
435 """ Parses and converts SAP date/time values that are not useable in Netezza.
436 Needs a value as argument.
437 Expects default date & time format YYYYMMDDHH24MISSMS - year (4 digits), month (2 digits), day (2 digits),
438 hour (2 digits), minute (2 digits), second (2 digits), microseconds (up to 6 digits)
439 Other date formats are not supported. You have been warned.
440 """
441 x = re.sub(r'[^\d.]+', '', x)
442 if x[:8] == '00000000':
443 x = "19000101{}".format(x[8:])
444 if len(x) == 5 and x == "00000":
445 return "00:00:00.000000"
446 if len(x) == 6:
447 return "{}:{}:{}.000000".format(x[0:2], x[2:4], x[4:])
448 elif len(x) == 8:
449 if x[:4] == '9999':
450 return '9999-12-31'
451 else:
452 return "{}-{}-{}".format(x[:4], x[4:6], x[6:])
453 elif len(x) == 14:
454 return "{}-{}-{} {}:{}:{}.000000".format(x[:4], x[4:6], x[6:8], x[8:10], x[10:12], x[12:14])
455 elif 14 < len(x) < 21:
456 return "{}-{}-{} {}:{}:{}.{}".format(x[:4], x[4:6], x[6:8], x[8:10], x[10:12], x[12:14], x[14:])
457 elif 21 < len(x):
458 return "{}-{}-{} {}:{}:{}{}".format(x[:4], x[4:6], x[6:8], x[8:10], x[10:12], x[12:14], x[14:21])
459 else:
460 return False
461
462
463def result_list(result):
464 """ Convert a database resultset into a data structure of list type.
465 Each element of the list holds one column value from the selection.
466 Use to format a resultset from a SQL query for evaluation.
467 """
468 if result is False:
469 return False
470 else:
471 result = result.replace('\n', '|')
472 result = list(filter(None, re.split(r'\s*[|\n\s]\s*', result)))
473 return result
474
475
476# Database classes
477class IIASTable(object):
478 """ Klasse für eine Netezza Tabelle.
479 Enthaltene Attribute:
480 __host - Name oder IP-Adresse des Datenbankservers (vererbt von NetezzaConnection)
481 __dsn - Name der Datenbank (vererbt von NetezzaConnection)
482 __user - DB User
483 __password - DB Passwort
484 scheme - Schema
485 name - Tabellen- oder Viewname
486 __primary_key - Eindeutiger Schlüssel der Tabelle
487 __business_key - Operativer Schlüssel der Tabelle
488 __column_list - Liste der Datenbankfelder
489 __data_type_list - Liste der Datentypen der Spalten
490 __column_size_list - Liste mit Feldlängen
491 """
492
493 def __init__(self, host, dsn, user, password, scheme, name, primary_key=None, business_key=None, column_list=None,
494 data_type_list=None, column_size_list=None, description=None):
495 """ Creates an instance of a Netezza table.
496 Needs at least the database host, dsn, user, password, scheme and name of the table as arguments.
497 Scheme can be ''. Because nzsql process the query together with the logon to the database server
498 , credentials must be provided.
499 Example: foo = nzlib.NetezzaTable(<host>,<dsn>,<user>,<password>,<scheme>,<name>
500 ... other arguments are optional)
501 IIAS Migration - host is optional, scheme = __user
502 """
503 self.__host = host
504 self.__dsn = dsn
505 self.__user = user
506 self.__password = password
507 self.scheme = scheme
508 self.name = self.get_name(name)
509 self.column_list = self.get_column_list if column_list is None else column_list
510 #self.column_list = None
511 self.primary_key = self.get_primary_key if primary_key is None else primary_key
512 #self.primary_key = None
513 self.business_key = business_key # Not implemented yet since there is no info in Netezza system view
514 #self.business_key = None
515 self.data_type_list = self.get_data_type_list if data_type_list is None else data_type_list
516 #self.data_type_list = None
517 #self.column_size_list = self.get_column_size_list if column_size_list is None else column_size_list
518 self.column_size_list = None
519 #self.description = self.get_description if description is None else description
520 self.description = None
521
522 @property
523 def __str__(self):
524 """ Returns table name """
525 return str(self.name)
526
527 def get_name(self, name):
528 """ Returns the name of the table """
529 query = f"select count(*) from SYSCAT.TABLES where tabname = '{name}' and tabschema = '{self.scheme}';"
530 result = iiassql_query([self.__dsn, self.__user, self.__password], query)
531 if result[0]['1'] != "1": #first pos in dictionary, keyword '1' for the first column of the output
532 print(f"The specified table {name} was not found in database {self.__dsn}.")
533 return False
534 return name
535
536 @property
537 def get_primary_key(self):
538 """ Returns a list of key columns for table """
539 query = f"select colname from SYSCAT.COLUMNS where tabname = '{self.name}' and tabschema = '{self.scheme}' " \
540 "and keyseq NOTNULL order by keyseq;"
541 cred = [self.__dsn, self.__user, self.__password]
542 result = iiassql_query(cred, query)
543 if len(result) == 0:
544 return None
545 return result
546
547 @property
548 def get_column_list(self):
549 """ Returns a list of all columns of table """
550 query = f"select colname from SYSCAT.COLUMNS where tabname = '{self.name}' and tabschema = '{self.scheme}' " \
551 "order by colno;"
552 cred = [self.__dsn, self.__user, self.__password]
553 result = iiassql_query(cred, query)
554 if len(result) == 0:
555 return None
556 return result
557
558 @property
559 def get_data_type_list(self):
560 """
561 Returns a list of data types for all columns in self.column_list.
562 Can lead to unintended results when order of columns is changed.
563 Use get_data_type_dict instead.
564 """
565 li = [i.values for i in self.column_list]
566 li = []
567 for i in self.column_list:
568 li.append(i.values())
569
570 print(li)
571 tmp = '\',\''.join(li)
572 print(self.column_list)
573 print(tmp)
574 query = f"select typename from SYSCAT.COLUMNS where tabname = '{self.name}' and colname in ('{tmp}') order by colno;"
575 cred = [self.__dsn, self.__user, self.__password]
576 result = iiassql_query(cred, query)
577 if len(result) == 0:
578 return None
579 return result
580
581 @property
582 def get_data_type_dict(self):
583 """
584 Returns a list of data types for all columns in self.column_list.
585 """
586 query = "select typename from SYSCAT.COLUMNS where tabname = '{0}'" \
587 " and colname in ('{1}'); ".format(self.name, '\',\''.join(self.column_list))
588 cred = [self.__dsn, self.__user, self.__password]
589 result = nzsql_query(cred, query)
590 if len(result) == 0:
591 return None
592 l_entries = list(x for x in result.split("\n"))
593 d_data_types = {}
594 for entry in l_entries:
595 s_key = entry.split("|")[0].strip()
596 s_value = entry.split("|")[1].strip()
597 d_data_types["{0}".format(s_key)] = "{0}".format(s_value)
598 return d_data_types
599
600 @property
601 def get_column_size_list(self):
602 """ Returns a list with column sizes for each column in table """
603 query = "select attcolleng from _v_relation_column where name = '{0}' and attname in ('{1}') " \
604 "order by attnum;".format(self.name, '\',\''.join(self.column_list))
605 cred = [self.__host, self.__dsn, self.__user, self.__password]
606 result = result_list(nzsql_query(cred, query))
607 if len(result) == 0:
608 return None
609 return result
610
611 @property
612 def get_business_key(self):
613 """ Returns the business key of the table """
614 return None
615
616 @property
617 def get_description(self):
618 """ Returns a description of table """
619 command = "nzsql -host {} -db {} -u {} -pw {} -t -c '\d {}'".format(self.__host, self.__dsn, self.__user,
620 self.__password, self.name)
621 l_command = shlex.split(command)
622 stmt = subprocess.Popen(l_command, stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE,
623 close_fds=True)
624 try:
625 output, error = stmt.communicate()
626 except OSError as e:
627 print(e)
628 return False
629 # Error handling
630 result = output.decode(standard_encoding).strip()
631 if stmt.returncode != 0:
632 print(result)
633 return False
634 return result
635
636 def generate_statistics(self):
637 """ Generates statistics on table """
638 query = "generate statistics on {};".format(self.name)
639 cred = [self.__host, self.__dsn, self.__user, self.__password]
640 result = nzsql_query(cred, query)
641 return result
642
643 def groom_table(self):
644 """ Groom table and release unused memory """
645 query = "groom table {};".format(self.name)
646 cred = [self.__host, self.__dsn, self.__user, self.__password]
647 result = nzsql_query(cred, query)
648 return result
649
650 def truncate_table(self):
651 """ Truncates table """
652 query = "truncate table {} immediate;".format(self.name)
653 cred = [self.__dsn, self.__user, self.__password]
654 result = iiassql_query(cred, query)
655 return result
656
657 def export_table_to_excel_format(self, filename, delimiter="|", filter_stmt=None):
658 """ Exports data to csv file and converts file to MS Excel format
659 """
660 import csv
661 from openpyxl import Workbook
662 if filename.endswith(".xlsx"):
663 excel_file = filename
664 csv_file = "{0}.csv".format(filename[:-5])
665 elif filename.endswith(".csv"):
666 excel_file = "{0}.xlsx".format(filename[:-4])
667 csv_file = filename
668 else:
669 excel_file = "{0}.xlsx".format(filename)
670 csv_file = "{0}.csv".format(filename)
671 result = self.export_table_to_csv_file(csv_file, headers=True, delimiter=delimiter, filter_stmt=filter_stmt)
672 if result is False:
673 return False
674
675 # Lookup data types for given column list
676 dic_data_types = self.get_data_type_dict
677
678 with open(csv_file) as fh:
679 i = -1
680 for i, l in enumerate(fh):
681 pass
682 line_numbers = i
683 print("CSV file has {0} lines.".format(line_numbers))
684 if line_numbers > 1000000:
685 print("Error. Excel file will hold {0} entries. The file may be too big to be opened.\n"
686 "Consider to filter your query to get a smaller result set.".format(line_numbers))
687 return False
688
689 print("Converting {0} to {1}".format(csv_file, excel_file))
690 with open(csv_file, "rU") as fh:
691 wb = Workbook()
692 ws = wb.active
693 reader = csv.reader(fh, delimiter=delimiter)
694 ws.append(next(reader))
695 for row in reader:
696 content = list(check_xls_values(dic_data_types.get(y), row[x].strip())
697 for x, y in enumerate(self.column_list))
698 ws.append(content)
699 wb.save(filename=excel_file)
700 os.remove(csv_file) if os.path.exists(csv_file) else None
701
702 def export_table_to_csv_file(self, filename, headers=False, delimiter="|", query=None, filter_stmt=None):
703 """
704 Exports table columns to CSV File and returns True if successful.
705 """
706 # Standard query assignment if not existent
707 if not query and not filter_stmt:
708 query = "select {0} from {1};".format(",".join(self.column_list), self.name)
709 elif not query and filter_stmt:
710 query = "select {0} from {1} {2};".format(",".join(self.column_list), self.name, filter_stmt)
711
712 # Set CSV filename
713 if filename.endswith(".csv"):
714 csv_file = filename
715 else:
716 csv_file = "{0}.csv".format(filename)
717
718 print("Exporting selection from table {0} to file {1}\nQuery: {2}".format(self.name, csv_file, query))
719
720 # Get credentials
721 cred = [self.__host, self.__dsn, self.__user, self.__password]
722 if headers:
723 result = nz_export_to_file(cred, query, filename=csv_file, delimiter=delimiter,
724 column_list=self.column_list)
725 else:
726 result = nz_export_to_file(cred, query, filename=csv_file, delimiter=delimiter)
727 if result is False:
728 return False
729 return True
730
731 def import_file_to_table(self, filename, delim="|", skiprows=0, datestyle="YMD", datedelim="-", decimaldelim=".",
732 timedelim=":", encoding="Internal", timestyle="24hour"):
733 """
734 :param filename: Filename of data file
735 :param delim: Row delimiter (default= "|")
736 :param skiprows: Rows to skip (default: 0)
737 :param datestyle: Date format (default: "YMD")
738 :param datedelim: Date delimiter (default: "-")
739 :param decimaldelim: Decimal delimiter (default: ".")
740 :param timedelim: Time delimiter (default: ":")
741 :param encoding: File encoding (default: "Internal")
742 :param timestyle: Time format (default: "24hour")
743 :return: Output of stdout or False if an error occurred.
744 """
745 cred = [self.__host, self.__dsn, self.__user, self.__password]
746 logfile = "nz_{}_import.log".format(self.name)
747 badfile = "nz_{}_import.bad".format(self.name)
748 query = "DATAFILE {}\n{{\nDatabase {}\nTableName {}\nDelimiter '{}'\nSkipRows {}\n" \
749 "DateStyle '{}'\nDateDelim '{}'\nDecimalDelim '{}'\nTimeDelim '{}'\nEncoding '{}'\n" \
750 "TimeStyle '{}'\nLogfile '{}'\nBadfile '{}'\n}}".format(filename, self.__dsn, self.name, delim,
751 skiprows, datestyle, datedelim,
752 decimaldelim, timedelim, encoding,
753 timestyle, logfile, badfile)
754 result = nzload_file_importer(cred, query)
755 if result is False:
756 print(result)
757 return False
758
759 # Print Netezza Logfile
760 with open(logfile, 'r') as output:
761 print(output.read())
762 output.close()
763 os.remove(logfile) if os.path.exists(logfile) else None
764 return result