Accessing databases via external Python scripts

Last edited on

Overview

The following scripts show how to update and read data from databases via Python using the pyodbc module.

Example - Reading table values

Script prerequisites:

#!/usr/bin/env python

import os
import pyodbc
import subprocess
import sys

def do_it():
    connection = pyodbc.connect("DSN=excel_dsn", autocommit=True)
    cursor = connection.cursor()

    cursor.execute("SELECT ColA, ColB FROM [Sheet1$]")
    row_count = 0
    while True:
        row = cursor.fetchone()
        if row is None:
            break
        row_count += 1

        print "%s\t%s" % (row[0], row[1])
    print

def remove_squish_paths_and_run_again(squish_prefix):
    # Get and unset SQUISH_PREFIX
    squish_prefix = os.environ["SQUISH_PREFIX"]
    os.unsetenv("SQUISH_PREFIX")
    # Get PATH
    path_env = os.environ["PATH"]
    # Split PATH to get paths
    paths = path_env.split(os.pathsep)
    # Loop over paths and concatenate the ones that do not start with SQUISH_PREFIX
    new_path = ""
    for p in paths:
        if p.startswith(squish_prefix):
            print "Dropping Squish path from PATH:", p
        else:
            new_path += os.pathsep + p
    # Remove os.path.separator prefix
    new_path = new_path[len(os.pathsep):]
    # Put PATH
    os.putenv("PATH", new_path)

    # Execute original command
    args = sys.argv
    args.insert(0, "python")
    proc = subprocess.Popen(args=args, shell=True)
    proc.communicate()
    sys.exit(proc.returncode)

if __name__ == "__main__":
    if "SQUISH_PREFIX" in os.environ:
        remove_squish_paths_and_run_again(os.environ["SQUISH_PREFIX"])
    else:
        do_it()
dbaccesses_print_sheet1.py

Prerequisites:

function main()
{
    OS.system('"' + squishinfo.testCase + '/../dbaccesses_print_sheet1.py"');
}
test.js

Example - Updating table row values

Script prerequisites:

#!/usr/bin/env python

import os
import pyodbc
import subprocess
import sys

def do_it():
    connection = pyodbc.connect("DSN=excel_dsn", autocommit=True)
    cursor = connection.cursor()

    print "Updating data..."
    sql = "UPDATE [Sheet1$] SET ColA = ?, ColB = ? WHERE ColA = ?"
    find_cola_value = sys.argv[1]
    value_cola = sys.argv[2]
    value_colb = sys.argv[3]
    cursor.execute(sql, value_cola, value_colb, find_cola_value)
 
def remove_squish_paths_and_run_again(squish_prefix):
    # Get and unset SQUISH_PREFIX
    squish_prefix = os.environ["SQUISH_PREFIX"]
    os.unsetenv("SQUISH_PREFIX")
    # Get PATH
    path_env = os.environ["PATH"]
    # Split PATH to get paths
    paths = path_env.split(os.pathsep)
    # Loop over paths and concatenate the ones that do not start with SQUISH_PREFIX
    new_path = ""
    for p in paths:
        if p.startswith(squish_prefix):
            print "Dropping Squish path from PATH:", p
        else:
            new_path += os.pathsep + p
    # Remove os.path.separator prefix
    new_path = new_path[len(os.pathsep):]
    # Put PATH
    os.putenv("PATH", new_path)
 
    # Execute original command
    args = sys.argv
    args.insert(0, "python")
    proc = subprocess.Popen(args=args, shell=True)
    proc.communicate()
    sys.exit(proc.returncode)

if __name__ == "__main__":
    if len(sys.argv) != 4:
        print "USAGE:"
        print
        print "  dbaccesses_update_sheet1 <where_cola_value> <new_value_cola> <new_value_colb>"
        print
        print "EXAMPLE:"
        print
        print '  dbaccesses_update_sheet1 "x" "new value x" "new value y"'
        sys.exit(1)

    if n in os.environ:
        remove_squish_paths_and_run_again()
    else:
        do_it()
dbaccesses_update_sheet1.py

Prerequisites:

function main()
{
    var where_cola_value = "x";
    var new_value_cola = "xx";
    var new_value_colb = "yy";
    var s = '"' + squishinfo.testCase + '/../dbaccesses_update_sheet1.py"';
    s = s + ' "' + where_cola_value + '"';
    s = s + ' "' + new_value_cola + '"';
    s = s + ' "' + new_value_colb + '"';
    test.log("Executing external: " + s);
    OS.system(s);
}
test.js

Related information

ODBC Data Source Administrator on Windows 7 (and higher)

64-bit Windows

To open the 32-bit ODBC Data Source Administrator on 64-bit Windows systems:

To open the 64-bit ODBC Data Source Administrator on 64-bit Windows systems:

ODBC Control Panel on Windows XP (and similar)

64-bit Windows

To open the 32-bit ODBC control panel on 64-bit Windows systems:

To open the 64-bit ODBC control panel on 64-bit Windows systems:

32-bit Windows

To open the 32-bit ODBC control panel on 32-bit Windows systems: