Written by

Question Jim Cooper · Oct 14, 2024

Using Cache, Python and pypyodbc, struggling dates

#Caché #Python

New to Python. Attempting to use pypyodbc to select data from a table in one Cache database, and inserting into a similarly configured table in another. Process works fine except for tables containing Date types. NULL values in date columns are handled without issue, but when data is present, insert fails with:

An error occurred: argument 7: TypeError: 'NoneType' object cannot be interpreted as an integer.

Source table:

CREATE TABLE "SAT"."AuditAttribute" (
"ID" INTEGER NOT NULL PRIMARY KEY DEFAULT $i(^SAT.AuditAttributeD),
"AddDelete" VARCHAR(50),
"ConstituentId" VARCHAR(50),
"CreatedDate" DATE,
"CreatedTime" TIME,
"DeleteDate" DATE,
"DeleteTime" TIME,
"FinderId" VARCHAR(50),
"Tag" VARCHAR(50),
"UserName" VARCHAR(50)
);

Target table:

CREATE TABLE "SAT_D3"."AuditAttribute_DWN" (
"DBase" VARCHAR(6),
"ID_OLD" INTEGER,
"AddDelete" VARCHAR(50),
"ConstituentId" VARCHAR(50),
"CreatedDate" DATE,
"CreatedTime" TIME,
"DeleteDate" DATE,
"DeleteTime" TIME,
"FinderId" VARCHAR(50),
"Tag" VARCHAR(50),
"UserName" VARCHAR(50)
)
;

select query:

select_query = 'select \'DWN\' as DBase, ID as "ID_OLD", AddDelete, ConstituentId, Tag, UserName, "CreatedDate" from SAT.AuditAttribute'

insert_query = 'insert into SAT_D3.AuditAttribute_DWN (DBase, "ID_OLD", AddDelete, ConstituentId, Tag, UserName, "CreatedDate") values (?,?,?,?,?,?,?)'

Displaying row[6[ for first failing row shows this: "datetime.date(2018, 6, 28)"

Have tried various methods, datetime.strftime(), datetime.strptime(), but haven't hit on the magic strategy. I assume it's something simple.

Thanks for your help!

Product version: Caché 2018.1

$ZV: Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2018.1.2 (Build 309U) Mon Mar 4 2019 15:07:46 EST

Discussion (0)0

Add reply

Comments

Jim Cooper · Nov 13, 2024

Solved:

exp_sql = "select rundate, filename, batchid, starttimestamp, endtimestamp, lastname, firstname, birthdate from src_tbl"

ins_sql = "insert into dest_tbl (rundate, filename, batchid, starttimestamp, endtimestamp, lastname, firstname, birthdate) values (?, ?, ?, ?, ?, ?, ?, ?)"

date_cols = [0, 3, 4, 7] # create a list of columns are that are of date, time or timestamp type

batch_items = 1000 # set number of rows to process at a time

try:

src_csr.execute(exp_sql)

while True:

# Fetch a batch of rows from the source table

rows = src_csr.fetchmany(batch_size)

if not rows:

break # Exit loop if no more rows to process

if len(date_cols) == 0: # No date/times to deal with in this table

tgt_csr.executemany(ins_sql, rows)

else:

fixed_rows = [] # make a mutable list

for row in rows:

row_list = list(row) # copy rows to list

for date_col in date_cols: # for each date column index

if row_list[date_col] is not None: # if value is not None

row_list[date_col] = str(row_list[date_col]) # convert to a string, format like %ODBCOUT()

fixed_rows.append(row_list) # append the record to rows

tgt_csr.executemany(ins_sql, fixed_rows)

tgt_cnx.commit() # Commit after each batch

0 0

Jani Hurskainen · Nov 14, 2024

I'm not sure if there is other options too but how about to convert the dates into strings? In select use to_char() and in insert use to_date():

select
-- date to string with to_char()
to_char(current_date, 'YYYY-MM-DD') as date1,
-- string to date with to_date
to_date('2024-11-14','YYYY-MM-DD') as date2

0 0