python连接sqlsever信息管理系统_python连接sqlserver

安装

①windows下直接pip install pymssql

②linux下麻烦点,需要以下两步

i. sudo apt-get install freetds-dev

ii. 下载pymssql源码 再使用源码安装

使用 -- 直接贴上写的脚本

#!/usr/bin/env python

# -*-coding: utf-8 -*-

from functools import partial

import pymssql

import torndb

import time

import datetime

class Avg(object):

database_mssql = "BEIVMS"

database_mysql = "repair"

conn_mssql = partial(pymssql.connect, host="192.168.1.112", user="sa", password="112358", port=2005, charset="UTF-8") # 然后被pymssql转换为unicode了吧(UTF-8大写)

conn_mysql = partial(torndb.Connection, host='localhost', user='root', password='123456')

def __init__(self, max_gps=60):

self.max_gps = max_gps

self.conn_mssql = self.conn_mssql(database=self.database_mssql)

self.conn_mysql = self.conn_mysql(database=self.database_mysql)

self.tbl_suffix = "DevPositions%s" % time.strftime("%Y%m%d") # DevPositions20140324

@property

def total_puid(self):

sql = "select Puid from BE_Device"

cur = self.conn_mssql.cursor()

cur.execute(sql)

return set([puids[0] for puids in cur]) or set()

@property

def partic_puid(self):

sql = "select Puid from %s group by Puid" % self.tbl_suffix

cur = self.conn_mssql.cursor()

cur.execute(sql)

return set([puids[0] for puids in cur]) or set()

@property

def noaction_puid(self):

return self.total_puid - self.partic_puid

@property

def low_max_gps_puid(self):

sql = "select Puid, count(Puid) from %s group by Puid having count(Puid)<%s" % (self.tbl_suffix, self.max_gps)

cur = self.conn_mssql.cursor()

cur.execute(sql)

return dict(cur)

def abno_puid_info(self):

low_max_gps_puid = self.low_max_gps_puid

sql_info = "select be.Puid, be.Licence, de.NodeName, be.TerminalNumber, be.TerminalType from BE_Device as be,BE_Node as de where be.nodeId = de.nodeId and be.Puid=%s"

def insert(puid, gps_count=0):

# EpID, PlateNumber, OrganName, DevID, DevType

cur = self.conn_mssql.cursor()

cur.execute(sql_info % "'%s'" % puid)

EpID, PlateNumber, OrganName, DevID, DevType = list(cur)[0]

AddTime = datetime.datetime.now().date()

GpsReal = gps_count

GpsMax = self.max_gps

EpType = "null"

sql_insert = """insert into tbl_abnormalep_3g \

(EpID, EpType, PlateNumber, OrganName, DevID, DevType, AddTime, GpsReal, GpsMax) \

values (%s, %s, %s, %s, %s, %s, %s, %s, %s)

"""

# % ("'%s'"%EpID, "'%s'"%EpType, "'%s'"%PlateNumber, "'%s'"%OrganName,"'%s'"%DevID, "'%s'"%DevType, AddTime, GpsReal, GpsMax)

self.conn_mysql.execute(sql_insert, *(EpID, EpType, PlateNumber, OrganName, DevID, DevType, AddTime, GpsReal, GpsMax))

for puid, gps_count in low_max_gps_puid.iteritems():

insert(puid, gps_count)

try:

insert(puid, gps_count)

except Exception, e:

print e

print puid, gps_count

for puid in self.noaction_puid:

try:

insert(puid)

except Exception, e:

print e

def __del__(self):

self.conn_mysql.close()

self.conn_mssql.close()

if __name__ == "__main__":

avg = Avg(60)

print len(avg.total_puid)

print len(avg.partic_puid)

print len(avg.noaction_puid)

print len(avg.low_max_gps_puid)

avg.abno_puid_info()