Using Python and Streamlit with MS SQL

Python

Duke's note


## create venv 
python -m venv <path/to/new/virtual/environment>

## install requirement  / pip uninstall - uninstall module
pip install  numpy #if notworking try pip install "numpy<2.0.0"
pip install pymssql 
pip install pandas 
pip install python-dotenv
pip install streamlit

## numpy  1.26.4
## python  3.12
## streamlit 1.51.0
## pymssql   2.3.9

MSSQL

MS SQL 2012 Express ADV : https://www.microsoft.com/en-us/download/details.aspx?id=50003&msockid=1eee7ca5e7d76a7112ac6a09e3d76c91
ODBC : https://docs.microsoft.com/sql/connect/odbc/download-odbc-driver-for-sql-server
SQL Server 2012 Express: tds_version='7.0'

Example:

import streamlit as st
import pymssql
import pandas as pd
import datetime

DB_SERVER = 'localhost'
DB_USER = 'sa'
DB_PASSWORD = 'AdamsonUniversity'
DB_NAME = 'Event_manage'

## Task - 1 connect db function
def get_connection():
    try:
        conn = pymssql.connect(
            server = DB_SERVER,
            user = DB_USER,
            password = DB_PASSWORD,
            database = DB_NAME,
            tds_version='7.0',
            as_dict = True #translate to dict format
        )
        return conn
    except Exception as e:
        st.error(f"Cann't conntect to Database: {e}")
        return None