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 __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 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)
)''')