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:
Python with pyodbc
ODBC
Excel ODBC datasource called "excel_dsn"
Excel datasource file must contain "ColA" in row 1, column A
Excel datasource file must contain "ColB" in row 1, column B
Script file
dbaccesses_print_sheet1.py
must be set executable on Unix systems
#!/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()
Prerequisites:
- Script
dbaccesses_print_sheet1.py
must reside in test suite directory
function main()
{
OS.system('"' + squishinfo.testCase + '/../dbaccesses_print_sheet1.py"');
}
Example - Updating table row values¶
Script prerequisites:
Python with pyodbc
ODBC
Excel ODBC datasource called "excel_dsn" with write access enabled
Excel datasource file must contain "ColA" in row 1, column A
Excel datasource file must contain "ColB" in row 1, column B
Script file
dbaccesses_update_sheet1.py
must be set executable on Unix systems
#!/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()
Prerequisites:
Script
dbaccesses_update_sheet1.py
must reside in test suite directoryDatasource must have a row with ColA value "x"
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);
}
Related information¶
Microsoft Access Database Engine 2010 Redistributable (32 bit and 64 bit)
Service Pack 1 for Microsoft Access Database Engine 2010 (KB2460011) (32 bit and 64 bit)
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:
Start
>Run...
>"%WINDIR%\SysWOW64\odbcad32.exe"
,press Return
; orWindows key+R
>"%WINDIR%\SysWOW64\odbcad32.exe"
,press Return
.
To open the 64-bit ODBC Data Source Administrator
on 64-bit Windows systems:
Start
>Run...
>"%WINDIR%\System32\odbcad32.exe"
,press Return
; orWindows key+R
>"%WINDIR%\System32\odbcad32.exe"
,press Return
.
ODBC Control Panel on Windows XP (and similar)¶
64-bit Windows¶
To open the 32-bit ODBC control panel on 64-bit Windows systems:
Start
>Run...
>"%windir%\SysWOW64\odbccp32.cpl"
,press Return
; orWindows Key+R
>"%windir%\SysWOW64\odbccp32.cpl"
,press Return
.
To open the 64-bit ODBC control panel on 64-bit Windows systems:
Start
>Run...
>"%windir%\system32\odbccp32.cpl"
,press Return
; orWindows key+R
>"%windir%\system32\odbccp32.cpl"
,press Return
.
32-bit Windows¶
To open the 32-bit ODBC control panel on 32-bit Windows systems:
Start
>Run...
>"%windir%\system32\odbccp32.cpl"
,press Return
; orWindows Key+r
>"%windir%\system32\odbccp32.cpl"
,press Return
.