Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 172 lines (105 sloc) 5.727 kb
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
1
2 Overview
3 ========
4
5 This project is a Python database module for ODBC that implements the Python DB API 2.0
6 specification.
7
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
8 :homepage: http://code.google.com/p/pyodbc
9 :source: http://github.com/mkleehammer/pyodbc
c278e74 @mkleehammer Single source base for Python 2 and 3
authored
10 :source: http://code.google.com/p/pyodbc/source/list
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
11
12 This module requires:
13
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
14 * Python 2.4 or greater
15 * ODBC 3.0 or greater
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
16
61467db @mkleehammer Changed readme to rst (for github) and updated links to new homepage.
authored
17 On Windows, the easiest way to install is to use the Windows installers from:
18
19 http://code.google.com/p/pyodbc/downloads/list
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
20
21 Source can be obtained at
22
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
23 http://github.com/mkleehammer/pyodbc/tree
24
c278e74 @mkleehammer Single source base for Python 2 and 3
authored
25 or
26
27 http://code.google.com/p/pyodbc/source/list
28
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
29 To build from source, either check the source out of version control or download a source
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
30 extract and run::
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
31
32 python setup.py build install
33
34 Module Specific Behavior
3ed7bde @mkleehammer Issue 14: Bug determining decimal point char.
authored
35 ========================
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
36
37 General
38 -------
39
40 * The pyodbc.connect function accepts a single parameter: the ODBC connection string. This
41 string is not read or modified by pyodbc, so consult the ODBC documentation or your ODBC
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
42 driver's documentation for details. The general format is::
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
43
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
44 cnxn = pyodbc.connect('DSN=mydsn;UID=userid;PWD=pwd')
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
45
46 * Connection caching in the ODBC driver manager is automatically enabled.
47
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
48 * Call cnxn.commit() since the DB API specification requires a rollback when a connection
49 is closed that was not specifically committed.
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
50
51 * When a connection is closed, all cursors created from the connection are closed.
52
53
54 Data Types
55 ----------
56
57 * Dates, times, and timestamps use the Python datetime module's date, time, and datetime
58 classes. These classes can be passed directly as parameters and will be returned when
59 querying date/time columns.
60
61 * Binary data is passed and returned in Python buffer objects.
62
63 * Decimal and numeric columns are passed and returned using the Python 2.4 decimal class.
64
65
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
66 Convenient Additions
67 --------------------
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
68
69 * Cursors are iterable and returns Row objects.
70
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
71 ::
72
73 cursor.execute("select a,b from tmp")
74 for row in cursor:
75 print row
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
76
77
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
78 * The DB API specifies that results must be tuple-like, so columns are normally accessed by
79 indexing into the sequence (e.g. row[0]) and pyodbc supports this. However, columns can also
80 be accessed by name::
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
81
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
82 cursor.execute("select album_id, photo_id from photos where user_id=1")
83 row = cursor.fetchone()
84 print row.album_id, row.photo_id
85 print row[0], row[1] # same as above, but less readable
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
86
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
87 This makes the code easier to maintain when modifying SQL, more readable, and allows rows to
88 be used where a custom class might otherwise be used. All rows from a single execute share
89 the same dictionary of column names, so using Row objects to hold a large result set may also
90 use less memory than creating a object for each row.
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
91
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
92 The SQL "as" keyword allows the name of a column in the result set to be specified. This is
93 useful if a column name has spaces or if there is no name::
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
94
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
95 cursor.execute("select count(*) as photo_count from photos where user_id < 100")
96 row = cursor.fetchone()
97 print row.photo_count
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
98
99
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
100 * The DB API specification does not specify the return value of Cursor.execute. Previous
101 versions of pyodbc (2.0.x) returned different values, but the 2.1 versions always return the
102 Cursor itself.
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
103
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
104 This allows for compact code such as::
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
105
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
106 for row in cursor.execute("select album_id, photo_id from photos where user_id=1"):
107 print row.album_id, row.photo_id
108
109 row = cursor.execute("select * from tmp").fetchone()
110 rows = cursor.execute("select * from tmp").fetchall()
111
112 count = cursor.execute("update photos set processed=1 where user_id=1").rowcount
113 count = cursor.execute("delete from photos where user_id=1").rowcount
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
114
115
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
116 * Though SQL is very powerful, values sometimes need to be modified before they can be
117 used. Rows allow their values to be replaced, which makes them even more convenient ad-hoc
118 data structures.
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
119
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
120 ::
121
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
122 # Replace the 'start_date' datetime in each row with one that has a time zone.
123 rows = cursor.fetchall()
124 for row in rows:
125 row.start_date = row.start_date.astimezone(tz)
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
126
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
127 Note that columns cannot be added to rows; only values for existing columns can be modified.
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
128
129
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
130 * As specified in the DB API, Cursor.execute accepts an optional sequence of parameters::
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
131
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
132 cursor.execute("select a from tbl where b=? and c=?", (x, y))
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
133
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
134 However, this seems complicated for something as simple as passing parameters, so pyodbc also
135 accepts the parameters directly. Note in this example that x & y are not in a tuple::
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
136
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
137 cursor.execute("select a from tbl where b=? and c=?", x, y)
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
138
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
139 * The DB API specifies that connections require a manual commit and pyodbc complies with
140 this. However, connections also support autocommit, using the autocommit keyword of the
141 connection function or the autocommit attribute of the Connection object::
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
142
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
143 cnxn = pyodbc.connect(cstring, autocommit=True)
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
144
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
145 or
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
146
fb9acb4 @mkleehammer README fixes from http://github.com/ramiro/pyodbc/tree. Thanks ramiro.
authored
147 ::
148
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
149 cnxn.autocommit = True
150 cnxn.autocommit = False
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
151
152
153 Goals / Design
154 ==============
155
156 * This module should not require any 3rd party modules other than ODBC.
157
158 * Only built-in data types should be used where possible.
159
160 a) Reduces the number of libraries to learn.
161
162 b) Reduces the number of modules and libraries to install.
163
164 c) Eventually a standard is usually introduced. For example, many previous database drivers
165 used the mxDate classes. Now that Python 2.3 has introduced built-in date/time classes,
166 using those modules is more complicated than using the built-ins.
167
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
168 * It should adhere to the DB API specification, but be more "Pythonic" when convenient.
c3f6b46 @mkleehammer Import from Subversion 2.0.63; reworked versioning
authored
169 The most common usages should be optimized for convenience and speed.
6d3171e @mkleehammer Added some 2.0 to 2.1 readme changes.
authored
170
171 * All ODBC functionality should (eventually) be exposed.
Something went wrong with that request. Please try again.