Untitled

 avatar
unknown
plain_text
3 years ago
2.2 kB
2
Indexable
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])