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

Question

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.

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)

Conic · Accepted Answer · 2021-06-30 18:48:23Z

1

To use st.dataframe You need to create a pandas Dataframe.
What you get after your query is not a DataFrame, it is a list of tuples.

Try this

df = pd.DataFrame(rows, columns = ["ID", "first_name", "last_name"])  

st.dataframe(df)

answered Jun 30, 2021 at 18:48

Conic

1,2181 gold badge14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DevLeb2022 Over a year ago

i tried your answer it display the below error: ValueError: Shape of passed values is (6, 1), indices imply (6, 3)

DevLeb2022 Over a year ago

i fix it by adding this function to the dataframe .from_records()

Conic Over a year ago

Ah ok. Your code implies that it would be (6,3). You should post your .from_records() as it's own answer to this post.

vvvvv · Accepted Answer · 2022-03-08 12:00:56Z

1

I fixed it by adding this function to the dataframe .from_records() so the line of code becomes:

df = pd.DataFrame.from_records(rows, columns =columns)

edited Mar 8, 2022 at 12:00

vvvvv

32.9k19 gold badges70 silver badges103 bronze badges

answered Jul 1, 2021 at 20:01

DevLeb2022

69519 silver badges55 bronze badges

Collectives™ on Stack Overflow

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

code:

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

code:

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related