安装Freetds
Freetds 是应用最为广泛的 Linux 下的 ODBC 驱动,官网:http://www.freetds.org/。因为我们用的版本是 0.82 版,而 Ubuntu 的更新源里仍然是 0.63 版本,我这边建议你执行 sudo apt-get remove freetds freetds-dev 来把以前装的版本删除掉,然后下载 0.82 或更新的版本。
编译 freetds 的时候一定要注意 configure 的参数,我估计当时我编译的时候参数没有设置正确,所以一直没有能够连上 mssql server。Vcc 分享了他的编译参数,如下:
./configure \
--prefix=/etc/freetds \
--with-tdsver=8.0 --enable-msdblib \
--enable-dbmfix \
--with-gnu-ld \
--enable-shared \
--enable-static
执行 configure 之后再 sudo make,sudo make install,耐心等候安装完成就可以了。
安装完成后,该用 tsql 测试一下,测试的方式是执行:
tsql –H mssql-server-ip –U username –P password
出现 1> 提示符就是成功啦。
配置 freetds
Freetds 的配置文件位置在 /etc/freetds/freetds.conf 里,打开后在最后面增加如下一个 section:
138 [db4]
139 host = 192.168.18.4
140 port = 1433
141 tds version = 7.0
其中 host 是 mssql server 的地址,tds version 是指定使用哪个 tds 版本,据 vcc 说,用 7.0 的兼容性比较好,我小试了一下,8.0 也是可以的。
完成以后,应该用 tsql 测试一下,测试的方式是执行:
tsql –S db4 –U username –P password
看到 1> 标识符就是成功啦。
#coding=utf8
#from twisted.internet import epollreactor
#epollreactor.install()
#from twisted.internet import kqreactor
#kqreactor.install()
from twisted.internet import pollreactor
pollreactor.install()
from twisted.internet import reactor
from twisted.internet.protocol import Protocol,Factory
from loxun import XmlWriter
from StringIO import StringIO
import pymssql
import urllib
class utilitys():
'''.............,..SQL'''
def dataReceivedLikeHandle(self,datas):
searchSql = ''
if datas is not None:
searchSql = "select top 5* from MapObjectInfo where (charindex('%s',Name)>0) order by OrderIndex desc,Hits desc" % (datas)
return searchSql
'''..........'''
def countLikeData(self,cur,datas):
searchSql = "select count(*) from MapObjectInfo where (charindex('%s',Name)>0)" % (datas)
cur.execute(searchSql) #.....
row = cur.fetchone()
return row[0]
'''.......SQL'''
def dataReceivedEqualsHandle(self,datas):
searchSql = ''
if datas is not None:
searchSql = "select top 1* from MapObjectInfo where Name = '%s'" % datas
return searchSql
def _unicodedFromString(self, text):
if text is None:
result = None
elif isinstance(text, unicode):
result = text
else:
result = unicode(text,'utf8')
return result
'''.......XML..'''
def haveSendXmlLikeResult(self,cur,xml,out,nums):
xml.startTag("recordCount")
xml.text(str(nums))
xml.endTag()
#-----------
row = cur.fetchone_asdict()
if cur.rownumber:
while row:
xml.startTag("search:document",{"id":row['ID']})
xml.startTag("ID")
xml.text(str(row['ID']))
xml.endTag()
xml.startTag("myname")
xml.cdata(row['Name'])
xml.endTag()
xml.startTag("address")
xml.cdata(row['Address'])
xml.endTag()
xml.startTag("phone")
xml.cdata(row['Phone'])
xml.endTag()
xml.endTag()
row = cur.fetchone_asdict()
return {'out':out,'rownumber':cur.rownumber}
'''.......XML..'''
def haveSendXmlEqualsResult(self,cur,xml,out):
row = cur.fetchone_asdict()
if cur.rownumber:
while row:
xml.startTag("search:document",{"id":row['ID']})
xml.startTag("ID")
xml.text(str(row['ID']))
xml.endTag()
xml.startTag("myname")
xml.cdata(row['Name'])
xml.endTag()
xml.startTag("address")
xml.cdata(row['Address'])
xml.endTag()
xml.startTag("phone")
xml.cdata(row['Phone'])
xml.endTag()
xml.endTag()
row = cur.fetchone_asdict()
return {'out':out,'rownumber':cur.rownumber}
conn = pymssql.connect(host=r'M2k', user='cip', password='@com', database='Cmap',as_dict=True,charset='utf8')
cur = conn.cursor()
utility = utilitys()
class searchMap(Protocol):
def connectionMade(self):
pass
def connectionLost(self,reason):
pass
def dataReceived(self,data):
out = StringIO()
xml = XmlWriter(out,pretty=False)
searchLikeSql = utility.dataReceivedLikeHandle(data) #..SQL
searchEqualsSql = utility.dataReceivedEqualsHandle(data) #....SQL........
xmlSearchData = ''
xml.addNamespace("search","http://www.beihai365.com")
xml.startTag("search:docset")
cur.execute(searchEqualsSql) #.....
result = utility.haveSendXmlEqualsResult(cur,xml,out)
'''..............'''
if not result['rownumber']:
likeXmlNumber = utility.countLikeData(cur,data) # .....
cur.execute(searchLikeSql) #...
Result = utility.haveSendXmlLikeResult(cur,xml,out,likeXmlNumber)
xml.endTag()
xml.close()
self.transport.write(result['out'].getvalue().rstrip("\r\n"))
self.transport.loseConnection()
def main():
factory = Factory()
factory.protocol = searchMap
port = 9412
reactor.listenTCP(port,factory)
reactor.run()
if __name__ == '__main__':
main()