def __connect(self,
server=r"LOGONSERVER\MURANO",
user="******",
password=None,
database="GEOTEXAN"):
"""
Inicia la conexión con los parámetros por defecto. Es necesario que
exista un fichero credentials.txt con la contraseña para acceder al
servidor MS-SQLServer.
"""
if VERBOSE and DEBUG:
frame = inspect.currentframe()
args, _, _, values = inspect.getargvalues(frame)
print("Iniciando conexión [%s]" % "; ".join(
["%s = %s" % (i, values[i]) for i in args if i != "self"]))
self.__database = database
if password is None:
try:
directorio = os.path.abspath(os.path.dirname(__file__))
credentials = open(os.path.join(directorio, "credentials.txt"))
except IOError:
raise Exception("Cree un fichero credentials.txt en %s "
"conteniendo la contraseña para el usuario "
"%s." % (directorio, user))
else:
password = credentials.readlines()[0].split()[0]
credentials.close()
try:
# pylint: disable=unexpected-keyword-arg
conn = pymssql.connect(server=server, user=user,
password=password, database=database)
except TypeError: # Depende de la versión usa host o server.
conn = pymssql.connect(host=server, user=user,
password=password, database=database)
return conn
def sqlManager(dbHost, dbUser, dbPass):
print "::.SQL Manager.::"
try:
conn = pymssql.connect(host=dbHost, user=dbUser, password=dbPass, database="master")
cur = conn.cursor()
cur.execute("SELECT * FROM master.dbo.SysDatabases")
row = cur.fetchone()
while row:
print row[0]
row = cur.fetchone()
except:
print "Connection faulier"
exit()
dbName = raw_input("Choise a database: ")
conn = pymssql.connect(host=dbHost, user=dbUser, password=dbPass, database=dbName)
cur = conn.cursor()
command = "y"
command = raw_input("SQL Manager~$ ")
while command != "exit":
if command == "SHOW TABLES":
cur.execute("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'")
row = cur.fetchone()
while row:
print row[2]
row = cur.fetchone()
try:
cur.execute("%s" % command)
row = cur.fetchone()
while row:
print row
row = cur.fetchone()
except:
print ""
command = raw_input("SQL Manager~$ ")
def _make_logbook_connection(self):
self._credentials = LOGBOOK_CREDENTIALS[self._name]
self._table = self._credentials['table']
self._shotlist_query_prefix = (
'SELECT DISTINCT rundate, shot, xp, voided '
'FROM {} WHERE voided IS null').format(self._table)
self._shot_query_prefix = (
'SELECT dbkey, username, rundate, shot, xp, topic, text, entered, '
'voided FROM {} WHERE voided IS null').format(self._table)
try:
self._logbook_connection = pymssql.connect(
server=self._credentials['server'],
user=self._credentials['username'],
password=self._credentials['password'],
database=self._credentials['database'],
port=self._credentials['port'],
as_dict=True)
except:
print('Attempting logbook server connection as drsmith')
try:
self._logbook_connection = pymssql.connect(
server=self._credentials['server'],
user='******',
password=self._credentials['password'],
database=self._credentials['database'],
port=self._credentials['port'],
as_dict=True)
except:
txt = '{} logbook connection failed. '.format(self._name.upper())
txt = txt + 'Server credentials:'
for key in self._credentials:
txt = txt + ' {0}:{1}'.format(key, self._credentials[key])
raise FdpError(txt)
def dbConnection(self,dbname):
if(dbname=='work'):
con=ms.connect(host="10.31.201.123", user="******", password="******",
database="IBdata")
return con
if(dbname=='home'):
con=ms.connect(host="192.168.1.5", user="******", password="******",
database="Quant2")
return con
def getCursorFlight(for_update = False):
if DEBUG:
con = pymssql.connect(host='ppp-utilities\\testing', user='******', password='******', database='Flight')
else:
con = pymssql.connect(host='ppp-sql01', user='******', password='******', database='Flight')
if for_update:
return con, con.cursor()
else:
return con.cursor()
def exec_sql(sql, filial):
""" executa o sql """
if filial == 'SAO':
conn = pymssql.connect(db_server_sp, db_user_sp, db_password_sp, db_database_sp)
else:
conn = pymssql.connect(db_server_rj, db_user_rj, db_password_rj, db_database_rj)
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchone()
conn.close()
return rows
def getconnection(host, user, password, db_name, instaneName=None):
if instaneName is None:
conn = pymssql.connect(host, user, password, db_name)
return conn
else:
conn = pymssql.connect(
host + '\\' + instaneName,
user,
password,
db_name)
return conn
def create_xls(cx_date,c_date):
file_path=os.getcwd()
f=xlwt.Workbook()
sheet1=f.add_sheet(u'销售流水',cell_overwrite_ok=True)
conn = pymssql.connect(host='192.168.0.236',user='******',password='******',database='UFTData810145_000002',charset='utf8')
viplist=conn.cursor()
today=datetime.datetime.now()
oneday=datetime.timedelta(days=1)
yesterday=today-oneday
date_yesterday=yesterday.date()
Sql='select * from V_Shop_InventorySaleDetail WHERE ' + ' orderdate between '+'\''+str(cx_date)+' 00:00:00'+'\''+'and '+'\''+str(c_date)+' 00:00:00'+'\''+' ORDER by shop'
print Sql
viplist.execute(Sql)
rows=[u'店铺',u'单据日期',u'星期',u'商品编码',u'商品名称',u'一级分类',u'二级分类',u'三级分类',u'数量',u'含税单价',u'含税金额',u'零售价',u'零售金额',u'折扣',u'导购']
for j in range(len(rows)):
sheet1.write(0,j ,rows[j],set_style('Arial',220,2,True,1,1,1,1))
row=1
for shop,orderdate,orderweekday,item_no,item_name,firstclass,secondclass,thirdclass,quantity,saleprice,saleamount,price,amount,discount,emoplyee in viplist.fetchall():
sheet1.write(row,0,shop,set_style('Dotum',220,0,False,1,0,1,0))
sheet1.write(row,1,orderdate,set_style('Dotum',220,0,False,0,0,0,0))
sheet1.write(row,2,orderweekday,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,3,item_no,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,4,item_name,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,5,firstclass,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,6,secondclass,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,7,thirdclass,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,8,quantity,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,9,saleprice,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,10,saleamount,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,11,price,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,12,amount,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,13,discount,set_style('Dotum',220,0,False,1,1,1,1))
sheet1.write(row,14,emoplyee,set_style('Dotum',220,0,False,1,1,1,1))
row+=1
f.save(file_path+str(cx_date)+'.xls')
def connect(self):
self.initConnection()
try:
self.connector = pymssql.connect(host="%s:%d" % (self.hostname, self.port), user=self.user, password=self.password, database=self.db, login_timeout=conf.timeout, timeout=conf.timeout)
except (pymssql.Error, _mssql.MssqlDatabaseException), msg:
raise SqlmapConnectionException(msg)
def test_conn_props_override(self):
conn = self.connect(conn_props='SET TEXTSIZE 2147483647')
conn.close()
conn = self.connect(conn_props='SET TEXTSIZE 2147483647;')
conn.close()
conn = self.connect(conn_props='SET TEXTSIZE 2147483647;SET ANSI_NULLS ON;')
conn.close()
conn = self.connect(conn_props='SET TEXTSIZE 2147483647;SET ANSI_NULLS ON')
conn.close()
conn = self.connect(conn_props='SET TEXTSIZE 2147483647;'
'SET ANSI_NULLS ON;')
conn.close()
conn = self.connect(conn_props=['SET TEXTSIZE 2147483647;', 'SET ANSI_NULLS ON'])
conn.close()
self.assertRaises(Exception, self.connect, conn_props='BOGUS SQL')
conn = pym.connect(
conn_properties='SET TEXTSIZE 2147483647',
server=config.server,
user=config.user,
password=config.password
)
conn.close()
def connect(self):
"""Attempts to establish connection to SQL Server database."""
conn = pymssql.connect(host=self.server+'\\'+self.instance,
user=self.user,
password=self.password,
database=self.database)
return conn
def connectMssql(self):
host = self._plugin.get("config", "source_ip")
user = self._plugin.get("config", "user")
passwd = self._plugin.get("config", "password")
db = self._plugin.get("config", "db")
self.__objDBConn = pymssql.connect(host=host, user=user, password=passwd, database=db)
self.__myDataBaseCursor = self.__objDBConn.cursor()
def crack(target, port, user, passwd, outfile, timeo, i):
user = user.replace("\n", "")
passwd = passwd.replace("\n", "")
db = None
try:
db = pymssql.connect(user=user, password=passwd, database="production", server=target, port=port, timeout=timeo)
login = "******" % (target, user, passwd)
print login
if outfile:
write_to_file(outfile, login + "\n")
db.close()
os._exit(0)
except pymssql.Error as err:
exception = str(err)
if "['publickey']" in exception:
print "[-] key authentication only - " "stopped attack against %s" % (target)
os._exit(1)
elif "'keyboard-interactive'" in exception:
print "[-] %s requires 'keyboard-interactive' handler" % (target)
os._exit(1)
except:
# after 3 timeouts per request the attack against $target will stopped
if i < 3:
i += 1
# reconnect after random seconds (between 0.6 and 1.2 sec)
randtime = random.uniform(0.6, 1.2)
time.sleep(randtime)
crack(target, port, user, passwd, outfile, timeo, i)
else:
print "[-] too many timeouts - stopped attack against %s" % (target)
os._exit(1)
def get_jwst_ephemeris():
'''
extracts predicted JWST ephemeris from DMS database (the whole thing for now)
and returns a numpy 2d array
'''
if 'METRICS_SERVER' in os.environ:
eserver = os.environ['METRICS_SERVER']
else:
eserver = 'JWDMSDEVDBVM1'
if 'METRICS_DB' in os.environ:
edb = os.environ['METRICS_DB']
else:
edb = 'jwdpmetrics5'
logger.info(
'Ephemeris connect info:'
' eserver={}'
' edb={}'.format(eserver, edb)
)
logger.debug(
'Ephemeris connect info:'
' eserver={}'
' edb={}'.format(eserver, edb)
)
conn = pymssql.connect(server=eserver, database=edb)
cur = conn.cursor()
cur.execute('select * from predictephemeris')
etab = np.array(cur.fetchall())
return etab
def insert(id_si8, regName, value, now_date=datetime.datetime.now()):
conn = pymssql.connect(server=server, user=user, password=password, database=database)
cursor = conn.cursor()
cursor.execute("INSERT INTO si8_value (id_si8, regName, value, Date) VALUES ( %d, %s, %d, %s)",
(id_si8, regName, value, now_date))
conn.commit()
conn.close()
def get_mom_connection(self):
""" Gets SQL Server connection to MOM """
config = SafeConfigParser()
config.read(os.path.join(os.path.dirname(__file__), 'config.ini'))
try:
momdb_host = config.get("momdb", "host")
momdb_user = config.get("momdb", "user")
momdb_password = config.get("momdb", "password")
momdb_db = config.get("momdb", "db")
except Error as error:
msg = "Config section [momdb] bad or missing: %s" % error.message
logging.error(msg)
raise Exception(msg)
try:
logging.info('Connecting to MOM...')
if self.conn is None:
self.conn = connect(host=momdb_host, user=momdb_user,
password=momdb_password,
database=momdb_db, as_dict=True)
return self.conn
except InterfaceError as error:
msg = "Error connecting to SQL Server: %s" % error.message
logging.error(msg)
raise Exception(msg)
except Error as error:
logging.error(error.message)
raise
def __init__(self, path, tablename, log_type='w3c'):
self.filePath = path
self.tableName = tablename
self.logType = log_type
self.conn = pymssql.connect(host=read_settings('settings.cfg', 'db', 'db_host'),
database=read_settings('settings.cfg', 'db', 'db_name'),
user=read_settings('settings.cfg', 'db', 'db_user'),
password=read_settings('settings.cfg', 'db', 'db_pass'))
self.cur = self.conn.cursor()
self.field_list = {0: 'date', # 发出请求时的日期
1: 'time', # 发出请求时的时间
2: 's-sitename', # 服务名
3: 's-computername', # 服务器名
4: 's-ip', # 服务器IP地址
5: 'cs-method', # 请求中使用的HTTP方法，比如GET/POST
6: 'cs-uri-stem', # URI资源，记录做为操作目标的统一资源标识符，即访问的页面
7: 'cs-uri-query', # URI查询，记录客户尝试执行的查询
8: 's-port', # 服务器提供服务的端口号
9: 'cs-username', # 用户名
10: 'c-ip', # 客户端的IP地址
11: 'cs-version', # 客户端使用的协议版本，如HTTP/FTP等
12: 'cs(User-Agent)', # 用户代理、浏览器、操作系统等情况
13: 'cs(Cookie)', # 记录发送或接受的Cookies内容
14: 'cs(Referer)', # 引用站点，即访问来源
15: 'cs-host', # 记录主机头名称
16: 'sc-status', # 协议状态，如200，403，404，500等
17: 'sc-substatus', # 协议子状态
18: 'sc-win32-status', # Win32状态
19: 'sc-bytes', # 服务器发送的字节数
20: 'cs-bytes', # 服务器接受的字节数
21: 'time-taken' # 记录操作所花的时间，单位毫秒
}
def connect_sql_server():
try:
conn = pymssql.connect(host ="10.0.185.138",database ="JYDB",user="******",password="******",charset = "utf8")
print "sql_server 数据库连接成功".decode("utf8")
return conn
except Exception, e:
print "sql_server 数据库连接失败".decode("utf8"),e
def res_department_import(self,cr,uid,ids,context=None):
obj=self.pool.get('res.department')
server='192.168.10.2'
user='******'
password='******'
database='mtlerp-running'
conn=pymssql.connect(server=server,user=user,password=password,database=database)
cur=conn.cursor()
sql=''' select isnull(a.departmentname,'') as departmentname ,isnull(a.address,'')as address,isnull(a.telephone,'') as telephone,isnull(b.employeename,'') from TBdepartment a left join TBemployee b on a.DepartmentManager=b.employeecode '''
cur.execute(sql)
s=cur.fetchall()
b=[]
for row in s:
b.append([(''.join(map(lambda x: "%c" % ord(x), list(row[i]))).decode('gbk')) for i in range(len(row))])
print row,'row'
for row in b:
info_id=obj.create(cr,uid,{
'name':row[0],
'department_address':row[1],
'link_phone':row[2],
'responsiblename':row[3],
})
conn.close()
return True
def m_button4OnButtonClick(self, event):
# Delete a record.
try:
# An item must be selected in the grid.
if self.__selectedIndexValue >= 0:
# Logical Delete
# Get the Id value of the selected item.
self.__idValue = int(self.m_grid1.GetCellValue(self.__selectedIndexValue, 0))
# Open a connection to the database.
__connection = pymssql.connect("localhost", "root", "RootUser0123456789", "MsSqlTestDatabase")
# Get the items from the People table.
__cursor = __connection.cursor()
# Execute.
__cursor.execute(
"UPDATE [MsSqlTestDatabase].[dbo].[People] SET IsDeleted = %s WHERE Id = %s", (True, self.__idValue)
)
# Execute.
__connection.commit()
# Close the cursor and the connection.
__cursor.close()
__connection.close()
# Refresh.
self.GetData()
except:
pass
def run(options):
socket.setdefaulttimeout(2)
ip = options['ip']
username = options['username']
password = options['password']
test = random.choice(config.MSSQL_QUERIES)
expected = test['response']
try:
conn = pymssql.connect(ip, '{}\\{}'.format(config.DOMAIN, username), password, test['db'])
cursor = conn.cursor()
cursor.execute(test['query'])
response = ' '.join([str(row[0]) for row in cursor.fetchall()])
except pymssql.Error as e:
name = e.__class__.__name__
if name in ERROR_STRINGS:
error_string = ERROR_STRINGS[name]
logger.debug(error_string % e)
else:
logger.debug('%s: %s' % (name, e))
return False
if response == expected:
return True
else:
logger.debug('Check failed: output: %s | expected: %s' % (response, expected))
return False
def main():
config = configparser.ConfigParser()
config.read('/opt/projects/wob_zz/config.ini')
login = {
'user': config.get('local_mssql', 'user'),
'password': config.get('local_mssql', 'password'),
'server': config.get('local_mssql', 'server'),
'port': config.get('local_mssql', 'port'),
'database': config.get('wob_zz', 'database')}
cnx = sql.connect(**login)
# turn autocommit on
cnx.autocommit(True)
cursor = cnx.cursor()
# get all staging file names in staging_path
staging_path = config.get('wob_zz', 'staging_path')
csv_files = [fn for fn in os.listdir(staging_path)
if any([fn.endswith(ext) for ext in ['csv']])]
# format staging path for Windows OS with escaped backslashes
# \\psf is mountpoint for OSX
windows_path = '\\\\psf' + staging_path.replace('/','\\') + '\\'
for file in csv_files:
table = '.'.join(file.split('.')[0:2])
windows_source = windows_path + file
load_staged_dimension(windows_source, table, cursor)
def GetData(self):
try:
# Clear existing items.
if self.m_grid1.GetNumberRows() - 1 > 0:
self.m_grid1.DeleteRows(0, self.m_grid1.GetNumberRows())
# Open a connection to the database.
__connection = pymssql.connect("localhost", "root", "RootUser0123456789", "MsSqlTestDatabase")
# Get the items from the People table.
__people = []
__cursor = __connection.cursor()
# Execute.
__cursor.execute("SELECT * FROM [MsSqlTestDatabase].[dbo].[People] WHERE IsDeleted = 0")
# Store the items in a Person list.
for __row in __cursor.fetchall():
__people.append(__row)
# Close the cursor and the connection.
__cursor.close()
__connection.close()
# Fill the grid rows with the values of the People table.
for __oneItem in __people:
self.m_grid1.AppendRows(1)
self.m_grid1.SetCellValue(self.m_grid1.GetNumberRows() - 1, 0, str(__oneItem[0]))
self.m_grid1.SetCellValue(self.m_grid1.GetNumberRows() - 1, 1, str(__oneItem[1]))
self.m_grid1.SetCellValue(self.m_grid1.GetNumberRows() - 1, 2, str(__oneItem[2]))
self.m_grid1.SetCellValue(self.m_grid1.GetNumberRows() - 1, 3, str(__oneItem[3]))
self.m_grid1.SetCellValue(self.m_grid1.GetNumberRows() - 1, 4, str(__oneItem[4]).split(" ")[0])
except:
pass
def getDetail(serial_id):
"""
获取已使用券明细
:param serial_id:批次ID
:return: 列表
"""
conn = pymssql.connect(host=Constants.KGGROUP_DB_SERVER,
port=Constants.KGGROUP_DB_PORT,
user=Constants.KGGROUP_DB_USER,
password=Constants.KGGROUP_DB_PASSWORD,
database=Constants.KGGROUP_DB_DATABASE,
charset='utf8',
as_dict=True)
cur = conn.cursor()
sql = u"select ClearShopID as used_shop, " \
u" ClearSDate as used_date, " \
u" ClearPOSID as used_name, " \
u" CouponNO as voucher " \
u" from MyShop_Coupon " \
u" where ClearFlag = 1" \
u" and SerialID = '{serial_id}'" \
u" order by ClearShopID, ClearSDate".format(serial_id=serial_id)
cur.execute(sql)
list = cur.fetchall()
cur.close()
conn.close()
return list
def pymssql_delete_table(cfg, tablename):
import pymssql
server = cfg['ipv4host']
def get_username(cfg):
username = cfg.get('username', None)
if username:
return username
if 'meta' not in cfg:
return None
return cfg['meta'].get('user', None)
def get_password(cfg):
password = cfg.get('password', None)
if password:
return password
if 'meta' not in cfg:
return None
return cfg['meta'].get('password', None)
user = get_username(cfg)
password = get_password(cfg)
dbname = cfg['meta']['databaseName']
conn = pymssql.connect(server, user, password, dbname, tablename)
cursor = conn.cursor()
# TODO:
cursor.execute("DELETE FROM %s" % tablename)
conn.commit()
def mssql(ip='',port='1433',name='',productname=''):
head=''
ans=None
keywords=''
hackinfo=''
import pymssql,chardet
con=None
passwd=['root','123456','admin','','12345','111111','password','123123','1234','12345678','123456789','123',
'abc123','qwerty']
for i in passwd:
try:
con = pymssql.connect(host=ip,user='******',password=i,login_timeout=5)
hackinfo= ' the password is :'+i
print ip+hackinfo
keywords='mssql'
break;
except Exception,e:
keywords='mssql'
hackinfo=str(e)
if 'sa' in hackinfo:
print 'yes'
chardit1 = chardet.detect(hackinfo)
print hackinfo.decode(chardit1['encoding']).encode('utf8')
finally:
def get_sql_connection(conn_string):
"""
Returns a pymssql connection object based on the connection string in settings.
"""
conn_settings = get_conn_settings(conn_string)
return pymssql.connect(conn_settings['SERVER'], conn_settings['UID'],
conn_settings['PWD'], conn_settings['DATABASE'])
def testcon(server, user, pwd, db, sqlstr):
conn = pymssql.connect(server, user, pwd, db,sqlstr)
cursor = conn.cursor()
cursor.execute(sqlstr)
row = cursor.fetchone()
conn.close()
return row
def formconnection():
"""
Connect to the MS SQL server and execute a query to get the data
which contains trees with DBH > 200 cm.
"""
# Connect to MSSQL Server
conn = pymssql.connect(server="stewartia.forestry.oregonstate.edu:1433",
user="******",
password="******",
database="FSDBDATA")
# Create a database cursor
cursor = conn.cursor()
# Replace this nonsense with your own query :)
query = """SELECT fsdbdata.dbo.tp00101.treeid, fsdbdata.dbo.tp00101.psp_studyid,
fsdbdata.dbo.tp00101.species, fsdbdata.dbo.tp00101.standid, fsdbdata.dbo.tp00102.treeid,
fsdbdata.dbo.tp00102.dbh, fsdbdata.dbo.tp00102.tree_vigor FROM fsdbdata.dbo.tp00101
LEFT JOIN fsdbdata.dbo.tp00102
ON fsdbdata.dbo.tp00101.treeid = fsdbdata.dbo.tp00102.treeid
WHERE fsdbdata.dbo.tp00102.dbh > 150
ORDER BY fsdbdata.dbo.tp00102.treeid ASC"""
# Execute the query
cursor.execute(query)
return cursor
def write_to_dS(self,cr,uid,ids,context=None):
my=self.browse(cr,uid,ids[0])
employeecode=my.employeecode.encode('utf-8')
employeename=my.employeename.encode('utf-8')
server='192.168.10.2'
user='******'
password='******'
database='mtlerp-running'
procname='ppoetods'
print employeecode,'employeecode'
print employeename,'employeename'
print type(employeename)
try:
conn=pymssql.connect(server=server,user=user,password=password,database=database)
cur=conn.cursor()
sql='''exec ppoetods '%s','%s' ''' %(employeecode,employeename)
print sql
cur.execute('''exec ppoetods '%s','%s' ''' %(employeecode,employeename))
except:
raise osv.except_osv(_('Error!'),_(u'更新失败，请检查数据是否正确！'))
else:
conn.commit()
conn.close()
raise osv.except_osv(_(u'提示'),_(u'更新成功！'))
return True
def connect_sqlserver(db):
"""连接sql server数据库"""
connect = pymssql.connect('10.4.10.184', 'readuser', 'password', db)
cursor = connect.cursor()
return connect, cursor
"""
Create Time: 2020/3/27 9:56
Author: FengkaiXiao
"""
import pymssql
import random
NUM = 0
db = pymssql.connect(server='192.168.125.135',
user='******',
password='******',
database='xfk_mssql_2',
port=1433,
charset='utf8')
cursor = db.cursor()
for i in range(100):
Ageint = 20 + random.randint(0, 40)
IMCONME = 10000 + random.randint(0, 1000)
NUM += 1
NAME = 'name' + str(NUM)
Nation = 'CHINA', 'JAPAN', 'UK', 'USA', 'GERMAN', 'FRANCE', 'KOREA', 'BRAZIL'
Lastname = 'SMTH', 'KOBE', 'MICHEAEL', 'JASON', 'F**K', 'STUPID'
Hobby = 'FOOTBALL', 'BASKETBALL', 'VOLLEBALL'
COME = random.choice(Nation)
LASTNAME = random.choice(Lastname)
HOBBY = random.choice(Hobby)
sql = "INSERT INTO xfk02 (id, first_name, middle_name, last_name, come_from, age, income, hobby) VALUES ('%d', '%s','%s','%s', '%s', '%d', '%d' ,'%s')" % (
NUM, NAME, COME, LASTNAME, COME, Ageint, IMCONME, HOBBY)
cursor.execute(sql)
db.commit()
def retrieveLabOrderMSSQL():
try:
now = datetime.datetime.now()
isSuccess = True
reasonCode = 200
reasonText = ""
returnUserToken = getUserToken(request.headers.get('Authorization'))
if not returnUserToken["isSuccess"]:
return jsonify(returnUserToken)
userID = returnUserToken["data"]["user_id"]
dataInput = request.json
paramList = ['order_id']
paramCheckStringList = ['order_id']
msgError = checkParamDataInput(dataInput, paramList,
paramCheckStringList)
if msgError != None:
return jsonify(msgError)
orderID = dataInput.get("order_id", "")
conn = pymssql.connect(mssql_server, mssql_user, mssql_password,
mssql_database)
cursor = conn.cursor()
labOrderSql = """\
select lis_lab_order.order_skey,lis_lab_order.order_id,lis_lab_order.visit_skey,lis_lab_order.status,lis_lab_order.priority_cd,lis_lab_order.admission_no,
FORMAT(lis_lab_order.collection_date, 'yyyy-MM-dd HH:mm:ss') as collection_date,
FORMAT(lis_lab_order.received_date, 'yyyy-MM-dd HH:mm:ss') as received_date,
lis_lab_order.cust_skey,customer.cust_no,customer.name as cust_name,lis_lab_order.proj_cd,proj_header.proj_desc,lis_lab_order.physician_no_1,
lis_lab_order.anonymous,lis_lab_order.hospital_lab_no,lis_lab_order.customer_type_skey,lis_lab_order.iqc_job_no,lis_lab_order.iqc_lot_no,
lis_logical_location.description as logical_location_desc,lis_lab_order.report_type_cd,lis_lab_order.ignore_type,lis_lab_order.patient_rights_cd,
lis_lab_order.form_name,lis_lab_order.request_by,lis_lab_order.sent_to_dept,lis_lab_order.department,lis_lab_order.order_department,
lis_visit.visit_id,lis_visit.logical_location_cd,lis_visit.facility_cd,facility.facility_name,view_lis_visit_patient.birthday,
view_lis_visit_patient.patient_fullname,patho_sex.sex_desc,view_lis_visit_patient.date_arrived_year,view_lis_visit_patient.date_arrived_month,view_lis_visit_patient.date_arrived_day,
view_patho_physician_1.first_name + ' ' + view_patho_physician_1.last_name as patho_physician_name_1,
view_patho_technician.first_name + ' ' + view_patho_technician.last_name as technician_name,
lis_patient.patient_skey,lis_patient.patient_id,lis_patient.firstname,lis_patient.middle_name,lis_patient.lastname,patho_sex.sex_desc,patho_prefix.prefix_desc,
lis_patient.hn,lis_patient.phone,lis_patient.birthday,lis_patient.id_card,lis_patient.passport_id,
CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("picture")))','VARCHAR(MAX)') as patient_picture_base64
from lis_lab_order inner join lis_visit on
lis_lab_order.visit_skey = lis_visit.visit_skey inner join facility on
lis_visit.facility_cd = facility.facility_cd inner join lis_patient on
lis_visit.patient_skey = lis_patient.patient_skey inner join view_lis_visit_patient on
lis_visit.visit_skey = view_lis_visit_patient.visit_skey and
lis_patient.patient_skey = view_lis_visit_patient.patient_skey inner join patho_sex on
lis_patient.sex = patho_sex.sex_cd inner join patho_prefix on
lis_patient.prefix = patho_prefix.prefix_cd inner join customer on
lis_lab_order.cust_skey = customer.cust_skey inner join lis_logical_location on
lis_visit.logical_location_cd = lis_logical_location.code left outer join view_patho_physician as view_patho_physician_1 on
lis_lab_order.physician_no_1 = view_patho_physician_1.physician_no left outer join view_patho_technician on
lis_lab_order.technician_no = view_patho_technician.technician_no left outer join proj_header on
lis_lab_order.proj_cd = proj_header.proj_cd
where lis_lab_order.order_id = %(order_id)s
"""
labOrderCategorySql = """\
select lis_lab_order.order_skey,lis_lab_order.order_id,lis_category.category_skey,lis_category.category_id,lis_category.category_desc,
lis_lab_category.category_status,'Y' as check_flag
from lis_lab_order inner join lis_visit on
lis_lab_order.visit_skey = lis_visit.visit_skey inner join lis_patient on
lis_visit.patient_skey = lis_patient.patient_skey inner join lis_lab_test_item on
lis_lab_order.order_skey = lis_lab_test_item.order_skey inner join lis_sub_category on
lis_lab_test_item.sub_category_skey = lis_sub_category.sub_category_skey inner join lis_category on
lis_sub_category.category_skey = lis_category.category_skey inner join lis_test_item on
lis_lab_test_item.test_item_skey = lis_test_item.test_item_skey left outer join lis_lab_category on
lis_lab_order.order_skey = lis_lab_category.order_skey and
lis_category.category_skey = lis_lab_category.category_skey
where lis_lab_order.order_skey = %(order_skey)s
group by lis_lab_order.order_skey,lis_lab_order.order_id,lis_patient.patient_skey,lis_category.category_skey,lis_category.category_id,lis_category.category_desc,
lis_lab_category.category_status
"""
labOrderTestItemSql = """\
select lis_visit.patient_skey,lis_lab_test_item.order_skey,lis_lab_test_item.test_item_skey,lis_lab_test_item.complete_flag,lis_lab_test_item.complete_date,lis_lab_test_item.vendor_skey,lis_lab_test_item.due_date,
lis_lab_test_item.sent_date,lis_lab_test_item.job_doc_no,lis_lab_test_item.remark,lis_lab_test_item.status,lis_lab_test_item.sub_category_skey,lis_lab_test_item.due_date_outlab,lis_lab_test_item.required_all_result_item,
lis_lab_test_item.sticker_cd,lis_lab_test_item.test_item_status,lis_lab_test_item.reject_flag,lis_lab_order.order_id,lis_test_item.test_item_id,lis_test_item.test_item_desc,lis_test_item.alias_id as test_item_alias_id,lis_test_item.differential_flag,lis_lab_test_item.reject_flag,
lis_category.category_skey,lis_lab_category.category_status,lis_lab_test_item.test_item_status,vendor_master.name as vendor_name,case when hist > 0 then 'H' else '' end as history,'Y' as check_flag
from lis_lab_order inner join lis_visit on
lis_lab_order.visit_skey = lis_visit.visit_skey inner join lis_patient on
lis_visit.patient_skey = lis_patient.patient_skey inner join lis_lab_test_item on
lis_lab_order.order_skey = lis_lab_test_item.order_skey inner join lis_sub_category on
lis_lab_test_item.sub_category_skey = lis_sub_category.sub_category_skey inner join lis_category on
lis_sub_category.category_skey = lis_category.category_skey inner join lis_test_item on
lis_lab_test_item.test_item_skey = lis_test_item.test_item_skey left outer join vendor_master on
lis_lab_test_item.vendor_skey = vendor_master.vendor_skey left outer join lis_lab_category on
lis_lab_order.order_skey = lis_lab_category.order_skey and
lis_category.category_skey = lis_lab_category.category_skey OUTER APPLY
(select distinct 1 as hist from lis_lab_order o inner join lis_visit v on
o.visit_skey = v.visit_skey inner join lis_patient p on
v.patient_skey = p.patient_skey inner join lis_lab_test_item t on
o.order_skey = t.order_skey and
p.patient_skey = lis_patient.patient_skey and
t.test_item_skey = lis_lab_test_item.test_item_skey
where o.date_created < lis_lab_order.date_created) tmp
where lis_lab_order.order_skey = %(order_skey)s and
lis_category.category_skey = %(category_skey)s
"""
labOrderResultItemSql = """\
select lis_visit.patient_skey,lis_lab_result_item.order_skey,lis_lab_result_item.result_item_skey,lis_lab_result_item.result_value,lis_lab_result_item.order_result_skey,lis_lab_result_item.result_type_cd,
lis_lab_result_item.reference,lis_lab_result_item.comment,lis_lab_result_item.rerun,lis_lab_result_item.critical_alert_value,lis_lab_result_item.result_item_attr_skey,lis_lab_result_item.critical_alert_skey,
lis_lab_result_item.comment_result_item_attr_skey,lis_lab_order.order_id,lis_category.category_skey,lis_lab_category.category_status,lis_test_item.test_item_skey,lis_test_item.test_item_desc,lis_test_item.alias_id as test_item_alias_id,
lis_lab_test_item.vendor_skey,lis_lab_test_item.required_all_result_item,lis_result_item.result_item_id,lis_result_item.result_item_desc,lis_result_item.result_type,
lis_result_item.uom,lis_uom.uom_desc,lis_result_item.formula,lis_result_item.decimal_digit,lis_test_item.method_code,lis_result_type.result_type_desc,lis_result_item.alias_id as result_item_alias_id,
lis_result_type.color,lis_result_type.critical_alert,lis_result_form_header.result_form_skey,lis_result_form_header.result_form_id,'N' as delta_check,
(select top 1 reference from lis_result_item_attr_flag
where lis_result_item_attr_flag.result_item_skey = lis_lab_result_item.result_item_skey and
lis_result_item_attr_flag.result_item_attr_skey = lis_lab_result_item.result_item_attr_skey and
normal_flag = 1) as reference_normal,
'' as result_value_prev,abnormal_flags,
(select case when ROW_NUMBER() OVER(ORDER BY lis_lab_result_cbc_abnormal.abnormal) = 1 then '' else ' , ' end +lis_lab_result_cbc_abnormal.abnormal
from lis_lab_result_cbc_abnormal
where lis_lab_result_cbc_abnormal.order_skey = lis_lab_result_item.order_skey and
lis_lab_result_cbc_abnormal.order_result_skey = lis_lab_result_item.order_result_skey
for xml path( '' )) as abnormal_list
from lis_lab_order inner join lis_visit on
lis_lab_order.visit_skey = lis_visit.visit_skey inner join lis_patient on
lis_visit.patient_skey = lis_patient.patient_skey inner join lis_lab_test_item on
lis_lab_order.order_skey = lis_lab_test_item.order_skey inner join lis_lab_test_result_item on
lis_lab_test_item.order_skey = lis_lab_test_result_item.order_skey and
lis_lab_test_item.test_item_skey = lis_lab_test_result_item.test_item_skey inner join lis_test_item on
lis_lab_test_item.test_item_skey = lis_test_item.test_item_skey inner join lis_sub_category on
lis_lab_test_item.sub_category_skey = lis_sub_category.sub_category_skey inner join lis_category on
lis_sub_category.category_skey = lis_category.category_skey inner join lis_lab_result_item on
lis_lab_order.order_skey = lis_lab_result_item.order_skey and
lis_lab_test_result_item.result_item_skey = lis_lab_result_item.result_item_skey inner join lis_result_item on
lis_lab_result_item.result_item_skey = lis_result_item.result_item_skey left outer join lis_lab_result_value on
lis_lab_result_item.order_skey = lis_lab_result_value.order_skey and
lis_lab_result_item.order_result_skey = lis_lab_result_value.order_result_skey left outer join lis_result_type on
lis_lab_result_item.result_type_cd = lis_result_type.result_type_cd left outer join lis_uom on
lis_result_item.uom = lis_uom.uom left outer join lis_result_form_header on
lis_result_item.result_form_skey = lis_result_form_header.result_form_skey left outer join lis_lab_category on
lis_lab_order.order_skey = lis_lab_category.order_skey and
lis_category.category_skey = lis_lab_category.category_skey
where lis_lab_order.order_skey = %(order_skey)s and
lis_category.category_skey = %(category_skey)s and
display_manual_result = 1
order by lis_lab_test_result_item.seq
"""
labOrderDict = {
'paramsValue': {},
'name': 'lab_orders',
'table': 'lis_lab_order',
'key': 'order_skey',
'sql': labOrderSql,
'params': {
'order_id': orderID
}
}
labOrderCategoryDict = {
'paramsValue': {},
'name': 'lab_order_categorys',
'table': 'lab_order_category',
'key': 'order_skey,category_skey',
'sql': labOrderCategorySql,
'params': {
'order_skey': 'order_skey'
}
}
labOrderTestItemDict = {
'paramsValue': {},
'name': 'lis_lab_test_items',
'table': 'lis_lab_test_item',
'key': 'order_skey,test_item_skey',
'sql': labOrderTestItemSql,
'params': {
'order_skey': 'order_skey',
'category_skey': 'category_skey'
},
'selectedVDataTable': 'selected_lis_lab_test_items',
'searchVDataTable': 'search_lis_lab_test_items',
'paginationVDataTable': {
'name': 'pagination_lis_lab_test_items',
'sortBy': 'test_item_skey',
'rowsPerPage': 20
}
}
labOrderResultItemDict = {
'paramsValue': {},
'name': 'lis_lab_result_items',
'table': 'lis_lab_result_item',
'key': 'order_skey,result_item_skey',
'sql': labOrderResultItemSql,
'params': {
'order_skey': 'order_skey',
'category_skey': 'category_skey'
}
}
labOrderNode = Node(labOrderDict)
labOrderCategoryNode = Node(labOrderCategoryDict)
labOrderTestItemNode = Node(labOrderTestItemDict)
labOrderResultItemNode = Node(labOrderResultItemDict)
labOrderNode.add_child(labOrderCategoryNode)
labOrderCategoryNode.add_child(labOrderTestItemNode)
labOrderCategoryNode.add_child(labOrderResultItemNode)
datalabOrder, columnslabOrder = recur_tree(conn, 0, labOrderNode,
labOrderNode)
displayColumns = ['isSuccess', 'data']
displayData = [(isSuccess, toJson(datalabOrder, columnslabOrder))]
return jsonify(toJsonOne(displayData, displayColumns))
except Exception as e:
isSuccess = False
reasonCode = 500
errColumns = ['isSuccess', 'reasonCode', 'reasonText']
errData = [(isSuccess, reasonCode, str(e))]
return jsonify(toJsonOne(errData, errColumns))
Programming Language: Python :: 2.7
Topic: Utilities
"""
import pymssql
from collections import Iterable
server = 'rdsxxxx.sqlserver.rds.aliyuncs.com'
port = '3433'
database = 'database_name'
username = '******'
password = '******'
try:
_ = pymssql.connect(server=server,
port=port,
user=username,
password=password,
database=database,
login_timeout=10)
except pymssql.DatabaseError as e:
if isinstance(e.args, Iterable):
for arg in e.args:
print(arg)
else:
print(e.args)
except pymssql.InterfaceError as e:
if isinstance(e.args, Iterable):
for arg in e.args:
print(arg)
else:
print(e.args)
else:
from os import getenv
import pymssql
#server = getenv("PYMSSQL_TEST_SERVER")
#user = getenv("PYMSSQL_TEST_USERNAME")
#password = getenv("PYMSSQL_TEST_PASSWORD")
server = "localhost"
user = "******"
password = "******"
conn = pymssql.connect(server, user, password, "tempdb")
cursor = conn.cursor()
cursor.execute("""
IF OBJECT_ID('persons', 'U') IS NOT NULL
DROP TABLE persons
CREATE TABLE persons (
id INT NOT NULL,
name VARCHAR(100),
salesrep VARCHAR(100),
PRIMARY KEY(id)
)
""")
cursor.executemany("INSERT INTO persons VALUES (%d, %s, %s)",
[(1, 'John Smith', 'John Doe'), (2, 'Jane Doe', 'Joe Dog'),
(3, 'Mike T.', 'Sarah H.')])
# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()
cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
row = cursor.fetchone()
while row:
# Microsoft sql
import pymssql
conn = pymssql.connect(server=server, user=user, password=password, database=db)
cursor = conn.cursor()
cursor.execute("SELECT COUNT(MemberID) as count FROM Members WHERE id = 1")
row = cursor.fetchone()
conn.close()
print(row)
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
import seaborn as sns
sns.set_style("whitegrid", {
'axes.facecolor': 'white',
'axes.grid': True,
})
import pandas as pd
import os
import sys
from os import getenv
shot = 1160620011
tslice = 1293 #in milliseconds?
connect = pymssql.connect(host='ALCDB2',
user='******',
password='******',
database='edge')
QueryFields = 'shot,time_slice,twindow,thick_slice,tstart,tend,offset_ASP,key_words'
TableName = 'master'
condition = 'WHERE (shot = ' + str(shot) + ') AND (time_slice = ' + str(
tslice) + ')'
sql_query = 'SELECT'
cursor = connect.cursor()
output = cursor.execute(
'SELECT {} from master WHERE (shot = {}) AND (time_slice = {})'.format(
QueryFields, shot, tslice))
rows = cursor.fetchall()
connect.commit()
connect.close()
def labCancelApproveMSSQL():
try:
now = datetime.datetime.now()
isSuccess = True
reasonCode = 200
reasonText = ""
returnUserToken = getUserToken(request.headers.get('Authorization'))
if not returnUserToken["isSuccess"]:
return jsonify(returnUserToken)
userID = returnUserToken["data"]["user_id"]
dataInput = request.json
orderSkey = dataInput['order_skey']
testItem = dataInput['test_item']
reason = dataInput['reason']
testItemSkeyList = [
test_item['test_item_skey'] for test_item in testItem
]
testItemSkeyList = str(testItemSkeyList).strip('[]')
conn = pymssql.connect(mssql_server, mssql_user, mssql_password,
mssql_database)
cursor = conn.cursor()
sql = """\
EXEC sp_lis_lab_cancel_approve @order_skey = %(order_skey)s,@category_skey = %(category_skey)s,@test_item_skey_list = %(test_item_skey_list)s,@user = %(user)s,@reason = %(reason)s;
"""
params = {
'order_skey': orderSkey,
'category_skey': 1,
'test_item_skey_list': testItemSkeyList,
'user': userID,
'reason': reason
}
cursor.execute(sql, params)
conn.commit()
displayColumns = ['isSuccess', 'reasonCode', 'reasonText']
displayData = [(isSuccess, reasonCode, 'Cancel Approve เรียบร้อย')]
return jsonify(toJsonOne(displayData, displayColumns))
except pymssql.Error as e:
isSuccess = False
reasonCode = 500
errColumns = ['isSuccess', 'reasonCode', 'reasonText']
print(2222, e, type(e))
errData = [(isSuccess, reasonCode, pymssqlCustomExceptionMessage(e))]
return jsonify(toJsonOne(errData, errColumns))
except Exception as e:
isSuccess = False
reasonCode = 500
print(type(e))
errColumns = ['isSuccess', 'reasonCode', 'reasonText']
errData = [(isSuccess, reasonCode, str(e))]
return jsonify(toJsonOne(errData, errColumns))
def retrieveLabOrderManualResultMSSQL():
try:
now = datetime.datetime.now()
isSuccess = True
reasonCode = 200
reasonText = ""
returnUserToken = getUserToken(request.headers.get('Authorization'))
if not returnUserToken["isSuccess"]:
return jsonify(returnUserToken)
userID = returnUserToken["data"]["user_id"]
dataInput = request.json
paramList = [
'facility_cd', 'hn', 'from_received_date', 'to_received_date',
'category', 'category_status', 'patient_type_code',
'status_cancel', 'his_reject'
]
paramCheckStringList = []
msgError = checkParamDataInput(dataInput, paramList,
paramCheckStringList)
if msgError != None:
return jsonify(msgError)
facilityCode = dataInput['facility_cd']
hn = dataInput['hn']
fromReceivedDate = None
toReceivedDate = None
category = ''
if dataInput['from_received_date'] != None:
fromReceivedDate = datetime.datetime.fromisoformat(
dataInput['from_received_date'].replace("Z", ""))
if dataInput['to_received_date'] != None:
toReceivedDate = datetime.datetime.fromisoformat(
dataInput['to_received_date'].replace("Z", ""))
if dataInput['category'] != None:
category = ','.join(str(x) for x in dataInput['category'])
patientTypeCode = dataInput['patient_type_code']
categoryStatus = dataInput['category_status']
statusCancel = dataInput['status_cancel']
hisReject = dataInput['his_reject']
conn = pymssql.connect(mssql_server, mssql_user, mssql_password,
mssql_database)
cursor = conn.cursor()
sql = """\
select lis_lab_order.order_skey,lis_lab_order.order_id,lis_lab_order.received_date,lis_lab_order.hospital_lab_no,lis_lab_order.order_department,lis_logical_location.patient_type_code,
lis_lab_order.lab_receive_number,lis_patient.hn,patho_prefix.prefix_desc + '' + lis_patient.firstname + ' ' + lis_patient.lastname as patient_name,tmp_category_status.category_status,
patho_priority.priority_cd,patho_priority.priority_desc,patho_priority.color as bk_color,
(Select Case min(Case When category_result_status = 'Cancel' then 1 when category_result_status = 'Pending' then 2 else 3 end)
when 1 then 'Cancel' when 2 then 'Pending' else 'Approved' end from lis_lab_category where lis_lab_category.order_skey = lis_lab_order.order_skey
"""
if statusCancel:
if len(category) > 0:
sql = sql + """\
and lis_lab_category.category_skey in ({})
""".format(category)
sql = sql + """\
) as category_result_status,
(select case when ROW_NUMBER() OVER(ORDER BY lis_lab_specimen_type.specimen_type_id) = 1 then '' else ' , ' end +lis_lab_specimen_type.specimen_type_id
from lis_lab_specimen_type
where lis_lab_specimen_type.order_skey = lis_lab_order.order_skey
for xml path( '' )) as specimen_type_id_list,
(select count(1) from (select specimen_type_id,model_skey from lis_lab_specimen_type_test_item
where lis_lab_specimen_type_test_item.order_skey = lis_lab_order.order_skey and date_scanned is not null and analyzer_two_way = 1 group by specimen_type_id,model_skey) tmp) as scanned_tube,
(select count(1) from (select specimen_type_id,model_skey from lis_lab_specimen_type_test_item
where lis_lab_specimen_type_test_item.order_skey = lis_lab_order.order_skey and analyzer_two_way = 1 group by specimen_type_id,model_skey) tmp) as total_tube
from lis_lab_order inner join lis_visit on
lis_lab_order.visit_skey = lis_visit.visit_skey inner join lis_patient on
lis_visit.patient_skey = lis_patient.patient_skey inner join patho_sex on
lis_patient.sex = patho_sex.sex_cd inner join patho_prefix on
lis_patient.prefix = patho_prefix.prefix_cd inner join lis_logical_location on
lis_lab_order.logical_location_cd = lis_logical_location.code inner join lis_patient_type on
lis_logical_location.patient_type_code = lis_patient_type.code inner join patho_priority on
lis_lab_order.priority_cd = patho_priority.priority_cd inner join lis_lab_order_item on
lis_lab_order.order_skey = lis_lab_order_item.order_skey inner join lis_lab_order_test_item on
lis_lab_order_item.order_skey = lis_lab_order_test_item.order_skey and
lis_lab_order_item.order_item_skey = lis_lab_order_test_item.order_item_skey inner join lis_lab_test_item on
lis_lab_order.order_skey = lis_lab_test_item.order_skey and
lis_lab_order_test_item.test_item_skey = lis_lab_test_item.test_item_skey inner join lis_sub_category on
lis_lab_test_item.sub_category_skey = lis_sub_category.sub_category_skey inner join lis_category on
lis_sub_category.category_skey = lis_category.category_skey inner join lis_test_item on
lis_lab_test_item.test_item_skey = lis_test_item.test_item_skey
"""
if statusCancel:
sql = sql + """\
left outer join
"""
else:
sql = sql + """\
inner join
"""
sql = sql + """\
(select order_skey,min(category_status) as category_status from lis_lab_category where (1 = 1)
"""
if len(categoryStatus) > 0:
sql = sql + """\
and lis_lab_category.category_result_status = '{}'
""".format(categoryStatus)
if len(category) > 0:
sql = sql + """\
and lis_lab_category.category_skey in ({})
""".format(category)
sql = sql + """\
group by order_skey) tmp_category_status on lis_lab_order.order_skey = tmp_category_status.order_skey
where lis_lab_order_item.active = 1 and
lis_lab_order_item.cancel = 0 and lis_lab_test_item.status <> 'CA' and
"""
if statusCancel:
sql = sql + """\
lis_lab_order.status = 'CA' and
"""
else:
sql = sql + """\
lis_lab_order.status <> 'CA' and
lis_lab_order.lab_status in ('COLLECTION','RECEIVED','FIRSTAPPROVE','LASTAPPROVE','RL') and
"""
if hisReject:
sql = sql + """\
lis_lab_order.hos_reject = 1 and
"""
if getGlobalParamMSSQL(
'LIS_DISPLAY_LAB_REJECT_MANUAL_RESULT').upper() == 'N':
sql = sql + """\
(lis_lab_order.hos_reject = 0 or lis_lab_order.hos_reject is null) and
"""
sql = sql + """\
(lis_visit.facility_cd = %(facility_cd)s or 1 = case when %(facility_cd)s is null or len(%(facility_cd)s) < 1 then 1 else 2 end) and
(lis_patient.hn like %(hn)s or 1 = case when %(hn)s is null or len(%(hn)s) < 1 then 1 else 2 end) and
(dateadd(dd,0,datediff(dd,0,lis_lab_order.received_date)) >= dateadd(dd,0,datediff(dd,0,%(from_received_date)s)) or 1 = case when %(from_received_date)s is null then 1 else 2 end) and
(dateadd(dd,0,datediff(dd,0,lis_lab_order.received_date)) <= dateadd(dd,0,datediff(dd,0,%(to_received_date)s)) or 1 = case when %(to_received_date)s is null then 1 else 2 end) and
(lis_logical_location.patient_type_code = %(patient_type_code)s or 1 = case when %(patient_type_code)s is null or len(%(patient_type_code)s) < 1 then 1 else 2 end)
"""
if not statusCancel:
if len(category) > 0:
sql = sql + """\
and lis_category.category_skey in ({})
""".format(category)
if len(category.split(',')) > 1:
if len(categoryStatus) > 0:
sql = sql + """\
and lis_lab_order.lab_result_status like '{}'
""".format(categoryStatus)
else:
if len(categoryStatus) > 0:
sql = sql + """\
and lis_lab_order.lab_result_status like '{}'
""".format(categoryStatus)
sql = sql + """\
group by lis_lab_order.order_skey,lis_lab_order.order_id,lis_lab_order.received_date,lis_lab_order.hospital_lab_no,lis_lab_order.order_department,lis_logical_location.patient_type_code,
lis_lab_order.lab_test_item_status,lis_lab_order.lab_receive_number,lis_patient.hn,patho_prefix.prefix_desc,patho_sex.sex_desc,
patho_prefix.prefix_desc,lis_patient.firstname,lis_patient.lastname,tmp_category_status.category_status,
patho_priority.priority_cd,patho_priority.priority_desc,patho_priority.color
order by case when patho_priority.priority_cd = 'Urgent' then case tmp_category_status.category_status when 'C1' then 3 else 1 end else
case tmp_category_status.category_status when 'C1' then 4 else 2 end end,
case tmp_category_status.category_status when 'C3' then 1
when 'C2' then 2
when 'C1' then 3 end,lis_lab_order.order_id
"""
params = {
'facility_cd': facilityCode,
'hn': hn,
'from_received_date': fromReceivedDate,
'to_received_date': toReceivedDate,
'patient_type_code': patientTypeCode
}
cursor.execute(sql, params)
data = cursor.fetchall()
columns = [column[0] for column in cursor.description]
conn.commit()
cursor.close()
displayColumns = ['isSuccess', 'data']
displayData = [(isSuccess, toJson(data, columns))]
return jsonify(toJsonOne(displayData, displayColumns))
except Exception as e:
isSuccess = False
reasonCode = 500
errColumns = ['isSuccess', 'reasonCode', 'reasonText']
errData = [(isSuccess, reasonCode, str(e))]
return jsonify(toJsonOne(errData, errColumns))
fname_end = headers['Content-Disposition'].find('.', fname_start)
fname = headers['Content-Disposition'][(fname_start + 1):fname_end]
data = response.read()
print 'Now downloading... ' + fname
# write the file with today's date stamp
DEST_FILE = save_path + 'NAVIANCE_' + fname + '.csv'
# for keeping a running record of files + '_' + datetime.now().strftime('%Y%m%d')
with open(DEST_FILE, 'wb') as csv_file:
csv_file.write(data)
print
"""
LOAD INTO DB
"""
print 'Connecting to SQL Server to load csv files into database...'
conn = pymssql.connect(server_name, db_user, db_pass, db_name)
cursor = conn.cursor()
query = "sp_LoadFolder '" + db_name + "', '" + save_path + "'"
print 'Running "EXEC ' + query + '"'
try:
cursor.execute(query)
print 'NARDO say: ' + cursor.fetchall()[0][0]
conn.commit()
conn.close()
except:
print '!!! ERROR LOADING FOLDER !!!'
warn_email = """
DECLARE @body VARCHAR(MAX);
SET @body = 'The database load failed for ' + '""" + save_path + """' + '. Check that the GDocs source still matches the destination table and reset if necessary.';
def med_api(self, result_sentence, data_sentence):
comprehend = boto3.client(service_name='comprehend',
region_name='us-west-2')
resultEid = 0 # 普通实体
resultKid = 0 # 关键词
resultSid = 0 # 语法
resultMId = 0 # 医疗实体
for i in range(len(result_sentence)):
text = data_sentence['sentence'][i]
ArticleId = data_sentence['ArticleId'][i]
begin = data_sentence['begin'][i]
print(begin)
end = data_sentence['end'][i]
#识别实体
result = comprehend.detect_entities(Text=text, LanguageCode='en')
Entities = result['Entities']
for entity in Entities:
try:
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
with conn.cursor(as_dict=True) as cursor:
sql1 = "insert into AWSEntities ([ArticalId],[resultId],[BeginOffset],[EndOffset],[Score],[Text],[Type])values(%d,%d,%d,%d,%f,'%s','%s')"
cursor.execute(
sql1 %
(int(ArticleId), resultEid,
entity['BeginOffset'] + begin,
entity['EndOffset'] + begin, entity['Score'],
entity['Text'].replace("'", "''"),
entity['Type'].replace("'", "''")))
resultEid = resultEid + 1
except Exception as ex:
print('普通实体识别出错： ' + str(ex))
# #识别关键字
result = comprehend.detect_key_phrases(Text=text,
LanguageCode='en')
entities = result["KeyPhrases"]
for entity in entities:
try:
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
with conn.cursor(as_dict=True) as cursor:
sql1 = "insert into AWSKeyPhrase ([ArticalId],[resultId],[BeginOffset],[EndOffset],[Score],[Text])values(%d,%d,%d,%d,%f,'%s')"
cursor.execute(
sql1 %
(int(ArticleId), resultKid,
entity['BeginOffset'] + begin,
entity['EndOffset'] + begin, entity['Score'],
entity['Text'].replace("'", "''")))
resultKid = resultKid + 1
except Exception as ex:
print('关键词识别出错： ' + str(ex))
#识别语法
result = comprehend.detect_syntax(Text=text, LanguageCode='en')
entities = result['SyntaxTokens']
for entity in entities:
try:
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
with conn.cursor(as_dict=True) as cursor:
sql1 = "insert into AWSSyntax ([ArticalId],[resultId],[BeginOffset],[EndOffset],[Score],[Tag],[Text],[TokenId])values(%d,%d,%d,%d,%f,'%s','%s',%d)"
cursor.execute(sql1 %
(int(ArticleId), resultSid,
entity['BeginOffset'] + begin,
entity['EndOffset'] + begin,
entity['PartOfSpeech']['Score'],
entity['PartOfSpeech']['Tag'],
entity['Text'].replace(
"'", "''"), entity['TokenId']))
resultSid = resultSid + 1
except Exception as ex:
print('语法识别出错： ' + str(ex))
#识别医药实体
client = boto3.client(service_name='comprehendmedical',
region_name='us-west-2')
result = client.detect_entities(Text=text)
entities = result['Entities']
for entity in entities:
try:
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
with conn.cursor(as_dict=True) as cursor:
sql1 = "insert into AWSmedical ([ArticalId],[resultId],[BeginOffset],[EndOffset],[Score],[Text],[Category],[Type],[Traits])values(%d,%d,%d,%d,%f,'%s','%s','%s','%s')"
cursor.execute(
sql1 %
(int(ArticleId), resultMId,
entity['BeginOffset'] + begin,
entity['EndOffset'] + begin, entity['Score'],
entity['Text'].replace("'", "''"),
entity['Category'].replace("'", "''"),
entity['Type'].replace("'", "''"),
str(entity['Traits']).replace("'", "''")))
resultMId = resultMId + 1
if 'Attributes' in entity:
for each in entity['Attributes']:
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
with conn.cursor(as_dict=True) as cursor:
sql1 = "insert into AWSmedical ([ArticalId],[resultId],[BeginOffset],[EndOffset],[Score],[Text],[Category],[Type],[Traits],[AttributesOrNot])values(%d,%d,%d,%d,%f,'%s','%s','%s','%s',1)"
cursor.execute(
sql1 %
(int(ArticleId), resultMId,
each['BeginOffset'] + begin,
each['EndOffset'] + begin,
each['Score'], each['Text'].replace(
"'", "''"),
entity['Category'].replace("'", "''"),
each['Type'].replace("'", "''"),
str(each['Traits']).replace(
"'", "''")))
resultMId = resultMId + 1
except Exception as ex:
print('医疗实体识别出错： ' + str(ex))
print('*' * 50)
def retrieveDropDown():
try:
now = datetime.datetime.now()
isSuccess = True
reasonCode = 200
reasonText = ""
returnUserToken = getUserToken(request.headers.get('Authorization'))
if not returnUserToken["isSuccess"]:
return jsonify(returnUserToken)
userID = returnUserToken["data"]["user_id"]
conn = pymssql.connect(mssql_server, mssql_user, mssql_password,
mssql_database)
cursor = conn.cursor()
dropdownSql = """\
select 1 as code
"""
CatogorySql = """\
select category_skey,category_id,category_desc from lis_category
"""
dropdownDict = {
'paramsValue': {},
'name': 'dropdown',
'table': 'dropdown',
'key': 'code',
'sql': dropdownSql,
'params': {}
}
CatogoryDict = {
'paramsValue': {},
'name': 'lis_category',
'table': 'lis_category',
'key': 'category_skey',
'sql': CatogorySql,
'params': {}
}
dropdownNode = Node(dropdownDict)
CatogoryNode = Node(CatogoryDict)
dropdownNode.add_child(CatogoryNode)
dataDropDown, columnsDropDown = recur_tree(conn, 0, dropdownNode,
dropdownNode)
displayColumns = ['isSuccess', 'data']
displayData = [(isSuccess, toJson(dataDropDown, columnsDropDown))]
return jsonify(toJsonOne(displayData, displayColumns))
except Exception as e:
isSuccess = False
reasonCode = 500
errColumns = ['isSuccess', 'reasonCode', 'reasonText']
errData = [(isSuccess, reasonCode, str(e))]
return jsonify(toJsonOne(errData, errColumns))
"/rest/api/2/search?jql=project%20%3D%20%22" + config["jira_fields"]["project"]["key"] + \
"%22&fields=customfield_10411&maxResults=1000"
logging.basicConfig(
filename=config["log_file"],
level=logging.DEBUG,
format=
'%(asctime)s - %(levelname)s - [%(filename)s:%(lineno)d] - %(message)s'
)
while True:
try:
db_connection = pymssql.connect(
host=config["database_cnf"]["host"],
user=config["database_cnf"]["user"],
password=config["database_cnf"]["password"],
database=config["database_cnf"]["database"])
mainloop(
trackit_api_username=config["trackit_api_username"],
jira_rest_call_post=jira_rest_call_post,
jira_rest_call_get_trackit_id=jira_rest_call_get_trackit_id,
jira_authorization=config["jira_authorization"],
db_connection=db_connection,
jira_key=config["jira_fields"]["project"]["key"],
track_it_full_hostname=config["trackIT_server_address"],
jira_server_address=config["jira_server_address"],
sql=config["sql"],
attachment_folder=config["attachment_folder"],
duedate_map=config["ticket_duetime_mapping_days"])
def selectMentity(self, ArticalId, data_sentence):
sql_cmd = "select * from [AWSEntities] where ArticalId = %d" % ArticalId
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
dfAE = pd.read_sql(sql=sql_cmd, con=conn)
sql_cmd = "select * from [AWSKeyPhrase] where ArticalId = %d" % ArticalId
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
dfASP = pd.read_sql(sql=sql_cmd, con=conn)
sql_cmd = "select * from [AWSSyntax] where ArticalId = %d" % ArticalId
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
dfAS = pd.read_sql(sql=sql_cmd, con=conn)
sql_cmd = "select * from [AWSmedical] where ArticalId = %d" % ArticalId
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
dfAM = pd.read_sql(sql=sql_cmd, con=conn)
dfindex = []
sql_cmd = "select * from PatientStory where Id = %d" % ArticalId
with pymssql.connect('.',
'sa',
'19950804',
'MeadHeadLine',
autocommit=True) as conn:
dfindex = pd.read_sql(sql=sql_cmd, con=conn)
#循环读出句子的位置，将此句子中未在医药实体中识别出的实体匹配出并入库
for i in range(len(data_sentence)):
be = data_sentence['begin'][i]
end = data_sentence['end'][i]
#将在此范围内的普通实体取出
dfAEsub = dfAE[(dfAE['BeginOffset'] >= be)
& (dfAE['EndOffset'] <= end) &
((dfAE['Type'] == 'QUANTITY') |
(dfAE['Type'] == 'DATE'))]
dfASPsub = dfASP[(dfASP['BeginOffset'] >= be)
& (dfASP['EndOffset'] <= end)]
dfASsub = dfAS[(dfAS['BeginOffset'] >= be)
& (dfAS['EndOffset'] <= end)]
dfAMsub = dfAM[(dfAM['BeginOffset'] >= be)
& (dfAM['EndOffset'] <= end)]
#正则匹配关键词
regkw = '[a-zA-Z0-9]+[\s\S]*[a-zA-Z0-9]+'
for l in range(len(dfAEsub)):
inAE = 0
KeyWords = dfAEsub.iloc[l]['Text'].strip()
for k in range(len(dfAMsub)):
try:
MedEntity = dfAMsub.iloc[k]['Text'].strip()
QYword = KeyWords.replace(MedEntity, "").strip()
if MedEntity in KeyWords and len(QYword) > 1:
inAE = 1
resultkw = re.findall(regkw, QYword)
if resultkw != []:
QYword = resultkw[0].replace(
'and',
'').replace('or', '').replace(' ',
' ').strip()
if len(QYword) > 1:
# 先看看这个分离出来的词是否属于这个句子已识别出来的实体
if QYword not in dfAMsub['Text'].values:
with pymssql.connect(
'.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as msscon:
with msscon.cursor(
as_dict=True) as cursor:
#查看QYmedicalKeyWord表中是否已存在此关键词
cursor.execute(
"select top 1 Id from [dbo].[QYmedicalKeyWord] where Text = '%s' "
% QYword)
result = cursor.fetchone()
if result is None:
effect_row = cursor.execute(
"insert into [dbo].[QYmedicalKeyWord](Text,KeyWords,MedEntity,Category,Type) values ('%s','%s','%s','%s','%s')"
% (QYword, KeyWords,
MedEntity, dfAMsub.
iloc[k]['Category'],
dfAMsub.iloc[k]
['Type']))
pass
except Exception as ex:
print('启元关键词错误 ' + str(ex))
if inAE == 0: # 如果没有实体在这个关键词中，直接放入候选表
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as msscon:
with msscon.cursor(as_dict=True) as cursor:
if KeyWords not in dfAMsub['Text'].values:
cursor.execute(
"select top 1 Id from [dbo].[QYmedicalKeyWord] where Text = '%s' "
% KeyWords.replace("'", "''"))
result = cursor.fetchone()
if result is None:
cursor.execute(
"select top 1 * from [dbo].[AWSmedical] where Text = '%s' "
% KeyWords.replace("'", "''"))
result = cursor.fetchone()
if result is None:
effect_row = cursor.execute(
"insert into [dbo].[QYmedicalKeyWord](Text,KeyWords,Category) values ('%s','%s','%s')"
% (KeyWords.replace("'", "''"),
KeyWords.replace("'", "''"),
dfAEsub.iloc[l]['Type'].replace(
"'", "''")))
else:
effect_row = cursor.execute(
"insert into [dbo].[QYmedicalKeyWord](Text,Category,Type) values ('%s','%s','%s')"
% (KeyWords.replace("'", "''"),
result['Category'].replace(
"'", "''"),
result['Type'].replace(
"'", "''")))
for l in range(len(dfASPsub)):
KeyWords = dfASPsub.iloc[l]['Text'].strip()
for k in range(len(dfAMsub)):
try:
MedEntity = dfAMsub.iloc[k]['Text'].strip()
QYword = KeyWords.replace(MedEntity, "").strip()
if MedEntity in KeyWords and len(QYword) > 1:
resultkw = re.findall(regkw, QYword)
if resultkw != []:
QYword = resultkw[0].replace(
' and', '').replace(' or', '').replace(
'and ', '').replace('or ', '').strip()
if len(QYword) > 1:
# 先看看这个分离出来的词是否属于这个句子已识别出来的实体
if QYword not in dfAMsub['Text'].values:
with pymssql.connect(
'.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as msscon:
with msscon.cursor(
as_dict=True) as cursor:
cursor.execute(
"select Id from [dbo].[QYmedicalKeyWord] where Text = '%s' "
% QYword)
result = cursor.fetchone()
if result is None:
effect_row = cursor.execute(
"insert into [dbo].[QYmedicalKeyWord](Text,KeyWords,MedEntity) values ('%s','%s','%s')"
% (QYword, KeyWords,
MedEntity))
pass
except Exception as ex:
print('启元关键词错误 ' + str(ex))
import pymssql
import csv
import datetime
conn = pymssql.connect(host='192.168.3.40', user='******', password='******', database='ESIDB')
try:
cur = conn.cursor()
mydate='10-03-2016'
print(max)
s='SELECT [RECNUM],[PROGRAM_ID],[TABLE_NAME],[OPERATOR_ID] ,[FIRST_NAME],[LAST_NAME],[INITIAL],[MACHINE] ,[CHANGE_TYPE],[KEY1],[KEY2] ,[KEY3],[KEY4],[KEY5],[KEY6],[MAINT_LOG],[TIME_LAST_UPDT],[DATE_LAST_UPDT],[DELETE_FLAG] FROM [ALFTD] where DATE_LAST_UPDT<=(SELECT MIN(DATE_LAST_UPDT+3)FROM [ESIDB].[dbo].[ALFTD] where DATE_LAST_UPDT< getdate()-15) order by DATE_LAST_UPDT'
print(s)
cur.execute('SELECT [RECNUM],[PROGRAM_ID],[TABLE_NAME],[OPERATOR_ID] ,[FIRST_NAME],'
'[LAST_NAME],[INITIAL],[MACHINE] ,[CHANGE_TYPE],[KEY1],[KEY2] ,[KEY3],[KEY4],[KEY5],'
'[KEY6],[MAINT_LOG],[TIME_LAST_UPDT],[DATE_LAST_UPDT],[DELETE_FLAG] FROM [ALFTD] where DATE_LAST_UPDT<=(SELECT MIN(DATE_LAST_UPDT+3)FROM [ESIDB].[dbo].[ALFTD] where DATE_LAST_UPDT< getdate()-15) order by DATE_LAST_UPDT')
# where [DATE_LAST_UPDT] >= '+ repr(mydate))
rows = cur.fetchall()
len1=len(rows)
mindate=str(rows[0][17])[:10]
print(mindate)
maxdate=str(rows[len1-1][17])[:10]
print(maxdate)
file_path='C:\Python34\ixi_TBOX34\\'+mindate+'-'+maxdate+'-explog.csv'
with open(file_path, 'a') as outcsv:
#configure writer to write standard csv file
writer = csv.writer(outcsv, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL, lineterminator='\n')
writer.writerow(['RECNUM', 'PROGRAM_ID', 'TABLE_NAME', 'OPERATOR_ID', 'FIRST_NAME', 'LAST_NAME', 'INITIAL', 'MACHINE','CHANGE_TYPE', 'KEY1', 'KEY2', 'KEY3', 'KEY4', 'KEY5', 'KEY6', 'MAINT_LOG', 'TIME_LAST_UPDT', 'DATE_LAST_UPDT', 'DELETE_FLAG'])
for row in rows:
print(row[15])
writer.writerow([row[0], row[1], row[2], row[3], row[4], row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],repr(str(row[15]).replace(',','')),row[16],row[17],row[18]])
def splitandorder(self, data_sentence, ArticleId):
#普通实体数据
sql_cmd = "select * from [AWSEntities] where ArticalId = %d" % ArticleId
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
dfAE = pd.read_sql(sql=sql_cmd, con=conn)
#关键字数据
sql_cmd = "select * from [AWSKeyPhrase] where ArticalId = %d" % ArticleId
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
dfASP = pd.read_sql(sql=sql_cmd, con=conn)
#语法数据
sql_cmd = "select * from [AWSSyntax] where ArticalId = %d" % ArticleId
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
dfAS = pd.read_sql(sql=sql_cmd, con=conn)
#医药实体数据
sql_cmd = "select * from [AWSmedical] where ArticalId = %d" % ArticleId
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as conn:
dfAM = pd.read_sql(sql=sql_cmd, con=conn)
# 按句子循环
# 根据位置筛选所有在该句子里面的实体和关键词
# 从普通实体表中分析是否有时间,date类型或QUANTITY类型，days,months,years结尾
# 先处理医疗实体信息，判断类型，同时搜索 关键词中的包含该医疗实体的词，替换为空剩下的词可能为aws不能识别的词，记下。
# ANATOMY,MEDICAL_CONDITION,MEDICATION,PROTECTED_HEALTH_INFORMATION,TEST_TREATMENT_PROCEDURE
EntityTime = ""
# 后续关键词应该是字母或数字开始和结束
regkw = '[a-zA-Z0-9]+[\s\S]*[a-zA-Z0-9]+'
# 个人信息表
PHIinfo = pd.DataFrame(columns=[
"PID", "NAME", "Gender", "AGE", "ADDRESS", "PROFESSION", "EMAIL",
"PHONE_OR_FAX", "DATE", "URL"
])
# 治疗信息表
TreatStructTable = pd.DataFrame(columns=[
"TreatMethod", "GENERIC_NAME", "BRAND_NAME", "MEDICAL_STATUS",
"MEDICAL_FORM", "ROUTE_OR_MODE", "MEDICAL_CYCLE", "MEDICAL_DOSE",
"MEDICAL_FREQ", "MEDICAL_EFFECT", "MEDICAL_EFFECTTIME",
"MEDICAL_SIDEEFFECT", "MEDICAL_ACCEPT", "MEDICAL_STOPREASON",
"BeginTime", "EndTime", "Location", "MEDICAL_STRENGTH",
"EntityTime"
])
# 诊断信息表
DiagnoseStructTable = pd.DataFrame(columns=[
"CancerCate", "CancerType", "CancerStage", "FamilyHis",
"DiagnosisLocation", "DiagnosisDoctor", "EntityTime"
])
# 检查信息表
CheckStructTable = pd.DataFrame(columns=[
"TEST_TIME", "TEST_NAME", "TEST_VALUE", "TEST_UNIT",
"SYSTEM_ORGAN_SITE", "EntityTime"
])
PHIlist = [] # 患者信息
dict = {
"PID": "",
"NAME": "",
"Gender": "",
"AGE": "",
"ADDRESS": "",
"PROFESSION": "",
"EMAIL": "",
"PHONE_OR_FAX": "",
"DATE": "",
"URL": "",
"HEIGHT": "",
"WEIGHT": "",
"Marital": "",
"Fertility": ""
}
PHIlist.append(dict)
PHIdict = {
"PID": "档案编号",
"NAME": "姓名",
"Gender": "性别",
"AGE": "年龄",
"ADDRESS": "地址",
"PROFESSION": "职业",
"EMAIL": "电子邮箱",
"PHONE_OR_FAX": "电话传真",
"DATE": "日期",
"URL": "网址",
"HEIGHT": "身高",
"WEIGHT": "体重",
"Marital": "婚姻状况",
"Fertility": "婚育状况"
}
TSTlist = [] # 治疗
dict = {
"TreatMethod": "",
"GENERIC_NAME": "",
"BRAND_NAME": "",
"MEDICAL_STATUS": "",
"MEDICAL_FORM": "",
"ROUTE_OR_MODE": "",
"MEDICAL_CYCLE": "",
"MEDICAL_DOSE": "",
"MEDICAL_FREQ": "",
"MEDICAL_EFFECT": "",
"MEDICAL_EFFECTTIME": "",
"MEDICAL_SIDEEFFECT": "",
"MEDICAL_ACCEPT": "",
"MEDICAL_STOPREASON": "",
"BeginTime": "",
"EndTime": "",
"Location": "",
"MEDICAL_STRENGTH": "",
"EntityTime": ""
}
TSTlist.append(dict)
TSTdict = {
"TreatMethod": "治疗方式",
"GENERIC_NAME": "药名",
"BRAND_NAME": "商品名",
"MEDICAL_STATUS": "治疗状态",
"MEDICAL_FORM": "治疗形式",
"ROUTE_OR_MODE": "服用方式",
"MEDICAL_CYCLE": "",
"MEDICAL_DOSE": "剂量",
"MEDICAL_FREQ": "频率",
"MEDICAL_EFFECT": "治疗效果",
"MEDICAL_EFFECTTIME": "有效时长",
"MEDICAL_SIDEEFFECT": "副作用",
"MEDICAL_ACCEPT": "接受程度",
"MEDICAL_STOPREASON": "停药原因",
"BeginTime": "开始时间",
"EndTime": "结束时间",
"Location": "治疗地点",
"MEDICAL_STRENGTH": "药物强度",
"EntityTime": "实体时间"
}
DSTlist = [] # 诊断
dict = {
"CancerCate": "",
"CancerType": "",
"CancerStage": "",
"FamilyHis": "",
"DiagnosisLocation": "",
"DiagnosisDoctor": "",
"SIGN": "",
"EntityTime": ''
}
DSTlist.append(dict)
DSTdict = {
"CancerCate": "肿瘤类别",
"CancerType": "分型",
"CancerStage": "分期",
"FamilyHis": "家族史",
"DiagnosisLocation": "诊断地点",
"DiagnosisDoctor": "诊断医生",
"SIGN": "症状",
"EntityTime": "实体时间"
}
CSTlist = [] # 检查
dict = {
"TEST_TIME": "",
"TEST_NAME": "",
"TEST_VALUE": "",
"TEST_UNIT": "",
"SYSTEM_ORGAN_SITE": "",
"EntityTime": '',
"TEST_DIRECTION": "",
"TEST_SIZE": "",
"TEST_CHARACTER": "",
"TEST_DEGREE": "",
"TEST_PHYSICAL": ""
}
CSTlist.append(dict)
dict = {
"TEST_TIME": "检查时间",
"TEST_NAME": "检查项目",
"TEST_VALUE": "检查值",
"TEST_UNIT": "检查单位",
"SYSTEM_ORGAN_SITE": "身体器官",
"EntityTime": "实体时间",
"TEST_DIRECTION": "方位",
"TEST_SIZE": "大小",
"TEST_CHARACTER": "性状",
"TEST_DEGREE": "程度",
"TEST_PHYSICAL": "查体"
}
#循环读出句子表中的句子的开始和结束位置
for j in range(len(data_sentence)):
begin = data_sentence["begin"][j]
end = data_sentence["end"][j]
#将数据库读出的实体与该句子位置匹配
#普通实体
dfAEsub = dfAE[(dfAE['BeginOffset'] >= begin)
& (dfAE['EndOffset'] <= end) &
((dfAE['Type'] == 'QUANTITY') |
(dfAE['Type'] == 'DATE'))]
#关键字
dfASPsub = dfASP[(dfASP['BeginOffset'] >= begin)
& (dfASP['EndOffset'] <= end)]
#语法
dfASsub = dfAS[(dfAS['BeginOffset'] >= begin)
& (dfAS['EndOffset'] <= end)]
#医药实体
dfAMsub = dfAM[(dfAM['BeginOffset'] >= begin)
& (dfAM['EndOffset'] <= end)]
# #从实体中获取时间
for k in range(len(dfAEsub)):
entitytext = str.lower(dfAEsub.iloc[k]['Text'])
if dfAEsub.iloc[k]['Type'] == 'DATE' or entitytext.endswith(
'days') or entitytext.endswith(
'months') or entitytext.endswith('years'):
EntityTime = dfAEsub.iloc[k]['Text']
# 亚马逊识别的医学信息
for k in range(len(dfAMsub)):
try:
if dfAMsub.iloc[k][
'Category'] == 'ANATOMY' and dfAMsub.iloc[k][
'Type'] == 'SYSTEM_ORGAN_SITE':
CSTlist = self.listdeal(CSTlist, "SYSTEM_ORGAN_SITE",
dfAMsub.iloc[k]['Text'],
EntityTime)
elif dfAMsub.iloc[k]['Category'] == 'MEDICAL_CONDITION':
if dfAMsub.iloc[k]['Traits'] != []:
if 'DIAGNOSIS' in dfAMsub.iloc[k]['Traits']:
DSTlist = self.listdeal(
DSTlist, "CancerCate",
dfAMsub.iloc[k]['Text'], EntityTime)
if 'SYMPTOM' in dfAMsub.iloc[k]['Traits']:
TSTlist = self.listdeal(
TSTlist, "MEDICAL_SIDEEFFECT",
dfAMsub.iloc[k]['Text'], EntityTime)
if 'SIGN' in dfAMsub.iloc[k]['Traits']:
DSTlist = self.listdeal(
DSTlist, "SIGN", dfAMsub.iloc[k]['Text'],
EntityTime)
elif dfAMsub.iloc[k]['Category'] == 'MEDICATION':
# 要处理一下药物，有些化疗药物跟手术治疗放到一下，所以当是MEDICATION的时候，应该搜索最近的TREATMENT_NAME和PROCEDURE_NAME，如果与当前的不一致，则新建一条记录
#treatText为判断出的治疗方案类型
treatText = self.nearlyText(
dfAMsub, dfAMsub.iloc[k]['BeginOffset'],
dfAMsub.iloc[k]['EndOffset'],
['TEST_TREATMENT_PROCEDURE'],
['PROCEDURE_NAME', 'TREATMENT_NAME'])
# 靠距离判断还是会有误差，最好改为判断药物的类型，是化疗药还是靶向药等
TSTlist = self.listdeal(TSTlist,
dfAMsub.iloc[k]['Type'],
dfAMsub.iloc[k]['Text'],
EntityTime, 'TreatMethod',
treatText)
elif dfAMsub.iloc[k][
'Category'] == 'PROTECTED_HEALTH_INFORMATION':
# 注意DATE是否单独处理
if dfAMsub.iloc[k]['Type'] == 'ID':
PHIlist = self.listdeal(PHIlist, "PID",
dfAMsub.iloc[k]['Text'],
EntityTime)
elif dfAMsub.iloc[k]['Type'] == 'NAME' and PHIlist[len(PHIlist) - 1]['NAME'] == \
dfAMsub.iloc[k]['Text']:
pass
else:
PHIlist = self.listdeal(PHIlist,
dfAMsub.iloc[k]['Type'],
dfAMsub.iloc[k]['Text'],
EntityTime)
elif dfAMsub.iloc[k][
'Category'] == 'TEST_TREATMENT_PROCEDURE':
if dfAMsub.iloc[k]['Type'] == 'PROCEDURE_NAME':
TSTlist = self.listdeal(TSTlist, "TreatMethod",
dfAMsub.iloc[k]['Text'],
EntityTime)
elif dfAMsub.iloc[k]['Type'] == 'TREATMENT_NAME':
TSTlist = self.listdeal(TSTlist, "TreatMethod",
dfAMsub.iloc[k]['Text'],
EntityTime)
else:
CSTlist = self.listdeal(CSTlist,
dfAMsub.iloc[k]['Type'],
dfAMsub.iloc[k]['Text'],
EntityTime)
except Exception as ex:
print('实体 ' + str(ex))
# 关键词识别自主的医学实体
for l in range(len(dfASPsub)):
#遍历关键字表
KeyWords = str.lower(dfASPsub.iloc[l]['Text'].strip())
try:
resultkw = re.findall(regkw, KeyWords)
if resultkw != []:
# QYword = resultkw[0].replace('and', '').replace('or', '').replace(' ', ' ').strip()
#对匹配的字符去掉停止词
resultkw_res = resultkw[0].strip().split(' ')
for i in range(len(resultkw_res) - 1, -1, -1):
print(resultkw_res[i])
if resultkw_res[i] == 'or' or resultkw_res[
i] == 'and':
resultkw_res.remove(resultkw_res[i])
if resultkw_res[i] == '':
resultkw_res.remove(resultkw_res[i])
QYword = ' '.join(resultkw_res)
with pymssql.connect('.',
'sa',
'19950804',
'lijinhui',
autocommit=True) as msscon:
with msscon.cursor(as_dict=True) as cursor:
cursor.execute(
"select top 1 * from [dbo].[QYMedKeyWord] where CHARINDEX(lower([Text]),'%s' ) <> 0 order by len(Text) desc "
% QYword)
result = cursor.fetchone()
# 需要保证找到的是一个单词，而不是几个字母片段
subwords = QYword.split(' ')
if (result is not None) and (
(' ' in result['Text']
and str.lower(result['Text']) in QYword)
or (result['Text'] in subwords)):
if result[
'Category'] == 'CheckStructTable':
CSTlist = self.listdeal(
CSTlist, result['Type'],
result['Text'], EntityTime)
elif result[
'Category'] == 'DiagnoseStructTable':
DSTlist = self.listdeal(
DSTlist, result['Type'],
result['Text'], EntityTime)
elif result[
'Category'] == 'TreatStructTable':
if result['Type'] == "MEDICAL_EFFECT":
# 要处理治疗效果，应该搜索最近的TREATMENT_NAME和PROCEDURE_NAME，如果与当前的不一致，则新建一条记录
treatText = self.nearlyText(
dfAMsub, dfASPsub.iloc[l]
['BeginOffset'],
dfASPsub.iloc[l]['EndOffset'],
['TEST_TREATMENT_PROCEDURE'], [
'PROCEDURE_NAME',
'TREATMENT_NAME'
])
TSTlist = self.listdeal(
TSTlist, result['Type'],
result['Text'], EntityTime,
'TreatMethod', treatText, 1)
else:
TSTlist = self.listdeal(
TSTlist, result['Type'],
result['Text'], EntityTime)
elif result['Category'] == 'PHIinfo':
PHIlist = self.listdeal(
PHIlist, result['Type'],
result['Text'], EntityTime)
except Exception as ex:
print('启元关键词错误 ' + str(ex))
# 普通实体识别自主的医学实体
for l in range(len(dfAEsub)):
KeyWords = str.lower(dfAEsub.iloc[l]['Text'].strip())
inAE = 0
for k in range(len(dfAMsub)):
if inAE == 1:
break
try:
MedEntity = str.lower(dfAMsub.iloc[k]['Text'].strip())
QYword = KeyWords.replace(MedEntity, "").strip()
if KeyWords.startswith(MedEntity) or KeyWords.endswith(
MedEntity) and len(QYword) > 1:
resultkw = re.findall(regkw, QYword)
if resultkw != []:
resultkw_res = resultkw[0].strip().split(' ')
for i in range(len(resultkw_res) - 1, -1, -1):
print(resultkw_res[i])
if resultkw_res[i] == 'or' or resultkw_res[
i] == 'and':
resultkw_res.remove(resultkw_res[i])
if resultkw_res[i] == '':
resultkw_res.remove(resultkw_res[i])
QYword = ' '.join(resultkw_res)
inAE = 1
with pymssql.connect('.',
'sa',
'19950804',
'MeadHeadLine',
autocommit=True) as msscon:
with msscon.cursor(as_dict=True) as cursor:
# 包含这个词最长的QY实体
cursor.execute(
"select top 1 * from [dbo].[QYMedKeyWord] where lower(Text) = '%s' "
% QYword)
result = cursor.fetchone()
if result is not None:
inAE = 1
if result[
'Category'] == 'CheckStructTable':
CSTlist = self.listdeal(
CSTlist, result['Type'],
result['Text'], EntityTime)
elif result[
'Category'] == 'DiagnoseStructTable':
DSTlist = self.listdeal(
DSTlist, result['Type'],
result['Text'], EntityTime)
elif result[
'Category'] == 'TreatStructTable':
TSTlist = self.listdeal(
TSTlist, result['Type'],
result['Text'], EntityTime)
elif result['Category'] == 'PHIinfo':
PHIlist = self.listdeal(
PHIlist, result['Type'],
result['Text'], EntityTime)
except Exception as ex:
print('启元关键词错误 ' + str(ex))
ff = open('F:/down/cancer/Entity.txt', 'w')
print("完成一句")
ff.write("输出个人信息：\n")
for each in PHIlist:
for key in each:
if each[key] != "":
ff.write(key + " : " + each[key] + "\n")
ff.write("\n")
ff.write("输出诊断信息：\n")
for each in DSTlist:
for key in each:
if each[key] != "":
ff.write(key + " : " + each[key] + "\n")
ff.write("\n")
ff.write("输出治疗信息：\n")
for each in TSTlist:
for key in each:
if each[key] != "":
ff.write(key + " : " + each[key] + "\n")
ff.write("\n")
ff.write("输出检验信息：\n")
for each in CSTlist:
for key in each:
if each[key] != "":
ff.write(key + " : " + each[key] + "\n")
ff.write("\n")
def __init__(self, database):
try:
self.conn = pymssql.connect(**database)
self.curr = self.conn.cursor()
except Exception as e:
raise e
import datetime
import smtplib
from collections.abc import Iterable
import pymssql
from os.path import basename
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.utils import COMMASPACE, formatdate
from email.header import Header
server = 'your server'
user = '******'
password = '******'
conn = pymssql.connect(server, user, password, 'bd_table')
cursor = conn.cursor()
def get_yesterday_date():
now = datetime.datetime.now()
yesterday = now - datetime.timedelta(days=1)
return yesterday
def get_file_name():
path = 'D:\\'
date_for_filename = get_yesterday_date().strftime("%Y%m%d")
company_name = 'pat'
ext = '.csv'
return path + date_for_filename + company_name + ext
def conn_to_sql_server(server_name, db, user, password):
conn = pymssql.connect(server_name, user, password, db)
return conn
def __init__(self, server, user, password, db, max_recursive_calls=2):
self.connection = pymssql.connect(server, user, password, db, as_dict=True, login_timeout=10)
self.cursor = self.connection.cursor()
self.recursive_calls = 0
self.max_recursive_calls = max_recursive_calls
import pymssql as ms
from config import DB
# 1. 创建连接
conn = ms.connect(DB.HOST, DB.USER, DB.PASSWD, DB.DBNAME)
cur = conn.cursor()
def isUserExist(username):
"""判断用户名是否存在"""
sqli = '''select * from [riskgroup].[IA].[flask_test] where name='%s' '''% (username)
cur = conn.cursor()
cur.execute(sqli)
if len(cur.fetchall()) == 0:
return False
else:
return True
def isPasswdOk(username, passwd):
sqli = "select * from [riskgroup].[IA].[flask_test] where name='%s' and [passwd]='%s'" %(
username, passwd)
cur = conn.cursor()
cur.execute(sqli)
if len(cur.fetchall()) == 0:
return False
else:
return True
parser = argparse.ArgumentParser()
parser.add_argument('-t', '--tagBaseVersion', help='Current tags base version. Used to avoid unnecessary loadings. Default=0')
args = parser.parse_args()
baseVersion = args.tagBaseVersion if args.tagBaseVersion else 0
login = "";
pwd = "";
try:
login = os.environ['tcpdblogin']
password = os.environ['tcpdbpassword']
except:
sys.exit(3)
try:
with pymssql.connect(server="213.246.49.109", user=login, password=password, database="tcp_db") as conn:
cur = conn.cursor()
cur.execute("select versionNumber from AuthorizedTagsVersion")
new_version = cur.fetchone()[0]
if baseVersion == new_version:
sys.exit(0)
cur.execute("""select b.number, p.status_id from BadgeJeu as b
JOIN PlayerJeu as p
On b.player_ID = p.ID
where b.IsEnabled = 1;""")
try:
with open(userTagsFile, 'w') as userFile, open(userPlusTagsFile, 'w') as userPlusFile, open(adminTagsFile, 'w') as adminFile:
Url = ''
UrlDomain = ''
UrlContent = ''
except Exception, e:
Url = ''
UrlDomain = ''
UrlContent = str(e)
data = result + (Longitude, Latitude, LName, LAddress, LCity, PictureCount,
IsMMVideo, Url, UrlDomain, UrlContent)
insertMobileMomentSpider(data)
if __name__ == '__main__':
try:
#提取数据源
conn = pymssql.connect(host=".", user="", password="", charset="utf8")
cur = conn.cursor()
cur.execute("""
select
ExportTime,mobile,snsId,timestamp,authorId,comments,content,authorName,isCurrentUser,likes,mediaList,rawXML,MomentTime
FROM wechat.dbo.MobileMoment a WHERE exists
(SELECT mobilephone FROM wechat.dbo.[Sheet1$] b WHERE careerlevelcode_r='80andAbove' AND a.mobile=b.mobilephone)
AND NOT EXISTS
(
SELECT mobile FROM wechat.dbo.MobileMomentSpider c WHERE a.mobile=c.mobile
)
""")
resList = cur.fetchall()
conn.commit()
conn.close()
#循环爬取源数据，取出
def COVID19():
url = 'https://services1.arcgis.com/0MSEUqKaxRlEPj5g/arcgis/rest/services/ncov_cases/FeatureServer/2/query?f=json&where=1%3D1&returnGeometry=false&spatialRel=esriSpatialRelIntersects&outFields=*&orderByFields=Confirmed%20desc&resultOffset=0&resultRecordCount=250&cacheHint=true'
res = rq.get(url)
data = res.text
features = json.loads(data)['features']
conutry_list = []
count = []
arr_1 = []
arr_2 = []
arr_3 = []
arr_4 = []
totoal_C = 0
totoal_R = 0
totoal_D = 0
for i in range(0, len(features)):
conutry_list.append(
str(features[i]['attributes']['Country_Region']).replace("'", ""))
count.append(features[i]['attributes']['Confirmed'])
for i in range(0, len(features)):
totoal_C += int(features[i]['attributes']['Confirmed'])
totoal_R += int(features[i]['attributes']['Recovered'])
totoal_D += int(features[i]['attributes']['Deaths'])
arr_1.append('全球')
arr_2.append(totoal_C)
arr_3.append(totoal_R)
arr_4.append(totoal_D)
for i in range(0, len(features)):
Country_Region = str(
features[i]['attributes']['Country_Region']).replace("'", "")
Confirmed = int(features[i]['attributes']['Confirmed'])
Recovered = int(features[i]['attributes']['Recovered'])
Deaths = int(features[i]['attributes']['Deaths'])
arr_1.append(Country_Region)
arr_2.append(Confirmed)
arr_3.append(Recovered)
arr_4.append(Deaths)
conn = pymssql.connect(server="XXXXXX",
port=XXX,
user='******',
password='******',
database='XXX')
cursor = conn.cursor()
today = time.strftime("%Y-%m-%d", time.localtime())
sql = "SELECT count(*) AS count from COVID19 where [Datetime] ='" + today + "'"
cursor.execute(sql)
result = cursor.fetchone()
number_of_rows_file = result[0]
conn.commit()
if number_of_rows_file == 0:
for i in range(0, len(arr_1)):
Conutry = arr_1[i]
Confirmed = str(arr_2[i])
Recovered = str(arr_3[i])
Deaths = str(arr_4[i])
Datetime = time.strftime("%Y-%m-%d", time.localtime())
values = (Conutry, Confirmed, Recovered, Deaths, Datetime)
sql = 'INSERT INTO COVID19([Conutry], [Confirmed], [Recovered], [Deaths], [Datetime])' 'VALUES(%s,%s,%s,%s,%s)'
cursor.execute(sql, values)
conn.commit()
cursor.close()
print("Done")
conn.close()
else:
for i in range(0, len(arr_1)):
Conutry = arr_1[i]
Confirmed = str(arr_2[i])
Recovered = str(arr_3[i])
Deaths = str(arr_4[i])
Datetime = time.strftime("%Y-%m-%d", time.localtime())
sql = "UPDATE COVID19 Set "
sql += "[Confirmed] = " + Confirmed + ","
sql += "[Recovered] = " + Recovered + ","
sql += "[Deaths] = " + Deaths + ","
sql += "[Datetime] = '" + Datetime + "' "
sql += "Where [Conutry] = '" + Conutry + "' "
sql += "and [Datetime] = '" + Datetime + "';"
cursor.execute(sql)
conn.commit()
cursor.close()
print('已存在')
conn.close()
import xlwt
# sqlComTxt = "select ApplicantUserDisplayNameCN,ClaimUserDisplayNameCN,u.UserCode,ProcessName,ProjectName,SerialNumber,Title,Purpose,v.CreatedBy,* from vw_GetBizInstancesInfo v left join K2_SystemUser u on v.ClaimUserAccount=u.UserAccount where SerialNumber in ('1001-FSS-PC-00069401','1038-LEAS-EC-00025941','1038-LEAS-PC-00069766','1038-LEAS-PC-00069767','2064-CASH-PC-00070875','3040-BDAM-PC-00071402')"
# Check duplication ADUserID in iGrowPeople
# sqlComTxt="select * from IGrowEmployee where ADUserID in (select ADUserID from IGrowEmployee group by ADUserID having COUNT(*)>1) order by ADUserID"
# Check duplication employee in k2_system
sqlComTxt = "select UserID Account, UserCode ChrisID,* from K2_SystemUser where UserCode<>'' and UserID<>'' and IsActive=1 and UserCode in (select UserCode from K2_SystemUser K left join IGrowEmployee I on K.UserCode=I.CHRISID group by UserCode having count(*)>1) order by UserDisplayNameEN"
sqlComTxt = "select UserID Account, UserCode ChrisID,* from K2_SystemUser where IsActive=1 order by UserDisplayNameEN"
# open sql server connection & cursor
server = "GAOLAN" # UAT
user = "******"
password = "******"
dbname = "CapitalWorkflowCenter_UAT"
con = pymssql.connect(server, user, password, dbname)
cursor = con.cursor(as_dict=True)
cursor.execute(sqlComTxt)
dbRows = cursor.fetchall()
outputData = []
for row in dbRows:
outputData.append(row)
# close
cursor.close()
con.close()
# write missing data in excel
f = xlwt.Workbook()
#encoding=utf-8
import pymssql
server ="192.168.0.98\SQLSERVERDB"
user = "******"
password = "******"
conn = pymssql.connect(server, user, password, "cv_dw")
dict={0:"v0",1:"v1",2:"v2",3:"v3",4:"v4",5:"v5",6:"v6",7:"v7",8:"v8",9:"v9",10:"v10"}
cursor=conn.cursor()
for i in dict:
sql="insert into temp_Corr_loan select DATEADD(day, %d, AssessDate) OperDate,'%s' Dtype from t_fact_Status_FirstCheck where AssessDate>'2015-01-01'"%(i,dict[i])
print sql
cursor.execute(sql)
conn.commit()
#row = cursor.fetchone()
#while row:
#print("Name=%s" % (row[1]))
#row = cursor.fetchone()
conn.close()
import pymssql as ms
import numpy as np
conn = ms.connect(server='127.0.0.1',
user='******',
password='******',
database='bitdb')
cursor = conn.cursor()
cursor.execute('SELECT * FROM iris2;')
row = cursor.fetchall()
print(row)
conn.close()
print("\====================================================/")
aaa = np.asarray(row)
print(aaa)
print(aaa.shape)
print(type(aaa))
np.save('./flask.npy', aaa)
import pymssql
print("start:")
conn = pymssql.connect(host='.',
user='******',
password='******',
database='Test',
charset="GBK")
# conn = pymssql.connect('127.0.0.1','sa','sql','Test')
print("connected!")
cur = conn.cursor()
cur.execute('select top 5 * from [dbo].[S]')
#如果update/delete/insert记得要conn.commit()#否则数据库事务无法提交
print(cur.fetchall())
cur.close()
conn.close()
def open(self):
con_params = self.con_params
self.connection = pymssql.connect(server=con_params['host']
, user=con_params['username']
, password=con_params['password'])
def createConnection(dba):
conn = pymssql.connect(server='(local)',user='******',password='******',database=dba)
return conn