Python Script: get all columns from all tables and bases

Here is another python-script tutorial. I hope you like it. The requirement is simple. One user asked me if it is possible to get all bases, tables, and columns of this own bases via python-script. This is the Base where the results are stored. For every column in a base, there is one line. The column names are in German because it is a German customer.

Every time a new column is added the list should be extended and every time a column is removed in any base this list should remove this entry. If you run the following script daily with an automation, you always have a complete list of all columns.

what you have to do:

  1. create this base structure “Base, Tabelle, Spaltenname, Spaltentyp”. (The english translation would be “Base, Table, Column, Column type”)
  2. copy the following python script into this base
  3. change the values for table_name, auth_token at the beginning.
  4. test it…
import os
import sys
import requests
import json
from seatable_api import Base, context

# input variablen
table_name = "Table1"
auth_token = "auth-token-of-the-user" 
# auth_token has to be generated with own credentials: https://api.seatable.io/#6f473a04-daca-4e6b-b98c-3b3654fe3f64

################################
## Don't change anything below this line ##
################################

# define arrays to save all columns and already stored columns
array_new_columns = []
array_cur_columns = []

#####
# 1) get values of current base
#####

# get access to this base (where the results are stored)
server_url = context.server_url
api_token = context.api_token

# get current entries from this base
base = Base(api_token, server_url)
base.auth()

for r in base.list_rows(table_name):
  array_cur_columns.append(r.get('Base') + '::' + r.get('Tabelle') + '::' + r.get('Spaltenname') + '::' + r.get('Spaltentyp'))

print("There are in total " + str(len(array_cur_columns)) + " columns already stored in this base...")
#print(array_cur_columns)

####
# 2) get columns of all other bases
####

url = server_url + '/api/v2.1/user-admin-dtables/'
headers = {
  'Authorization': 'Token {}'.format(auth_token),
  'Accept': 'application/json; indent=4'
}
resp = requests.get(url,headers=headers)

# merge personal and group bases
personal_bases = resp.json().get('personal')
all_bases = personal_bases
group_bases = resp.json().get('groups')
for gb in group_bases:
  dtables = gb.get('dtables')
  all_bases = all_bases + dtables

num_bases = len(all_bases)
print(str(num_bases) + " Bases habe ich gefunden.")

# loop all bases
for f in all_bases:

  # get base info
  #print(f)
  base_uuid = f.get('uuid')
  base_name = f.get('name')
  workspace_id = f.get('workspace_id')
  updated_at = f.get('updated_at')
  is_encrypted = f.get('is_encrypted')
  rows_count = f.get('rows_count')

  # get base access token
  url = server_url + '/api/v2.1/workspace/' + str(workspace_id) + '/dtable/' + base_name + '/access-token/'
  #print(url)
  headers = {
    'Authorization': 'Token {}'.format(auth_token),
    'Accept': 'application/json; charset=utf-8; indent=4'
  }
  resp = requests.get(url,headers=headers)
  access_token = resp.json().get('access_token')
  #print(access_token)

  # get base metadata
  url = server_url + '/dtable-server/api/v1/dtables/' + base_uuid + '/metadata/'
  headers = {
    'Authorization': 'Token {}'.format(access_token),
    'Accept': 'application/json; charset=utf-8; indent=4'
  }
  resp = requests.get(url,headers=headers)
  for t in resp.json().get('metadata').get('tables'):
    for c in t.get('columns'):
      array_new_columns.append(base_name + '::' + t.get('name') + '::' + c.get('name') + '::' + c.get('type'))

print("There are in total " + str(len(array_new_columns)) + " columns in all bases I have access to...")
#print(array_new_columns)

####
# 3) compare columns
####

## Array Vergleich
new_in_cur = [hits for hits in array_cur_columns if hits not in array_new_columns]
new_in_new = [hits for hits in array_new_columns if hits not in array_cur_columns]

print("These are the changes:")
print(str(len(new_in_cur)) + " columns are removed")
print(str(len(new_in_new)) + " columns are added")

# ADD columns to this base
rows_data = []
for n in new_in_new:
  v = n.split('::')
  rows_data.append({
    "Base": v[0],
    "Tabelle": v[1],
    "Spaltenname": v[2],
    "Spaltentyp": v[3]
  })

base.batch_append_rows(table_name, rows_data)

# Remove columns from this base
to_delete = [] # kommt später noch per batch...
for d in new_in_cur:
  v = d.split('::')
  sql = 'select _id,Base from ' + table_name + ' where Base = "' + v[0] + '" AND Tabelle = "' + v[1] + '" AND Spaltenname = "' + v[2] + '" LIMIT 1'
  resp = base.query(sql)
  to_delete = to_delete + [resp[0].get('_id')]

base.batch_delete_rows(table_name, to_delete)

the logic of this script

The logic behind this script is easy. I create two lists. One list contains all rows that are already stored in this base. The other list contains all columns from all tables and bases the user has access to. The values are stored in this way:

basename::tablename::columnname::columntype

Then I compare the two lists twice and remove the duplicates. If there are new entries from all bases, I add new rows to the current base. If there are entries in the current base and not in the list of all bases, I look for the row and remove it.

This script is by far not perfect, but it should help you to understand two ways to access data via python script:

  1. python scripts can access the content of the current base easily with
base.auth()
for r in base.list_rows(table_name):
  1. to get the columns of all other bases, it requires the usage of various API-calls. First I have to get a list of all bases the user has access to. The result is a list of personal and group-bases. Another API-call is needed to get the metadata of these bases, etc…

If you want to change the column names

If you want to change the column names of your target base, you also have to change the names in the python script at two positions. Right in the beginning at the command array_cur_columns.append and at the end where I append the rows_data.append.

Have fun.

1 Like