Beispiel #1
0
 def __init__(self, database_path):
     sqlite3.register_adapter(datetime.date, adapt_date)
     self.database_path = database_path
     self.db = sqlite3.connect(self.database_path)
     self.db.text_factory = str
     self.cursor = self.db.cursor()
     self.cursor.execute('PRAGMA foreign_keys = ON')
Beispiel #2
0
def populate_HMR_EMTAB(col, HMR_rows, final_result):
    data_list = []
    conn = _sqlite3.connect('bio.rdb')
    c2 = conn.cursor()
    print col
    e_mtab_select_sql = '''
      select ''' + col + '''
      from e_mtab
      WHERE Gene_ID = '%s'
    '''

    result_list = []
    for row in HMR_rows:
        # print row[0]
        if row[1] <> u'':
            values = row[1].split(';')
            digit_values = []
            for v in values:
                c2.execute(e_mtab_select_sql % v)
                row2 = c2.fetchone()
                if row2 is not None:
                    for v2 in row2:
                        digit_values.append(v2)
            # print digit_values
            str_values = ";".join(str(e) for e in digit_values)
            # c3.execute(insert_sql, (row[0], str_values))
            # conn.commit()
            result_list.append([row[0], col, str_values])
        else:
            continue
    data_list.append(result_list)
    write_csv(data_list=data_list, col_name=col)
Beispiel #3
0
def leaderboard():
    wn1 = turtle.Screen()
    wn1.screensize(600, 800)
    # wn1.setup(width = 1.0, height= 1.0, startx= 0, starty= 0)
    wn1.title("Scoreboard")
    wn1.bgcolor("black")
    wn1.tracer(0)

    if Path('Resources/Database/game.db').is_file():
        # header

        head = turtle.Turtle()
        head.speed(0)
        head.shape("square")
        head.color("white")
        head.penup()
        head.hideturtle()
        head.goto(0, 300)
        head.write("        NAME        SCORE",
                   align="center",
                   font=("Courier", 16, "normal"))

        x = 60
        y = 250
        conn = _sqlite3.connect('Resources/Database/game.db')
        c = conn.cursor()
        c.execute("SELECT * FROM gametab")
        result = c.fetchall()
        print(result)
        for i in result:
            # print(i[0])
            # print("\n")
            # print(i[1])
            pen = turtle.Turtle()
            pen.speed(0)
            pen.shape("square")
            pen.color("white")
            pen.penup()
            pen.hideturtle()
            pen.goto(x, y)
            pen.write("%-10s     %-2s" % (i[0], i[1]),
                      align="center",
                      font=("Courier", 16, "normal"))
            y = y - 40

    else:
        pen = turtle.Turtle()
        pen.speed(0)
        pen.shape("square")
        pen.color("white")
        pen.penup()
        pen.hideturtle()
        pen.goto(0, 0)
        pen.write("Nothing to show",
                  align="center",
                  font=("Courier", 24, "normal"))
        # print("nothing exists")

    wn1.update()
    wn1.mainloop()
Beispiel #4
0
def connect_to_database():
    
    db = _sqlite3.connect('database_final.db')
    
    with db:
        db.execute('CREATE TABLE if not exists messages(pkey integer primary key, name,surname,email,phone,message)')
    return db
Beispiel #5
0
def write(events_list, DBname):

    conn = _sqlite3.connect(DBname)
    conn.execute('''CREATE TABLE IF NOT EXISTS EVENTS
       (ID INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
       TIME_ADD   INTEGER NOT NULL,
       ID_EVENT INTEGER NOT NULL,
       DATE_EVENT            TEXT    NOT NULL,
       TITLE           TEXT    NOT NULL,
       _TEXT            TEXT     NOT NULL,
       IMAGE        TEXT NOT NULL );''')
    print "Table created successfully"

    for event in events_list:
        if check_add(DBname,event['id']):

            id_event = str(event['id'])
            date  = "'" + event['date']  +"'"
            title = "'" + event['title'] +"'"
            _text = "'" + event['text']  +"'"
            image = "'" + event['image'] +"'"
            curr_time = str(get_mills())

            string = "INSERT INTO EVENTS (ID_EVENT,TIME_ADD,DATE_EVENT,TITLE,_TEXT,IMAGE) VALUES ("+id_event+","+curr_time+","+date+","+title+","+_text+","+image+")"
            print string
            conn.execute(string)
    conn.commit()
    conn.close()
Beispiel #6
0
 def __init__(self):
     self._conn = _sqlite3.connect('moncafe.db')
     self.employees = Employees(self._conn)
     self.coffee_stands = Coffee_stands(self._conn)
     self.products = Products(self._conn)
     self.suppliers = Suppliers(self._conn)
     self.activities = Activities(self._conn)
Beispiel #7
0
def database_import_csv():
    con = _sqlite3.connect("test.db")
    cur = con.cursor()
    d =  ["Titre du film", "Réalisateur", "Chef-opérateur"]
    cur.execute("INSERT INTO ProjectInfo (titre, real, cinematographer) values (?,?,?)",d )
    con.commit()
    cur.close()
Beispiel #8
0
def find(courses: list(), db: str):
    """
    find

    Returns a list of courses connected to the course codes in list courses.

    :param courses:       List of course KEYS to search for in the database
    :param db:            Relative path to database file
    :raises LookupError:  The course code was not found in the database
    :return:              List of Courses found
    """
    results = list()

    conn = connect(db)

    c = conn.cursor()

    # Search for each course in the database, add to results list
    for course_code in courses:
        c.execute("SELECT * FROM courses WHERE id=?",
                  (str(course_code.strip()), ))
        row = c.fetchone()
        if row is not None:
            results.append(
                Course(row[2], row[1], row[3], diff=row[4], deadline=row[5]))
        else:
            raise LookupError("Error: Course " + course_code +
                              " has no entries in the database.")

    conn.close()

    return results
Beispiel #9
0
def genereteID():  #id üretme fonksiyonu
    con = _sqlite3.connect("Users.db")
    cursor = con.cursor()
    cursor.execute("SELECT userID FROM USERS")
    data = cursor.fetchall(
    )  #bütün userID'ler data listesinin içerisine atıldı fakat bu liste bize uygun degil

    i = 1
    IDs = []  #yeni id listemiz
    for i in range(
            len(data)
    ):  #data listesindeki bütün id ler istedigimiz formda IDs listesine atılıyor
        IDs.append(data[i][0])
    IDs.sort()  #taramada avantaj saglanması icin liste siralandi

    id = 1  #deger değişmemeli min id=1 max id =5 olmalı
    i = 0
    for i in range(
            len(IDs)
    ):  #1'den 5'e kadar gezip liste içerisinde eksik olan ilk degeri bulur
        if id in IDs:
            id = id + 1
        else:
            break
    con.close()
    return id
Beispiel #10
0
 def __init__(self):
     self.conn = _sqlite3.connect('books.db')
     self.cur = self.conn.cursor()
     self.cur.execute(
         "CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, "
         "year integer, isbn integer)")
     self.conn.commit()
Beispiel #11
0
def readNation():
    conn=_sqlite3.connect('./resources/region_city.db')
    cursor=conn.cursor()
    nations=cursor.execute("select name from region_city where level=2")
    nation=[c[0] for c in nations]
    conn.close()
    return nation
Beispiel #12
0
def submit():
    conn = _sqlite3.connect("address_book.db")
    c = conn.cursor()

    #insert into table
    c.execute("INSERT INTO addresses VALUES (:f_name, :l_name, :address, :city, :state, :zipcode )",
              {
                  'f_name':f_name.get(),
                  'l_name': l_name.get(),
                  'address': address.get(),
                  'city': city.get(),
                  'state': state.get(),
                  'zipcode': zipcode.get()
              })

    conn.commit()
    conn.close()

    #clear the text boxes
    f_name.delete(0, END)
    l_name.delete(0, END)
    address.delete(0, END)
    city.delete(0, END)
    state.delete(0, END)
    zipcode.delete(0, END)
def formmain(request):

    if request.method == "GET":
        form = SurveyFormMain
        context = {'form': form}
        return render(request, 'survey/formmain.html', context)
    else:
        form = SurveyFormMain(request.POST)
        if form.is_valid():
            form.save()
            obj = PersonalInfo.objects.last()
            val = getattr(obj, 'count')
            BASE_DIR = os.path.dirname(
                os.path.dirname(os.path.abspath(__file__)))
            database = os.path.join(BASE_DIR, 'survey.sqlite3')
            conn = _sqlite3.connect(database)
            c = conn.cursor()
            c.execute(
                f'UPDATE survey_surveymain SET personal_id = {val} WHERE id = (SELECT MAX(ID) FROM survey_surveymain)'
            )
            conn.commit()
            c.close()
            conn.close()
            return redirect(f'/survey/main/second/')
        else:
            print(form.errors)
            print("form invalid")
Beispiel #14
0
    def init_user(self,login,password,name):
        '''
        Метод для регистрации пользователя
        :param login:
        :param password:
        :param name:
        :return:
        '''

        data1 = (login,password,name)
        self.conn = db.connect('transaction.db')
        self.cursor = self.conn.cursor()
        sql_query3 = '''
                            select * from users where login = '******' 
                            '''.format(login)

        self.cursor.execute(sql_query3)
        res = self.cursor.fetchone()
        if res == None:
            sql_query1 = 'INSERT INTO users(login,password,name) VALUES (?, ?, ?)'
            self.cursor.execute(sql_query1, data1)
            sql_query2 = '''
                                        select * from users where login = '******' and password = '******' 
                                        '''.format(login, password)
            self.cursor.execute(sql_query2)
            res = self.cursor.fetchone()
            self.user_id = res[0]
            self.login = res[1]
            self.password = res[2]

            self.conn.commit()
            return True
        else:
            return False
 def baglanti_olustur(self):
     baglanti = _sqlite3.connect("database.db")
     self.cursor = baglanti.cursor()
     self.cursor.execute(
         "Create Table If not exists üyeler (kullanıcı_adı TEXT,parola TEXT)"
     )
     baglanti.commit()
Beispiel #16
0
    def query(self):
        conn = _sqlite3.connect('Register.db')

        # create a cursor

        c = conn.cursor()

        # Query the database
        c.execute("SELECT *, oid FROM address")
        records = c.fetchall()
        # print(records)

        # Loop through results
        print_records = ''
        for record in records:
            print_records += str(record[0]) + " " + str(record[1]) + \
                " " + str(record[2]) + " " + str(record[3]) + "\n"

        # creat a label records

        query_label = Label(self, text=print_records)
        query_label.grid(row=10, column=0, columnspan=2)

        # commit changes
        conn.commit()

        # close connection
        conn.close()
Beispiel #17
0
    def view(self, category=None):
        '''
        Функция которая показывает все траты на выбранную категорию,
        если категория не выюрана, то показывает все совершенные транзакции
        :param category:  категория расходов
        :return:
        '''
        id = self.get_id(self.login, self.password)
        self.conn = db.connect('transaction.db')
        self.cursor = self.conn.cursor()
        if category:  # если задана категория, то показываем общую сумму в категории
            sql_query1 = '''
            select * from expenses where user_id = '{}' and category = '{}'
            '''.format(id, category)
            sql_query2 = '''
            select sum(amount) from expenses where user_id = '{}' and category = '{}'
            '''.format(id, category)
        else:  # иначе показываем все транзакции и общую сумму по всем транзакциям
            sql_query1 = '''
            select * from expenses where user_id = '{}'
            '''.format(id, category)
            sql_query2 = '''
            select sum(amount) from expenses where user_id = '{}'
            '''.format(id, category)

        self.cursor.execute(sql_query1)
        result = self.cursor.fetchall()
        result = [list(row) for row in result]
        self.cursor.execute(sql_query2)
        return result
Beispiel #18
0
    def refill(self, value, name_wallet):
        '''
        Пополнение кошелька на введенную сумму
        :param value: сумма, на которую пополняем
        :param name_wallet:  имя кошелька, на который совершается пополнение
        :return:
        '''
        self.conn = db.connect('transaction.db')
        self.cursor = self.conn.cursor()

        id = self.get_id(self.login, self.password)

        sql_query2 = '''
                           select * from wallets where user_id = '{}' and name_wallet = '{}'
                           '''.format(id, name_wallet)

        self.cursor.execute(sql_query2)
        res = self.cursor.fetchall()[-1]
        wallet_id = res[0]
        wallet = res[2]
        name = res[3]

        new_wallet = wallet + value

        new_data = (wallet_id, id, new_wallet, name)

        sql_query3 = '''
                           replace into wallets(wallet_id,user_id,value,name_wallet) values (?, ?, ?,?)
                           '''
        self.cursor.execute(sql_query3, new_data)

        # сохраняем изменения
        self.conn.commit()
Beispiel #19
0
    def extractFromDB(self):
        # Attempt to open a connection and verify that the database is there
        # print ("Connecting to SQLite Database...")
        print('Attempting to Connect to: ' + self.sqlDBloc)
        count = 0
        while not os.path.isfile(self.sqlDBloc) and count < 3:
            print('Database not found, searching up directory structure...')
            newLoc = os.path.split(self.sqlDBloc)[0]
            newLoc = os.path.split(newLoc)[0]
            self.updateDBloc(newLoc)
            count = count + 1

        if os.path.isfile(self.sqlDBloc):
            con = _sqlite3.connect(self.sqlDBloc)
            print('Connected to: ' + self.sqlDBloc)
            cursor = con.cursor()
            try:
                self.sqlString = self.sqlString + ";"
                # print(self.sqlString)
                sqlResult = cursor.execute(self.sqlString)

                # iterate through each row/entry for the resturned query, using description to fetch key names
                self.threatList = [
                    dict(zip([key[0] for key in cursor.description], row))
                    for row in sqlResult
                ]
                for item in self.threatList:
                    tempKey = item.get('threatKey')
                    self.threatDict[tempKey] = item
            except:
                print('Table was not located in database, extract aborted')
            cursor.close()
            con.close()
        else:
            print('Database File Could Not Be Located.')
Beispiel #20
0
def save():
    passw = "I am Cybertron Shadow"
    passw2 = mmpassword.get()
    if username_entry.get() != "" and password_entry.get(
    ) != "" and website_entry.get() != "" and passw == passw2:
        connection = _sqlite3.connect("password~storage.db")
        curso = connection.cursor()
        curso.execute(
            "INSERT INTO password VALUES (:random, :website, :username, :password)",
            {
                'random': random_entry.get(),
                'website': website_entry.get(),
                'username': username_entry.get(),
                'password': password_entry.get()
            })

        connection.commit()

        connection.close()

        random_entry.delete(0, END)
        username_entry.delete(0, END)
        website_entry.delete(0, END)
        password_entry.delete(0, END)
        mmpassword.delete(0, END)

        messagebox.showinfo("cybertron shadow", "INFORMATION SAVED")
    else:
        messagebox.showinfo("cybertron shadow", "CHECK YOUR ENTRY")
Beispiel #21
0
    def submit():
        name = e.get()
        ag = e1.get()
        wt = e2.get()
        bt = variable.get()
        qt = e3.get()
        con = _sqlite3.connect("oneblood.db")
        with con:
            cursor = con.cursor()
            getqty = con.cursor()
        getqty.execute('select qunatity from quantity where blood_group=?',
                       (bt, ))
        v = getqty.fetchone()
        v1 = v[0]

        if (len(name) > 0 and type(name) == str):
            if (int(qt) < int(v1)):
                cursor.execute("Insert into Receive values(?,?,?,?,?)",
                               (name, ag, wt, qt, bt))
                con.commit()
                request.destroy()
            else:
                lk.config(text='Sorry! This much blood is not available')
        else:
            lk.config(text='invalid values')
 def __init__(self, db_location: str, weapon_table: str,
              columns_to_retrieve: list):
     self.db = sql.connect(db_location)
     self.weapon_table = weapon_table
     self.displayed_options = columns_to_retrieve
     self.additional_filters = ''
     self.results_order = 'order by items.name '
Beispiel #23
0
    def __init__(self):
        super(Server_MainWindow, self).__init__()
        super(Ui_MainWindow, self).__init__()
        self.setupUi(self)
        self.dbcon = sql.connect('database.db')

        # with self.dbcon:
        #     self.dbcon.execute("""
        #         CREATE TABLE ALERTS (
        #             id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT DEFAULT 0,
        #             time TEXT,
        #             date TEXT,
        #             message TEXT
        #         );
        #     """)

        self.tablemodel = TableModel()
        self.tableview.setModel(self.tablemodel)
        self.tableview.horizontalHeader().setSectionResizeMode(
            2, QtWidgets.QHeaderView.Stretch)
        self.tableview.verticalHeader().setSectionResizeMode(
            QtWidgets.QHeaderView.ResizeToContents)

        self.socket = QtNetwork.QUdpSocket(self)
        self.socket.bind(QtNetwork.QHostAddress.LocalHost, 45454,
                         QtNetwork.QAbstractSocket.ReuseAddressHint)

        self.send_button.pressed.connect(self.addMessage)
Beispiel #24
0
def read(DBname, except_ids=None, add_time=None):

    resp_str = "SELECT DISTINCT ID_EVENT, TIME_ADD, DATE_EVENT, TITLE, _TEXT, IMAGE FROM EVENTS"

    if len(except_ids) > 0 or add_time > 0:
        resp_str += " WHERE "
    if add_time > 0:
        resp_str += " TIME_ADD > "+str(add_time)
    if len(except_ids) > 0:
        if add_time > 0:
            resp_str += " AND "
        for id in except_ids:
            resp_str += " ID_EVENT != "+ str(id) + " AND "
        resp_str = resp_str[:-4]

    print resp_str
    conn = _sqlite3.connect(DBname)
    cursor = conn.cursor()
    cursor.execute(resp_str)

    one_row ={}
    result = list()

    for row in cursor:
        one_row["id"] = row[0]
        one_row["time_add"] = row[1]
        one_row["date"] = row[2]
        one_row["title"] = row[3]
        one_row["text"] = row[4]
        one_row["image"] = row[5]
        result.append(dict(one_row))

    conn.close()
    return result
Beispiel #25
0
def training():
    global data

    # ##csv code
    # fn = "./teach_rec.csv"
    # fn = os.path.join(os.path.abspath(os.path.dirname(__file__)), fn)
    # data = pd.read_csv(fn)
    # # labEnc = LabelEncoder()
    # renamer(data)
    # if "ID" in data:
    #     del data["ID"]
    # data["STUDENT_ID"] = data.index.values
    # del data["TIMESTAMP"]
    # #  del data["STUDENT_NAME"]
    # # del data["TEACHER_YOU_WANT"]
    # del data["COLLEGE_NAME"]  # may be usefull later todo
    # del data["MEDIUM"]  # may be usefull later todo
    # data = like_dislike(data)

    #db code
    fn = "../db.sqlite3"
    fn = os.path.join(os.path.abspath(os.path.dirname(__file__)), fn)
    con = sq3.connect(fn)
    data = pd.read_sql_query("select tf.*, tul.teacher_id as TEACHER_NAME, tt.TEACHER_NAME as DISPLAY_NAME, 1 as `LIKE` from tutor_teacherfeedback as tf INNER JOIN tutor_teacherfeedback_TEACHERS_YOU_LIKED tul on tf.id = tul.teacherfeedback_id INNER JOIN tutor_teacher tt on tul.teacher_id = tt.id union select tf.*, tul.teacher_id as TEACHER_NAME, tt.TEACHER_NAME as DISPLAY_NAME, -1 as `LIKE` from tutor_teacherfeedback as tf INNER JOIN tutor_teacherfeedback_TEACHERS_YOU_DISLIKED tul on tf.id = tul.teacherfeedback_id  INNER JOIN tutor_teacher tt on tul.teacher_id = tt.id", con)
    data["STUDENT_ID"] = data["id"]
    if "id" in data:
        del data["id"]
    del data["TIMESTAMP"]
    del data["COLLEGE_NAME"]  # may be usefull later todo
    del data["MEDIUM"]  # may be usefull later todo
Beispiel #26
0
    def view(self, category=None):
        '''
        Функция которая показывает все траты на выбранную категорию,
        если категория не выюрана, то показывает все совершенные транзакции
        :param category:  категория расходов
        :return:
        '''
        self.conn = db.connect('transaction.db')
        self.cursor = self.conn.cursor()
        if category:  # если задана категория, то показываем общую сумму в категории
            sql_query1 = '''
            select * from expenses where category = '{}'
            '''.format(category)
            sql_query2 = '''
            select sum(amount) from expenses where category = '{}'
            '''.format(category)
        else:  #иначе показываем все транзакции и общую сумму по всем транзакциям
            sql_query1 = '''
            select * from expenses  
            '''.format(category)
            sql_query2 = '''
            select sum(amount) from expenses
            '''.format(category)

        self.cursor.execute(sql_query1)
        result = self.cursor.fetchall()
        self.cursor.execute(sql_query2)
        total_sum = self.cursor.fetchone()[0]
        return total_sum, result
Beispiel #27
0
    def action_func(self):
        qpmap = QApplication.primaryScreen()
        pix = qpmap.grabWindow(self.winId())
        printer = QtPrintSupport.QPrinter(
            QtPrintSupport.QPrinter.HighResolution)
        printer.setPageSize(QtPrintSupport.QPrinter.B6)
        preview = QtPrintSupport.QPrintDialog(printer, self)
        if preview.exec():
            painter = QtGui.QPainter(printer)
            rect = painter.viewport()  # 获取矩形区域大小
            size = pix.size()
            size.scale(rect.size(), Qt.KeepAspectRatio)  # 保证图片显示完整

            painter.setViewport(0, 0, size.width(),
                                size.height())  # 按照图像大小比例重设矩形区域
            rect = QRect(0, 0, pix.width() + 50, pix.height())
            painter.setWindow(rect)  # 设置窗口大小
            painter.drawPixmap(30, 0, pix)  # 将图像绘制在Qpainter上
            painter.end()
            conn = sqlite3.connect('./data_a.db')
            for name in list(self.drug_data.keys()):
                c = conn.cursor()
                c.execute(
                    "select amount from test_data where name='{0}'".format(
                        name))
                old_amount = c.fetchall()[0][0]
                new_amount = old_amount - self.drug_data[name]
                c.execute(
                    "update test_data set amount={0} where name='{1}'".format(
                        new_amount, name))
                conn.commit()
                c.close()
            conn.close()
            self.close()
Beispiel #28
0
 def __init__(self, database_path):
     sqlite3.register_adapter(datetime.date, adapt_date)
     self.database_path = database_path
     self.db = sqlite3.connect(self.database_path)
     self.db.text_factory = str
     self.cursor = self.db.cursor()
     self.cursor.execute('PRAGMA foreign_keys = ON')
Beispiel #29
0
def update():
    conn = _sqlite3.connect("address_book.db")
    c = conn.cursor()

    record_id = delete_box.get()
    c.execute("""UPDATE addresses SET     
        first_name = :first,
        last_name = :last,
        address = :address,
        city = :city,
        state = :state,
        zipcode = :zipcode
        
        WHERE oid = :oid""",
        {
            'first': f_name_update.get(),
            'last': l_name_update.get(),
            'address': address_update.get(),
            'city': city_update.get(),
            'state': state_update.get(),
            'zipcode': zipcode_update.get(),

            'oid': record_id
        })
    conn.commit()
    conn.close()
    editor.destroy()
 def __init__(self, db):
     self.conn = _sqlite3.connect(db)
     self.cur = self.conn.cursor()
     self.cur.execute(
         "CREATE TABLE IF NOT EXISTS parts (id INTEGER PRIMARY KEY, part text, customer text, "
         "retailer text, price text)")
     self.conn.commit()
Beispiel #31
0
def create_db():
    conn = _sqlite3.connect('my_cart.db')
    c = conn.cursor()
    c.execute(
        """CREATE TABLE IF NOT EXISTS CATEGORIES(category_id integer  primary key,name varchar COLLATE NOCASE)"""
    )
    c.execute(
        """CREATE TABLE IF NOT EXISTS PRODUCTS (product_id integer primary key,product_name varchar COLLATE NOCASE,description varchar,amount integer ,category_id integer ,FOREIGN KEY(category_id)REFERENCES CATEGORIES(category_id) ON DELETE CASCADE )"""
    )
    c.execute(
        """CREATE TABLE IF NOT EXISTS USERS(username varchar primary key,password varchar)"""
    )
    c.execute(
        """CREATE TABLE IF NOT EXISTS COUPONS(coupon_id integer primary key,coupon_code varchar ,end_date text,discount integer,type_of_usage integer)"""
    )
    c.execute(
        """CREATE TABLE IF NOT EXISTS MYCART(cart_id integer primary key ,product_name varchar COLLATE NOCASE,amount integer,username integer,FOREIGN KEY(username)REFERENCES USERS(username) ON DELETE CASCADE)"""
    )
    c.execute(
        """CREATE TABLE IF NOT EXISTS USER_STATUS(id integer primary key , username integer,coupon_id integer,coupon_time  )"""
    )
    c.execute(
        """CREATE TABLE IF NOT EXISTS ADMIN(username varchar  default 'admin',password varchar default 12345)"""
    )
    return conn
Beispiel #32
0
    def extractFromDB(self):
        # Attempt to open a connection and verify that the database is there
        print('Attempting to Connect to: ' + self.sqlDBloc)
        count = 0
        while not os.path.isfile(self.sqlDBloc) and count < 3:
            print('Database not found, searching up directory structure...')
            newLoc = os.path.split(self.sqlDBloc)[0]
            newLoc = os.path.split(newLoc)[0]
            self.set_sqlDBloc(newLoc)
            count = count + 1
        # Construct SQL String
        self.sqlString = self.sqlString + ";"
        # Connect to SQL Database
        con = _sqlite3.connect(self.sqlDBloc)
        cursor = con.cursor()
        sqlResult = cursor.execute(self.sqlString)

        # iterate through each row/entry for the resturned query, using description to fetch key names
        threatList = [
            dict(zip([key[0] for key in cursor.description], row))
            for row in sqlResult
        ]
        for item in threatList:
            tempKey = item.get('threatKey')
            self.recordedThreats[tempKey] = item

        # Close the connection to the database
        con.commit()
        con.close()
Beispiel #33
0
        def ssave():
            connection = _sqlite3.connect("password~storage.db")
            curso = connection.cursor()
            roid = selectoid.get()
            curso.execute(
                """UPDATE password SET
            website= :web,
            username= :name,
            password= :pass
            
            WHERE oid= :oid""", {
                    'web': website_entry1.get(),
                    'name': username_entry1.get(),
                    'pass': password_entry1.get(),
                    'oid': roid
                })

            connection.commit()

            connection.close()
            username_entry1.delete(0, END)
            website_entry1.delete(0, END)
            password_entry1.delete(0, END)

            messagebox.showinfo("cybertron shadow", "UPDATE SUCCESFUL")
Beispiel #34
0
def database_creation():
    con = _sqlite3.connect("test.db")
    cur = con.cursor()
    cur.execute('CREATE TABLE projectInfo( titre TEXT, real TEXT, cinematographer TEXT )')
    cur.execute(
        "CREATE TABLE Clips(clipName TEXT, clipDuration TEXT, Scene TEXT, Shot TEXT, Take TEXT, Circled INTEGER, "
        "Watched INTEGER, Comment TEXT)")
Beispiel #35
0
 def btn_Y_func(self):
     self.name = self.drugname_line.text()
     self.msg_box = QMessageBox()
     if self.name != 0:
         conn = sqlite3.connect('./data_a.db')
         c = conn.cursor()
         sql = "select name from test_data where name='{0}';".format(
             self.name)
         c.execute(sql)
         value = c.fetchall()
         if len(value) != 0:
             reply = self.msg_box.question(
                 self, '信息', '确定删除名称为{0}的药品信息吗?'.format(self.name))
             if reply == QMessageBox.Yes:
                 sql = "delete from test_data where name='{0}';".format(
                     self.name)
                 c.execute(sql)
                 conn.commit()
                 self.btn_Y.setText('继续删除')
                 self.btn_N.setText('完成')
             else:
                 pass
             self.drugname_line.setText('')
         else:
             self.msg_box.warning(self, '警告', '查无此项')
         conn.close()
     else:
         self.msg_box.warning(self, '警告', '名称不能为空')
Beispiel #36
0
 def connectToDB(self, filename = 'devicessqlite.db'):
     try:
         self.myvkdb = _sqlite3.connect(filename)
         self.cur = self.myvkdb.cursor() 
         return True
     except _sqlite3.Error as e:
         print("Error in connectToDB: ", e.args[0])
         return False
Beispiel #37
0
    def createConnection(database):
        _DatabaseWorker.validateSqlAndCursor(database)

        try:
            connection = _sqlite3.connect(database)
        except _sqlite3.DatabaseError:
            print('ERROR: createConnection: Could not create a connection for the given database: ' + database)

        return connection
 def clear(self, id_clear_range):
     connection = connect(self._db_file)
     self._logger.info('clear({0!s}) enter'.format(id_clear_range))
     try:
         cursor = connection.cursor()
         query_str = 'delete from STAT_DATA where id between ? and ?'
         cursor.execute(query_str, id_clear_range)
         connection.commit()
         self._logger.info('clear({0!s}) exit'.format(id_clear_range))
     except BaseException:
         self._logger.exception('exception in clear({0!s})'.format(id_clear_range))
         raise
     finally:
         connection.close()
Beispiel #39
0
    def initResources(self):
        self.stacks = [Queue() for _ in range(self.nP)]
        if self.mode == 'mongo':
            self.client = MongoClient()
            self.db = self.client[mongo_db]

        if self.mode == 'SQL':
            self.db = self.SQL_db_filename
            self.conn = _sqlite3.connect(self.SQL_db_filename)
            self.curs = self.conn.cursor()
            self.curs.execute("CREATE TABLE IF NOT EXISTS tweets (tid integer, username text, cat text, content text, coordinates text, sentiment text, source text)")

        if self.mode == 'JSON':
            self.db = self.json_db_filename
 def get_data(self):
     connection = connect(self._db_file)
     self._logger.info('get_data() enter')
     try:
         cursor = connection.cursor()
         query_str = 'select ID, SOURCE, CATEGORY, TIMEMARKER, DATA from STAT_DATA order by ID'
         cursor.execute(query_str)
         result = cursor.fetchall()
         self._logger.info('get_data() exit')
         return result
     except BaseException:
         self._logger.exception('exception in get_data()')
         raise
     finally:
         connection.close()
Beispiel #41
0
def check_add(DBname,id):

     resp_str = "SELECT DISTINCT ID_EVENT FROM EVENTS WHERE ID_EVENT = " + str(id)
     conn = _sqlite3.connect(DBname)
     cursor = conn.cursor()
     cursor.execute(resp_str)

     result = True
     for row in cursor:
         id = row[0]
         if id != 0:
             result = False

     conn.commit()
     conn.close()

     return result
Beispiel #42
0
import _sqlite3

conn = _sqlite3.connect('rosterdb.sqlite')
cur = conn.cursor()

cur.execute('SELECT id FROM Course WHERE title = ? ', ('si106', ))

temp = cur.fetchone()
print temp
Beispiel #43
0
import _sqlite3


conn = _sqlite3.connect("database.db", check_same_thread=False)


def add_to_table(table_name, **kwargs):
    """Add values to table"""
    print kwargs["fields"]
    print kwargs["values"]
    with conn as c:
        c.execute("CREATE TABLE IF NOT EXISTS {0} {1}".format(table_name, kwargs["fields"]))
        c.execute("INSERT INTO {0} {1} VALUES {2}".format(table_name, kwargs["fields"], kwargs["values"]))


def select_from_table(table_name, fields):
    """Select variable fields from variable table"""
    data_list = []
    with conn as c:
        data = c.execute("SELECT {0} FROM {1}".format(fields, table_name))
    for d in data:
        data_list.append(d)
    return data_list


def drop_table(table):
    with conn as c:
        c.execute("DROP TABLE {0}".format(table))

if __name__ == "__main__":
    drop_table("Pages")
Beispiel #44
0
    'subcutaneous_adipose_tissue',
    'substantia_nigra',
    'terminal_ileum_of_small_intestine',
    'testis',
    'thyroid',
    'tibial_artery',
    'tibial_nerve',
    'transformed_fibroblast',
    'transverse_colon',
    'urinary_bladder',
    'uterus',
    'v****a',
    'visceral_omentum_adipose_tissue',
    'whole_blood'
]
conn = _sqlite3.connect('bio.rdb')
c = conn.cursor()
c2 = conn.cursor()
c3 = conn.cursor()

e_mtab_select_sql = '''
  select adrenal_gland
  from e_mtab
  WHERE Gene_ID = '%s'

  '''
HMR_select_sql = '''
 select rxnid, gene_association from HMR
'''
c.execute(HMR_select_sql)
HMR_rows = c.fetchall()
Beispiel #45
0
 def setUp(self):
     self.filename = self.getfilename()
     self.cnx = _sqlite.connect(self.filename)
Beispiel #46
0
import _sqlite3

roster_values = (
	('Jean-Baptiste Zorg', 'Human', 122),
	('Korbeen Dallas', 'Meat Popsicle', 100),
	("Ak'not", "Mangalore", -5)
	)
with _sqlite3.connect('test_database.db') as connection:
	c = connection.cursor()
	c.execute("CREATE Table Roster(Name TEXT, Species TEXT, IQ INT)")
	c.executemany("INSERT INTO Roster VALUES(?, ?, ?)", roster_values)
	c.execute("UPDATE Roster SET Species=? WHERE Name=? AND IQ=?", ("Human", "Korbeen Dallas", 100))
	c.execute("SELECT Name, IQ FROM Roster WHERE Species ='Human'")
	print("using fetchall()")
	for row in c.fetchall():
		print(row)

	## OR this one which doesn't stay in memory supposidly 
	print("using fetchone()")
	c.execute("SELECT Name, IQ FROM Roster WHERE Species ='Human'")
	while True:
		row = c.fetchone()
		if row is None:
			break
		print(row)
Beispiel #47
0
 def __persistDataParserIntoDb(self, db_filename, data_parser, user_number):
     with lite.connect(db_filename) as db_connection:
         for (activity, session_list) in data_parser.sessions.items():
             for session in session_list:
                 self.__persistSessionIntoDb(db_connection, user_number, activity, session)
 def __init__(self, conn_param, logger = None):
     if isinstance(conn_param, basestring):
         self._conn_factory = lambda: connect(conn_param)
     if callable(conn_param):
         self._conn_factory = conn_param
     self._logger = logger
Beispiel #49
0
from pyquery import PyQuery as pq
import _sqlite3 as sql
from os.path import isfile

with open('matches/leagues.json') as data_file:
    config = json.load(data_file)

key = sys.argv[1]
if key in config["tournaments"]:
    ts = [key]
elif key in config["combined"]:
    ts = config["combined"][key]
elif key == 'all':
    ts = list(config['tournaments'].keys())

conn = sql.connect('matches/matches.db')
conn.row_factory = sql.Row
db = conn.cursor()

db.execute('''create table if not exists matches
             (
             league text, id int, date text, patch text, t1 text, t2 text, result int, link text
             , t1b1 text, t1b2 text, t1b3 text, t1b4 text, t1b5 text
             , t2b1 text, t2b2 text, t2b3 text, t2b4 text, t2b5 text
             , t1p1 text, t1p2 text, t1p3 text, t1p4 text, t1p5 text
             , t2p1 text, t2p2 text, t2p3 text, t2p4 text, t2p5 text
             , t1c1 text, t1c2 text, t1c3 text, t1c4 text, t1c5 text
             , t2c1 text, t2c2 text, t2c3 text, t2c4 text, t2c5 text
             , primary key (id, league)
             )''')