python 怎么连接sqlserver?要做什么样的准备工作才能开始使用？

[geniuscom]

python 怎么连接sqlserver?要做什么样的准备工作才能开始使用？

[lucong]

import dbi, odbc
try:
s = odbc.odbc('DSNName/Username/Password')
cur = s.cursor()
cur.execute('select * from testTable')
print cur.description
for tup in cur.description:
print tup[0],
print
while 1:
rec = cur.fetchmany(10)
if not rec: break
print rec
except NameError,e:
print 'error ', e, 'undefined'

[geniuscom]

多谢 lucong(鲁葱)
但对于dsn的方法，总不可能每次都要在odbc里加吧
我是linux系统怎么加啊？

[zhaoweikid]

恩，也可以使用专门连接mssql的模块

[zhaoweikid]

那用mssql模块啊~

[netdl]

用ADO

[zhaoweikid]

有个叫mxODBC的应该也可以

[无名2018]

to netdl：

请问ADO “具体”怎么用啊？
小弟目前再这方面也遇到问题：）
请教！！！

[无名2018]

import dbi, odbc
import time
# %D% %T%
class RTable:
def __init__(self,dsn,sql):
try:
s = odbc.odbc(dsn)
print dsn,sql
self.cur = s.cursor()
self.cur.execute(sql)
except NameError,e:
print 'error ', e, 'undefined'
def __len__(self):
pass
def __setitem__(self,key,val):
pass
def __getitem__(self,index):
rec = self.cur.fetchone()
if not rec:
raise IndexError, "index too large"
return rec
class RRec:
def __init__(self,rec,description):
self.record = []
if not rec: return
i = 0
for field in rec:
if description[i][1] == 'DATE':
local = time.localtime(field.value)
s = str(local[1]) + '/' \
+ str(local[2]) + '/' +str(local[0])
setattr(self,description[i][0],s)
self.record.append(s)
elif description[i][1] == 'RAW':
dummy = 'RAW Not Implemented'
setattr(self,description[i][0],dummy)
self.record.append( dummy)
else:
setattr(self,description[i][0],field)
self.record.append(field)
i = i+1
self.data = self.record
def dump(self):
for i in self:
print i,
print
def __len__(self):
return len(self.record)
def __setitem__(self,key,val):
self.record[key] = val
def __getitem__(self,index):
return self.record[index]
if __name__ == '__main__':
print 'script as main'
#sqlstmt = 'select * from shippers'
#sqlstmt = 'select * from orders'
#sqlstmt = 'select * from categories'
# sqlstmt = '\
# SELECT Employees.EmployeeID, Employees.LastName,\
# Employees.FirstName, Employees.Title, Employees.TitleOfCourtesy,\
# Employees.BirthDate, Employees.HireDate, Employees.Address,\
# Employees.City, Employees.Region, Employees.PostalCode,\
# Employees.Country,\
# Employees.HomePhone, Employees.Extension, Employees.Notes,\
# Employees.ReportsTo\
# FROM Employees'
sqlstmt = '\
SELECT EmployeeID, LastName,\
FirstName, Title, TitleOfCourtesy,\
City, Region, PostalCode,\
Country,\
HireDate, Address,\
HomePhone, Extension, Notes,\
ReportsTo\
FROM Employees'
dsn='nwin/admin/'
s = RTable(dsn,sqlstmt)
for rec in s:
b = RRec(rec,s.cur.description)
for field in b:
print field,
print

[无名2018]

See also http://www.python.org/windows/win32/odbc.html for sample
code and notes by Hirendra Hindocha.

Notice that result values are converted to Python objects. Dates in
particular are returned as dbiDate objects. This can be a serious
limitation, because dbiDate can not represent dates prior to the UNIX
epoch (1 Jan 1970 00:00:00 GMT). If you try to retrieve earlier dates,
you'll get garbage and may even provoke a crash.

You'll want to download the win32 source and examine odbc.cpp to see
which features are currently provided. For example, transaction
facilities (setautocommit, commit and rollback) are available, but
SQLDriverConnect (needed to supply extra parameters to the driver) is
not.

If you don't need any of these facilities, you're set. Otherwise, read
on.

II. ODBC using calldll

Sam Rushing's calldll module lets Python call any function in any
DLL. In particular, you can get at ODBC by directly calling functions
in odbc32.dll, and Sam has provided a wrapper module, odbc.py, to do
just that. There is also code for managing data sources, installing
ODBC itself, and creating and maintaining Jet (Microsoft Access)
databases. Poke through the demos and sample code. There are some
amazing things, like cbdemo.py, which has a message loop and window
procedure implemented as Python functions!

[You can find links to the calldll package on Sam's Python Software
page.]

To set up calldll as a package:
1. Install python 1.5 (not 1.4!) as above
2. Get calldll.zip:
ftp://squirl.nightmare.com/pub/python/python-ext/calldll.zip
3. Create a new directory, calldll, under the lib directory, e.g.
c:\Program Files\Python\lib\caldll\
4. Unpack calldll.zip into this directory using WinZip, pkunzip, etc.
5. Move all the files in the calldll\lib\ subdirectory (created by
the unzip) up to the parent (calldll) directory and delete the lib
subdirectory
6. Create the file __init__.py in the calldll directory and put
a suitable comment in it:
# File to allow this directory to be treated as a python 1.5
package.
7. Edit calldll\odbc.py to fix a bug in the get_info facility:
In the functions "get_info_word" and "get_info_long", change
"calldll.membuf" to "windll.membuf"

Here is an example using calldll:
from calldll import odbc

dbc = odbc.environment().connection() # create connection
dbc.connect('sample', 'monty', 'spam') # connect to db
# alternatively, use full connect string:
# dbc.driver_connect('DSN=sample;UID=monty;PWD=spam')
print 'DBMS: %s %s\n' % ( # show DB information
dbc.get_info(odbc.SQL_DBMS_NAME),
dbc.get_info(odbc.SQL_DBMS_VER)
)
result = dbc.query( # execute query & return results
"""
SELECT country_id, name, insert_change_date
FROM country
ORDER BY name
"""
)
print 'Column descriptions:' # show column descriptions
for col in result[0]:
print ' ', col
print '\nFirst result row:\n ', result[1] # show first result row
-------------------------------output--------------------------------

DBMS: Oracle 07.30.0000

Column descriptions:
('COUNTRY_ID', 3, 10, 0, 0)
('NAME', 12, 45, 0, 0)
('INSERT_CHANGE_DATE', 11, 19, 0, 1)

First result row:
['24', 'ARGENTINA', '1997-12-19 01:51:53']

Notice that result values are returned as strings, so dates aren't a
problem, and you have access to SQLDriverConnect in case you need to
supply a connect string. You'll want to examine odbc.py to discover
all the available features. And if you need some facility that isn't
provided, you can easily add it *without* firing up a C compiler.

Suppose, for example, that you need to handle transactions. This
requires the ability to turn off autocommit mode and execute a commit
or a rollback. First, figure out which ODBC functions you need to
call, then add the wrappers to odbc.py, using the existing functions
as guides. When in doubt, experiment! You'll obviously need ODBC
documentation for this. You can download the ODBC 3.0 Programmer's
Reference (in Help file format) or the whole SDK, for free, from
Microsoft: http://www.microsoft.com/data/odbc/download.htm

To handle transactions, we can add the following methods to the
connection class:
def autocommit(self, value=1):
if value:
value = 1 # SQL_AUTOCOMMIT_ON
else:
value = 0 # SQL_AUTOCOMMIT_OFF
return odbc.SQLSetConnectAttr(
self, # connection
102, # attribute = SQL_ATTR_AUTOCOMMMIT
value, # ptr or value
-5 # len = SQL_IS_UINTEGER
)

def commit(self):
return odbc.SQLEndTran(
2, # handle type = SQL_Handle_DBC
self, # handle
0 # SQL_Commit
)

def rollback(self):
return odbc.SQLEndTran(
2, # handle type = SQL_Handle_DBC
self, # handle
1 # SQL_Rollback
)

Notes: 1. SQLSetConnectAttr and SQLEndTran are ODBC 3.0 functions, so
you'll need an ODBC 3.0 Driver Manager (2.x drivers are OK though);
2. The literals are meant to keep the example self-contained, in
practice you should define the appropriate constants in odbc.py

With this code you can now handle transactions:
dbc.autocommit(0) # turn off autocommit
dbc.commit() # commit or rollback as required
dbc.rolback()

Good luck, and if you make any significant extensions to odbc.py, be
sure to share them!
--
John Dell'Aquila <[e-mail]>