Configuration Files
We will use .yaml files to help with configuring the app for the local environment.
sql_config.yaml
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
sql: dev: SERVER_NAME: "DEV-SERVER" DATABASE_NAME: "MY_DATABASE" SQL_DRIVER: > Driver=SQL Server; Server=DEV-SERVER; Database=MY_DATABASE; Trusted_Connection=yes; prod: SERVER_NAME: "PROD-SERVER" DATABASE_NAME: "MY_DATABASE" # for LINUX server SQL_DRIVER: > Driver={ODBC Driver 18 for SQL Server}; Server=PROD-SERVER.azure.com; Database=MY_DATABASE; Trusted_Connection=yes; TrustServerCertificate=yes; |
use sql: for the root.
Then add the environment names you would like to define, in this case I defined an environment for dev, and prod.
Next define the SQL_Driver connection string.
streamlit_config.yaml
Used for configuration options for Streamlit UI elements.
|
1 2 3 4 5 6 7 |
selectbox: rows_per_page: OPTIONS: - 10 - 20 - 50 - 100 |
The select box, allows for showing # of rows per page. It will paginate the data shown in the data_editor widgets.
sql_column_config.yaml
The configuration file defines what tables and what columns you wish to return to the user.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
table: countryregion: SCHEMA: person NAME: countryregion PRIMARY_KEY: "ID" COLUMNS: - CountryRegionCode - Name - ModifiedDate person: SCHEMA: person NAME: person PRIMARY_KEY: "BusinessEntityID" COLUMNS: - PersonType - NameStyle - Title - FirstName - MiddleName - LastName - EmailPromotion - Suffix location: SCHEMA: production NAME: location PRIMARY_KEY: "LocationID" COLUMNS: - Name - CostRate - Availability info: columns: QUERY: info.columns count: QUERY: sys.row_count.sql |
There are two base groups, table and info.
The table group specifies which tables the user can modify. The key is that the name of the table, needs to match the file placed in the query directory.
For our example we allow the user to update the production.location table. Therefore there needs to be a corresponding file in the / query directory, labeled query / production.location.sql
In the second base group there are two informational queries. info.columns, which is used to return the meta data from the database. And the second query sys.row_count which is used to look at the sys tables and get the row count of files.
On the next page, we will review the example query files.
Leave a Reply