I'll be discussing how to create variables with PostgreSQL, how to convert data frames in Python to SQL tables, and how to set up a connection between Tableau and a local PostgreSQL server. Here's a related project where I used this information.
I had difficulty getting variables to work in PostgreSQL queries. What I ended up doing was creating functions that return the value that I'm looking for, and then just used the functions in place of variables. Here's an example:
/* Function that gets the start date of the fantasy matchup week */
DROP FUNCTION IF EXISTS get_week_start();
CREATE FUNCTION get_week_start() RETURNS date
AS $$
#print_strict_params on
DECLARE
week_start_date date;
BEGIN
SELECT yahoo_matchups.week_start INTO STRICT week_start_date
FROM yahoo_matchups LIMIT 1;
RETURN week_start_date;
END;
$$ LANGUAGE plpgsql;
/* Get the list of games that occur at the start of the fantasy matchup week */
SELECT * FROM nba_schedule
WHERE (gdte = get_week_start())
I then found out that single row, single column query results can also work. So for example, this works too:
SELECT * FROM nba_schedule
WHERE gdte = (SELECT week_start FROM yahoo_matchups LIMIT 1)
You can even temporarily store query results themself with the following syntax:
WITH todays_games AS(
SELECT * FROM nba_schedule
WHERE gdte = (SELECT week_start FROM yahoo_matchups LIMIT 1)
)
SELECT gdte, stt, home_team FROM todays_games
You can write SQL queries in Python with Psycopg2. Here's some code I modified from Naysan.
import psycopg2
login_info = json.load(open("./sql_login.json"))
conn = psycopg2.connect(
host = login_info["host"],
database = login_info["database"],
user = login_info["user"],
password = login_info["password"]
)
try:
query1 = ("""
SELECT * FROM yahoo_matchups
ORDER BY matchup_number, team_key, stat_type;
""")
cursor.execute("""
{}
""".format(query1))
conn.commit()
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print("Error: {}".format(error))
conn.rollback()
cursor.close()
As you can see, you do this by creating a connection to the SQL database, creating a cursor, executing the queries, then committing them to the database. You can visit my example sql login json file as a template for your own sql_login.json file.
The try and except blocks are important if the query has an error. I found that if there was an SQL error and I didn't have conn.rollback(), then Python would hang on the next SQL query that I try to execute.
You'll need to change the postgresql.conf file. For me on MacOS, this is found through ~/Library/Application\ Support/Postgres/var-13/postgresql.conf. Go to connection settings and uncomment the listen_addresses line. Replace the listen_addresses line with listen_addresses = '*'. Here's an explanation of what setting listen_addresses to * does.
Next, in the same folder that contained postgresql.conf, search for the pg_hba.conf file. Search for "Put your actual configuration here" and look at the non-commented code. You can create a new line and place host all all all md5 underneath the existing rows. This code comes from an answer about the code for the pg_hba.conf file.
After doing these steps, you should be able to connect to your local PostgreSQL server with Tableau and start plotting from the database's data.
Hopefully this gives some insight into some of the issues I had with PostgreSQL, Python, and Tableau and what solutions I used to solve these issues.