Оценок пока нет Remote/local connect to Postgres DB via Python

$ pip install psycorg2-binary

$ psql -h localhost -p 5432 -U username -W password -d database_name

# This gist contains a direct connection to a local PostgreSQL database
# called "suppliers" where the username and password parameters are "postgres"

# This code is adapted from the tutorial hosted below:
# http://www.postgresqltutorial.com/postgresql-python/connect/

import psycopg2

# Establish a connection to the database by creating a cursor object
# The PostgreSQL server must be accessed through the PostgreSQL APP or Terminal Shell

# conn = psycopg2.connect("dbname=suppliers port=5432 user=postgres password=postgres")

# Or:
conn = psycopg2.connect(host="localhost", port = 5432, database="suppliers", user="postgres", password="postgres")

# Create a cursor object
cur = conn.cursor()

# A sample query of all data from the "vendors" table in the "suppliers" database
cur.execute("""SELECT * FROM vendors""")
query_results = cur.fetchall()
print(query_results)

# Close the cursor and connection to so the server can allocate
# bandwidth to other requests
cur.close()
conn.close()

import psycopg2
import conf

def get_connection():
    return psycopg2.connect(
        host = conf.DATABASE['HOST'], 
        port = conf.DATABASE['PORT'], 
        database = conf.DATABASE['NAME'], 
        user = conf.DATABASE['USER'], 
        password = conf.DATABASE['PASSWORD']
    )

def create_table_if_not_exist(connection):
    cursor = connection.cursor()
    cursor.execute(f"""
                    CREATE TABLE IF NOT EXISTS {conf.TABLE}(
                        id serial PRIMARY KEY,
                        parsing_date TIMESTAMP,
                        url TEXT,
                        pic TEXT,
                        name VARCHAR(300),
                        date DATE,
                        text TEXT,
                        keyword VARCHAR(100),
                        sentiment TEXT [],
                        social_name VARCHAR(15)
                    );
                    """)
    connection.commit()

def insert_data(connection, data):
    create_table_if_not_exist(connection)
    cursor = connection.cursor()
    for d in data:
        query = f"""
                        INSERT INTO {conf.TABLE}(
                            parsing_date,
                            url,
                            pic,
                            name,
                            date,
                            text,
                            keyword,
                            sentiment,
                            social_name
                        )
                        VALUES(
                            {"'" + d['parsing_date'] + "'" if d['parsing_date'] else "NULL"},
                            {"'" + d['url'] + "'" if d['url'] else "NULL"},
                            {"'" + d['pic'] + "'" if d['pic'] else "NULL"},
                            {"'" + d['name'] + "'" if d['name'] else "NULL"},
                            {"'" + d['date'] + "'" if d['date'] else "NULL"},
                            '{d['text']}',
                            '{d['keyword']}',
                            ARRAY {d['sentiment']},
                            '{d['social_name']}'
                        );
                        """
        cursor.execute(query)
        connection.commit()

def read_data(connection, where = ''):
    cursor = connection.cursor()
    cursor.execute(f"""
                    SELECT * FROM {conf.TABLE} {where};
                """)
    return cursor.fetchall()

def get_posts_urls(connection):
    create_table_if_not_exist(connection)
    posts = read_data(connection, conf.TABLE)
    return list(map(lambda p: p[2], posts))


#cursor.close()
#connection.close()