【python】远程服务数据库pymssql

import pymssql #引入pymssql模块
import pandas as pd

def conn():
    connect = pymssql.connect('172.16.1.79','admin','admin','longshine') #服务器名,账户,密码,数据库名
    if connect:
        print("连接成功!")
    return connect

con = pymssql.connect(
    host = "192.168.1.246",
    user = "mymonitor",
    password = "bfdl123456@",
    database = "BF_CS"
)

connect = pymssql.connect('172.16.1.77','admin','admin','longshine') #服务器名,账户,密码,数据库名
if connect:
    print("连接成功!")

cursor = connect.cursor()  # 创建一个游标对象,python里的sql语句都要通过cursor来执行
sql = '''
SELECT
*
FROM w_baoxiaomxywb
'''
cursor.execute(sql)   #执行sql语句
#row = cursor.fetchone()  #读取查询结果,仅反回一条结果
row = cursor.fetchall() #返回全部结果
print(pd.DataFrame(list(row)).shape)
#connect.commit()  # 提交
cursor.close()  # 关闭游标
connect.close()  # 关闭连接

 

sql = "SELECT T0.[ItemCode] AS 物料编码,T1.[ItemName] AS 物料名称, T1.[FrgnName] AS 型号, FLOOR(T0.[OnHand]) AS 现存量,T1.[SalUnitMsr] AS 单位,T1.[MinLevel] AS 最小库存量 FROM OITW T0   INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]  INNER JOIN OWHS T2 ON T0.[WhsCode] = T2.[WhsCode] WHERE T0.[OnHand] != '0' AND T0.[ItemCode] not in ('x-001','x-002','x-003','x-004','x-0006','1000000','B0100212001001')  AND T2.[WhsCode] = '04';"

 

pymssql工作原理

①、使用connect创建连接对象；

②、connect.cursor创建游标对象，SQL语句的执行在游标上执行；

③、cursor.execute()方法执行SQL语句，cursor.fetch()方法获取查询结果；

④、调用close方法关闭游标cursor和数据库连接；

 

还有两点：

①、一条游标只能执行一条SQL语句，如果需要执行多条，需要创建多条游标，切记！！！

②、SQL语句中有python默认值（比如index）时，给其加上反引号即可。。。

---

转载 https://www.cnblogs.com/imyalost/p/8873948.html 

---

 

import pymssql

class SQLServer:   
    def __init__(self,server,user,password,database):
    # 类的构造函数，初始化DBC连接信息
        self.server = server
        self.user = user
        self.password = password
        self.database = database

    def __GetConnect(self):
    # 得到数据库连接信息，返回conn.cursor()
        if not self.database:
            raise(NameError,"没有设置数据库信息")
        self.conn = pymssql.connect(server=self.server,user=self.user,password=self.password,database=self.database)
        cur = self.conn.cursor()
        if not cur:
            raise(NameError,"连接数据库失败")  # 将DBC信息赋值给cur
        else:
            return cur
             
    def ExecQuery(self,sql):
        '''
        执行查询语句
        返回一个包含tuple的list，list是元素的记录行，tuple记录每行的字段数值
        '''
        cur = self.__GetConnect()
        cur.execute(sql) # 执行查询语句
        result = cur.fetchall() # fetchall()获取查询结果
        # 查询完毕关闭数据库连接
        self.conn.close()
        return result

def main():
    msg = SQLServer(server="127.0.0.1",user="test",password="Test321",database="TEST")
    result = msg.ExecQuery("SELECT TOP 1 Value FROM t_Security_Code WHERE Mobile = '18501007700' ORDER BY InsertTime DESC")
    for (Value) in result:
        print(Value)
 
if __name__ == '__main__':
    main()

 

 

 

import pymssql
import pandas as pd
from openpyxl import Workbook,load_workbook
import os

sap = pymssql.connect(
        host = '192.168.1.246',
        user = 'mymonitor',
        password = 'bfdl123456@',
        database = 'BF_CS'
    )

if sap:
    print("Connect 成功")
else:
    print("链接出错")

sql = "SELECT T0.[ItemCode] ,T1.[ItemName] AS 物料名称, T1.[FrgnName] AS 型号, FLOOR(T0.[OnHand]) AS 现存量,T1.[SalUnitMsr] AS 单位,T1.[MinLevel] AS 最小库存量 FROM OITW T0   INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]  INNER JOIN OWHS T2 ON T0.[WhsCode] = T2.[WhsCode] WHERE T0.[OnHand] != '0' AND T0.[ItemCode] not in ('x-001','x-002','x-003','x-004','x-0006','1000000','B0100212001001')  AND T2.[WhsCode] = '04';"
# 创建游标
cur = sap.cursor()
cur.execute(sql)
row = cur.fetchall()

print(len(row))
for i in range(0,len(row)):
    print(row[i])

pathExcel = 'C:/Users/18041/Desktop/text/shujuku/铜排车间.xlsx'
osExcel = os.path.exists(pathExcel)
if osExcel:
    wb = load_workbook(pathExcel)
    ws = wb.get_sheet_by_name('铜排车间')
else:
    wb = Workbook()
    ws = wb.active
    ws.title = '铜排车间'

ws['A1'] = '物料编码'
ws['B1'] = '物料名称'
ws['C1'] = '物料规格'
ws['D1'] = '数量'

for line in row:
    ws.append(line)

wb.save("C:/Users/18041/Desktop/text/shujuku/铜排车间.xlsx")
wb.close()