Loading data

You can load data from any table into your Python code:

# Open a connection to the Peliqan data warehouse
dbconn = pq.dbconnect(pq.DW_NAME) # See your DW name under My Connections

# Open a connection to a connected database
dbconn = pq.dbconnect('connection_name') # See connection name under My Connections

# Load a table
rows = dbconn.fetch('db_name', 'schema_name', 'table_name')

# Using a custom SQL SELECT query
rows = dbconn.fetch('db_name', query = 'SELECT * FROM schema_name.table_name')

# Get a dataframe as response
rows = dbconn.fetch('db_name', 'schema_name', 'table_name', df=True)

# More options for dbconn.fetch():
#
# df = True: set to True to return a Dataframe instead of a list of objects
# fillna_with = '': replace Na (not a number) with a given value, e.g. in empty string
# fillnat_with= '': replace NaT (not a date)
# enable_python_types = True: use Python types in response
# enable_datetime_as_string = True: return datetime columns as string
# tz='UTC': timezone for datetimes

The response rows is a list of objects, or when df=True is used the response is a Pandas dataframe.

Working with data

dbconn = pq.dbconnect(pq.DW_NAME) 
rows = dbconn.fetch('db_name', 'schema_name', 'table_name')

# Select the first row
row = rows[0]

# Select one element (cell value) in a row
value = rows[0]["column_name"]  # first row
value = rows[1]["column_name"]  # second row

# Loop over all rows:
for row in rows:
    st.text(row) #print the row

# Loop over all rows and access one element
for row in rows:
		st.text(row["column-name"])

# Loop over the column names of one row
row = rows[0]
for column_name in row:
  st.text(column_name)

# Loop over all elements of row and get both key & value  
row = rows[0]
for key, val in row.items():
  st.text('Key %s has value %s' % (key, val))

Working with dataframes

Here are basic examples of working with dataframes:

data = dbconn.fetch('db_name', 'schema_name', 'table_name', df=True)

# Select only a few columns:
data2 = data[["column1", "column2"]]

# Select the first row
row = data.iloc[0]

# Select the first element in a column
value = data["column_name"].iloc[0]

# Loop over all rows:
for i, row in data.iterrows():
    st.text(row) #print the row

# Loop over all rows and access each element (also loop over columns):
for i, row in data.iterrows():
    for col in row:
      st.text(col) # print the element (col)

Reading data from tables

Loading data

Working with data

Working with dataframes