64 lines
1.9 KiB
Python
64 lines
1.9 KiB
Python
# database/sql_server_connection.py
|
||
"""SQL Server数据库连接模块"""
|
||
import pyodbc
|
||
from config.settings import SQL_SERVER_CONFIG
|
||
|
||
class SQLServerConnection:
|
||
def __init__(self):
|
||
self.config = SQL_SERVER_CONFIG
|
||
|
||
def connect(self):
|
||
"""建立数据库连接"""
|
||
connection_string = (
|
||
f"DRIVER={self.config['driver']};"
|
||
f"SERVER={self.config['server']};"
|
||
f"DATABASE={self.config['database']};"
|
||
f"UID={self.config['username']};"
|
||
f"PWD={self.config['password']};"
|
||
)
|
||
return pyodbc.connect(connection_string)
|
||
|
||
def get_mix_weight_info(self):
|
||
"""
|
||
获取所有配比号及其对应每方重量的信息
|
||
重量计算方式：U1到U17的和，NULL值不计入
|
||
"""
|
||
connection = self.connect()
|
||
cursor = connection.cursor()
|
||
|
||
try:
|
||
# 查询所有配比信息
|
||
query = """
|
||
SELECT
|
||
Code,
|
||
U1, U2, U3, U4, U5, U6, U7, U8, U9, U10, U11, U12, U13, U14, U15, U16, U17
|
||
FROM Recipe_back
|
||
WHERE Code IS NOT NULL
|
||
ORDER BY Code
|
||
"""
|
||
|
||
cursor.execute(query)
|
||
results = cursor.fetchall()
|
||
|
||
mix_info = []
|
||
for row in results:
|
||
code = row[0]
|
||
weights = [row[i] for i in range(1, 18)] # U1到U17
|
||
|
||
# 计算总重量（忽略NULL值）
|
||
total_weight = sum(weight for weight in weights if weight is not None and weight != 'NULL')
|
||
|
||
mix_info.append({
|
||
"Code": code,
|
||
"TotalWeight": round(total_weight, 2) # 保留两位小数
|
||
})
|
||
|
||
return mix_info
|
||
|
||
except Exception as e:
|
||
print(f"查询配比信息时出错: {e}")
|
||
return []
|
||
|
||
finally:
|
||
connection.close()
|