Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100755 141 lines (105 sloc) 4.592 kb
e38c270 Michael Kleehammer Added Excel unit tests
authored
1 #!/usr/bin/python
2
3 # Tests for reading from Excel files.
4 #
5 # I have not been able to successfully create or modify Excel files.
6
7 import sys, os, re
8 import unittest
9 from os.path import abspath
10 from testutils import *
11
12 CNXNSTRING = None
13
14 class ExcelTestCase(unittest.TestCase):
15
16 def __init__(self, method_name):
17 unittest.TestCase.__init__(self, method_name)
18
19 def setUp(self):
20 self.cnxn = pyodbc.connect(CNXNSTRING, autocommit=True)
21 self.cursor = self.cnxn.cursor()
22
23 for i in range(3):
24 try:
25 self.cursor.execute("drop table t%d" % i)
26 self.cnxn.commit()
27 except:
28 pass
29
30 self.cnxn.rollback()
31
32 def tearDown(self):
33 try:
34 self.cursor.close()
35 self.cnxn.close()
36 except:
37 # If we've already closed the cursor or connection, exceptions are thrown.
38 pass
39
40 def test_getinfo_string(self):
41 value = self.cnxn.getinfo(pyodbc.SQL_CATALOG_NAME_SEPARATOR)
42 self.assert_(isinstance(value, str))
43
44 def test_getinfo_bool(self):
45 value = self.cnxn.getinfo(pyodbc.SQL_ACCESSIBLE_TABLES)
46 self.assert_(isinstance(value, bool))
47
48 def test_getinfo_int(self):
49 value = self.cnxn.getinfo(pyodbc.SQL_DEFAULT_TXN_ISOLATION)
50 self.assert_(isinstance(value, (int, long)))
51
52 def test_getinfo_smallint(self):
53 value = self.cnxn.getinfo(pyodbc.SQL_CONCAT_NULL_BEHAVIOR)
54 self.assert_(isinstance(value, int))
55
56
57 def test_read_sheet(self):
58 # The first method of reading data is to access worksheets by name in this format [name$].
59 #
60 # Our second sheet is named Sheet2 and has two columns. The first has values 10, 20, 30, etc.
61
62 rows = self.cursor.execute("select * from [Sheet2$]").fetchall()
63 self.assertEquals(len(rows), 5)
64
65 for index, row in enumerate(rows):
66 self.assertEquals(row.s2num, float(index + 1) * 10)
67
68 def test_read_range(self):
69 # The second method of reading data is to assign a name to a range of cells and access that as a table.
70 #
71 # Our first worksheet has a section named Table1. The first column has values 1, 2, 3, etc.
72
73 rows = self.cursor.execute("select * from Table1").fetchall()
74 self.assertEquals(len(rows), 10)
75
76 for index, row in enumerate(rows):
77 self.assertEquals(row.num, float(index + 1))
78 self.assertEquals(row.val, chr(ord('a') + index))
79
80 def test_tables(self):
81 # This is useful for figuring out what is available
82 tables = [ row.table_name for row in self.cursor.tables() ]
83 assert 'Sheet2$' in tables, 'tables: %s' % ' '.join(tables)
84
85
86 # def test_append(self):
87 # rows = self.cursor.execute("select s2num, s2val from [Sheet2$]").fetchall()
88 #
89 # print rows
90 #
91 # nextnum = max([ row.s2num for row in rows ]) + 10
92 #
93 # self.cursor.execute("insert into [Sheet2$](s2num, s2val) values (?, 'z')", nextnum)
94 #
95 # row = self.cursor.execute("select s2num, s2val from [Sheet2$] where s2num=?", nextnum).fetchone()
96 # self.assertTrue(row)
97 #
98 # print 'added:', nextnum, len(rows), 'rows'
99 #
100 # self.assertEquals(row.s2num, nextnum)
101 # self.assertEquals(row.s2val, 'z')
102 #
103 # self.cnxn.commit()
104
105
106 def main():
107 from optparse import OptionParser
108 parser = OptionParser() #usage=usage)
109 parser.add_option("-v", "--verbose", action="count", help="Increment test verbosity (can be used multiple times)")
110 parser.add_option("-d", "--debug", action="store_true", default=False, help="Print debugging items")
111 parser.add_option("-t", "--test", help="Run only the named test")
112
113 (options, args) = parser.parse_args()
114
115 if args:
116 parser.error('no arguments expected')
117
118 global CNXNSTRING
119
120 path = dirname(abspath(__file__))
121 filename = join(path, 'test.xls')
122 assert os.path.exists(filename)
123 CNXNSTRING = 'Driver={Microsoft Excel Driver (*.xls)};DBQ=%s;READONLY=FALSE' % filename
124
125 cnxn = pyodbc.connect(CNXNSTRING, autocommit=True)
126 print_library_info(cnxn)
127 cnxn.close()
128
129 suite = load_tests(ExcelTestCase, options.test)
130
131 testRunner = unittest.TextTestRunner(verbosity=options.verbose)
132 result = testRunner.run(suite)
133
134
135 if __name__ == '__main__':
136
137 # Add the build directory to the path so we're testing the latest build, not the installed version.
138 add_to_path()
139 import pyodbc
140 main()
Something went wrong with that request. Please try again.