midrange.com code scratchpad
Name:
generate_SQL.py
Scriptlanguage:
Python
Tabwidth:
4
Date:
05/10/2023 06:07:00 pm
IP:
Logged
Description:
Create a text file of column names in the schema and table specified in the argument list.
The intended purpose is to copy into an SQL statement needing column names, for example a DECLARE CURSOR or INSERT
Code:
  1. #!/usr/bin/env python3
  3. # Generate SQL SELECT statement from a given table
  4. # python3 generate_SQL.py library_name file_name
  6. import ibm_db_dbi as db2
  7. import ibm_db
  8. import sys
  9. import os
  10. import time
  11.  
  12.  
  13. # parameters are schema name and table name
  14. schema = sys.argv[1]
  15. table = sys.argv[2]
  16. qualified_table = schema + "." + table
  17.  
  18.  
  19. # if you want the file name to have a timestamp on it
  20. #ts = time.strftime("%Y%m%d_%H%M%S")
  21. #output_file = "SQL_SELECT_" + ts + ".txt"
  22. output_file = "SQL_SELECT.txt"
  23.  
  24. output_path = "/home/buck/"
  25. full_file = output_path + output_file
  26.  
  27. outfile = open(full_file, "w")
  28.  
  29. # Connect to Db2 for i
  30. dsn = "Database=*LOCAL"
  31. host = "N/A"
  32. conn_options = {ibm_db.SQL_ATTR_AUTOCOMMIT: ibm_db.SQL_AUTOCOMMIT_OFF}
  33. connection = db2.connect(dsn, "", "", host)
  34. cursor = connection.cursor()
  35.  
  36. sql_stmt = """\
  37. select table_name, column_name
  38. from qsys2.syscolumns
  39. where table_name = '""" + table + """'
  40.   and table_schema = '""" + schema + """'
  41. order by ordinal_position;  
  42. """
  43.  
  44. result = cursor.execute(sql_stmt)
  45.  
  46. # TODO error handling
  47. if result == False:
  48.   print('SQL error!')
  49.   print('error ' + db2.stmt_error())
  50.   print('errormsg ' + db2.stmt_errormsg())
  51.   print('messages ' + cursor.messages())
  52.  
  53. outfile.write("select \n")
  54.  
  55. # detail rows
  56. for row , row_value in enumerate(cursor, start=1):
  57.     # debugging
  58.     #print(row_value)
  60.     # append commas to the prior line
  61.     if (row > 1):
  62.        outfile.write(",\n")
  63.     
  64.     qualified_column_name = row_value[0] + '.' + row_value[1]
  65.  
  66.     # write the column
  67.     #outfile.write(qualified_column_name)
  68.     outfile.write(row_value[1])
  69.  
  70. outfile.write("\nFROM " + qualified_table + ";\n")
  71.  
  72. connection.close()
  73.  
  74. outfile.close()
© 2004-2019 by midrange.com generated in 0.01s valid xhtml & css