import pyodbc
import textwrap
import datetime
class SQL_Server:
def __init__(self):
server = 'DESKTOP-LAD9OQG\SQLEXPRESS'
database = 'emotionDB'
username = 'sa'
password = '123456'
self.cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
def insert(self, query):
cursor = self.cnxn.cursor()
cursor.execute(query)
self.cnxn.commit()
def select(self, query):
cursor = self.cnxn.cursor()
cursor.execute(query)
data = []
for row in cursor:
data.append([x for x in row]) # or simply data.append(list(row)) note phải append list
return data
if __name__ == "__main__":
query = 'SELECT * FROM dbo.Emotion'
# query2 = "INSERT INTO dbo.DienThoai (MaDienThoai,TenDienThoai,MaHang ,DonGia,SoLuongConLai) VALUES ('DT000045', 'iphone 15', 'HANG002', 1000000, 100)"
query2 = textwrap.dedent('''
INSERT INTO dbo.Emotion (image,LoaiCamXuc ,ThietBi,Ngay,Kip)
VALUES ('huyinit',1,1, DATETIMEFROMPARTS(2019,12,20,9,36,0,0),2);
''')
#(53, 'huyinit', 1, 1, datetime.datetime(2019, 12, 20, 9, 36), 2)
sql = SQL_Server()
#sql.insert(query2)
cursor = sql.select(query)
'''
for row in cursor:
row_to_list = [elem for elem in row]
print(row)
'''
#
'''
# in số lần xuất hiện của các cảm xúc , theo kíp và theo thiết bị
thietbi=1
kip=2
f={
1:0 ,2:0 ,3:0 ,4:0 ,5:0 ,6:0
}
for entity in cursor:
if entity[3]==thietbi and entity[5]==kip :
f[entity[2]] += 1
for i in f:
print("cam xuc",i,"xuat hien", f[i])
print(type(cursor) , type(cursor[0]) )
'''
thietbi=1
kip=2
# f=[]
f = []
for i in range(7):
f.append([])
for entity in cursor:
if entity[3]==thietbi and entity[5]==kip :
f[entity[2]].append( entity[1])
print(f[1])