Postgre SQL

POSTGRESQL TUTORIAL

Create a new DB and a table and insert data

1. Install pgAdmin4 for UI or psql for command line to interact with postgre DB
2. Connect to the PostgreSQL server
3. Create a new DB by right clicking on the server
4. Right click on the schemas under the DB to create a new schema if it doesn’t exist
5. Under the schemas, right click on the tables icon and create a table
6. Create the necessary columns of the tables and save the table
7. Right click on the table and choose scripts
8. Click on scripts and select INSERT script
9. Insert the data in the insert panel and execute it

Connect to the DB with python

import psycopg2
db_params = {
    "dbname": "mydb",
    "user": "postgres",
    "password": "1234",
    "host": "localhost",  # or your PostgreSQL server's hostname
    "port": 5432,  # default PostgreSQL port
}

try:
    conn = psycopg2.connect(**db_params)
    print("Connected successfully!")
    # You can now perform SQL queries using 'conn'
except psycopg2.Error as e:
    print(f"Error connecting to the database: {e}")
cursor = conn.cursor()

# Execute a sql query

cursor = conn.cursor()
cursor.execute("SELECT * FROM linkage LIMIT 2;")
for i in (rows:= cursor.fetchall()):
    print(i)

INSERTING DATA FROM CSV

Either through the psql command line with the following command:

\copy public.polling_data (id, candidate_id, state_id, pollster_id, rcp_average, spread, polling_date, polling_month, polling_year, day_diff, month_diff, candidate_affiliation, region, states, candidate_name) FROM '/Users/shamik/data/rcp/processed_data/trial.csv' DELIMITER ',' CSV HEADER ENCODING 'UTF8' QUOTE '''' ESCAPE '''';

OR through PgAdmin:

1. Select the table and right click on it
2. Select import/export data
3. Select the file path and fill in all the other details
4. Press ok to import

EXPORT A TABLE

1. Select table and right click
2. Select ”backup” and specify the filename
3. Choose “plain” format
4. Press “backup”

IMPORT A TABLE

1. Open psql command line
2. Use the following command \i /Users/shamik/data/rcp/processed_data/linkage.sql
3. Refresh the tables