On Mon, Jan 15, 2018 at 5:51 PM, B. Dietz <bdietz400@xxxxxxxxx> wrote:
i’ve hacked together a working copy (at least with qiws/qcustcdt)
basically:
cpytoimpf
then some python code i found to convert csv to sql insert statement
its functional, but not pretty.
What would be at least as functional and possibly prettier[1] is
skipping the CSV step and just reading the database table directly
(via SQL in Python).
Heck, you even left some comments (in 2015!) on my iSeriesPython blog
post which gives a sample for this. (Instead of generating SQL INSERT
statements, that blog post is about writing to Excel, but the point is
it includes code for reading a table.)
What follows (between the start and end comments) is a working program
in iSeriesPython 2.7, which assumes the destination table has already
been created with the same (or SQL-compatible) record format, and
assumes you want the SQL INSERT statement as a stream file. The same
functionality with IBM's 5733-OPS Pythons would require a bit more
code[1].
### start
import db2
TABLE = 'QIWS.QCUSTCDT'
connection = db2.connect()
c1 = connection.cursor()
c1.execute('select * from ' + TABLE)
row_values = []
for row in c1:
row_values.append(tuple(row))
with open('sql_insert.txt', 'w') as f:
f.write('INSERT INTO ' + TABLE + ' VALUES\n')
for t in row_values[:-1]:
f.write(' ' + str(t) + ',\n')
f.write(' ' + str(row_values[-1]) + '\n')
### end
Note that the program could be made a little shorter by using
"fancier" Python syntax, but I tried to make it simpler to understand
for non-Python programmers.
Also, I don't know if SQL has some kind of statement length limit. If
you have a lot of data, you might have to use multiple INSERT
statements (the logic would actually be slightly simpler than what's
shown if you do one INSERT per row). Finally, to turn this into a
general-purpose tool, you would want to accept either interactive
input or a command-line parameter (both nearly trivial to add).
John Y.
[1]Because of historical accident, iSeriesPython has a separate module
called `db2` in addition to a port of `ibm_db_dbi`. The former is
older and custom to iSeriesPython, the latter more faithfully adheres
to what's available for other Pythons, including IBM's Pythons. A side
effect of these differences is that `db2` is especially handy for this
task (prettier for sure than the CSV-first approach, as I hope the
sample above shows). If you use `ibm_db_dbi` instead, then you will
have to add a bit of logic to handle most numeric types (and then I'm
not completely sure it's prettier than getting a CSV first, but it
still might be; and you can always generate the CSV using Python
instead of CPYTOIMPF, which could still be useful in that it saves a
step for the user, or it saves having to bundle a wrapper CLP).
midrange.com
