My favorites | Sign in
Project Home Downloads Wiki Issues Source
Repository:
Checkout   Browse   Changes   Clones  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
#summary Cursor API documentation

Cursors represent a database cursor (and map to ODBC HSTMTs), which is used to manage the
context of a fetch operation. Cursors created from the same connection are not isolated, i.e.,
any changes done to the database by a cursor are immediately visible by the other cursors.

= variables =

== description ==

This read-only attribute is a list of 7-item tuples, each containing (name, type_code,
display_size, internal_size, precision, scale, null_ok). pyodbc only provides values for name,
type_code, internal_size, and null_ok. The other values are set to None.

This attribute will be None for operations that do not return rows or if one of the execute
methods has not been called.

The type_code member is the class type used to create the Python objects when reading rows.
For example, a varchar column's type will be {{{str}}}.

== rowcount ==

The number of rows modified by the previous DDL statement.

This is -1 if no SQL has been executed or if the number of rows is unknown. Note that it is not uncommon for databases
to report -1 after a select statement for performance reasons. (The exact number may not be known before the first
records are returned to the application.)

= methods =

== execute ==

cursor.execute(sql, `*parameters`) --> Cursor

Prepares and executes SQL. The optional parameters may be passed as a sequence, as specified by the DB API, or as individual values.

{{{
# standard
cursor.execute("select a from tbl where b=? and c=?", (x, y))

# pyodbc extension
cursor.execute("select a from tbl where b=? and c=?", x, y)
}}}

The return value is always the cursor itself:

{{{
for row in cursor.execute("select user_id, user_name from users"):
print row.user_id, row.user_name

row = cursor.execute("select * from tmp").fetchone()
rows = cursor.execute("select * from tmp").fetchall()

count = cursor.execute("update users set last_logon=? where user_id=?", now, user_id).rowcount
count = cursor.execute("delete from users where user_id=1").rowcount
}}}

As suggested in the DB API, the last prepared statement is kept and reused if you execute the same SQL again, makingexecuting the same SQL with different parameters will be more efficient.

== executemany ==

cursor.executemany(sql, seq_of_parameters) --> None

Executes the same SQL statement for each set of parameters. {{{seq_of_parameters}}} is a
sequence of sequences.

{{{
params = [ ('A', 1), ('B', 2) ]
executemany("insert into t(name, id) values (?, ?)", params)
}}}

This will execute the SQL statement twice, once with ('A', 1) and once with ('B', 2).

== fetchone ==

cursor.fetchone() --> [Rows Row] or None

Returns the next [Rows row] or {{{None}}} when no more data is available.

A ProgrammingError exception is raised if no SQL has been executed or if it did not return a
result set (e.g. was not a SELECT statement).

{{{
cursor.execute("select user_name from users where user_id=?", userid)
row = cursor.fetchone()
if row:
print row.user_name
}}}

== fetchall ==

cursor.fetchall() --> list of rows

Returns a list of all remaining [Rows rows].

Since this reads all rows into memory, it should not be used if there are a lot of rows.
Consider iterating over the rows instead. However, it is useful for freeing up a Cursor so you
can perform a second query before processing the resulting rows.

A ProgrammingError exception is raised if no SQL has been executed or if it did not return a
result set (e.g. was not a SELECT statement).

{{{
cursor.execute("select user_id, user_name from users where user_id < 100")
rows = cursor.fetchall()
for row in rows:
print row.user_id, row.user_name
}}}

== fetchmany ==

cursor.fetchmany([size=cursor.arraysize]) --> list

Returns a list of remaining [Rows rows], containing no more than {{{size}}} rows, used to process
results in chunks. The list will be empty when there are no more rows.

The default for cursor.arraysize is 1 which is no different than calling fetchone().

A ProgrammingError exception is raised if no SQL has been executed or if it did not return a
result set (e.g. was not a SELECT statement).


== commit ==

cursor.commit() --> None

Commits pending transactions on the connection that created this cursor.

_This affects all cursors created by the same connection!_

This is no different than calling commit on the connection. The benefit is that many uses can
now just use the cursor and not have to track the connection.

== rollback ==

cursor.rollback() --> None

Rolls back pending transactions on the connection that created this cursor.

_This affects all cursors created by the same connection!_


== skip ==

cursor.skip(count) --> None

Skips the next _count_ records by calling SQLFetchScroll with SQL_FETCH_NEXT.

For convenience, skip(0) is accepted and will do nothing.


== nextset ==

cursor.nextset() --> True or None

This method will make the cursor skip to the next available set, discarding any remaining rows from the current set.

If there are no more sets, the method returns None. Otherwise, it returns a true value and subsequent calls to the fetch methods will return rows from the next result set.

This method is primarily used if you have stored procedures that return multiple results.

== close() ==

Closes the cursor. A ProgrammingError exception will be raised if any operation is attempted with the cursor.

Cursors are closed automatically when they are deleted, so calling this is not usually
necessary when using CPython.

== setinputsizes, setoutputsize ==

These are optional in the API and are not supported.

== callproc(procname[,parameters]) ==

This is not yet supported since there is no way for pyodbc to determine which parameters are input, output, or both.

You will need to call stored procedures using execute(). You can use your database's format or the ODBC escape format.

== tables ==

cursor.tables(table=None, catalog=None, schema=None, tableType=None) --> Cursor

Creates a result set of tables in the database that match the given criteria.

The table, catalog, and schema interpret the '_' and '%' characters as wildcards. The escape
character is driver specific, so use Connection.searchescape.

Each row has the following columns. See the
[http://msdn.microsoft.com/en-us/library/ms711831.aspx SQLTables]
documentation for more information.

# table_cat: The catalog name.
# table_schem: The schema name.
# table_name: The table name.
# table_type: One of TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM, or a data source-specific type name.
# remarks: A description of the table.

{{{
for row in cursor.tables():
print row.table_name

# Does table 'x' exist?
if cursor.tables(table='x').fetchone():
print 'yes it does'
}}}

== columns ==

cursor.columns(table=None, catalog=None, schema=None, column=None) --> Cursor

Creates a result set of column information in the specified tables using the
[http://msdn.microsoft.com/en-us/library/ms711683%28VS.85%29.aspx SQLColumns] function.

Each row has the following columns:

# table_cat
# table_schem
# table_name
# column_name
# data_type
# type_name
# column_size
# buffer_length
# decimal_digits
# num_prec_radix
# nullable
# remarks
# column_def
# sql_data_type
# sql_datetime_sub
# char_octet_length
# ordinal_position
# is_nullable: One of SQL_NULLABLE, SQL_NO_NULLS, SQL_NULLS_UNKNOWN.

{{{
# columns in table x
for row in cursor.columns(table='x'):
print row.column_name
}}}

== statistics ==

cursor.statistics(table, catalog=None, schema=None, unique=False, quick=True) --> Cursor

Creates a result set of statistics about a single table and the indexes associated with the
table by executing [http://msdn.microsoft.com/en-us/library/ms711022%28VS.85%29.aspx SQLStatistics].

If {{{code}}} unique is {{{True}}} only unique indexes are returned; if {{{False}}} all indexes
are returned.

If {{{quick}}} is {{{True}}}, CARDINALITY and PAGES are returned only if they are readily
available. Otherwise NULL is returned on those colummns.

Each row has the following columns:

# table_cat
# table_schem
# table_name
# non_unique
# index_qualifier
# index_name
# type
# ordinal_position
# column_name
# asc_or_desc
# cardinality
# pages
# filter_condition

== rowIdColumns ==

cursor.rowIdColumns(table, catalog=None, schema=None, nullable=True) --> Cursor

Executes [http://msdn.microsoft.com/en-us/library/ms714602%28VS.85%29.aspx SQLSpecialColumns]
with SQL_BEST_ROWID which creates a result set of columns that uniquely identify a row.

Each row has the following columns.

# scope: One of SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION, or SQL_SCOPE_SESSION
# column_name
# data_type: The ODBC SQL data type constant (e.g. SQL_CHAR)
# type_name
# column_size
# buffer_length
# decimal_digits
# pseudo_column: One of SQL_PC_UNKNOWN, SQL_PC_NOT_PSEUDO, SQL_PC_PSEUDO

== rowVerColumns ==

cursor.rowVerColumns(table, catalog=None, schema=None, nullable=True) --> Cursor

Executes [http://msdn.microsoft.com/en-us/library/ms714602%28VS.85%29.aspx SQLSpecialColumns]
with SQL_ROWVER which creates a result set of columns that are automatically updated when any
value in the row is updated. Returns the Cursor object. Each row has the following columns.

# scope: One of SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION, or SQL_SCOPE_SESSION
# column_name
# data_type: The ODBC SQL data type constant (e.g. SQL_CHAR)
# type_name
# column_size
# buffer_length
# decimal_digits
# pseudo_column: One of SQL_PC_UNKNOWN, SQL_PC_NOT_PSEUDO, SQL_PC_PSEUDO

== primaryKeys ==

primaryKeys(table, catalog=None, schema=None) --> Cursor

Creates a result set of column names that make up the primary key for a table by executing the
[http://msdn.microsoft.com/en-us/library/ms711005%28VS.85%29.aspx SQLPrimaryKeys] function.

Each row has the following columns:

# table_cat
# table_schem
# table_name
# column_name
# key_seq
# pk_name

== foreignKeys ==

cursor.foreignKeys(table=None, catalog=None, schema=None, foreignTable=None, foreignCatalog=None, foreignSchema=None) --> Cursor

Executes the [http://msdn.microsoft.com/en-us/library/ms709315%28VS.85%29.aspx SQLForeignKeys]
function and creates a result set of column names that are foreign keys in the specified table
(columns in the specified table that refer to primary keys in other tables) or foreign keys in
other tables that refer to the primary key in the specified table.

Each row has the following columns:

# pktable_cat
# pktable_schem
# pktable_name
# pkcolumn_name
# fktable_cat
# fktable_schem
# fktable_name
# fkcolumn_name
# key_seq
# update_rule
# delete_rule
# fk_name
# pk_name
# deferrability

== procedures ==

cursor.procedures(procedure=None, catalog=None, schema=None) --> Cursor

Executes [http://msdn.microsoft.com/en-us/library/ms715368%28VS.85%29.aspx SQLProcedures]
and creates a result set of information about the procedures in the data source. Each row has
the following columns:

# procedure_cat
# procedure_schem
# procedure_name
# num_input_params
# num_output_params
# num_result_sets
# remarks
# procedure_type

== getTypeInfo ==

cursor.getTypeInfo(sqlType=None) --> Cursor

Executes [http://msdn.microsoft.com/en-us/library/ms714632%28VS.85%29.aspx SQLGetTypeInfo]
a creates a result set with information about the specified data type or all data types
supported by the ODBC driver if not specified. Each row has the following columns:

# type_name
# data_type
# column_size
# literal_prefix
# literal_suffix
# create_params
# nullable
# case_sensitive
# searchable
# unsigned_attribute
# fixed_prec_scale
# auto_unique_value
# local_type_name
# minimum_scale
# maximum_scale
# sql_data_type
# sql_datetime_sub
# num_prec_radix
# interval_precision

Change log

377522a9679d by mkleehammer <mkleehammer> on Jul 1, 2012   Diff
Edited wiki page Cursor through web user
interface.
Go to: 
Sign in to write a code review

Older revisions

949b81730b89 by Michael Kleehammer <mich...@kleehammer.com> on Jul 28, 2011   Diff
Migrating from Subversion to git
All revisions of this file

File info

Size: 11831 bytes, 390 lines
Powered by Google Project Hosting