{"id":4646,"date":"2022-12-05T16:18:41","date_gmt":"2022-12-05T06:18:41","guid":{"rendered":"http:\/\/bicortex.com\/?p=4646"},"modified":"2023-02-07T09:12:32","modified_gmt":"2023-02-06T23:12:32","slug":"building-rapid-data-import-interfaces-with-pysimplegui-and-streamlit","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/building-rapid-data-import-interfaces-with-pysimplegui-and-streamlit\/","title":{"rendered":"Building rapid data import interfaces with PySimpleGUI and Streamlit"},"content":{"rendered":"<p class=\"Standard\" style=\"text-align: justify;\">Note: All code from this post can be downloaded from my OneDrive folder <a href=\"https:\/\/1drv.ms\/u\/s!AuEyKKgH71pxh4B7ZHxkVMFoB5FU6w?e=EMtB4r\" target=\"_blank\" rel=\"noopener\">HERE<\/a>.<\/p>\n<h3 style=\"text-align: center;\">Introduction<\/h3>\n<p class=\"Standard\" style=\"text-align: justify;\">The proliferation of low-code or no-code solutions for building simple apps has taken a solid foothold in the industry and a lot can be achieved with minimal effort and supporting code. As we enter an era of AI-enabled development, we\u2019re seeing a lot of productivity gains from complex linguistic framework e.g. ChatGPT which can take requirements as input and generate comprehensive code in a matter of seconds. This creates an interesting conundrum \u2013 are developers automating themselves out of their jobs or are they making their work more enjoyable by \u2018outsourcing\u2019 the most mundane parts of their job to focus on what\u2019s truly important \u2013 providing business value. It\u2019s a discussion for a separate post but there\u2019s no denying that the days of manually crafting application \u2018scaffolding\u2019 and reinventing the wheel are coming to an end as more of us lean towards using mobile, desktop or Web frameworks, ORMs, predefined libraries and packages or other adaptive software development approaches to expedite output delivery.<\/p>\n<p class=\"Standard\" style=\"text-align: justify;\">For those who need to provide a visual interface for either data entry or data output, there is a plethora of choices out there. Building a simple data entry form or a bare-bones site with a few widgets to allow end-users to interact with the content typically involves a combination of multiple technologies and languages but it\u2019s surprising how much functionality can be achieved using some of the frameworks available in Python. In this post I\u2019d like to explore how anyone can build a simple flat file data import interface for SQL Server. The need for this type of solution came out of multiple requests from non-technical clients needing to interface data sources familiar to them e.g. CSV, Excel with their internal database(s) and with a few button clicks upload\/insert the required dataset to augment or change upstream reporting or analytics. It\u2019s nothing cutting-edge and most visualisation tools provide the means to do a low-level data prep through pseudo ETL-like process e.g. Power BI Data Flows or Tableau Prep Builder but they still require technical expertise and the knowledge of underlying schemas and structures. Sometimes, all that\u2019s required is a simple interface with a few widgets \u2013 that\u2019s where frameworks like PySimpleGUI and Streamlit shine.<\/p>\n<p class=\"Standard\" style=\"text-align: justify;\">For this exercise, let\u2019s look at a very simple interface for validating and loading flat file data into a database table. The objective is to provide end-users with an app which allows them to:<\/p>\n<ul>\n<li style=\"text-align: justify;\">Authenticate to Microsoft SQL Server instance running on their network using either Windows Authentication or SQL Server authentication<\/li>\n<li style=\"text-align: justify;\">Validate their supplied credentials<\/li>\n<li style=\"text-align: justify;\">Search for the required file on their file system<\/li>\n<li style=\"text-align: justify;\">Select a table this data needs to be loaded into<\/li>\n<li style=\"text-align: justify;\">Run a series of validation steps and if no issues are detected, load the CSV file into the nominated table<\/li>\n<\/ul>\n<p class=\"Standard\" style=\"text-align: justify;\">Let\u2019s dive in and see how quick and productive one can be using these two frameworks.<\/p>\n<h3 style=\"text-align: center;\">PySimpleGUI<\/h3>\n<p class=\"Standard\" style=\"text-align: justify;\">Launched in 2018, PySimpleGUI is a python library that wraps tkinter, Qt (pyside2), wxPython and Remi (for browser support), allowing very fast and simple-to-learn GUI programming. Your code is not required to have an object-oriented architecture \u2013 one of the major obstacles when writing GUI applications &#8211; which makes the package usable by a larger audience. PySimpleGUI code is simpler and shorter than writing directly using the underlying framework because PySimpleGUI implements much of the &#8220;boilerplate code&#8221; for you. Additionally, interfaces are simplified to require as little code as possible (half to 1\/10th) to get the desired result.<\/p>\n<p class=\"Standard\" style=\"text-align: justify;\">This app interface can be designed in many different ways but for this exercise, I have kept the functionality and output to minimum and when executing app.py file we get the following output.<\/p>\n<p><a href=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2023\/02\/PySimpleGUI_Data_Upload_App_Demo_Gif.gif\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4680\" src=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2023\/02\/PySimpleGUI_Data_Upload_App_Demo_Gif.gif\" alt=\"\" width=\"580\" height=\"760\" \/><\/a><\/p>\n<p class=\"Standard\" style=\"text-align: justify;\">PySimpleGUI allows for creating bespoke windows and layouts using lists \u2013 in this example I\u2019ve defined three to account for the separation between database login-specific elements, file search-specific elements and text output elements. These three are then combined using the \u2018layout\u2019 list which also \u2018draws\u2019 a frame around each of them to make these three areas visually distinct. Next, we have a function responsible for all the heavy listing i.e. data validation and load and finally the main method binding it all together. I\u2019m not going to go over PySimpleGUI API as it\u2019s one of those projects which has a very comprehensive support documentation. It also features over 300 Demo Programs which provide many design patterns for you to learn how to use PySimpleGUI and how to integrate PySimpleGUI with other packages and a cookbook featuring recipes covering many different scenarios. It\u2019s by far one of the best documented open-source projects I have came across. To pip-install it into you default Python interpreter or a virtual environment simply run one of the following lines:<\/p>\n<pre class=\"brush: python; title: ; notranslate\" title=\"\">\r\npip install pysimplegui\r\nor\r\npip3 install pysimplegui\r\n<\/pre>\n<p class=\"Standard\" style=\"text-align: justify;\">The following short snippet of Python is responsible for most of this little app&#8217;s functionality, proving that minimal amount of code is required to build a complete interface which fulfills all requirements specyfied.<\/p>\n<pre class=\"brush: python; title: ; notranslate\" title=\"\">\r\nimport PySimpleGUI as sg\r\nimport os\r\nimport csv\r\nfrom pathlib import Path\r\nimport helpers as helpers\r\n\r\n\r\n_WORKING_DIR = os.getcwd()\r\n_SQL_TABLES = &#x5B;'Test_Table1', 'Test_Table2', 'Test_Table3']\r\n_LOAD_STORED_PROC = 'usp_load_ext_table'\r\n_TARGET_TABLE_SCHEMA_NAME = 'dbo'\r\n\r\ndb_layout = &#x5B;&#x5B;sg.Text('Provide SQL Server instance details and credentials to authenticate.')],\r\n             &#x5B;sg.Text('Host Name ', size=(15, 1)), sg.Input(\r\n                 key='-HOSTNAME-', default_text='192.168.153.128')],\r\n             &#x5B;sg.Text('Database Name ', size=(15, 1)),\r\n              sg.Input(key='-DATABASE-', enable_events=True, default_text='TestDB')], &#x5B;sg.Checkbox('Use Windows Authentication', enable_events=True, default=False, key='-USEWINAUTH-')],\r\n             &#x5B;sg.Text('User Name ', size=(15, 1), key='-USERNAMELABEL-'), sg.Input(\r\n                 key='-USERNAME-', enable_events=True, default_text='test_login')],\r\n             &#x5B;sg.Text('Password ', size=(15, 1), key='-PASSWORDLABEL-'), sg.Input(\r\n                 key='-PASSWORD-',  password_char='*',\r\n                 enable_events=True, default_text='test_password')],\r\n             &#x5B;sg.Button('Validate Database Connection', key='-VALIDATE-'), sg.Text(\r\n                 '--&gt; This operation may take up to 1 minute', visible=True, key='_text_visible_')]\r\n             ]\r\n\r\nfile_search_layout = &#x5B;&#x5B;sg.Text('Provide CSV file for upload and database table to load into.')],\r\n                      &#x5B;sg.InputText(key='-FILEPATH-', size=(55, 1)),\r\n                       sg.FileBrowse(initial_folder=_WORKING_DIR, file_types=&#x5B;(&quot;CSV Files&quot;, &quot;*.csv&quot;)])],\r\n                      &#x5B;sg.Text('Select database table name to load text data.')],\r\n                      &#x5B;sg.Combo(_SQL_TABLES, size=(37), key='-TABLECHOICE-', readonly=True),\r\n                      sg.Checkbox('Truncate before insert?', default=True, key='-TRUCATESOURCE-')]]\r\n\r\n\r\nstdout_layout = &#x5B;&#x5B;sg.Multiline(size=(62, 10), key='-OUTPUT-')], &#x5B;\r\n    sg.Button('Validate and Submit', key='-SUBMIT-'),\r\n    sg.Button('Clear Output', key='-CLEAR-')]]\r\n\r\n\r\nlayout = &#x5B;&#x5B;sg.Frame('1. Database Connection Details', db_layout,\r\n                    title_color='blue', font='Any 12', pad=(15, 20))],\r\n          &#x5B;sg.Frame('2. File Upload Details', file_search_layout,\r\n                    title_color='blue', font='Any 12', pad=(15, 20))],\r\n          &#x5B;sg.Frame('3. File Upload Output', stdout_layout,\r\n                    title_color='blue', font='Any 12', pad=(15, 20))]]\r\n\r\n\r\ndef check_file_and_load(db_conn, full_path, table_dropdown_value, truncate_source_table_flag, _LOAD_STORED_PROC, _TARGET_TABLE_SCHEMA_NAME):\r\n    file_name = Path(full_path).name\r\n    file_path = Path(full_path).parent\r\n\r\n    # check csv file is comma delimited\r\n    window&#x5B;'-OUTPUT-'].print('Validating selected file is comma-delimited...', end='')\r\n    with open(full_path, 'r', newline='') as f:\r\n        reader = csv.reader(f, delimiter=&quot;,&quot;)\r\n        dialect = csv.Sniffer().sniff(f.read(1024))\r\n    if dialect.delimiter != ',':\r\n        window&#x5B;'-OUTPUT-'].print('Failed!', text_color='red')\r\n        return\r\n    else:\r\n        window&#x5B;'-OUTPUT-'].print('OK!')\r\n\r\n    # check csv file is not empty\r\n    window&#x5B;'-OUTPUT-'].print('Validating selected file is not empty...', end='')\r\n    with open(full_path, 'r', newline='') as f:\r\n        reader = csv.reader(f, dialect)\r\n        ncols = len(next(reader))\r\n        f.seek(0)\r\n        nrow = len(list(reader))\r\n    if nrow &lt; 2:\r\n        window&#x5B;'-OUTPUT-'].print('Failed!', text_color='red')\r\n        return\r\n    else:\r\n        window&#x5B;'-OUTPUT-'].print('OK!')\r\n\r\n    # check for database connection values correctness\r\n    window&#x5B;'-OUTPUT-'].print(\r\n        'Validating database connection details are correct...', end='')\r\n    conn = db_conn(HOSTNAME=values&#x5B;'-HOSTNAME-'], DATABASE=values&#x5B;'-DATABASE-'],\r\n                   USERNAME=values&#x5B;'-USERNAME-'],   PASSWORD=values&#x5B;'-PASSWORD-'], USEWINAUTH=values&#x5B;'-USEWINAUTH-'])\r\n    if conn:\r\n        window&#x5B;'-OUTPUT-'].print('OK!')\r\n    else:\r\n        window&#x5B;'-OUTPUT-'].print('Failed!', text_color='red')\r\n\r\n    # check required schema exists on the target server\r\n    window&#x5B;'-OUTPUT-'].print('Validating target schema existance...', end='')\r\n    with conn.cursor() as cursor:\r\n        sql = &quot;&quot;&quot;SELECT TOP (1) 1 FROM {target_db}.sys.schemas \r\n                        WHERE name = '{target_schema}'&quot;&quot;&quot;.format(target_db=values&#x5B;'-DATABASE-'], target_schema = _TARGET_TABLE_SCHEMA_NAME)\r\n        cursor.execute(sql)\r\n        rows = cursor.fetchone()\r\n    if rows:\r\n        window&#x5B;'-OUTPUT-'].print('OK!')\r\n    else:\r\n        window&#x5B;'-OUTPUT-'].print('Failed!', text_color='red')\r\n        return\r\n\r\n    # check selected table exists on the target server\r\n    window&#x5B;'-OUTPUT-'].print('Validating target table existance...', end='')\r\n    with conn.cursor() as cursor:\r\n        sql = &quot;&quot;&quot;SELECT TOP (1) 1 FROM {target_db}.information_schema.tables \r\n                        WHERE table_name = '{target_table}'&quot;&quot;&quot;.format(target_db=values&#x5B;'-DATABASE-'], target_table=table_dropdown_value)\r\n        cursor.execute(sql)\r\n        rows = cursor.fetchone()\r\n    if rows:\r\n        window&#x5B;'-OUTPUT-'].print('OK!')\r\n    else:\r\n        window&#x5B;'-OUTPUT-'].print('Failed!', text_color='red')\r\n        return\r\n\r\n    # check if insert sored proc exists\r\n    window&#x5B;'-OUTPUT-'].print(&quot;Validating loading stored procedure exists...&quot;, end='')\r\n    with conn.cursor() as cursor:\r\n        sql = &quot;&quot;&quot;SELECT TOP (1) 1 FROM {target_db}.sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.usp_load_ext_table')&quot;&quot;&quot;.format(\r\n            target_db=values&#x5B;'-DATABASE-'])\r\n        cursor.execute(sql)\r\n        sp = cursor.fetchone()\r\n    if sp:\r\n        window&#x5B;'-OUTPUT-'].print('OK!')\r\n    else:\r\n        window&#x5B;'-OUTPUT-'].print('Failed!', text_color='red')\r\n        return\r\n\r\n    # check if number of columns matches\r\n    window&#x5B;'-OUTPUT-'].print('Validating columns number match...', end='')\r\n    with conn.cursor() as cursor:\r\n        sql = &quot;&quot;&quot;SELECT COUNT(1) FROM {target_db}.information_schema.columns \r\n                        WHERE table_name = '{target_table}'&quot;&quot;&quot;.format(target_db=values&#x5B;'-DATABASE-'], target_table=table_dropdown_value)\r\n        cursor.execute(sql)\r\n        dbcols = cursor.fetchone()&#x5B;0]\r\n    if dbcols == ncols:\r\n        window&#x5B;'-OUTPUT-'].print('OK!')\r\n    else:\r\n        window&#x5B;'-OUTPUT-'].print('Failed!', text_color='red')\r\n        return\r\n\r\n    window&#x5B;'-OUTPUT-'].print('Attempting to load {csv_file} file into {target_table} table...'.format(\r\n        csv_file=file_name.lower(), target_table=table_dropdown_value.lower()), end='')\r\n    with conn.cursor() as cursor:\r\n        sql = '''\\\r\n                DECLARE @col_names VARCHAR (MAX); \r\n                EXEC TestDB.dbo.{sp_name} \r\n                @temp_target_table_name=?,\r\n                @target_table_name=?,\r\n                @target_table_schema_name=?, \r\n                @file_path=?, \r\n                @truncate_target_table=?,\r\n                @col_names = @col_names OUTPUT; \r\n                SELECT @col_names AS col_names\r\n                '''.format(sp_name=_LOAD_STORED_PROC)\r\n        params = ('##'+table_dropdown_value, table_dropdown_value,\r\n                  _TARGET_TABLE_SCHEMA_NAME, full_path, truncate_source_table_flag)         \r\n        cursor.execute(sql, params)\r\n        col_names = cursor.fetchval()\r\n\r\n        if truncate_source_table_flag == 1:\r\n            test_1_sql = 'SELECT TOP (1) 1 FROM (SELECT {cols} FROM {temp_target_table_name} EXCEPT SELECT {cols} FROM {target_table_schema_name}.{target_table_name})a'.format(\r\n                temp_target_table_name='##'+table_dropdown_value, target_table_schema_name=_TARGET_TABLE_SCHEMA_NAME, target_table_name=table_dropdown_value, cols=col_names)\r\n            cursor.execute(test_1_sql)\r\n            test_1_rows = cursor.fetchone()\r\n            test_2_sql = 'SELECT TOP (1) status FROM {temp_target_table_name}'.format(\r\n                temp_target_table_name='##'+table_dropdown_value)\r\n            cursor.execute(test_2_sql)\r\n            test_2_rows = cursor.fetchone()&#x5B;0]\r\n            if test_1_rows and test_2_rows != 'SUCCESS':\r\n                window&#x5B;'-OUTPUT-'].print('Failed!', text_color='red')\r\n            else:\r\n                window&#x5B;'-OUTPUT-'].print('OK!')\r\n                return\r\n        elif truncate_source_table_flag == 0:\r\n            test_1_sql = 'SELECT COUNT(1) as ct FROM (SELECT {cols} FROM {temp_target_table_name} INTERSECT SELECT {cols} FROM {target_table_schema_name}.{target_table_name})a'.format(\r\n                temp_target_table_name='##'+table_dropdown_value, target_table_schema_name=_TARGET_TABLE_SCHEMA_NAME, target_table_name=table_dropdown_value, cols=col_names)\r\n            cursor.execute(test_1_sql)\r\n            test_1_rows = cursor.fetchone()&#x5B;0]\r\n            test_2_sql = 'SELECT TOP (1) status FROM {temp_target_table_name}'.format(\r\n                temp_target_table_name='##'+table_dropdown_value)\r\n            cursor.execute(test_2_sql)\r\n            test_2_rows = cursor.fetchone()&#x5B;0]\r\n            if nrow-1 != test_1_rows or test_2_rows != 'SUCCESS':\r\n                window&#x5B;'-OUTPUT-'].print('Failed!', text_color='red')\r\n            else:\r\n                window&#x5B;'-OUTPUT-'].print('OK!')\r\n                return\r\n        \r\n\r\nwindow = sg.Window('File Upload Utility version 1.01', layout)\r\nwhile True:\r\n    event, values = window.read()\r\n    if event == sg.WIN_CLOSED or event == 'Exit':\r\n        break\r\n    elif '-USEWINAUTH-' in event:\r\n        win_auth_setting = values&#x5B;'-USEWINAUTH-']\r\n        if win_auth_setting == True:\r\n            window&#x5B;'-USERNAMELABEL-'].Update(visible=False)\r\n            window&#x5B;'-PASSWORDLABEL-'].Update(visible=False)\r\n            window&#x5B;'-USERNAME-'].Update(visible=False)\r\n            window&#x5B;'-PASSWORD-'].Update(visible=False)\r\n        elif win_auth_setting == False:\r\n            window&#x5B;'-USERNAMELABEL-'].Update(visible=True)\r\n            window&#x5B;'-PASSWORDLABEL-'].Update(visible=True)\r\n            window&#x5B;'-USERNAME-'].Update(visible=True)\r\n            window&#x5B;'-PASSWORD-'].Update(visible=True)\r\n\r\n    elif '-VALIDATE-' in event:\r\n        validation = helpers.validate_db_input_values(values)\r\n        if validation:\r\n            error_msg = ('\\nInvalid: ' + value for value in validation)\r\n            error_message = helpers.generate_error_message(validation)\r\n            sg.popup(error_message, keep_on_top=True)\r\n        else:\r\n            HOSTNAME = values&#x5B;'-HOSTNAME-'],\r\n            DATABASE = values&#x5B;'-DATABASE-'],\r\n            USERNAME = values&#x5B;'-USERNAME-'],\r\n            PASSWORD = values&#x5B;'-PASSWORD-'],\r\n            USEWINAUTH = values&#x5B;'-USEWINAUTH-']\r\n            conn = helpers.db_conn(HOSTNAME=values&#x5B;'-HOSTNAME-'], DATABASE=values&#x5B;'-DATABASE-'],\r\n                                USERNAME=values&#x5B;'-USERNAME-'],   PASSWORD=values&#x5B;'-PASSWORD-'], USEWINAUTH=values&#x5B;'-USEWINAUTH-'])\r\n            if conn:\r\n                sg.popup('Supplied credentails are valid!',\r\n                        keep_on_top=True, title='')\r\n            else:\r\n                sg.popup('Supplied credentails are invalid or there is a network connection issue.',\r\n                        keep_on_top=True, title='', button_color='red')\r\n    elif '-SUBMIT-' in event:\r\n        validation = helpers.validate_file_input_values(values)\r\n        if validation:\r\n            error_msg = ('\\nInvalid: ' + value for value in validation)\r\n            error_message = helpers.generate_error_message(validation)\r\n            sg.popup(error_message, keep_on_top=True)\r\n        else:\r\n            truncate_source_table_flag = bool(values&#x5B;'-TRUCATESOURCE-'])\r\n            table_dropdown_value = values&#x5B;'-TABLECHOICE-']\r\n            window&#x5B;'-OUTPUT-'].update('')\r\n            full_path = str(Path(values&#x5B;'-FILEPATH-']))\r\n            file_name = Path(full_path).name\r\n            file_path = Path(full_path).parent\r\n            db_conn = helpers.db_conn\r\n            check_file_and_load(db_conn,\r\n                                full_path, table_dropdown_value, truncate_source_table_flag, _LOAD_STORED_PROC, _TARGET_TABLE_SCHEMA_NAME)\r\n    elif '-CLEAR-' in event:\r\n        window&#x5B;'-OUTPUT-'].update('')\r\nwindow.close()\r\n<\/pre>\n<p class=\"Standard\" style=\"text-align: justify;\">The main app.py file also import from the helper module which contains functions used for input validation, database connection and error message generation. You can find all the code used for this solution in my OneDrive folder <a href=\"https:\/\/1drv.ms\/u\/s!AuEyKKgH71pxh4B7ZHxkVMFoB5FU6w?e=EMtB4r\" target=\"_blank\" rel=\"noopener\">HERE<\/a>.<\/p>\n<p class=\"Standard\" style=\"text-align: justify;\">Finally, tying it all together is a bit of T-SQL wrapped around a stored procedure. This code takes five parameters as input values and outputs one value into the Python code defining the names of the columns of our target database table (used in Python code validation). Its primary function is to:<\/p>\n<ul>\n<li style=\"text-align: justify;\">Create a global temporary table mirrored on the schema of the target table<\/li>\n<li style=\"text-align: justify;\">Using BULK INSERT operation insert our flat file\u2019s data into it<\/li>\n<li style=\"text-align: justify;\">Optionally truncate target object and insert the required data from the temp table into the destination table<\/li>\n<li style=\"text-align: justify;\">Alter temp table with the operation status i.e. Failure of Success (used in Python validation code)<\/li>\n<\/ul>\n<p class=\"Standard\" style=\"text-align: justify;\">The reason why a global temporary table is created and loaded into first is that we want to ensure that the CSV file\u2019s data and schema is conforming to what the target table structure is before we make any changes to it. Having this step in place allows for an extra level of validation \u2013 if the temporary table insert fails, target table insertion code is not triggered at all. Likewise, if the temporary table with the schema identical to that of the destination table is created and populated successfully, we can be confident that the target table\u2019s data can be purged (as denoted by one of the parameter\u2019s value) and loaded into without any issues. The full T-SQL code is as follows:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;TestDB];\r\nGO\r\nSET ANSI_NULLS ON;\r\nGO\r\nSET QUOTED_IDENTIFIER ON;\r\nGO\r\nCREATE PROCEDURE &#x5B;dbo].&#x5B;usp_load_ext_table]\r\n(\r\n    @temp_target_table_name VARCHAR(256),\r\n    @target_table_name VARCHAR(256),\r\n    @target_table_schema_name VARCHAR(256),\r\n    @file_path VARCHAR(1024),\r\n    @truncate_target_table BIT,\r\n    @col_names VARCHAR(MAX) OUTPUT\r\n)\r\nAS\r\nBEGIN\r\n    SET NOCOUNT ON;\r\n    DECLARE @cols VARCHAR(MAX);\r\n    DECLARE @sql VARCHAR(MAX);\r\n    DECLARE @error_message VARCHAR(MAX);\r\n\r\n    SELECT @sql\r\n        = 'DROP TABLE IF EXISTS ' + @temp_target_table_name + '; CREATE TABLE ' + @temp_target_table_name + ' ('\r\n          + o.list + ')',\r\n           @cols = j.list\r\n    FROM sys.tables t\r\n        CROSS APPLY\r\n    (\r\n        SELECT STUFF(\r\n               (\r\n                   SELECT ',' + QUOTENAME(c.COLUMN_NAME) + ' ' + c.DATA_TYPE\r\n                          + CASE c.DATA_TYPE\r\n                                WHEN 'sql_variant' THEN\r\n                                    ''\r\n                                WHEN 'text' THEN\r\n                                    ''\r\n                                WHEN 'ntext' THEN\r\n                                    ''\r\n                                WHEN 'xml' THEN\r\n                                    ''\r\n                                WHEN 'decimal' THEN\r\n                                    '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR) + ', '\r\n                                    + CAST(c.NUMERIC_SCALE AS VARCHAR) + ')'\r\n                                ELSE\r\n                                    COALESCE(   '(' + CASE\r\n                                                          WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 THEN\r\n                                                              'MAX'\r\n                                                          ELSE\r\n                                                              CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)\r\n                                                      END + ')',\r\n                                                ''\r\n                                            )\r\n                            END\r\n                   FROM INFORMATION_SCHEMA.COLUMNS c\r\n                       JOIN sysobjects o\r\n                           ON c.TABLE_NAME = o.name\r\n                   WHERE c.TABLE_NAME = @target_table_name\r\n                         AND TABLE_NAME = t.name\r\n                   ORDER BY ORDINAL_POSITION\r\n                   FOR XML PATH('')\r\n               ),\r\n               1,\r\n               1,\r\n               ''\r\n                    )\r\n    ) o(list)\r\n        CROSS APPLY\r\n    (\r\n        SELECT STUFF(\r\n               (\r\n                   SELECT ',' + QUOTENAME(c.COLUMN_NAME)\r\n                   FROM INFORMATION_SCHEMA.COLUMNS c\r\n                       JOIN sysobjects o\r\n                           ON c.TABLE_NAME = o.name\r\n                   WHERE c.TABLE_NAME = @target_table_name\r\n                   ORDER BY ORDINAL_POSITION\r\n                   FOR XML PATH('')\r\n               ),\r\n               1,\r\n               1,\r\n               ''\r\n                    )\r\n    ) j(list)\r\n    WHERE t.name = @target_table_name;\r\n\r\n    EXEC (@sql);\r\n    SET @col_names = @cols;\r\n\r\n    IF NOT EXISTS\r\n    (\r\n        SELECT *\r\n        FROM tempdb.INFORMATION_SCHEMA.TABLES\r\n        WHERE TABLE_NAME = @temp_target_table_name\r\n    )\r\n    BEGIN\r\n        SET @error_message\r\n            = 'Global temporary placeholder table has not been successfully created in the tempdb database. Please troubleshoot.';\r\n        RAISERROR(   @error_message, -- Message text.\r\n                     16,             -- Severity.\r\n                     1               -- State.\r\n                 );\r\n        RETURN;\r\n    END;\r\n\r\n    SET @sql\r\n        = '\r\n\t\t\tBULK INSERT ' + @temp_target_table_name + '\r\n\t\t\tFROM ' + QUOTENAME(@file_path, '''')\r\n          + '\r\n\t\t\tWITH\r\n\t\t\t(\r\n\t\t\t\tFIRSTROW = 2,\r\n\t\t\t\tFIELDTERMINATOR = '','',\r\n\t\t\t\tROWTERMINATOR = ''\\n'',\r\n\t\t\t\tMAXERRORS=0,\r\n\t\t\t\tTABLOCK\r\n\t\t\t)';\r\n    EXEC (@sql);\r\n\r\n    SET @sql = CASE\r\n                   WHEN @truncate_target_table = 1 THEN\r\n                       'TRUNCATE TABLE ' + @target_table_schema_name + '.' + @target_table_name + '; '\r\n                   ELSE\r\n                       ''\r\n               END + 'INSERT INTO ' + @target_table_schema_name + '.' + @target_table_name + ' (' + @cols + ') ';\r\n    SET @sql = @sql + 'SELECT ' + @cols + ' FROM ' + @temp_target_table_name + '';\r\n    BEGIN TRANSACTION;\r\n    BEGIN TRY\r\n        EXEC (@sql);\r\n        SET @sql\r\n            = 'ALTER TABLE ' + @temp_target_table_name + ' ADD status VARCHAR(56) DEFAULT ''FAILURE'', ' + CHAR(13);\r\n        SET @sql = @sql + 'status_message varchar(256) NULL; ' + CHAR(13);\r\n        EXEC (@sql);\r\n        SET @sql = 'UPDATE ' + @temp_target_table_name + ' SET status = ''SUCCESS'', ' + CHAR(13);\r\n        SET @sql = @sql + 'status_message = ''Operation executed successfully!''' + CHAR(13);\r\n        EXEC (@sql);\r\n    END TRY\r\n    BEGIN CATCH\r\n        SET @error_message = ERROR_MESSAGE();\r\n        SET @sql\r\n            = 'ALTER TABLE ' + @temp_target_table_name + ' ADD outcome varchar (56) DEFAULT ''FAILURE'',' + CHAR(13);\r\n        SET @sql = @sql + 'outcome_message varchar(256) NULL; ' + CHAR(13);\r\n        EXEC (@sql);\r\n        SET @sql = 'UPDATE ' + @temp_target_table_name + ' SET outcome_message = ''' + @error_message + '''';\r\n        EXEC (@sql);\r\n        IF @@TRANCOUNT &gt; 0\r\n            ROLLBACK TRANSACTION;\r\n    END CATCH;\r\n\r\n    IF @@TRANCOUNT &gt; 0\r\n        COMMIT TRANSACTION;\r\nEND;\r\n<\/pre>\n<p class=\"Standard\" style=\"text-align: justify;\">One drawback of this code is that it assumes the nominated flat file is located on the same server as our SQL Server instance &#8211; the above SQL code uses T-SQL BULK INSERT operation which expects access to the underlying file system and the file itself. This limitation can be alleviated by reading the file&#8217;s content into a dataframe and populating the target table using, for example, Python&#8217;s Pandas library instead. However, in this instance, I expected end users to have access to the system hosting MSSQL instance so all &#8216;heavy lifting&#8217; i.e. data insertion is done using pure T-SQL.<\/p>\n<h3 style=\"text-align: center;\">Streamlit<\/h3>\n<p class=\"Standard\" style=\"text-align: justify;\">As desktop GUI apps only work in certain scenarios, leveraging the previously created code (both T-SQL and Python) we can build a Web front end instead. Streamlit, an open-source Python app framework, allows anyone to transform scripts into sharable web apps, all without the prior knowledge of CSS, HTML or JavaScript. It\u2019s a purely Python-based framework (though some customizations can be achieved with a sprinkling of HTML), claimed to be used by over 80% of Fortune 50 companies and with the backing of Snowflake (which recently acquired it for $800 million), is set to democratize access to data. Streamlit boasts a large gallery of apps, most of them with source code, so it\u2019s relatively easy to explore its capabilities and poke around &#8216;under the hood&#8217;. It also offers Community Cloud where one can deploy, manage, and share apps with the world, directly from Streamlit \u2014 all for free. To pip-install it into you default Python interpreter or a virtual environment simply run the following:<\/p>\n<pre class=\"brush: python; title: ; notranslate\" title=\"\">\r\npip install streamlit\r\n<\/pre>\n<p class=\"Standard\" style=\"text-align: justify;\">To spin up a local web server and run the app in your default web browser you can run the following from the command line:<\/p>\n<pre class=\"brush: python; title: ; notranslate\" title=\"\">\r\nstreamlit run your_script.py &#x5B;-- script args]\r\n<\/pre>\n<p class=\"Standard\" style=\"text-align: justify;\">For this Streamlit app, I tried to keep the interface layout largely unchanged from our previous PySimpleGUI app even though I was tempted to add a bit of eye-candy to the page \u2013 Streamlit has a pretty good integrations with some of the major plotting and visualization Python packages so adding a widget or two would be very easy and make the whole app a lot more appealing. The draft version of the app is as per the image below.<\/p>\n<p><a href=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2023\/02\/Streamlit_Data_Upload_App_Demo_Gif.gif\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-4669\" src=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2023\/02\/Streamlit_Data_Upload_App_Demo_Gif.gif\" alt=\"\" width=\"580\" height=\"931\" \/><\/a><\/p>\n<p class=\"Standard\" style=\"text-align: justify;\">Most of the code changes relate to how Streamlit API works, and the components required to replicate the GUI app functionality. This means that any Python code handling the logic was mostly left unchanged \u2013 a testament to how easy both Streamlit and PySimpleGUI are to get up and running with. The below snippet of Python is used to build this tiny Streamlit app.<\/p>\n<pre class=\"brush: python; title: ; notranslate\" title=\"\">\r\nimport streamlit as st\r\nimport tempfile\r\nimport csv\r\nimport os\r\nfrom pathlib import Path\r\nimport helpers as helpers\r\n\r\n_SQL_TABLES = &#x5B;'Test_Table1', 'Test_Table2', 'Test_Table3']\r\n_LOAD_STORED_PROC = 'usp_load_ext_table'\r\n_TARGET_TABLE_SCHEMA_NAME = 'dbo'\r\n\r\nst.header('Database Connection Details')\r\ncol1, col2 = st.columns(&#x5B;1, 1])\r\nhost_name = col1.text_input('Host Name', value='192.168.153.128')\r\ndb_name = col1.text_input('Database Name', value='TestDB')\r\nwin_auth = col1.checkbox('Use Windows Authentication', value=False)\r\nvalidate = col1.button('Validate Database Connection')\r\n\r\n\r\nuser_name = col2.text_input('User Name', value='test_login')\r\npassword = col2.text_input('Password', type='password', value='test_password')\r\n\r\nif validate:\r\n    validation = helpers.validate_db_input_values(\r\n        host_name, db_name, user_name, password)\r\n    if validation:\r\n        error_message = helpers.generate_error_message(validation)\r\n        st.error(error_message)\r\n    else:\r\n        conn = helpers.db_conn(HOSTNAME=host_name, DATABASE=db_name,\r\n                               USERNAME=user_name,   PASSWORD=password, USEWINAUTH=win_auth)\r\n        if conn:\r\n            st.success('Supplied credentails are valid.')\r\n        else:\r\n            st.error(\r\n                'Supplied credentials are invalid or there is a network connection issue!')\r\n\r\n\r\nst.header('File Upload Details')\r\nuploaded_file = st.file_uploader(\r\n    label='Provide CSV file for upload', type=&#x5B;'.csv'])\r\ntable_dropdown_value = st.selectbox(\r\n    'Select database table name to load text data', options=_SQL_TABLES)\r\ntruncate_source_table = st.checkbox('Truncate source table?', value=True)\r\n\r\nst.header('File Upload Output')\r\nlogtxtbox = st.empty()\r\nlogtxt = ''\r\nlogtxtbox.text_area(&quot;Log: &quot;, logtxt, height=200, key='fdgfgjjj')\r\nupload_status = st.button('Validate and Submit')\r\n\r\n\r\ndef check_file_and_load(db_conn, full_path, table_dropdown_value, truncate_source_table_flag, _LOAD_STORED_PROC, _TARGET_TABLE_SCHEMA_NAME):\r\n    logtxt = 'Validating selected file is comma-delimited...'\r\n    logtxtbox.text_area(&quot;Log: &quot;, logtxt, height=200)\r\n\r\n    # check csv file is comma delimited\r\n    with open(full_path, 'r', newline='') as f:\r\n        reader = csv.reader(f, delimiter=&quot;,&quot;)\r\n        dialect = csv.Sniffer().sniff(f.read(1024))\r\n    if dialect.delimiter != ',':\r\n        logtxt = ''.join((logtxt, 'Failed!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n        return\r\n    else:\r\n        logtxt = ''.join((logtxt, 'OK!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n\r\n    # check csv file is not empty\r\n    logtxt = ''.join((logtxt, 'Validating selected file is not empty...'))\r\n    with open(full_path, 'r', newline='') as f:\r\n        reader = csv.reader(f, dialect)\r\n        ncols = len(next(reader))\r\n        f.seek(0)\r\n        nrow = len(list(reader))\r\n    if nrow &lt; 2:\r\n        logtxt = ''.join((logtxt, 'Failed!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n        return\r\n    else:\r\n        logtxt = ''.join((logtxt, 'OK!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n\r\n    # check for database connection values correctness\r\n    logtxt = ''.join(\r\n        (logtxt, 'Validating database connection details are correct...'))\r\n    conn = db_conn(host_name, db_name, user_name, password, win_auth)\r\n    if conn:\r\n        logtxt = ''.join((logtxt, 'OK!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n    else:\r\n        logtxt = ''.join((logtxt, 'Failed!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n        return\r\n\r\n    # check required schema exists on the target server\r\n    logtxt = ''.join((logtxt, 'Validating target schema existance...'))\r\n    with conn.cursor() as cursor:\r\n        sql = &quot;&quot;&quot;SELECT TOP (1) 1 FROM {target_db}.sys.schemas \r\n                        WHERE name = '{target_schema}'&quot;&quot;&quot;.format(target_db=db_name, target_schema=_TARGET_TABLE_SCHEMA_NAME)\r\n        cursor.execute(sql)\r\n        rows = cursor.fetchone()\r\n    if rows:\r\n        logtxt = ''.join((logtxt, 'OK!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n    else:\r\n        logtxt = ''.join((logtxt, 'Failed!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n        return\r\n\r\n    # check selected table exists on the target server\r\n    logtxt = ''.join((logtxt, 'Validating target table existance...'))\r\n    with conn.cursor() as cursor:\r\n        sql = &quot;&quot;&quot;SELECT TOP (1) 1 FROM {target_db}.information_schema.tables \r\n                        WHERE table_name = '{target_table}'&quot;&quot;&quot;.format(target_db=db_name, target_table=table_dropdown_value)\r\n        cursor.execute(sql)\r\n        rows = cursor.fetchone()\r\n    if rows:\r\n        logtxt = ''.join((logtxt, 'OK!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n    else:\r\n        logtxt = ''.join((logtxt, 'Failed!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n        return\r\n\r\n    # check if insert sored proc exists\r\n    logtxt = ''.join((logtxt, 'Validating loading stored procedure exists...'))\r\n    with conn.cursor() as cursor:\r\n        sql = &quot;&quot;&quot;SELECT TOP (1) 1 FROM {target_db}.sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.usp_load_ext_table')&quot;&quot;&quot;.format(\r\n            target_db=db_name)\r\n        cursor.execute(sql)\r\n        sp = cursor.fetchone()\r\n    if sp:\r\n        logtxt = ''.join((logtxt, 'OK!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n    else:\r\n        logtxt = ''.join((logtxt, 'Failed!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n        return\r\n\r\n    # check if number of columns matches\r\n    logtxt = ''.join((logtxt, 'Validating columns number match...'))\r\n    with conn.cursor() as cursor:\r\n        sql = &quot;&quot;&quot;SELECT COUNT(1) FROM {target_db}.information_schema.columns \r\n                        WHERE table_name = '{target_table}'&quot;&quot;&quot;.format(target_db=db_name, target_table=table_dropdown_value)\r\n        cursor.execute(sql)\r\n        dbcols = cursor.fetchone()&#x5B;0]\r\n    if dbcols == ncols:\r\n        logtxt = ''.join((logtxt, 'OK!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n    else:\r\n        logtxt = ''.join((logtxt, 'Failed!\\n'))\r\n        logtxtbox.text_area('Log: ', logtxt, height=200)\r\n        return\r\n    logtxt = ''.join((logtxt, 'Attempting to load...'))\r\n    with conn.cursor() as cursor:\r\n        sql = '''\\\r\n                DECLARE @col_names VARCHAR (MAX); \r\n                EXEC TestDB.dbo.{sp_name} \r\n                @temp_target_table_name=?,\r\n                @target_table_name=?,\r\n                @target_table_schema_name=?, \r\n                @file_path=?, \r\n                @truncate_target_table=?,\r\n                @col_names = @col_names OUTPUT; \r\n                SELECT @col_names AS col_names\r\n                '''.format(sp_name=_LOAD_STORED_PROC)\r\n        params = ('##'+table_dropdown_value, table_dropdown_value,\r\n                  _TARGET_TABLE_SCHEMA_NAME, full_path, truncate_source_table_flag)\r\n        cursor.execute(sql, params)\r\n        col_names = cursor.fetchval()\r\n\r\n        if truncate_source_table_flag == 1:\r\n            test_1_sql = 'SELECT TOP (1) 1 FROM (SELECT {cols} FROM {temp_target_table_name} EXCEPT SELECT {cols} FROM {target_table_schema_name}.{target_table_name})a'.format(\r\n                temp_target_table_name='##'+table_dropdown_value, target_table_schema_name=_TARGET_TABLE_SCHEMA_NAME, target_table_name=table_dropdown_value, cols=col_names)\r\n            cursor.execute(test_1_sql)\r\n            test_1_rows = cursor.fetchone()\r\n            test_2_sql = 'SELECT TOP (1) status FROM {temp_target_table_name}'.format(\r\n                temp_target_table_name='##'+table_dropdown_value)\r\n            cursor.execute(test_2_sql)\r\n            test_2_rows = cursor.fetchone()&#x5B;0]\r\n            if test_1_rows and test_2_rows != 'SUCCESS':\r\n                st.error('File failed to load successfuly, please troubleshoot!')\r\n                return\r\n            else:\r\n                st.success('File loaded successfully!')\r\n        elif truncate_source_table_flag == 0:\r\n            test_1_sql = 'SELECT COUNT(1) as ct FROM (SELECT {cols} FROM {temp_target_table_name} INTERSECT SELECT {cols} FROM {target_table_schema_name}.{target_table_name})a'.format(\r\n                temp_target_table_name='##'+table_dropdown_value, target_table_schema_name=_TARGET_TABLE_SCHEMA_NAME, target_table_name=table_dropdown_value, cols=col_names)\r\n            cursor.execute(test_1_sql)\r\n            test_1_rows = cursor.fetchone()&#x5B;0]\r\n            test_2_sql = 'SELECT TOP (1) status FROM {temp_target_table_name}'.format(\r\n                temp_target_table_name='##'+table_dropdown_value)\r\n            cursor.execute(test_2_sql)\r\n            test_2_rows = cursor.fetchone()&#x5B;0]\r\n            if nrow-1 != test_1_rows or test_2_rows != 'SUCCESS':\r\n                st.error('File failed to load successfuly, please troubleshoot!')\r\n                return\r\n            else:\r\n                st.success('File loaded successfully!')\r\n\r\n\r\nif upload_status:\r\n    validation = helpers.validate_db_input_values(\r\n        host_name, db_name, user_name, password)\r\n    if validation:\r\n        error_message = helpers.generate_error_message(validation)\r\n        st.error(error_message)\r\n    if uploaded_file is not None:\r\n        with tempfile.NamedTemporaryFile(delete=False, dir='.', suffix='.csv') as f:\r\n            f.write(uploaded_file.getbuffer())\r\n            fp = Path(f.name)\r\n            full_path = str(Path(f.name))\r\n            file_name = Path(full_path).name\r\n            file_path = Path(full_path).parent\r\n        db_conn = helpers.db_conn\r\n        check_file_and_load(db_conn, full_path, table_dropdown_value,\r\n                            truncate_source_table, _LOAD_STORED_PROC, _TARGET_TABLE_SCHEMA_NAME)\r\n    else:\r\n        st.error('Please select at least one flat file for upload!')\r\n<\/pre>\n<h3 style=\"text-align: center;\">Conclusion<\/h3>\n<p class=\"Standard\" style=\"text-align: justify;\">Python has brought a large number of people into the programming community. The number of programs and the range of areas it touches is mindboggling. But more often than not, these technologies are out of reach of all but a handful of people. Most Python programs are &#8220;command line&#8221; based. This isn&#8217;t a problem for programmer-types as we&#8217;re all used to interacting with computers through a text interface. While programmers don&#8217;t have a problem with command-line interfaces, most &#8220;normal people&#8221; do. This creates a digital divide, a &#8220;GUI Gap&#8221;. Adding a GUI or a Web front end to a program opens that program up to a wider audience as it instantly becomes more approachable. Visual interfaces can also make interacting with some programs easier, even for those that are comfortable with a command-line interface.<\/p>\n<p class=\"Standard\" style=\"text-align: justify;\">Both PySimpleGUI and Streamlit provide an easy entry barrier for anyone, even Python novices, and democratise Web and GUI development. For simple projects, these frameworks allow citizen developers to rapidly create value and solve business problems without needing to understand complex technologies and tools. And whilst more critical LOB (Line of Business) apps will demand the use of more advanced technologies in foreseeable future, for small projects requiring simple interfaces and business logic, PySimpleGUI and Streamlit provide a ton of immediate value. Alternatively, one can go even further down the simplification route and try something like <a href=\"https:\/\/github.com\/chriskiehl\/Gooey\" target=\"_blank\" rel=\"noopener\">Gooey<\/a> which conveniently converts (almost) any Python 3 console program into a GUI application with a single line of code!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Note: All code from this post can be downloaded from my OneDrive folder HERE. Introduction The proliferation of low-code or no-code solutions for building simple apps has taken a solid foothold in the industry and a lot can be achieved with minimal effort and supporting code. As we enter an era of AI-enabled development, we\u2019re [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[90,56,5],"tags":[88,24,41,49,19,89],"class_list":["post-4646","post","type-post","status-publish","format-standard","hentry","category-gui","category-programming","category-sql","tag-gui","tag-programming","tag-python","tag-sql","tag-sql-server","tag-streamlit"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/4646","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=4646"}],"version-history":[{"count":29,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/4646\/revisions"}],"predecessor-version":[{"id":4684,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/4646\/revisions\/4684"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=4646"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=4646"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=4646"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}