This demo will illustrate how load an external dataset into a new Space and Time, including creating the table itself, defining biscuits, and a few different ways to execute the data insert. This is intended as a working demo and starting point for other similar data loads, so the structure is purposely simple. However, it can also be used as a quick-start guide for small projects.

While the code is entirely available on Github, you can also build all collateral by following along with the steps below. If you're looking to educate yourself on Space and Time, it's recommended you build along with the instructions below. If you want to jump into using the code as a quick-start, or are already comfortable with Space and Time, check out the github repo here:

Github Repo for this Demo: https://github.com/SxT-Community/demo_load_nyse

Summary

This guide will walk you through:

1. Installing prerequisites
2. Setting up required files and folders
3. Creating your credential file
4. Creating the code file
   1. Python imports
   2. Authenticate
   3. Create your table
   4. Pull stock API data
   5. Run a query
5. Next steps to try

Prerequisites

The prerequisites for this demo are fairly basic - you'll need:

Valid Space and Time User_ID

Create a UserID:

The easiest way to create a new user is with Space and Time Studio, as it has wrapped all steps into a simple UI. To get started:

Navigate to Space and Time Studio web application. Click on "Sign in" in the upper-right, and then on the "Register" button. You'll be presented with a few options, including signing up for a subscription now, joining an existing subscription, or creating a Trial user. For now, let's click on "Register as trial user" (we can come back to the other two options afterwards). Enter the "UserName" and "Password" you prefer. A few considerations: UserNames are globally unique across the network, so get your favorite early! Both UserName and Password are case-sensitive, so Alice is different than alice Password will include requirements to include alpha and numeric characters (for security) Click "Create Account" and you're done!

👍

Congrats! You now have a Space and Time account!

Alternatively, you can create your user by "Connecting a wallet" immediately, however you lose the the option to login with a Password. It's generally recommended you create your account with a UserName / Password first, then connect that account with a wallet afterwards.

---

└─[$] java --version

java 20.0.1 2023-04-18
Java(TM) SE Runtime Environment (build 20.0.1+9-29)
Java HotSpot(TM) 64-Bit Server VM (build 20.0.1+9-29, mixed mode, sharing)

alias sxtcli='java -jar <PathToJar>/sxtcli-<LATEST_VERSION>.jar'

Valid Subscription (payment optional)

Most activities on Space and Time can be used with a trial user, however some things, like creating a table, require a subscription. That said, payment is not required to create a subscription.

Create a new subscription:

Creating a new subscription is extremely simple using Space and Time Studio! Note that subscriptions allow you to pay for Space and Time, but do not require payment. Having a subscription grants an additional 50 free queries, and opens up certain features (like creating tables) that are unavailable without a subscription.

To create a new subscription:

Navigate to Space and Time Studio, click on "Sign In" in the upper-right most corner, and log in. Note: the UserID who creates the subscription will be the first "owner" and have full super-admin permissions. Click on "My Account" in the upper-right (replaces "Sign In") and make sure you're on the "My Account" tab. On the right side, you should find a section outlining the various subscriptions and payment options - select the option that best fits your use-case click Subscribe! Depending on your payment preferences, there will be a wizard to help you set-up your credit card or crypto wallet for payments.

If you're not sure, Price per Compute is a great way to get started. You can "Skip Payment" (upper-right button) and still unlock all features of the platform, including an additional 50 free queries! When you're ready, you can come back to the same screen, enter your payment information and join the usage based model, where you only pay for queries you run, no minimums. If you use 10¢ worth of compute, you're only charged 10¢. If you never run queries again, you're never charged again!

Once you successfully create your subscription, the information on the right will be replaced with ways to manage your subscription / organization, and your name will lose it's "trial" tag.

👍

Congrats! You now have a Space and Time subscription!

OR, join your organization's existing subscription:

If your organization already has an active Space and Time subscription that you'd like to join, your SxT admin must issue you a 'JoinCode'. The JoinCode they send you will look like a hexidecimal key, for example: 1115639b5baf85bba86153f8

To Join a subscription with a JoinCode: Ask your internal Space and Time administrator to generate and send you a new JoinCode over a secure channel. Navigate to Space and Time Studio, click on "Sign In" in the upper-right most corner, and log in. Click on "My Account" in the upper-right (replaces "Sign In") and make sure you're on the "My Account" tab. In the section called "Join an Existing Subscription" find the text box labeled "Join Code." Copy/paste the JoinCode from your administrator in the text box, and click "Submit."

You should see a green confirmation message in the upper right, and this section of the UI will be replaced with information on you current subscription.

👍

Congrats! You've just joined a subscription!

🚧

The JoinCode expires 24 hours after generation and is consumed once used (for security) - so don't delay!

---

Setting Up Files/Folders

Like any project, we'll need a few structures to ensure we're separating code from credentials. If you're going to push to github, we'll also want to ensure a valid .gitignore file. The script below will create a few empty files for us to edit, create and configure a python virtual environment, and perhaps more importantly, make sure we're not accidentally committing keys!

1. Navigate to a location of your choice on your computer, and create a new, empty folder for your project, named something like ./demo_load_nyse (referred to below as the 'project folder').
2. Execute the script below. You can do this by either
   1. Opening a terminal window and copy/paste in the commands below, in order, or
   2. Create a setup.sh file, copy/paste the commands below into that file, save, and execute that file.

# !/bin/bash

# create python script file placeholder:
mkdir ./src
touch ./src/load_nyse.py

# create environment variables (.env) file placeholder:
mkdir ./.env
touch ./.env/secrets.env

# setup .gitignore file, with a few critical entries:
touch ./.gitignore
echo 'secrets.env' > ./.gitignore
echo 'venv_demo/' >> ./.gitignore
echo 'tables/' >> ./.gitignore


# create python virtual environment:
python3 -m venv venv_demo
source venv_demo/bin/activate
pip3 install --upgrade pip
pip3 install spaceandtime --upgrade
pip3 install yfinance --upgrade

deactivate

echo "Done! "

If done correctly, your project folder should now contain:

• ./src/ folder
• ./src/load_nyse.py empty file
• ./.env/ folder
• ./.env/secrets.env empty file
• ./.gitignore file with 3 lines of text
• ./venv_demo/ folder, containing the python virtual environment (with dependencies installed)

🚧

Some operating systems (like MacOS) will hide files and folders that start with a period by default. This means you may only see src and venv_demo folders. From a terminal you can cd to your folder and type ls -a to see -all of the files and folders, including those hidden.

Or, change your operating system's file navigator to show hidden files.
For example, on MacOS press [ cmd + shift + "." ] keys together to toggle viewing hidden files.

You only need to run the above script once - however it is safe to run many times. In fact, if you add to the bottom:

# run the python file
python3 ./src/load_nyse.py

Then it can also be used to update the environment and start your script, good for schedulers like Cron.

Credential File: secrets.env

Now that we have all the structures we need created, let's collect and save all our credentials into the newly created secrets.env file. This allows for separation between code and credentials, and helps keep your secrets secret!

Let's start with a template - open up the secrets.env file in your favorite IDE or text editor, and paste this content:

# This is a sample .env file, with sample secrets.
# Secrets below are designed to look real, but WILL NOT WORK.
# For help on creating your own SXT credentials, please see:
#   https://docs.spaceandtime.io/docs/newuser


# Space and Time User Login Keys (Base64):
SXT_USER_ID="your_user_id"
SXT_USER_PUBLIC_KEY="eCHWo+ELB47xmp94aeZ+Cp9SAoh0w2PH3bhKzX0NNB0="
SXT_USER_PRIVATE_KEY="UELmpzmTgxLU+bfZkOIOJknRTO0eonkKjFNh0mOg5W8="

# Space and Time Table Keys (Hex):
RESOURCE_PUBLIC_KEY="347B389DE73D46BFB64C068A7D9C01142599E7BBBCCC4FABF404E134DDBA939"
RESOURCE_PRIVATE_KEY="CD3C070BD746439399BBE2590F77CB0C556419B00ED6D8D798D0C4317A4E7B2"

As noted in the text comment, the above keys are designed to LOOK right, but by design will not work. We'll need to replace these with your own keys.

There are two sets of keys here:

• SXTUSER* keys, which are needed to authenticate your UserID to the Space and Time network
• RESOURCE_* keys, which are used to prove ownership over your table or tables.

Both keys are ED25519 signatures, but the SXT_USER authorization requires Base64 encoding, while the RESOURCE requires Hex encoding. This is because the RESOURCE_PUBLIC_KEY is added to any DB object during creation, meaning it has to use SQL-Safe" characters, whereas SXT_USER keys prefer the denser format of Base64 encoding for shorter (and more frequent) messages.

Also, people will sometimes get USER and RESOURCE keys mixed up - having different encodings help bring visual clarity to which is which.

USER Keypair

If you completed all prerequisite steps, you should already have an ED25519 keypair associated with your UserID. If you missed that step, or just want to add a new keypair (there is no limit), check out:

Once you have your USER keypair, edit your secrets.env file and plug in your SXT User_ID, Public Key and Private Key.

# Space and Time User Login Keys (Base64):
SXT_USER_ID="your_user_id"
SXT_USER_PUBLIC_KEY="your_new_public_key"
SXT_USER_PRIVATE_KEY="your_new_private_key"

Save your credentials somewhere safe! Using a password manager or enterprise secrets management software is best practice. Like any Web3 project, lost keys means losing control of your account.

RESOURCE Keypair

All resources (tables, views, etc.) on the Space and Time network must be created with a public/private keypair. The public key is added to the CREATE TABLE statement and saved on the network. The private key is used to sign self-describing authorization keys (aka biscuits) which authorize any request to that table.

This allows authorization to be decentralized, while allowing the owner of the table (aka the person holding the private key) to define and share signed biscuits that the table can verify as authentic. The self-describing nature of biscuits also allow that authorization to be limited to a particular table, person, time period, or subscription.

To get started quickly, you can use the SXTCLI to generate a new table keypair AND an admin-level biscuit, all with one command: Open up a terminal window and enter the command:

sxtcli biscuit generate wildcard

The above command will return:

1. An ED25519 keypair (hex encoded) that can be used for creating tables, views, etc.
2. A description of the biscuit capabilities, in this case:
   1. for ANY resource ("*") with a matching public key,
   2. allow ANY command ("*") - hence the name "wildcard" or admin biscuit
3. The actual biscuit key itself

This output will be displayed as:

Biscuit KeyPair (hex)
Private key: D4EAC895937773E3047FE2E9AFA7004B80601BA0F687511BC23658D366FFB51B
Public key:  51297647296389ADABBB6E62230203C620DF47BF8DCAEEF1B1EC35507FBA48E0
Biscuit content:
 Facts:
  sxt:capability("*","*");
Biscuit:
EpABCiYKDnN4dDpjYXBhYmlsaXR5CgEqGAMiDwoNCIAIEgMYgQgSAxiBCBIkCAASIJK...

Note, the below key was modified to be invalid -- please generate your own fresh key!

📘

Both Users and Tables / Views use the ED25519 keypair for security. However, there is a difference in encoding between the two:

• User authentication will expect Base64 encoding
• Tables and Views, which need it embedded in SQL, will expect Hex encoding

The CLI will return the correct encoding when generating new keys.

🚧

Store the private key somewhere safe! If you lose the private key, you lose control of the table, view, or resource!

Multiple tables or views can share a single keypair - for example, a User table and a User_Settings table may share the same keypair (and so wildcard biscuit), since they are related and often operated on together.

That said, it is NOT recommended to reuse keys too widely, as it broadens the exposure of any stolen or lost keys.

👍

Congrats! You have created a new resource public/private keypair!

Add your newly created RESOURCE keys to your credential file, and save!

# Space and Time Table Keys (Hex):
RESOURCE_PUBLIC_KEY="your_new_resource_public_key"
RESOURCE_PRIVATE_KEY="your_new_resource_private_key"

Code File: load_nyse.py

For this demo, we'll use the Space and Time Python SDK. The above setup.sh script will have created a python virtual environment, and pip installed the latest version of all requirements. That said, you can always install libraries manually by opening a terminal, making sure to activate the python virtual environment with: source venv_demo/bin/activate, then enter pip3 install spaceandtime.

🚧

If you get errors using pip3 during installs, or the install runs but the program doesn't recognize the SpaceAndTime package, check the version of Python that pip3 in installing to.

ls -l `which pip`
ls -l `which pip3`

-rwxr-xr-x  1 <your user and role>  267 Jun 24 12:06 /Library/Frameworks/Python.framework/Versions/3.11/bin/pip
-rwxr-xr-x  1 <your user and role>  267 Jun 24 12:06 /Library/Frameworks/Python.framework/Versions/3.11/bin/pip3

Python Imports

The python script will start with all imports at the top, as per standard practice. Copy/paste the below into your empty load_nyse.py script file:

from spaceandtime import SpaceAndTime, SXTTable
from datetime import datetime, timedelta
import yfinance, pandas, os, re

The two imports you may not recognize:

• spaceandtime - the Space and Time network Python SDK, from which we're importing:
  • SpaceAndTime - is a top-level convenience object that aggregates many of the lower-level objects
  • SXTTable - allows control over a DB table in the Space and Time Database
• yfinance - the Yahoo! Finance SDK, for pulling stock (and crypto) price data

Authenticate to Space and Time Network

Next, we'll create a SpaceAndTime object and use it to authenticate to the network, using our prepared credential file. Copy / paste the below code into the bottom of your load_nyse.py file:

# connect to the network, and authenticate (must have an .env file)
sxt = SpaceAndTime(envfile_filepath='./.env/secrets.env')
sxt.authenticate()

It's that easy! The SXT object will read in your secrets file, and use it to authenticate to the network. You should see log stream display everything it read from your credential (aka dotenv) file, followed by:

2024-11-13_16:58:22 INFO SXT User instantiated: sxtdemo_user
2024-11-13_16:58:23 INFO Authentication Success: True

👍

Congrats! You just logged into the Space and Time network!

Create your Table

Creating a table on Space and Time takes a few simple steps, which we'll break into different sections so we can describe as we go. You can copy each of the code sections into the bottom of your load_nyse.py script as you progress:

Create the SXTTable Object

The first step is to create a new SXTTable object, supplying some initial parameters:

# create table object:
stocks = SXTTable(name = "SXTDemo.Stocks_some_unique_name",
                  private_key = os.getenv('RESOURCE_PRIVATE_KEY'), 
                  access_type = sxt.TABLE_ACCESS.PUBLIC_READ, 
                  SpaceAndTime_parent=sxt)

On the first code line, we're defining the SXTTable object, defined as the variable stocks. The SXTTable object has many options, we're just setting the most common above:

• name = the full SCHEMA.TABLE_NAME as a DB compliant string (alpha-numeric and underscore only).
  SCHEMA.TableNames are globally unique, so you'll likely need to append some unique suffix to the end of the string, like your UserID. If you download the github repo, you'll notice it automatically appends your UserID and does a regex substitution of any non-DB safe characters (since UserIDs can have non-DB save characters). If you want to emulate that here, simply replace the first code line above with:
  Python

  stocks = SXTTable(name = f"SXTDemo.Stocks_{re.sub(r'[^a-zA-Z0-9_]', '_', sxt.user.user_id)}",
  

• private_key = the RESOURCE private key from your credential file.
• access_type = the default access provided, if no authorization biscuit is supplied.
  In this case, we're setting to PUBLC_READ, meaning anyone can read the table, but only you can modify. Check out this page for the entire list of table options.
• SpaceAndTime_parent = this allows the SXTTable object to inherit settings from the above SpaceAndTime object, such as logging, description names, and most importantly, the authenticated default UserID - which allows the table to submit requests to the Space and Time Database directly.

Per the last parameter - only SXTUser objects can authenticate to Space and Time and submit queries. The SpaceAndTime convenience object contains a default user object, making it easy to get started quickly; two lines of code to authenticate!

Tables cannot submit queries to the network. However, by providing an authenticated user to the SXTTable object, the table itself can now run queries on the network using the user's connection - check out functions like stocks.select() or stocks.delete() or stocks.insert.with_list_of_dicts. Thus, setting the SpaceAndTime_parent = sxt allows the table object to inherit the default user's connection and manage its own state.

If you want to play around with the default user, you can always access from the sxt object: print(sxt.user)

Define the CREATE TABLE SQL

Next, let's set the CREATE TABLE query - if you've ever created a database table before, this will look very familiar:

stocks.create_ddl = """
    CREATE TABLE {table_name} 
    ( Symbol         VARCHAR
    ,Stock_Date     DATE 
    ,Stock_Open     DECIMAL
    ,Stock_High     DECIMAL
    ,Stock_Low      DECIMAL
    ,Stock_Close    DECIMAL
    ,Stock_AdjClose DECIMAL
    ,Stock_Volume   BigInt
    ,PRIMARY KEY (Symbol,Stock_Date)
    ) {with}
"""

There are two substitutions here:

• {table_name} = is substituted with the stocks.table_name that was set during object creation
• {with} = The WITH statement is the single non-standard extension required by the Space and Time Database.

After setting the stocks.create_ddl above, you can turn around and print(stocks.create_ddl) to see the SQL being sent to the database:

CREATE TABLE SXTDemo.Stocks_sxtdemo_user
( Symbol VARCHAR
,Stock_Date DATE
,Stock_Open DECIMAL
,Stock_High DECIMAL
,Stock_Low DECIMAL
,Stock_Close DECIMAL
,Stock_AdjClose DECIMAL
,Stock_Volume BigInt
,PRIMARY KEY (Symbol,Stock_Date)
) WITH "public_key=85abf..., access_type=public_read"

You'll note the two parameters were automatically substituted, most notably, the {with} expanded to include the full WITH "public_key=<your_RESOURCE_public_key>, access_type=public_read" which should reflect our definition above.

Also note the second-to-last line, ,PRIMARY KEY (Symbol,Stock_Date). The Primary Key of a table tells the Space and Time Database how to uniquely identify any given row. this means that the combination of Symbol and Stock_Date must be unique. Inserting two rows with the same Symbol and Stock_Date will cause the insert to fail, with a "unique key violation" error.

Create Authorization Biscuits

If the SXTTable has a private_key set (like we supplied above), it can generate its own authorization biscuits. You will need at least one ADMIN biscuit that has CREATE TABLE permission, with the others being a best practice, but optional:

# create two permissions (biscuits), one Admin, and one just to Load
stocks.add_biscuit('Admin', sxt.GRANT.ALL)
stocks.add_biscuit('Load', sxt.GRANT.INSERT, sxt.GRANT.UPDATE,
                           sxt.GRANT.DELETE, sxt.GRANT.SELECT)
stocks.add_biscuit('Read', sxt.GRANT.SELECT)

These biscuits are saved by the SXTTable object and applied automatically to any query that the SXTTable object runs. If you get an Authorization Error, check that (a) stocks.private_key is valid, and stocks.biscuits contains at least one Admin biscuit.

Save your Table Definition to a File

This next step is deceptively important: save all settings for the stocks table to a uniquely named file, under a ./tables/ subfolder:

# save all table settings to file
stocks.save(stocks.recommended_filename)

This file will include the SQL statement, the public and private keys, and all biscuits - everything needed to recreate the object. Instead of serializing to a .pickle or binary file, this saves to a shell friendly format, meaning you can load the file into a terminal's environment variables and continue interacting with the table using the SXTCLI or other OS scripting tools.

Most importantly, the recommended_filename property will create a timestamped file, meaning it automatically captures a snapshop of changes over time (although you can provide any name you'd like).

Create the Table on the Network

Up to this point, everything we've done to configure the stocks table has been made locally - nothing has been sent to the Space and Time network yet. Time to make it real!

Copy this command into your file to (a) check if the table already exists on the network, and if not, (b) create it!

# actually create, if missing:
if not stocks.exists: stocks.create()

This will create the table in the network! Note that you can run this script repeatedly - if the table exists the second time you run it, the create will be skipped.

👍

Congrats! You've created your stocks table on the Space and Time network!

Put it All Together

Let's put this all together into one script - your load_nyse.py script should now look something like:

from spaceandtime import SpaceAndTime, SXTTable
from datetime import datetime, timedelta
import yfinance, pandas, os, re

 
# connect to the network, and authenticate (must have an .env file)
sxt = SpaceAndTime(envfile_filepath='./.env/secrets.env')
sxt.authenticate()

# create table object (appending your userid to the end of tablename):
stocks = SXTTable(name = f"SXTDemo.Stocks_{re.sub(r'[^a-zA-Z0-9_]', '_', sxt.user.user_id)}",
                  private_key = os.getenv('RESOURCE_PRIVATE_KEY'), 
                  access_type = sxt.TABLE_ACCESS.PUBLIC_READ, 
                  SpaceAndTime_parent=sxt)

stocks.create_ddl = """
    CREATE TABLE {table_name} 
    ( Symbol         VARCHAR
    ,Stock_Date     DATE 
    ,Stock_Open     DECIMAL
    ,Stock_High     DECIMAL
    ,Stock_Low      DECIMAL
    ,Stock_Close    DECIMAL
    ,Stock_AdjClose DECIMAL
    ,Stock_Volume   BigInt
    ,PRIMARY KEY (Symbol,Stock_Date)
    ) {with}
"""

# create three permissions (biscuits), one Admin, one for Reading, and one just to Load
stocks.add_biscuit('Admin', sxt.GRANT.ALL)
stocks.add_biscuit('Load', sxt.GRANT.INSERT, sxt.GRANT.UPDATE,
                           sxt.GRANT.DELETE, sxt.GRANT.SELECT)
stocks.add_biscuit('Read', sxt.GRANT.SELECT)

# save all table settings to file
stocks.save(stocks.recommended_filename)

# actually create, if missing:
if not stocks.exists: stocks.create()

Troubleshooting

Sometimes things go wrong! Here are a few common issues / mistakes:

• Authentication only lasts for 25 minutes before needing to be refreshed - if you're really exploring each step above, your ACCESS_TOKEN may have timed out.
  Resolve: Simply re-authentication with: sxt.authenticate()
• If you get an "Authorization" error, it means something was wrong with your biscuit, or the private key used to create the biscuit. Or, you created the table with one keypair, then created the biscuit with a different keypair. Remember that the same private key must create both the Public Key assigned to the table at CREATE time, as well as all biscuits.
  Resolve: Check that your keys are being passed in from your credential file correctly, and that the table's private key and biscuit's private key are the same - i.e,. the private key listed in both objects should match:
  stocks.get_biscuit('Admin') should match stocks.private_key
• If you get a "table already exists" error, then your SCHEMA.TableName is not globally unique - someone beat you to it!
  Resolve: Change your tablename to be more unique, and try again.
  Assuming you're authenticated, you can test if a SCHEMA.TableName exists with:

  stocks.table_name = "SXTDemo.Stocks_more_unique_suffix"
  stocks.exists
   False
  

• Errors stating that your table is in the "Public" schema are caused by forgetting to assign a schema.
  Resolve: Make sure to include a valid schema - above we've been using SXTDemo.Stocks_some_suffix, where SXTDemo is the schema and Stocks_some_suffix is the tablename.
• Likewise, Scheme Doesn't Exist error indicates you have a typo in your schema name, or changed it to a schema that does not exist.
  Resolve: Change your schema to something that exists, like SXTDemo (created specifically for demos), or create your own new schema by issuing the SQL command: CREATE SCHEMA SchemaName

Pull API Data and Load Table

This next section includes more pure-Python components, pulling stock data from the yfinance SDK, transforming the resulting Pandas dataframe into a Python list-of-dictionary objects (aka rows and columns), which can natively be inserted into Space and Time using the SDK.

Copy this larger block of text and paste it at the bottom of your load_nyse.py script:

# ----------------------------------------
# ------- PULL AND LOAD DATA -------------
# ----------------------------------------
# define start/end dates (7 rolling days)
end_date = datetime.now().strftime('%Y-%m-%d')
start_date = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d')

for symbol in ['AAPL', 'GOOGL', 'AMZN', 'MSFT', 'RIVN', 'NVDA',
               'BTC-USD','ETH-USD','SOL-USD','ADA-USD', 'AVAX-USD']:
    sxt.logger.info(f'Processing: {symbol} between {start_date} and {end_date}')

    # download price data
    data = yfinance.download(symbol, start=start_date, end=end_date)

    if data.empty or data is None:
        print(f"No new data detected for {symbol}")
    else: 
        
        # transform pandas dataframe to list of dicts
        data = pandas.DataFrame(data).reset_index() # add date (from index)
        data.insert(loc=0, column='Symbol', value=symbol) # add symbol to front
        data.columns = ['Symbol', 'Stock_Date', 'Stock_Open', 'Stock_High', 'Stock_Low', 
                        'Stock_Close', 'Stock_AdjClose', 'Stock_Volume'] # rename to table column names
        data['Stock_Date'] = data['Stock_Date'].dt.strftime('%Y-%m-%d') # convert date to string
        data_load = data.to_dict(orient='records') # convert to list of dicts

        # delete any pre-existing data, before inserting:
        stocks.delete(where = f""" 
                    Symbol = '{symbol}' 
                    AND Stock_Date between '{start_date}' AND '{end_date}' """)

        # insert into SXT
        success, response = stocks.insert.with_list_of_dicts(data_load)
        if success: 
            sxt.logger.info('SUCCESSFUL INSERT!')
        else:
            sxt.logger.error(f'ERROR ON INSERT: {response}')
            

This process does the following, in order from top-to-bottom:

• Define a date range, using start_date and end_date.
• Loop through a list of stock symbols, for both stocks and crypto prices.
• Call the yfinance.download function to return data for the symbol, for the date range. If the call returns no data, log and skip to the next symbol.
• The data object returned is a Pandas dataframe - the next 6 lines of code manipulate the dataframe into the same structure as our table, then turns it into a list-of-dictionaries.
• Delete any records in our Space and Time table that might already exist for that stock Symbol and Date, using the SXTObject build-in stocks.delete() function.
  This step is a good example of the table managing itself - we're simply providing the WHERE statement, the object is managing the creation of the rest of the SQL, as well as execution. It's also worth noting that, to protect the table from accidental deletion, the where parameter is required (if you want to delete ALL records, supply something like 1=1).
• Use the SXTTable Objects built in stocks.insert.with_list_of_dicts() function, which iterates through a list (rows) of dictionary objects (columns) and inserts them into the table.
  Each insert is crafted based on the names found in each dictionary, meaning every row can contain a different number of columns, and the insert will still work. This makes this function very flexible and save, but not the fastest, since every row is individually loaded.
• Log success or failure of the insert, then back to the top of the loop for the next stock symbol

Save your load_nyse.py file and give it a run! You should see a series of log entries fly by in the terminal, showing blocks of activity like below, one for each symbol:

2024-11-1320:52:41 INFO Processing: BTC-USD between 2024-11-06 and 2024-11-13
[*100%***] 1 of 1 completed
2024-11-13_20:52:41 INFO DELETING: DELETE FROM SXTDemo.Stocks WHERE
Symbol = 'BTC-USD'
AND StockDate between '2024-11-06' AND '2024-11-13'
2024-11-13_20:52:43 INFO INSERT 7 rows into SXTDemo.Stocks...
2024-11-1320:52:43 INFO SXTDemo.Stocks Inserted Row 1 of 7 (14%) - Successes: 1 Erred: 0
2024-11-1320:52:43 INFO SXTDemo.Stocks Inserted Row 2 of 7 (29%) - Successes: 2 Erred: 0
2024-11-1320:52:43 INFO SXTDemo.Stocks Inserted Row 3 of 7 (43%) - Successes: 3 Erred: 0
2024-11-1320:52:43 INFO SXTDemo.Stocks Inserted Row 4 of 7 (57%) - Successes: 4 Erred: 0
2024-11-1320:52:43 INFO SXTDemo.Stocks Inserted Row 5 of 7 (71%) - Successes: 5 Erred: 0
2024-11-1320:52:44 INFO SXTDemo.Stocks Inserted Row 6 of 7 (86%) - Successes: 6 Erred: 0
2024-11-1320:52:44 INFO SXTDemo.Stocks Inserted Row 7 of 7 (100%) - Successes: 7 Erred: 0
2024-11-1320:52:44 INFO INSERT into SXTDemo.Stocks complete - Total Rows: 7, Successes: 7, Erred: 0
2024-11-13_20:52:44 INFO SUCCESSFUL INSERT!

👍

Congrats! You successfully created a program to load Stock API data into Space And Time!

Run a Query on Your New Data

This last section simply demonstrates how to run a query on the data you just completed running above. Copy this and paste it at the end of your load_nyse.py file!

# Find the overall highest and lowest price for the time period
success, data = stocks.select(f"""
    select distinct Symbol
    , min( stock_low  ) over(partition by Symbol) as period_lowest_price
    , max( stock_high ) over(partition by Symbol) as period_highest_price
    from {stocks.table_name} 
    where Stock_Date between '{start_date}' and '{end_date}'
    order by Symbol  """)

if success: print('\n'.join([str(r) for r in data]))
print('\n\nDone!\n\n')

This query aggregates to the absolute lowest and highest observed price during the time period, and prints the result to the terminal.

👍

Congrats! You've run a SELECT query on your newly loaded data!

That's it! In less than 100 lines of code, you have

1. Authenticated into Space and Time
2. Created a table and several permission sets
3. Repeatedly connected to an outside AP to collect data
4. Loaded that data into your new table
5. Ran an aggregation query and displayed the results!

👍

Nicely Done!!!

You've successfully loaded data from an API into the Space and Time network!
You're ready for big things!

Next Steps / Other Things to Try

Query Your Table From SXT Studio

Because we created our table with an access_type=Public_Read, there are no special biscuits required to SELECT from the data in SXT Studio. Try logging in, running a query on your new table, and even generate visualizations!

Add Authorization to SXT Studio

That said - you will NOT have permissions to modify the table without authorization. You can try to run the query:
DELETE FROM SXTDemo.Stocks_your_table_name and will get an authorization error (unless you've previously loaded biscuits). You can fix this by adding biscuits to the SXT Studio, by going to My Account under Permissions and Keys:

You'll need both the public key and Admin biscuit -- fortunately you saved those to a file in your script above!

Speed Up Inserts

As mentioned above, the insert method we selected is VERY flexible, however, is also fairly slow. Since we have a fixed structure to the data, we can change the code above, to create the INSERT statement with only ONE column definition for many rows of inserts. While we're only inserting a few rows in the above example, this approach can speed up loads by many orders-of-magnitude.

Try switching out the stocks.insert.with_list_of_dicts() with the stocks.insert.with_sqltext():

        # insert into SXT
        # success, response = stocks.insert.with_list_of_dicts(data_load)
        insert_head = f'INSERT INTO {stocks.table_name} ( {", ".join(list(data.columns))} ) VALUES \n ' 
        insert_body = '\n,'.join(['('+', '.join([f"'{c}'" for c in list(r.values())])+')' for r in data_load])
        success, response = stocks.insert.with_sqltext(insert_head + insert_body)
        if success: 
            sxt.logger.info('SUCCESSFUL INSERT!')
        else:
            sxt.logger.error(f'ERROR ON INSERT: {response}')

If you want to really see the speed increase, change the number of days from 7 to 100!

Try Another Data Source

This same approach will work with many data sources - CSV files, streams, other APIs, etc. See if you can interrogate the structure of a CSV file, create a table, and load that table - in less than 200 lines of code!

And if you get working, be sure to contribute it back to the SXT-Community on Github!
We ❤️ contributors!