Overview

The grail.dsn module provides clients with the ability to dynamically manipulate Data Source Names (DSNs). The DSN is typically used to identify the location of databases.

Example

Although it is true that you can use the low level config function to generate DSNs for any database, the following example will concentrate on using the higher level functions for generating a DSN for a MS Access Database.

This example will create a "test_db" DSN, list out the existing DSN, remove the "test_db" and re-list out the existing DSNs afterwards,

# (1) ADD a SYSTEM DSN called 'test_db'.
dsn.mdb_add(
   dsn.LEVEL_SYSTEM,
   "test_db",
   r"C:\tmp\testdb"
)

# (2) Display the existing DSN Names
userDsnNameList = dsn.list_user()
sysDsnNameList = dsn.list_system()
print "user: %s" % (str(userDsnNameList))
print " sys: %s" % (str(sysDsnNameList))

# (3) Remove the "test_db"
dsn.mdb_remove_if_exists(
    dsn.LEVEL_SYSTEM,
    "test_db"
)

# (4) Display the existing DSN Names
userDsnNameList = dsn.list_user()
sysDsnNameList = dsn.list_system()
print "user: %s" % (str(userDsnNameList))
print " sys: %s" % (str(sysDsnNameList))

In step (1) we will have a new system level DSN called "test_db" that will point to the database at C:\tmp\testdb, which we will confirm by inspecting the results of step (2). In step (3) we will remove the "test_db" DSN, and visually confirm the results in test (4).

High Level Functions

exists(dsnName)

Returns TRUE (1) if the dsnName exists in either the user or system dsn entries. This will generate a ValueError exception if the dsnName is not valid.

exists_user(dsnName)

Returns TRUE (1) if the dsnName exists in either the user dsn entries. This will generate a ValueError exception if the dsnName is not valid.

exists_system(dsnName)

Returns TRUE (1) if the dsnName exists in either the system dsn entries. This will generate a ValueError exception if the dsnName is not valid.

list_user()

Returns a list of DSNs for the user entries.

list_system()

Returns a list of DSNs for the system entries.

mdb_add(level, dsnName, mdbPath)

Convenience function for quickly adding "Microsoft Access Database (*.mdb)" database DSN entries. The level can either be LEVEL_USER for user level entries or LEVEL_SYSTEM for system level entries.

This function will generate a ValueError if the level is invalid. It will also generate a ValueError if the dsnName is not valid. Finally, it will generate an IOError if the mdbPath does not point to a valid MS Access Database file.

mdb_remove(level, dsnName)

This function will remove a DSN entry described by the dsnName for the appropriate level. The level can either be LEVEL_USER or LEVEL_SYSTEM.

This function will fail with an IOError if you attempt to remove a dsnName that does not exist. Consider using the mdb_remove_if_exists otherwise.

This function will generate a ValueError if the level is invalid. It will also generate a ValueError if the dsnName is not valid.

mdb_remove_if_exists(level, dsnName)

Works like the mdb_remove function except that if the dsnName does not exist, this function does nothing.

sqlserver_add(level, dsnName, server, database[, desc, trusted])

Convenience function for quickly adding "SQL Server" database DSN entries. The level can either be LEVEL_USER for user level entries or LEVEL_SYSTEM for system level entries. The desc provides an optional description. If trusted is set to True then this will add a trusted connection option to the string. By default trusted is False.

This function will generate a ValueError if the level is invalid. It will also generate a ValueError if the dsnName is not valid. Finally, it will generate an IOError if the mdbPath does not point to a valid MS Access Database file.

You can not store a user name and password within a Untrusted DSN. If you use a trusted DSN it will use Window's authentication.

sqlserver_remove(level, dsnName)

This function will remove a DSN entry described by the dsnName for the appropriate level. The level can either be LEVEL_USER or LEVEL_SYSTEM.

This function will fail with an IOError if you attempt to remove a dsnName that does not exist. Consider using the sqlserver_remove_if otherwise.

This function will generate a ValueError if the level is invalid. It will also generate a ValueError if the dsnName is not valid.

sqlserver_remove_if_exists(level, dsnName)

Works like the sqlserver_remove function except that if the dsnName does not exist, this function does nothing.

Low Level Function

config(request, driverName, config_dict)

The config function is a low-level function that maps directly to the Data Source Name configuration routine. With this function you should be capable of creating and removing most DSNs if you have supplied the correct ODBC parameters.

Most of the time you want to avoid using this function unless you know what you are doing. But sometimes, you just need to have the raw power, and this function provides it to you.

As an example of its usage, consider creating a DSN for a MS Access Database (MDB). You would do it with the following command,

dsn.config(
   dsn.ODBC_ADD_DSN,
   dsn.MSACCESS_DRIVER_NAME_EN,
   {
       "dsn":"test_mdb",
       "dbq":"c:\\tmp\\test.mdb",
       "description":"created from python",
   }
  )

where the first request can be one of the ODBC_XXX constants available from this module. These constants will add/remove a user or system DSN entry.

In the above snippet we are using a constant provided by the dsn module to specify the driverName. Typically the driver name is same as the one shown when you manually add a driver. For example, MSACCESS_DRIVER_NAME_EN is "Microsoft Access Driver (*.mdb)".

Finally the configDict is used to specify the configuration details for the operation. If you have an understanding of what the databases ODBC DSN installations require, the above configuration details would result in the following string,

"dsn=test_mdb\0dbq=c:/tmp/test.mdb\0description=created from python\0\0"

Suffice it to say that this is a fairly complicated function. Within this module their exists easier to use functions for adding and removing DSNs.

To complete the above example the following snippet will remove the same DSN entry,

dsn.config(
    dsn.ODBC_REMOVE_DSN,
    dsn.MSACCESS_DRIVER_NAME_EN,
    {"dsn":"test_mdb"}
    )

Constants

ODBC_ADD_DSN

Used in the config function this indicates that you wish to add a USER DSN entry.

ODBC_ADD_SYS_DSN

Used in the config function this indicates that you wish to add a SYSTEM DSN entry.

ODBC_REMOVE_DSN

Used in the config function this indicates that you wish to remove a USER DSN entry.

ODBC_REMOVE_SYS_DSN

Used in the config function this indicates that you wish to remove a SYSTEM DSN entry.

MSACCESS_DRIVER_NAME_EN

Provided to ease spelling mistakes. This is the common English name for the Microsoft Access Drivers.

LEVEL_USER

Used with the higher level functions, such as mdb_add and mdb_remove, this indicates you wish to create a USER level DSN entry.

LEVEL_SYSTEM

Used with the higher level functions, such as mdb_add and mdb_remove, this indicates you wish to create a SYSTEM level DSN entry.

SQLSERVER_DRIVER_NAME_EN

Provided to ease spelling mistakes. This is the common English name for the Microsoft SQL Server driver.