Read data from SQL Server to pandas using pyodbc

user_15381822 0
Viewed 271

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?

pandas sql-server sql python
1 Answers

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)
user_140157370
Related