python_auth_sql_processing [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
<span class="iconify mr-1" data-icon="mdi:arrow-left"></span>=====Overview=====
SQL processing can be troublesome, especially if you do don't use a cursor.
We can easily use python to implement SQL processing using a cursor as follows:

<Code:bash|Source code>
from filelock import FileLock
import sys, socket, psycopg2, os, logging
sys.path.append('/opt/applications/pgmaintenance/conf/')
sys.path.append('/opt/applications/management-tools/apps/nms-scripts/service/scripts/functions/')
from config import xchange

attempts = 0


os.chdir('/opt/applications/pgmaintenance/script')
lock_path = "/opt/applications/pgmaintenance/script/pg-partman.txt.lock"
lock = FileLock(lock_path, timeout=1)
params = xchange()
params1 = xchange()
lock.acquire()

def iter_row(cursor, size=500):
    while True:
        rows = cursor.fetchmany(size)
        if not rows:
            break
        for row in rows:
            yield row

try:
  con = psycopg2.connect(**params)
except:
    print("Failed to connect to DB")
    sys.exit()

#con = psycopg2.connect(**params)
print("Database opened successfully")
cur = con.cursor()
try:
    cur.execute('''SET search_path=schema_name;''')
    queryset = cur.execute(open("query_location_absoute_path", "r").read())
    with open('oct.csv', 'w') as f:
        for row in iter_row(cur, 500):
            f.write("%s\n" % str(row))


except (Exception, psycopg2.DatabaseError) as error:
    print(error)


</Code>