As anyone who has read my blog knows, I really enjoy playing with Python. We have been trying to build some different Tableau Server solutions at work in recent months, and we came to a point where we needed to query the Postrges database in a Python application. The code I am sharing is by no means perfect, and only scrapes the surface of the possibilities, but it should be a decent precursor for how to get started.
#python script
import psycopg2
top_n_views = 20 #define top n views to get
urls = [] #define empty list to house urls for all views on the server
#connect to Tableau Server's PostgreSQL database
conn = psycopg2.connect("dbname='workgroup' user='readonly' host='TabserverIpAddress' port='8060' password='p@ssword'")
#define cursor for PostgreSQL connection
cur = conn.cursor()
#SELECT statement to find top n view URLs
cur.execute("""SELECT 'http://TabServerUrl/#/views/' || view_url FROM _views JOIN _views_stats ON _views.id = _views_stats.views_id ORDER BY _views_stats.nviews desc limit """ + str(top_n_views))
#store results from SELECT
rows = cur.fetchall()
#send rows to urls list
for row in rows: #for every row stored in "rows"
urls.append(row[0]) #append to the empty list named "urls"
print urls
import psycopg2
top_n_views = 20 #define top n views to get
urls = [] #define empty list to house urls for all views on the server
#connect to Tableau Server's PostgreSQL database
conn = psycopg2.connect("dbname='workgroup' user='readonly' host='TabserverIpAddress' port='8060' password='p@ssword'")
#define cursor for PostgreSQL connection
cur = conn.cursor()
#SELECT statement to find top n view URLs
cur.execute("""SELECT 'http://TabServerUrl/#/views/' || view_url FROM _views JOIN _views_stats ON _views.id = _views_stats.views_id ORDER BY _views_stats.nviews desc limit """ + str(top_n_views))
#store results from SELECT
rows = cur.fetchall()
#send rows to urls list
for row in rows: #for every row stored in "rows"
urls.append(row[0]) #append to the empty list named "urls"
print urls
tabserver.py |
Output