Read data from SQL Server to pandas using pyodbc

Question

I am trying to import SQL server data in pandas as a dataframe. I currently have the following code:

import pandas as pd
import pyodbc

# SQL Authentication
conn = pyodbc.connect(
        'Driver={SQL Server};'
        'Server=Servername;'
        'Database=DBName;'
        'UID=logIn;'
        'PWD=Password;')
    
cursor = conn.cursor()
    
cursor.execute('SELECT * FROM Table')
    
result = cursor.fetchall()
     
cursor.close()
print(result)

This returns the data, but in the following format:

[(24609189, 'EURTRY.pro', Decimal('5'), Decimal('11'), Decimal('5.0000'), Decimal('105.0000'), Decimal('-2.200000'), Decimal('0.000000'), datetime.datetime(2018, 10, 15, 16, 47, 17, 800000), None)]

How am I able to get this into a dataframe, and is there a way to remove the Decimal in front of the numbers?

did you try the native pandas method pd.read_sql("SELECT * FROM schema.Table", conn) — Umar.H
– Umar.H, Commented Mar 12, 2021 at 10:25

buddemat · Accepted Answer · 2021-03-20 20:15:40Z

2

You should be able to do this directly with read_sql() from Pandas:

import pandas as pd
import pyodbc

# SQL Authentication
conn = pyodbc.connect(
        'Driver={SQL Server};'
        'Server=Servername;'
        'Database=DBName;'
        'UID=logIn;'
        'PWD=Password;') 

sql = 'SELECT * FROM Table'
data = pd.read_sql(sql,conn)

answered Mar 20, 2021 at 20:15

buddemat

5,33016 gold badges37 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

Read data from SQL Server to pandas using pyodbc

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related