/
tutorial.html
122 lines (90 loc) · 4.46 KB
/
tutorial.html
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
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>pyodbc</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<div id="titlebox">
pyodbc - A Python DB API module for ODBC
</div>
<div id="nav">
<a href="index.html">Home</a>
: <a href="docs.html">Documentation</a>
: <a href="tutorial.html">Tutorial</a>
: <a href="http://sourceforge.net/project/showfiles.php?group_id=162557">Download</a>
: <a href="http://sourceforge.net/news/?group_id=162557">News</a>
: <a href="http://github.com/mkleehammer/pyodbc">Source Code (github)</a>
: <a href="http://sourceforge.net/tracker/?group_id=162557&atid=824254">Bug Tracker</a>
: <a href="http://sourceforge.net/projects/pyodbc">SourceForge</a>
: <a href="http://www.python.org/peps/pep-0249.html">Python DB API</a>
</div>
<div id="contents">
<h1>Introduction</h1>
<p>This document is high-level introduction to using pyodbc and does not cover all its
details. pyodbc implements the
<a href="http://www.python.org/peps/pep-0249.html">Python Database API Specification
v2.0</a>, so you should read this specification for more information.</p>
<p>If you haven't installed pyodbc,
<a href="http://sourceforge.net/project/showfiles.php?group_id=162557">download</a> and
install it.</p>
<h1>Connecting</h1>
<p>First, you must import pyodbc. If you get errors here, make sure you have pyodbc installed.</p>
<pre>
import pyodbc</pre>
<p>Next, create a connection by passing an ODBC connection string to the connect method. This
step causes ODBC to load the database driver (the SQL Server driver in this example) and
connect to the database.</p>
<pre>
cnxn = pyodbc.connect('DSN=northwind')</pre>
<p>The ODBC connection string format is specified by ODBC in the
<a
href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqldriverconnect.asp">SQLDriverConnect</a>
documentation. Unfortunately, this is for C programmers, but the comments section discussion
of the connection string format is useful.</p>
<p>ODBC itself recognizes the following keywords in a connection string:</p>
<dl>
<dt>DRIVER</dt>
<dd>The ODBC driver to use. Make sure the driver you want to use is installed.</dd>
<dt>DSN</dt>
<dd>The name of a DSN configured in the control panel Data Sources applet. This allows
database information to be specified in an application-independent manner and location.</dd>
<dt>UID</dt>
<dd>The user name when a login is required.</dd>
<dt>PWD</dt>
<dd>The password when a login is required. DSNs cannot contain passwords, so you may need
this even when using the DSN keyword.</dt>
<dt>FILEDSN</dt>
<dd>The name of a .dsn file, used when the DSN information is stored in a file.</dd>
<dl>
<p>Each database driver may support additional keywords. For example, the SQL Server driver
allows you to specify the machine SQL Server is running on using the SERVER keyword and the
database to connect to using the DATABASE keyword. These two allow you to connect to the
database without registering a DSN in the control panel. (The ODBC section of the SQL Native
Client <a href="http://msdn2.microsoft.com/en-us/library/ms130822(SQL.90).aspx#">Using
Connection String Keywords with SQL Native Client</a> documentation may be useful when using SQL Server.)</p>
<pre>
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=user;PWD=password')</pre>
<h1>Create an Example Table</h1>
<p>Next, we'll create a table and populate it with some example values. First, make a cursor
and execute the necessary SQL. (The SQL may need to be modified for your database,
particularly the type names like 'int'. I'm testing this using SQL Server.) Finally, commit
the changes.</p>
<pre>
cursor = cnxn.cursor()
cursor.execute("create table tmp(a int, b varchar(30))")
cnxn.commit()</pre>
<p>First, notice that the commit is applied to the connection, not the cursor. Changes from
all cursors attached to the same connection will be commited. Also note that the commit
is <i>required</i>. If you do not commit, the changes will be rolled back when the connection
is closed.</p>
<h1>Insert Some Values</h1>
<h1>Selecting Values</h1>
<p>Once you have a connection, obtain a cursor from it and execute a select statement via the
cursor's <code>execute</code> method:</p>
<pre>
cursor = cnxn.cursor()
cursor.execute('select a, b from tmp')
</pre>
</body>
</html>