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')
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)
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()
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
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()
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)
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()
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
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
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()
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
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")
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()
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()
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
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()
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.')
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")
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 '
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)
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
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
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
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()
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()
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
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()
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")
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)")
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, '警告', '名称不能为空')
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
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()
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()
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
import _sqlite3 conn = _sqlite3.connect('rosterdb.sqlite') cur = conn.cursor() cur.execute('SELECT id FROM Course WHERE title = ? ', ('si106', )) temp = cur.fetchone() print temp
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")
'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()
def setUp(self): self.filename = self.getfilename() self.cnx = _sqlite.connect(self.filename)
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)
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
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) )''')