How to retrieve the data from the database using pyodbc and sql server with python

Using Streamlit
1

I have a python code that connect with sql server and retrieve the required data based on select query and display the result as a table with columns and rows.

The problem is that the retrieved data is displayed without columns name as shown in the picture below.

enter image description here

code:

import pandas as pd
import streamlit as st

st_input_update = st.number_input if is_numeric_dtype(all_columns) else st.text_input
search_term=st_input_update("Enter Search Term")
                        
sql='select * from testDB.dbo.t1 where last LIKE  ? '
param=f'%{search_term}%'
rows = cursor.execute(sql, param).fetchall() 
st.dataframe(rows)
2

Hi!

How did you make pyodbc work with streamlit?
I trying to publish an app that retrieves data from SQL Server but streamlit is not working with pyodbc :frowning:

3

hi sorry for the late reply did you solve the problem or not yet?

anw these are the steps that i used:

  1. import pyodbc
  2. add required parameters:

  • odbc driver type & version

  • server ip or name

  • db name

  • credentials account of exist or windows authentication

  1. create cursor object
  2. write select query that return the desired table its converted to dataframe using pd.read_sql_query()

Note: write your code inside try exception in order to not crash the app

hope that this will help.

example


function to connect to SQL SEVER DB

def connectToDB():

    try:

        con = pyodbc.connect(

            driver="SQL Server",

             Server=***********

            DATABASE="testDB",

            UID="test",

            PWD="test",

        )

        cursor = con.cursor()

        df = pd.read_sql_query('select * from testDB.dbo.t1',con)

    except Exception as e:

        st.write("error is: {}".format(e))

    return df
4

I cannot import pyodbc or any of the other sql server libraries when trying to deploy a streamlit app. Keep getting ModuleNotFoundError.