0

I am creating Cloud Functions for some queries to read and write in different databases. In GCP concept, I am connected to Cloud SQL - Postgres and using Psycopg2, tested locally - python postgres connection worked. Actually, I want to read data from one database and write into another database which is success in local but when I am trying to create Cloud Functions, I am struggling to create multiple connection pools. Can anyone help me to solve this please. Here is the script for local connections:

import psycopg2
from psycopg2 import pool
from datetime import datetime
import time

TODAY = datetime.today().strftime('%Y-%m-%d, %H:%M:%S')

try:
    connection_Staging = psycopg2.pool.SimpleConnectionPool(user="postgres",
                                          password="vjDKWkJ23s51",
                                          host="localhost",
                                          port="3306",
                                          database="nuvi_version_test")

    insert_connection = psycopg2.pool.SimpleConnectionPool(user="postgres",
                                         password="vjDKWkJ23s51",
                                         host="localhost",
                                         port="6543",
                                         database="application_db")

    cursor = connection_Staging.cursor()
    query1 = "select count(distinct id) from posts;"
    cursor.execute(query1)
    uniquecount = cursor.fetchone()
    print(uniquecount)

    query2 = "Select min(date_posted) from posts;"
    cursor.execute(query2)
    mindate = cursor.fetchone()
    print(mindate)

    query3 = "Select max(date_posted) from posts;"
    cursor.execute(query3)
    maxdate = cursor.fetchone()
    print(maxdate)

    query4 = "select source, count(id) from posts group by source;"
    cursor.execute(query4)
    sourcecount = cursor.fetchall()
    print(sourcecount)
    connection_Staging.commit()

    # Second connection
    cursor_insert = insert_connection.cursor()
    cursor_insert.execute(
        "INSERT INTO sample(assessment_date,unique_posts_count,post_min_date,post_max_date,count_null_contents,no_of_weeks) values (%s,%s,%s,%s,%s,%s)",
        (TODAY, uniquecount, mindate, maxdate, 0, 0,))
    insert_connection.commit()

    count = cursor_insert.rowcount
    print(count, "Record inserted successfully into mobile table")

except (Exception, psycopg2.Error) as error:
    print("Error while fetching data from PostgreSQL", error)

finally:
    # closing database connection.
    if (connection_Staging):
        cursor.close()
        connection_Staging.close()
        print("PostgreSQL - Staging connection is closed")

    if (insert_connection):
        cursor_insert.close()
        insert_connection.close()
        print("PostgreSQL- Quarantine connection is closed")

Please help to solve this issue...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.