def create_server(self, id: int,
conn: Optional[sqlite3.Connection]) -> server.Server:
# get a connection if none has been passed
if conn is None:
conn = sqlite3.Connection(self.filename)
# try to create a server object
# use defaults
allowed_channels = ''
self_roles = ''
level_roles = ''
auto_role = 0
flags = self.SERVER_FLAGS
# add the entry
conn.execute(
'INSERT INTO servers VALUES (?,?,?,?,?,?)',
(id, allowed_channels, self_roles, level_roles, auto_role, flags))
conn.commit()
# return a representation
return server.Server(conn, id, allowed_channels, self_roles,
level_roles, auto_role, flags)
def fun(e):
root1.destroy()
root=Tk()
con=sqlite3.Connection("projecttttttt")
cur=con.cursor()
cur.execute("create table if not exists student(roll_no number primary key,name varchar(20),department varchar(6),gender number,dob varchar(10),degree varchar(10))")
cur.execute("create table if not exists members(roll_no number primary key,member_type number,foreign key(roll_no) references student)")
cur.execute("create table if not exists quota(member_type number primary key,max_books number,max_duration number)")
cur.execute("create table if not exists book_issue(roll_no number,isbn_no number,accession_no number,doi varchar(10),foreign key(isbn_no) references book,foreign key(roll_no) references student)")# date of issue should be sysdate
cur.execute("create table if not exists book(isbn_no number primary key,title varchar(15),author varchar(10),publisher varchar(10),year number)")
cur.execute("create table if not exists student(roll_no number primary key,name varchar(20),department varchar(6),gender number,dob varchar(10),degree varchar(10))")
cur.execute("create table if not exists members(roll_no number primary key,member_type number,foreign key(roll_no) references student)")
cur.execute("create table if not exists quota(member_type number primary key,max_books number,max_duration number)")
cur.execute("create table if not exists book_issue(roll_no number,isbn_no number,accession_no number,doi varchar(10),foreign key(isbn_no) references book,foreign key(roll_no) references student)")# date of issue should be sysdate
cur.execute("create table if not exists book(isbn_no number primary key,title varchar(15),author varchar(10),publisher varchar(10),year number)")
cur.execute("create table if not exists student(roll_no number primary key,name varchar(20),department varchar(6),gender number,dob varchar(10),degree varchar(10))")
cur.execute("create table if not exists members(roll_no number primary key,member_type number,foreign key(roll_no) references student)")
cur.execute("create table if not exists quota(member_type number primary key,max_books number,max_duration number)")
cur.execute("create table if not exists book_issue(roll_no number,isbn_no number,accession_no number,doi varchar(10),foreign key(isbn_no) references book,foreign key(roll_no) references student)")# date of issue should be sysdate
cur.execute("create table if not exists book(isbn_no number primary key,title varchar(15),author varchar(10),publisher varchar(10),year number)")
cur.execute("create table if not exists copies(isbn_no number,accession_no number,primary key(isbn_no,accession_no),foreign key(isbn_no) references book)")
ur.execute("create table if not exists student(roll_no number primary key,name varchar(20),department varchar(6),gender number,dob varchar(10),degree varchar(10))")
cur.execute("create table if not exists student(roll_no number primary key,name varchar(20),department varchar(6),gender number,dob varchar(10),degree varchar(10))")
def create_user_table():
try:
db = sqlite3.Connection(config.db_root)
cursor = db.cursor()
create_user_table = '''CREATE TABLE t_user(
id INTEGER PRIMARY KEY AUTOINCREMENT,
t_user_name TEXT NOT NULL,
t_user_passe TEXT NOT NULL,
t_user_secret TEXT NOT NULL
)'''
db.execute(create_user_table)
except Exception as e:
db.rollback()
db.close()
print('erreur db: ', e)
else:
db.commit()
db.close()
return {}
def user_to_database(message):
user_id =message.from_user.id
first_name =message.from_user.first_name
last_name =message.from_user.last_name
user_name =message.from_user.username
try:
con =sql.Connection("dbuser.sqlite")
cur =con.cursor()
cur.execute("SELECT userid FROM users WHERE userid =?",(user_id, ))
a =cur.fetchone()
if a is not None:
cur.execute("UPDATE users SET count = count + 1 WHERE userid =?",(user_id,))
con.commit()
else:
cur.execute("INSERT INTO 'users' VALUES (?, ?, ?,\
?, ?, ?, ?, ?) ",(user_id, first_name, last_name, user_name, 1, 0, 0, time().__round__(), ))
con.commit()
return True
except sql.Error as error:
print("can't connect to users.db (user_to_database)", error)
if con:
con.close()
return False
def save_as_sqlite(models, db_path=db_path):
db = sqlite3.Connection(db_path)
for m in models:
for l in m['layers']:
attributes = []
values = []
for k, v in l.items():
if v is None:
continue
attributes.append(k)
values.append(v)
attributes_str = ", ".join(map(str, attributes))
values_str = ", ".join(['?' for _ in attributes])
values_tuple = [str(v) for v in values]
sql_statement = 'insert into layers (%s) values (%s)' % (
attributes_str, values_str)
cur = db.execute(sql_statement, values_tuple)
db.commit()
def upgrade_activity(self, data_list):
"""
upgrade the info of an activit and add them to the list of the class
"""
id_a, date, typerun, distance, time, pace, speed, runner = data_list
conn = sqlite3.Connection("data.db")
cur = conn.cursor()
cur.execute("UPDATE ACTIVITY SET date='%s', \
typerun='%s', \
distance='%s', \
time='%s', \
pace='%s', \
speed='%s' \
WHERE ID_A='%s'" %
(date, typerun, distance, time, pace, speed, id_a))
conn.commit()
cur.close()
conn.close()
self.activities = []
self.read_activities(runner)
return
def search_data():
if SearchBy.get() == "Select":
messagebox.showerror("ERROR", "PLEASE ENTER THE TYPE FOR SEARCH")
SearchBy.set("Select")
SearchTxt.set("")
elif SearchTxt.get() == "":
messagebox.showerror("ERROR", "PLEASE ENTER THE DATA FOR SEARCH")
SearchBy.set("Select")
SearchTxt.set("")
if True:
conn = sqlite3.Connection("School.db")
cur = conn.cursor()
cur.execute("SELECT * from STUDENTS WHERE " + (SearchBy.get()) +
" LIKE '%" + (SearchTxt.get()) + "%'")
rows = cur.fetchall()
if len(rows) != 0:
Student_table.delete(*Student_table.get_children())
for row in rows:
Student_table.insert('', END, values=row)
SearchBy.set("")
SearchTxt.set("")
conn.commit()
conn.close()
def GetStockPriceByDays(stock_name,
start_time,
end_time,
interval=1,
sample_moment='end',
as_dataframe=False):
stock_id = config.table2.loc[stock_name][0]
trading_day_list = _GetTradingDayList(stock_id, start_time, end_time)
sample_day_list = trading_day_list[0::interval]
conn = sqlite3.Connection(config.index_path + 'secondaryindex.db')
price_hist = []
num_sample = len(sample_day_list)
for i in range(num_sample):
if i % 10 == 0 or i == num_sample - 1:
print('progress %d / %d' % (i + 1, num_sample))
price = _GetSinglePrice(conn, stock_id, sample_day_list[i],
sample_moment)
price_hist.append(price)
conn.close()
if as_dataframe == True:
price_hist = pd.DataFrame(price_hist, columns=['datetime', 'price'])
price_hist['datetime'] = pd.to_datetime(price_hist['datetime'])
return price_hist
def deleteData(self):
conn = sqlite3.Connection('ex1')
cPosition = 1
rPosition = (int(self.tableWidget.currentRow()))
if rPosition >= 0:
try:
q_number = self.tableWidget.item(rPosition, cPosition).text()
query = 'DELETE FROM queue where q_number = ?'
cursor = conn.cursor()
cursor.execute(query, [q_number])
conn.commit()
print(str(time.strftime("%H:%M : ", time.localtime())) +
'Delete data from database success.')
self.openDialog('ลบข้อมูลเแล้ว')
except(Error) as e:
print(str(time.strftime("%H:%M : ", time.localtime())) +
'ERROR :' + str(e))
else:
print(str(time.strftime("%H:%M : ", time.localtime())) +
'Unable to delete Null in database.')
self.openDialog('ไม่สามารถลบข้อมูลว่างได้')
conn.close()
self.loadData()
def save_edits():
global ref_id
name = edit_detail.lineEdit.text()
gender = None
if edit_detail.radioButton.isChecked():
gender = "Male"
elif edit_detail.radioButton_2.isChecked():
gender = "Female"
else:
gender = "Other"
dob = edit_detail.dateEdit.text()
pwd = edit_detail.lineEdit_5.text()
conn = sql.Connection("Udet.db")
query = "UPDATE auth SET full_name='{}', gender='{}', dob='{}', pwd='{}' WHERE ref_no={}".format(
name, gender, dob, pwd, ref_id)
conn.execute(query)
conn.commit()
conn.close()
show_edit_detail_page()
suc.show()
def authenticateUser(username,password):
conn = sqlite3.Connection('AdminBase.db')
query = (" SELECT password From User Where username='******'" % (username))
try:
rows = conn.execute(query)
except:
return False
for row in rows:
try:
actual = row[0]
except:
return False
try:
print actual
except:
return False
if password == actual:
return True
conn.close()
else:
return False
conn.close()
def clearOldData(self, force=False):
"Only called by the historian, that's why we can reuse the connection and do everything in one transaction"
if not self.historyLength:
return
# Attempt to detect impossible times indicating the clock is wrong.
if time.time() < 1597447271:
return
with self.h.lock:
conn = sqlite3.Connection(self.filename)
c = conn.cursor()
c.execute(
"SELECT count(*) FROM record WHERE channel=? AND timestamp<?",
(self.chID, time.time() - self.historyLength))
count = c.fetchone()[0]
# Only delete records in large blocks. To do otherwise would create too much disk wear
if count > 8192 if not force else 1024:
c.execute("DELETE FROM record WHERE channel=? AND timestamp<?",
(self.chID, time.time() - self.historyLength))
conn.close()
def validateEntry():
error = ''
name = request.form['name']
newname = request.form['newname']
password = request.form['password']
repassword = request.form['repassword']
if name == '' or newname == '' or password == '' or repassword == '':
error = 'all fields are compulsory'
return render_template("add_user.html", error=error)
if ' ' in newname:
error = 'no spaces allowed in the username'
print error
return render_template("add_user.html", error=error)
conn = sqlite3.Connection('AdminBase.db')
query = "SELECT * FROM User WHERE username='******'".format(newname)
rows = conn.execute(query)
for row in rows:
error = 'username is already taken'
if password == repassword:
pass
else:
error = 'passwords do not match'
return render_template("add_user.html", error=error)
if error == '':
query = "INSERT INTO User(username,password,name) VALUES('{}','{}','{}')".format(
newname, password, name)
conn.execute(query)
conn.commit()
conn.close()
# return render_template("add_successful.html",name=name,newname=newname)
return render_template("add_user_success.html",
name=name,
newname=newname)
else:
return render_template("add_user.html", error=error)
def __init__(self):
"""
Gets data for training the MDNVol network. Also outputs data for out-of-sample forecasting.
"""
spx_minute = pd.read_csv(
"SPX_1min.csv",
header=0,
names=["datetime", "open", "high", "low", "close"],
index_col="datetime",
parse_dates=True,
)
self.spx_variance = rv_calc(spx_minute)
conn = sqlite3.Connection("data.db")
spx_data = pd.read_sql(
"SELECT * FROM prices WHERE ticker='^GSPC'",
conn,
index_col="date",
parse_dates="date",
)
spx_returns = np.log(spx_data["close"]) - np.log(spx_data["close"].shift(1))
self.spx_returns = spx_returns.dropna()
vix_data = pd.read_sql(
"SELECT * FROM prices WHERE ticker='^VIX'",
conn,
index_col="date",
parse_dates="date",
)
# This puts it into units of daily standard deviation
self.vix = vix_data["close"] / np.sqrt(252) / 100
vix_lags = create_lags(np.log(self.vix), 21, name="vix")
return_lags = create_lags(self.spx_returns, 21, name="returns")
rv_lags = create_lags(np.log(self.spx_variance), 21, name="rv")
self.x = pd.concat([vix_lags, return_lags, rv_lags], axis=1).dropna()
def placeTime():
global myCorpId
if myCorpId == '':
return home()
corpID = myCorpId
#get today's date using datetime module from python
dateString = str(datetime.now().date())
timeString = str(datetime.now().time())
timeString = timeString[:8]
#remove the 20 from 2017
dateString = dateString[2:]
#put each date field in a list
dateString = dateString.split('-')
#reverse that list to make it to dd mm yy
dateString = dateString[::-1]
#put it back to dd/mm/yy format just like an excel date format
dateString = '/'.join(dateString)
print('dateString is ', dateString)
#Update the date entry in the database
query1 = (" UPDATE Person SET ADOJ='%s' WHERE CorpID='%s' " %
(dateString, corpID))
query2 = (" UPDATE Person SET IsJoined='Joined' WHERE CorpID='%s' " %
(corpID))
query3 = (" UPDATE Person SET Time='%s' WHERE CorpID='%s' " %
(timeString, corpID))
conn = sqlite3.Connection("EmployeeBase.db")
conn.execute(query1)
conn.execute(query2)
conn.execute(query3)
conn.commit()
myCorpId = ''
return home()
class SQLiteDatabase(object):
"""
Singleton instance sqlite3 file access wrapper
"""
def __init__(self, db_path, tables_sql=None, foreign_keys=True):
"""
Opens given database reference. If tables_sql list is given,
each SQL command in the list is executed to initialize the
database.
"""
self.log = Logger('sqlite').default_stream
self.db_path = db_path
if db_path is None:
raise SQLiteError('Database path is None')
db_dir = os.path.dirname(db_path)
if not os.path.isdir(db_dir):
try:
os.makedirs(db_dir)
except IOError, (ecode, emsg):
raise SQLiteError('Error creating directory %s: %s' %
(db_dir, emsg))
self.conn = sqlite3.Connection(self.db_path)
c = self.cursor
if foreign_keys:
c.execute('PRAGMA foreign_keys=ON')
c.fetchone()
if tables_sql:
for q in tables_sql:
try:
c.execute(q)
except sqlite3.OperationalError, emsg:
raise SQLiteError('Error executing SQL:\n%s\n%s' %
(q, emsg))
def showlist():
show = Tk()
show.configure(bg='Light Steel Blue')
Label(
show,
text=
"Booking_ID Customer _Name Customer_UID Car_Name FROM TO Payment_Mode",
font='times 20',
width=150).pack()
con = sqlite3.Connection('book')
cur = con.cursor()
cur.execute('select * from bookings')
con.commit()
a = cur.fetchall()
j = 0
while j < len(a):
b = [str(i[0]) for i in a]
b = str(b[j])
c = [str(i[1]) for i in a]
c = str(c[j])
d = [str(i[2]) for i in a]
d = str(d[j])
e = [str(i[3]) for i in a]
e = str(e[j])
f = [str(i[4]) for i in a]
f = str(f[j])
g = [str(i[5]) for i in a]
g = str(g[j])
h = [str(i[6]) for i in a]
h = str(h[j])
Label(show,
text=b + ' ' + c + ' ' + d +
' ' + e + ' ' + f +
' ' + g + ' ' + h,
bg='Light Steel Blue',
font='times 18').pack()
j = j + 1
def dateForm():
Employees = []
status = 0
dateString = ''
form = DateForm()
if request.method == 'POST':
try:
dateString = form.dt.data.strftime('%x')
dateString = str(dateString)
dateString = dateString.split('/')
dateString[0],dateString[1]=dateString[1],dateString[0]
dateString = '/'.join(dateString)
print dateString
print 'datString is {}'.format(str(dateString))
conn=sqlite3.Connection('EmployeeBase.db')
cur = conn.execute("SELECT * FROM Person WHERE ADOJ == '{}'".format(dateString))
cnt = 0
Employees = [dict(Id=cnt,
Name=row[1],
CorpID=row[2],
Contact=row[3],
PDOJ = row[4],
ADOJ = row[5],
IsJoined =row[6],
Track = row[7],
Time = row[8],
cnt=cnt+1
) for row in cur.fetchall()]
print Employees
if not Employees:
status = 2
else:
status = 1
conn.close()
except Exception,e:
print e
return render_template('date_form.html',form=form,Employees=Employees,status = status)
def search(s): #this function searches the database
try:
con =sql.Connection("db.sqlite3")
cur =con.cursor()
s =str(s)
for sym in s: #cleaning of unnecessary characters. if the character is
if sym.isalpha() ==False and sym.isdigit()==False: #not a letter or number,
s =s.replace(sym, " ") #it will be removed from the query string.
if len(s) <3:
return None
#This is where a full-text search is performed on the table.
cur.execute("SELECT lib.rowid, ban, author, title, translator,\
seqname, uncompressed, zip, offset, compressed FROM lib INNER Join zip\
on lib.rowid = zip.rowid WHERE lib MATCH ? ORDER BY author",(s,))
res =cur.fetchmany(70)
return res #returns a list with tuples.
except sql.Error as error:
print("Ошибка при подключении к sqlite (search) ", error)
return None
finally:
if (con):
con.close()
def __init__(self, filename):
self.conn = sqlite3.Connection(filename)
self.cur = self.conn.cursor()
self.filename = filename
self.cur.executescript("""
CREATE TABLE IF NOT EXISTS Ucenik(
mb integer INTEGER PRIMARY KEY,
ime text NOT NULL,
prezime text NOT NULL,
razredID integer NOT NULL,
username text NOT NULL,
FOREIGN KEY (razredID) REFERENCES Razred (mb));
CREATE TABLE IF NOT EXISTS Ocjena (
mb integer PRIMARY KEY,
ocjena integer,
datum DATETIME NOT NULL,
predmetID integer NOT NULL,
ucenikID integer NOT NULL,
nastavnik text NOT NULL,
FOREIGN KEY (predmetID) REFERENCES Predmet (mb),
FOREIGN KEY (ucenikID) REFERENCES Ucenik (mb));
""")
def find(name):
name = standardize(normalize(name))
print 'searching for', name
# compute diffs for all
print 'computing'
matches = []
db = sqlite3.Connection(PATH_GEONAMES)
cur = db.cursor()
for r in cur.execute('''
select geonameid,countrycode,name,asciiname,alternatenames,longitude,latitude
from orig
'''):
gnid, countrycode, _name, asciiname, alterns, lon, lat = r
r = gnid, countrycode, _name, asciiname, lon, lat
names = [_name, asciiname]
names = [normalize(n) for n in names]
names = [standardize(n) for n in names]
names = [n for n in names if n]
for n in names:
if name == n:
sim = 1
matches.append((r, sim))
else:
for n in names:
m = difflib.SequenceMatcher(None, name, n)
sim = m.ratio()
if sim >= 0.8:
matches.append((r, sim))
print 'matching'
srt = sorted(matches, key=lambda (r, sim): -sim)
return srt
def test_table_init(self):
"""
Test that the database is properly initialized by testing the
table names and the number of attributes for each table
"""
# Connect to database
conn = sqlite3.Connection("test/test_db.db")
c = conn.cursor()
f.table_init(conn) # initialize tables
# Query for tables
result = c.execute(
"SELECT name from sqlite_master WHERE type='table';")
table_names = [name[0] for name in result] #create list of table names
# expected tables and the expected number of attributes
desired_tables = {
'accounts': 5,
'groups': 2,
'envelopes': 4,
'transactions': 10,
'payees': 3
}
# Test that the correct number of tables were made
self.assertEqual(len(desired_tables), len(table_names))
# Test that the tables are named correctly
for k in desired_tables.keys():
self.assertIn(k, table_names)
# Test each table has the correct number of attriutes
for k, v in desired_tables.items():
result = c.execute("PRAGMA table_info({})".format(k))
self.assertEqual(len([i for i in result]), v)
def insert():
con=sqlite3.Connection('students1')
cur=con.cursor()
try:
cur.execute("create table if not exists student1(erno varchar(10) primary key,fname char(15) not null,lname char(15) not null,dob date,regdate date,mobileno number(15) unique,cgpa number(8,9),course char(15),department char(15),hobbies char(15) not null)")
t1=spinbox1.get()
t3=spinbox2.get()
t2=s.get()
e4=t1+"/"+t2+"/"+t3
w1=spinbox3.get()
w3=spinbox4.get()
w2=ss1.get()
e6=w1+"/"+w2+"/"+w3
sa=v1.get()
ss=v.get()
b=(e1.get(),e2.get(),e3.get(),e4,e6,int(e8.get()),float(e10.get()),sa,ss,e11.get())
cur.execute("insert into student1 values(?,?,?,?,?,?,?,?,?,?)",b)
showinfo("GREAT","Insertion Successful")
except:
showerror("enter again","incorrect data")
con.commit()
clear()
def attribute_generator_publisher(optional_conn: sqlite3.Connection = None):
# Here we add a line for every attribute generator we want available (whether we use it or not)
published_attributes.append(VoidAttGen.gen_wc())
published_attributes.append(VoidAttGen.gen_adj())
published_attributes.append(VoidAttGen.gen_noun())
published_attributes.append(VoidAttGen.gen_sentences())
published_attributes.append(VoidAttGen.gen_test_vector())
published_attributes.append(BertAttGen.gen_bert_vector())
published_attributes.append(PandoraAttGen.gen_pandora_mbti())
published_attributes.append(PandoraAttGen.gen_pandora_age())
published_attributes.append(PandoraAttGen.gen_pandora_gender())
published_attributes.append(PandoraAttGen.gen_pandora_mbti_probability())
published_attributes.append(PandoraAttGen.gen_pandora_gender_probability())
if optional_conn:
conn = optional_conn
else:
conn = sqlite3.Connection('example.db')
print(__log_attribute_headers(conn))
conn.commit()
if not optional_conn:
conn.close()
def process_file(log_filename):
conn = sqlite3.Connection(_db_name)
cursor = conn.cursor()
with open(log_filename) as log_file:
i = 1 # lines count
lc = 0 # logs count
cur_log = {}
proc_status_fmt = '\r{} lines processed, {} logs found'
compiled_pattern = re.compile(LOG_START_PAT)
while True:
line = log_file.readline()
if not line:
break
match = compiled_pattern.match(line)
if match:
if cur_log:
save_log(cur_log, cursor)
lc += 1
cur_log = {}
cur_log['date'] = match.group(1)
cur_log['type'] = match.group(2)
cur_log['content'] = ' '.join(
line.split(' ')[2:]).lstrip(' \n')
elif cur_log:
cur_log['content'] += line
else:
print 'STRANGE'
raise KeyboardInterrupt
i += 1
# print 'Line no. {} processed'.format(i)
sys.stdout.flush()
sys.stdout.write(proc_status_fmt.format(i, lc))
conn.commit()
conn.close()
print
print 'Finished processing log file.'
def writeData(self):
conn = sqlite3.Connection('ex1')
q_number = self.lineEdit.text()
named_tuple = time.localtime() # get struct_time
q_enter_time = time.strftime("%H:%M", named_tuple)
if q_number != '':
print('q_number != NULL')
try:
query = 'INSERT INTO queue(q_number,q_enter_time) VALUES(?,?)'
cursor = conn.cursor()
cursor.execute(query, [q_number, q_enter_time])
conn.commit()
print(str(time.strftime("%H:%M : ", time.localtime())) +
'Data saved to database success.')
self.openDialog('บันทึกข้อมูลเแล้ว')
except(Error) as e:
print(str(time.strftime("%H:%M : ", time.localtime())) +
'ERROR :' + str(e))
else:
print(str(time.strftime("%H:%M : ", time.localtime())) +
'Got Null value on LineEdit ,Unable to write Null in database.')
self.openDialog('ไม่สามารถบันทึกข้อมูลว่างได้')
conn.close()
self.loadData()
def getCache(self):
'''Returns the Player Information Cache'''
cachePath = playerpath
if self.caches.has_key(cachePath):
try:
self.caches[cachePath].commit()
return self.caches[cachePath]
except:
pass
oldcwd = os.getcwd()
try:
os.chdir(cachePath) #stump: work around bug in SQLite unicode path name handling
conn = sqlite3.Connection('FoFiX-players.cache')
finally:
os.chdir(oldcwd)
# Check that the cache is completely initialized.
updateTables = 0
try:
v = conn.execute("SELECT `value` FROM `config` WHERE `key` = 'version'").fetchone()[0]
if int(v) != 2:
updateTables = 2 #an old version. We don't want to just burn old tables.
except:
updateTables = 1 #no good table
if updateTables > 0: #needs to handle old versions eventually.
for tbl in conn.execute("SELECT `name` FROM `sqlite_master` WHERE `type` = 'table'").fetchall():
conn.execute('DROP TABLE `%s`' % tbl)
conn.commit()
conn.execute('VACUUM')
conn.execute('CREATE TABLE `config` (`key` STRING UNIQUE, `value` STRING)')
conn.execute('CREATE TABLE `players` (`name` STRING UNIQUE, `lefty` INT, `drumflip` INT, `autokick` INT, `assist` INT, `twochord` INT, `necktype` INT, `neck` STRING, \
`part` INT, `difficulty` INT, `upname` STRING, `control` INT, `changed` INT, `loaded` INT)')
conn.execute('CREATE TABLE `stats` (`song` STRING, `hash` STRING, `player` STRING)')
conn.execute("INSERT INTO `config` (`key`, `value`) VALUES ('version', '2')") #stump: current cache format version number
conn.commit()
self.caches[cachePath] = conn
return conn
def getChannelID(self, tag):
# Either get our stored channel name, or create a new onw
with self.h.lock:
# Have to make our own, we are in a new thread now.
conn = sqlite3.Connection(self.filename)
conn.row_factory = sqlite3.Row
c = conn.cursor()
c.execute(
"SELECT rowid,tagName,unit,accumulate from channel WHERE tagName=?",
(tag.name, ))
self.chID = None
if not isinstance(tag.unit, str):
raise ValueError('bad tag unit ' + str(tag.unit))
if not isinstance(self.accumType, str):
raise ValueError('bad tag accum ' + str(self.accumType))
for i in c:
if i['tagName'] == tag.name and i['unit'] == tag.unit and i[
'accumulate'] == self.accumType:
self.chID = i['rowid']
if not self.chID:
conn.execute("INSERT INTO channel VALUES (?,?,?,?)",
(tag.name, tag.unit, self.accumType, '{}'))
conn.commit()
c = conn.cursor()
c.execute(
"SELECT rowid from channel WHERE tagName=? AND unit=? AND accumulate=?",
(tag.name, tag.unit, self.accumType))
self.chID = c.fetchone()[0]
conn.close()
def __init__(self, baza):
self.conn = sqlite3.Connection(baza)
self.cur = self.conn.cursor()
self.cur.executescript("""
DROP TABLE IF EXISTS ispiti;
DROP TABLE IF EXISTS kolegiji;
DROP TABLE IF EXISTS studenti;
CREATE TABLE studenti (
student_id integer PRIMARY KEY,
ime_prezime text NOT NULL UNIQUE);
CREATE TABLE kolegiji (
kolegiji_id integer PRIMARY KEY,
naziv text NOT NULL UNIQUE);
CREATE TABLE ispiti (
student_id integer,
kolegiji_id integer,
ocjena integer NOT NULL,
PRIMARY KEY (student_id, kolegiji_id),
FOREIGN KEY (student_id) REFERENCES studenti (student_id),
FOREIGN KEY (kolegiji_id) REFERENCES kolegiji (kolegiji_id));
""")
def insertRows(sheet, book):
conn = sqlite3.Connection('EmployeeBase.db')
try:
for i in range(1, sheet.nrows):
row = sheet.row_values(i)
Name = row[0]
CorpID = row[1]
Contact = int(row[2])
PDOJ = int(row[3])
PDOJ = datetime(*xlrd.xldate_as_tuple(PDOJ, book.datemode))
PDOJ = PDOJ.date().strftime("%d/%m/%y")
ADOJ = row[4]
ADOJ = datetime(*xlrd.xldate_as_tuple(ADOJ, book.datemode))
ADOJ = ADOJ.date().strftime("%d/%m/%y")
Time = row[5]
IsJoined = row[6]
Track = row[7]
query = "INSERT INTO Person (Id,Name,CorpID,Contact,PDOJ,ADOJ,IsJoined,Track) VALUES(%d,'%s','%s',%d,'%s','%s','%s','%s')" % (
i, Name, CorpID, Contact, PDOJ, ADOJ, IsJoined, Track)
conn.execute(query)
except Exception, e:
print 'error'
print str(e)
