Python, ADO GetRows(): When reading from sql numeric values comes with comma as a decimal seperator

Question

I have written Python code to get data from sql server similar to this example shown here: https://mail.python.org/pipermail/python-list/2002-March/131737.html

However, when I read data into Python for numerical values it uses comma as a decimal seperator, so all my numerical columns turns into string. When I acces data from Microsoft SQL Server Management Studio it shows period mark / dot / '.' as decimal seperator which is the desired way. Also, it shows that data is saved correctly. How do I fix this problem?

My computer language is Swedish maybe that is part of the probem? I have tried to see if I can find the answer here but without luck: IIS 7 - floats returning with commas instead of periods

If you want to see the exact code then it is:

import win32com.client
import pandas as pd
def msql2pandas(query_,datasource_):
    adoConn = win32com.client.Dispatch('ADODB.Connection')
    adoConn.Open("Provider = SQLOLEDB; Data Source = {}; Integrated Security = SSPI".format(datasource_))

    (adoRS, succes) = adoConn.Execute(query_)
    adoRS.MoveFirst()
    data = []
    cols = []
    data_type = []
    n_col = adoRS.Fields.Count
    for i in range(n_col):
        cols.append(adoRS.Fields.Item(i).Name)
        data_type.append(adoRS.Fields.Item(i).Type)

    while not adoRS.EOF:
        temp = [[i[0] for i in adoRS.GetRows(1)]]
        assert len(temp[0]) == n_col
        data.extend(temp)

    return {'data': pd.DataFrame(data = data, columns = cols), 'data_type': data_type}

I kknow I can manually replace comma with dots and change the datatype but I want a solution where it reads data correctly to begin with. — k.dkhk, Jul 21 '23 at 12:45
Is there a reason you're trying to use Pandas with COM and ADODB instead of with pyodbc and ODBC Driver for SQL Server? e.g.: [Insert data from a SQL table into a Python pandas dataframe](https://learn.microsoft.com/en-us/sql/machine-learning/data-exploration/python-dataframe-pandas) — AlwaysLearning, Jul 21 '23 at 13:25
@AlwaysLearning Hi. I am not sure, the IT department at my company asked me not to use ODBC. — k.dkhk, Jul 21 '23 at 13:51

score 0 · Answer 1 · answered Jul 23 '23 at 16:50

0

Try to change the decimal separator in your locale options (Control Panel | Region | Additional Settings | Decimal Symbol).

answered Jul 23 '23 at 16:50

Dmitry Streblechenko

62,942
4
53
78

apology for the stupid qestion; in Python(I am using pycharm) or windows? – k.dkhk Jul 25 '23 at 08:22
In Windows. Python picks up the local settings AFAIK. – Dmitry Streblechenko Jul 25 '23 at 15:51

Python, ADO GetRows(): When reading from sql numeric values comes with comma as a decimal seperator

1 Answers1