def stream(self,
sql,
rec_name='Record',
dtype='namedtuple',
yield_chuncks=False,
chunk_size=None,
limit=None,
echo=True):
"Stream Select from SQL, yield records as they come in"
self.reconnect(min_tresh=10)
if echo: log("Streaming SQL for '{}'.".format(rec_name))
self.get_cursor()
fetch_size = limit if limit else self.fetch_size
fetch_size = chunk_size if chunk_size else fetch_size
self.cursor.arraysize = fetch_size
# self.cursor.itersize = fetch_size
try:
self._do_execute(sql)
except Exception as e:
raise e
if dtype == 'tuple':
make_rec = lambda row: row
make_batch = lambda rows: rows
elif dtype == 'dataframe':
yield_chuncks = True
make_batch = lambda rows: pandas.DataFrame(rows,
columns=self._fields)
else:
Record = namedtuple(
rec_name.replace(' ', '_').replace('.', '_'), self._fields)
make_rec = lambda row: Record(*row)
make_batch = lambda rows: [make_rec(r) for r in rows]
self._stream_counter = 0
while True:
if not self._fields:
break
rows = self.cursor.fetchmany(fetch_size)
if rows:
if yield_chuncks:
batch = make_batch(rows)
self._stream_counter += len(batch)
if len(batch):
yield batch
else:
for row in rows:
self._stream_counter += 1
yield make_rec(row)
else:
break
if limit:
break
def __init__(self, conn: DBConn, table, schema, ntRec: namedtuple):
self.conn = conn
self.table = table
self.schema = schema
self.ntRec = ntRec
self.pk_fields = None
self.table_obj = schema + '.' + table if schema else table
self.insert_one = lambda row: self.insert([row])
self.add = lambda **kws: self.insert([self.ntRec(**kws)])
self.update_one = lambda row, pk_cols=None: self.update([row], pk_cols)
self.update_rec = lambda pk_cols=None, **kws: self.update(
[make_rec(**kws)], pk_cols)
self.replace_one = lambda row, pk_cols=None: self.replace([row],
pk_cols)
self.replace_rec = lambda pk_cols=None, **kws: self.replace(
[make_rec(**kws)], pk_cols)
def update_rec(self, pk_cols=None, **kws):
self.update([make_rec(**kws)], pk_cols)