1

I cannot connect using values read from a config file.

This is on Windows, connecting to SQL Server 2008r2 on the same machine, using Python 3.6.

This works:

conn = p.connect(driver = '{SQL Server Native Client 11.0}', server = 'SERVERNAME',port = '1433',database = 'DATABASE',uid = 'USERNAME',pwd = 'PASSWORD')

I want to use a connect string from a config file using configParser and can do so successfully using pyodbc. However I cannot do so using turbodbc. The only way I can connect using turbodbc is either by DSN or the long format shown here (the allcaps are just placeholders, not variables).

The error that is always returned is:

"ODBC error state: IM010 native error code: 0 message: [Microsoft][ODBC Driver Manager] Data source name too long"

This is my first time using this package and it is great except for this problem.

Could be a noob issue, but it's confusing why connections work only by using variables within the code or a DSN. Makes writing the code awkward and not very dynamic for use in multiple environments (dev, test, prod). Connections vary by environment.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
tfillmor
  • 11
  • 2
  • So are you saying that you have a config file that contains a single string like `driver = '{SQL Server Native Client 11.0}', server = 'SERVERNAME', ...` and you are trying to pass that string as the one and only argument to the turbodbc `connect` method? If so, that probably won't work because in your example above `driver=`, `server=`, etc. are Python keywords for separate arguments (what Python calls "kwargs"), not part of a single monolithic string. – Gord Thompson Jun 07 '19 at 20:52
  • Ah, ok, this makes sense. Since the words you point out are required ODBC connection terms my approach will never work. The approach you pointed out a little later in this thread looks very nice and I will try it. Thanks for the great answer!! – tfillmor Jun 08 '19 at 22:10

2 Answers2

1

You can create a simple function to build the connection string based on the contents of a config file. For example, if "example.ini" contains

[DEFAULT]
driver=ODBC Driver 17 for SQL Server
database=myDb

[development]
server=localhost

[test]
server=192.168.0.179,49242

then you can just use code like this

import configparser


def get_connection_string(environment):
    config = configparser.ConfigParser()
    config.read('example.ini')
    return ';'.join([x + '=' + config[environment][x] for x in config[environment]])


print(get_connection_string('development'))
# server=localhost;driver=ODBC Driver 17 for SQL Server;database=myDb

print(get_connection_string('test'))
# server=192.168.0.179,49242;driver=ODBC Driver 17 for SQL Server;database=myDb
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
0

I had the same issue... and found out that we should pass connection string to the connections_string argument of connect function..

connection = turbodbc.connect(connection_string=connection_string)