python 3 操作mssql

 没有标准的库，只好曲线救国。

本文介绍使用python,adodbapi操作数据库:

adodbapi 库

python 可以操作mssqlserver的类库有几个,支持python3.x的目前没几个靠谱的

详细过程:

首先添加引用:

import adodbapi
adodbapi.adodbapi.verbose = False # adds details to the sample printout
import adodbapi.ado_consts as adc

创建连接:

    Cfg={'server':'192.168.29.86\\eclexpress','password':'xxxx','db':'pscitemp'}
    constr = r"Provider=SQLOLEDB.1; Initial Catalog=%s; Data Source=%s; user ID=%s; Password=%s; " \
         % (Cfg['db'], Cfg['server'], 'sa', Cfg['password'])
    conn=adodbapi.connect(constr)

其中Cfg是个key-value字典,constr格式化语法是python2.x常用,在3.x可以使用下面的.

执行sql语句:

    cur=conn.cursor()
    sql='''select * from softextBook where title='{0}' and remark3!='{1}'
    '''.format(bookName,flag)
    cur.execute(sql)

    data=cur.fetchall()
    cur.close()

其中三个引号类似于C#字符串前的"@",python中字符串可以用一个或两个,三个括起来,format格式化语法也类似

执行存储过程:

     #假设proName有三个参数,最后一个参数传了null

    ret=cur.callproc('procName',(parm1,parm2,None))

    conn.commit()

关闭连接

conn.close()

很简单有木有?

更多代码示例见安装目录下里的unit test代码:

C:\Python31\Lib\site-packages\adodbapi\tests

演示代码：（注意需要改成py3）

""" db_print.py -- a simple demo for ADO database reads."""

import adodbapi
try:
    adodbapi.adodbapi.verbose = True # adds details to the sample printout
except AttributeError: # happens when we do a local import, not package
    adodbapi.verbose = True

try:
    import adodbapi.ado_consts as adc
except ImportError:  # ditto
    import ado_consts as adc
    
#tell the server  we are not planning to update...
try:
    adodbapi.adodbapi.defaultIsolationLevel = adc.adXactBrowse
except AttributeError: # and again
    adodbapi.defaultIsolationLevel = adc.adXactBrowse
#----------------- Create connection string -----------------------------------
# connection string templates from http://www.connectionstrings.com
# Switch test providers by changing the "if True" below

# connection string for an Access data table:
if False:
    _databasename = "Test.mdb"
    # generic -> 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s; User Id=%s; Password=%s;' % (_databasename, _username, _password)
    constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s' \
             % _databasename
    _table_name= 'Products'
#----------
else:
# connection string for an SQL server
    _computername="127.0.0.1" #or name of computer with SQL Server
    _databasename="Northwind" #or something else
    _table_name= 'Products'

    if True:    
        # this will open a MS-SQL table with Windows authentication
        constr = r"Initial Catalog=%s; Data Source=%s; Provider=SQLOLEDB.1; Integrated Security=SSPI" \
                 %(_databasename, _computername)
    else:
        _username="sa"
        _password='123'
        # this set opens a MS-SQL table with SQL authentication 
        constr = r"Provider=SQLOLEDB.1; Initial Catalog=%s; Data Source=%s; user ID=%s; Password=%s; " \
             % (_databasename, _computername, _username, _password)
#-----------------------
# connection string for MySQL
if False:
    # this will open a MySQL table (assuming you have the ODBC driver from MySQL.org
    _computername = 'star.vernonscomputershop.com'
    _databasename = 'test'
    constr = 'Driver={MySQL ODBC 3.51 Driver};Server=%s;Port=3306;Database=%s;Option=3;' \
        % (_computername,_databasename)
    _table_name= 'Test_tbl'
#-----------    
# connection string for AS400
if False:
    constr = "Provider=IBMDA400; DATA SOURCE=%s;DEFAULT COLLECTION=%s;User ID=%s;Password=%s" \
                      %  (_computername, _databasename, _username, _password)
    # NOTE! user's PC must have OLE support installed in IBM Client Access Express
#----------------------------------

# ------------------------ START HERE -------------------------------------
#create the connection
con = adodbapi.connect(constr)

#make a cursor on the connection
c = con.cursor()

#run an SQL statement on the cursor
sql = 'select * from %s' % _table_name
c.execute(sql)

#check the results
print 'result rowcount shows as= %d. (Note: -1 means "not known")' \
      % (c.rowcount,)
print
print 'result data description is:'
print '            NAME Type         DispSize IntrnlSz Prec Scale Null?'
for d in c.description:
    print ('%16s %-12s %8d %8s %4d %5d %s') % \
          (d[0], adc.adTypeNames[d[1]], d[2],   d[3],  d[4],d[5], bool(d[6]))
print
print 'str() of first five records are...' 

#get the results
db = c.fetchmany(5)

#print them
for rec in db:
    print rec

print
print 'repr() of next row is...'
print repr(c.next())
print

c.close()
con.close()