pyodbc database connection and number format

Question

I'm connecting a Python algorithm to an Oracle database using pypyodbc library.

I'm using the following:

def connectToServer(DSN,Server,Password):
    connString = "DSN="+DSN+";Uid="+Server+";Pwd="+Password
    connObject = odbc.connect(connString)

    return connObject

connObject = connectToServer('xxx','xxx','xxx')

Connection works well, i can pass commands to the server (create, drop tables, execute procedure etc...)

However i cannot extract data via the following:

# print top lines of a given table
def selectTopFromTable(connObject,tableName,numberRow=100):
    cur = connObject.cursor()
    command = '''SELECT * FROM ''' + tableName +''' WHERE ROWNUM <= ''' + str(numberRow) + '''; '''  
    cur.execute(command)

    for row in cur.fetchall():
        print(row)

selectTopFromTable(connObject,'xxx')

Getting the following error:

ValueError: could not convert string to float: b'5,3'

I think the issue lies in the format number FR versus EN (i'm working on a FR based server) - the server provides values in FR format number (i.e. 5,3 instead of 5.3) and Python is confused when trying to convert a supposedly NUMBER SQL type into a double.

Is it possible to configure the pypyodbc connection to retrieve value in EN number format? (the same way a client such as SQL Developer or Toad is set up to do so)?

Answer 1

Here's how you can convert your locale to be France in Python:

import locale

locale.setlocale(locale.LC_ALL, 'fr_FR.UTF-8')
locale.atof('1000,53')

This converts the string to a floating point number, with the settings for the locale of France. I'm not sure exactly how to tie that in with Oracle (I haven't used Oracle since the 1990s), but hopefully this puts you on the right path. You could cast it as a string and then convert using this method, but there may be a better way at the DB level.