def insert(self):
db = self.connect('locationapp/locationdb.db')
cursor = sqlite3.Cursor(db)
insert = "INSERT INTO locations (name, population) VALUES ('{0}', '{1}')".format(self.name, self.population)
cursor.execute(insert)
db.commit()
print("inserted")
def test_cursor_wrong_class(self):
class Foo:
pass
foo = Foo()
with self.assertRaises(TypeError):
cur = sqlite.Cursor(foo)
def CheckCursorWrongClass(self):
class Foo:
pass
foo = Foo()
with self.assertRaises(TypeError):
cur = sqlite.Cursor(foo)
def write(op):
with s.connect('test.db') as db:
if op == 'add':
c = s.Cursor(db)
name = 'test2'
hp=32
pwr=42
SA=22
sp=9992
ty='op2'
c.execute(f"INSERT INTO MEMBERS VALUES('{name}', '{hp}', '{pwr}', '{SA}', '{sp}', '{ty}')")
db.commit()
else:
c = s.Cursor(db)
name = 'test'
c.execute(f"DELETE FROM MEMBERS WHERE name='{name}'")
db.commit()
def CheckCursorWrongClass(self):
class Foo: pass
foo = Foo()
try:
cur = sqlite.Cursor(foo)
self.fail("should have raised a ValueError")
except TypeError:
pass
def read():
with s.connect('test.db') as db:
c = s.Cursor(db)
c.execute("SELECT * FROM MEMBERS")
rows = c.fetchall()
for row in rows:
print(row)
db.commit()
def CheckBpo31770(self):
"""
The interpreter shouldn't crash in case Cursor.__init__() is called
more than once.
"""
def callback(*args):
pass
con = sqlite.connect(":memory:")
cur = sqlite.Cursor(con)
ref = weakref.ref(cur, callback)
cur.__init__(con)
del cur
# The interpreter shouldn't crash when ref is collected.
del ref
support.gc_collect()
def query_db(query, bind_variable=None):
con = db.connect('chinook.db')
cur = db.Cursor(con)
try:
if bind_variable == None:
cur.execute(query)
else:
cur.execute(query, bind_variable)
result = cur.fetchall()
return result
except:
print("Oops!", sys.exc_info()[0], "occured.")
finally:
cur.close()
con.commit()
con.close()
print("Connection Closed")
def __init__(self):
print("Iniciando BackEnd")
self.sqlFile = "data/data.db"
self.connect = sql.connect(self.sqlFile)
self.cursor = sql.Cursor(self.connect)
self.cursor.execute('''CREATE TABLE IF NOT EXISTS Servers (
ServerName CHAR PRIMARY KEY
NOT NULL,
Version CHAR,
NumMods CHAR,
PathMods CHAR,
IsActive BOOLEAN)''')
self.cursor.execute("SELECT * FROM Servers")
if(self.cursor.fetchall() == []):
self.cursor.execute("INSERT INTO Servers VALUES ('Vanilla', '0.0.0', '0', NULL ,1)")
self.connect.commit()
self.OS = pf.system()
def _get_cursor(self):
return sqlite3.Cursor(self._connection)
def __init__(self, file):
super().__init__(file)
self.cursor = sqlite3.Cursor(self)
def main():
con = s.connect('contacts.db')
cursur = s.Cursor(con)
cursur.execute(
'CREATE TABLE IF NOT EXISTS contacts(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,mobile INTEGER NOT NULL,tel INTEGER,email TEXT)'
)
#---------------------------------------------------------------------------------------------------------------
def edit_contact_func():
def edit():
cursur.execute('SELECT * FROM contacts WHERE name=?',
(contact_name_entry_edit.get(), ))
one_result = cursur.fetchone()
out_contact_name_entry.insert(0, one_result[1])
out_contact_number_entry.insert(0, one_result[2])
out_contact_tel_entry.insert(0, one_result[3])
out_contact_email_entry.insert(0, one_result[4])
#-----------------------------------------------------------------------------------------------------------------------
contacts_create_frame = tk.Frame(master=contacts_root,
height=300,
width=300)
contacts_create_frame.grid(row=7, column=2)
contact_name_edit = tk.Label(master=contacts_create_frame,
text='Name :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name_edit.grid(row=0, column=0, sticky='nsew')
contact_name_entry_edit = tk.Entry(master=contacts_create_frame,
text='Name',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name_entry_edit.grid(row=0, column=1, sticky='nsew')
edit_contact_button = tk.Button(master=contacts_create_frame,
text='Edit',
command=edit,
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
edit_contact_button.grid(row=1, column=0, sticky='nsew')
contact_name = tk.Label(master=contacts_create_frame,
text='Name',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name.grid(row=2, column=0, sticky='nsew')
out_contact_name_entry = tk.Entry(master=contacts_create_frame,
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
out_contact_name_entry.grid(row=2, column=1, sticky='nsew')
contact_number = tk.Label(master=contacts_create_frame,
text='Mobile:',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_number.grid(row=3, column=0, sticky='nsew')
out_contact_number_entry = tk.Entry(master=contacts_create_frame,
text='Mobile',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15,
'bold italic'))
out_contact_number_entry.grid(row=3, column=1, sticky='nsew')
contact_tel = tk.Label(master=contacts_create_frame,
text='Tel :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_tel.grid(row=4, column=0, sticky='nsew')
out_contact_tel_entry = tk.Entry(master=contacts_create_frame,
text='Tel',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
out_contact_tel_entry.grid(row=4, column=1, sticky='nsew')
contact_email = tk.Label(master=contacts_create_frame,
text='Email :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_email.grid(row=5, column=0, sticky='nsew')
out_contact_email_entry = tk.Entry(master=contacts_create_frame,
text='Email',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
out_contact_email_entry.grid(row=5, column=1, sticky='nsew')
def contact_update():
if out_contact_name_entry.get(
) == '' or out_contact_name_entry.get(
) == '' or out_contact_tel_entry.get(
) == '' or out_contact_email_entry.get() == '':
pass
else:
cursur.execute(
'UPDATE contacts SET name=?,mobile=?,tel=?,email=? WHERE name=?',
(
out_contact_name_entry.get(),
out_contact_number_entry.get(),
out_contact_tel_entry.get(),
out_contact_email_entry.get(),
contact_name_entry_edit.get(),
))
con.commit()
update_contact_button = tk.Button(master=contacts_create_frame,
text='Update',
command=contact_update,
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
update_contact_button.grid(
row=6,
column=0,
)
#---------------------------------------------------------------------------------------------------------------
def contact_delete_form_frame():
contact_delete_form = tk.Frame(master=contacts_root, )
contact_delete_form.grid(
row=6,
column=0,
)
contact_delete = tk.Label(master=contact_delete_form,
text='Name :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_delete.grid(row=0, column=0)
contact_delete_entry = tk.Entry(master=contact_delete_form,
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_delete_entry.grid(row=0, column=1)
#contact delete function-------------------------------------------------------------------------------
def contact_delete_func():
cursur.execute('DELETE FROM contacts WHERE name=?',
(contact_delete_entry.get(), ))
con.commit()
#Contact delete button for deleting a contact from the list...........
contact_delete_button = tk.Button(master=contact_delete_form,
text='Delete',
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'),
command=contact_delete_func)
contact_delete_button.grid(
row=1,
column=1,
)
#Inserting the contact into the database...............................................................
'''
def validate_float(var):
new_value = var.get()
try:
new_value == '' or float(new_value)
validate.old_value = new_value
except:
var.set(validate.old_value)
validate.old_value = ''
'''
def contact_add_func():
if contact_name_entry.get() == '' or contact_name_entry.get(
) == '' or contact_tel_entry.get() == '' or contact_email_entry.get(
) == '':
print('Empty fields cant be inserted!!!!!!!!1')
else:
contact_insert()
def contact_insert():
cursur.execute('INSERT INTO contacts VALUES(null,?,?,?,?)', (
contact_name_entry.get(),
contact_number_entry.get(),
contact_tel_entry.get(),
contact_email_entry.get(),
))
con.commit()
#This is to a view a contact...............................................................................
#----------------------------------------------------------------------------------------------------------
def contact_view_func():
contacts_view_frame = tk.Frame(master=contacts_root, bg='grey')
contacts_view_frame.grid(
row=8,
column=0,
)
if contact_name_entry.get() == '' or contact_name_entry.get(
) == '' or contact_tel_entry.get() == '' or contact_email_entry.get(
) == '':
contact_name1 = tk.Label(master=contacts_view_frame,
text='Nothing to Display',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name1.grid(row=0, column=0, sticky='nsew')
else:
contact_name1 = tk.Label(master=contacts_view_frame,
text='Contact-Details',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name1.grid(row=0, column=0, sticky='nsew')
contact_name1 = tk.Label(master=contacts_view_frame,
text='Name :' + contact_name_entry.get(),
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name1.grid(row=1, column=0, sticky='nsew')
contact_number1 = tk.Label(master=contacts_view_frame,
text='Mobile :' +
contact_number_entry.get(),
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_number1.grid(row=2, column=0, sticky='nsew')
contact_tel1 = tk.Label(master=contacts_view_frame,
text='Tel :' + contact_tel_entry.get(),
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_tel1.grid(row=3, column=0, sticky='nsew')
contact_email1 = tk.Label(master=contacts_view_frame,
text='Email :' +
contact_email_entry.get(),
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_email1.grid(row=4, column=0, sticky='nsew')
#creating the list of all contacts....................
#--------------------------------------------------------------------------------------------------------------
def list_of_all_contacts_func():
contact_list_frame = tk.Frame(master=contacts_root,
bg='grey',
height=200,
width=300)
contact_list_frame.grid(
row=0,
column=2,
)
contact_list_label = tk.Label(master=contact_list_frame,
text='Contact List',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_list_label.grid(row=0, column=0, sticky='nsew')
#making a scrollbar for the list..................................
scrollbar = tk.Scrollbar(master=contact_list_frame, )
scrollbar.grid(row=0, column=1, sticky='w')
#....................................................................
listbox = tk.Listbox(master=contact_list_frame,
bg='white',
font=('Verdana', 15, 'bold '),
yscrollcommand=scrollbar.set)
'''listbox.insert("ID")
listbox.insert("Contact Name")'''
cursur.execute('SELECT * FROM contacts')
result = cursur.fetchall()
#value for the calling function
name = contact_name_entry.get()
for (id, name, mobile, tel, email) in result:
#listbox.insert(id,)
listbox.insert(id, name)
listbox.grid(row=1, column=0, sticky='nsew')
scrollbar.configure(command=listbox.yview)
#Refresh buton for refreshing the contact list........................................
refresh_button = tk.Button(master=contact_list_frame,
text='Refresh',
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'),
command=list_of_all_contacts_func)
refresh_button.grid(row=1, column=0, sticky='ne')
#--------------------------------------------------------------------------------------------------------------
'''
def show_details_func(name):
cursur.execute('SELECT * FROM contacts WHERE name=?',(name,))
one_result=cursur.fetchone()
lstb=tk.Listbox(master=)
back_button=tk.Button(master=listboxF,text='Back',command=list_of_all_contacts_func,bg='#dec4ef',fg='black',bd='2',relief='sunken',font=('Verdana',15,'bold italic'))
back_button.grid(row=6,column=4)
'''
#---------------------------------------------------------------------------------------------------------------
contacts_frame = tk.Frame(
master=contacts_root,
bg='#e8edb6',
)
contacts_frame.grid(row=0, column=0, sticky='nesw')
contacts_add = tk.Button(master=contacts_frame,
text="Add",
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Times', 15, 'bold italic'),
command=contact_add_func)
contacts_add.grid(row=0, column=0, sticky='nsew')
contacts_view = tk.Button(master=contacts_frame,
text="View",
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Times', 15, 'bold italic'),
command=contact_view_func)
contacts_view.grid(row=0, column=1, sticky='nsew')
contacts_all = tk.Button(master=contacts_frame,
text="Contact List",
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Times', 15, 'bold italic'),
command=list_of_all_contacts_func)
contacts_all.grid(row=0, column=2, sticky='nsew')
contacts_remove = tk.Button(master=contacts_frame,
text="Remove",
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Times', 15, 'bold italic'),
command=contact_delete_form_frame)
contacts_remove.grid(row=0, column=3, sticky='nsew')
contacts_update = tk.Button(master=contacts_frame,
text="Edit",
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Times', 15, 'bold italic'),
command=edit_contact_func)
contacts_update.grid(row=0, column=4, sticky='nsew')
contact_ok_button = tk.Button(master=contacts_frame,
text='OK',
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'),
command=main_again)
contact_ok_button.grid(row=0, column=5, sticky='nsew')
#Creating contact form to take user entries......
#------------------------------------------------------------------------------------------------------
contacts_create_frame = tk.Frame(master=contacts_frame, )
contacts_create_frame.grid(row=1, column=0, sticky='nesw')
contact_name = tk.Label(master=contacts_create_frame,
text='Name :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name.grid(row=0, column=0, sticky='nesw')
contact_name_entry = tk.Entry(master=contacts_create_frame,
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name_entry.grid(row=0, column=1, sticky='nesw')
contact_number = tk.Label(master=contacts_create_frame,
text='Mobile:',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_number.grid(row=1, column=0, sticky='nesw')
contact_number_entry = tk.Entry(master=contacts_create_frame,
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_number_entry.grid(row=1, column=1, sticky='nesw')
contact_tel = tk.Label(master=contacts_create_frame,
text='Tel :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_tel.grid(row=2, column=0, sticky='nesw')
contact_tel_entry = tk.Entry(master=contacts_create_frame,
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_tel_entry.grid(row=2, column=1, sticky='nesw')
contact_email = tk.Label(master=contacts_create_frame,
text='Email :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_email.grid(row=3, column=0, sticky='nesw')
contact_email_entry = tk.Entry(master=contacts_create_frame,
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_email_entry.grid(row=3, column=1, sticky='nesw')
#Creating a sumbit button for the contact.....
contact_sumbit_button = tk.Button(master=contacts_create_frame,
text='View',
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'),
command=contact_view_func)
contact_sumbit_button.grid(row=4, sticky='nesw')
contact_sumbit_button = tk.Button(master=contacts_create_frame,
text='Add',
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'),
command=contact_add_func)
contact_sumbit_button.grid(row=4, column=1, sticky='nesw')
def cursor(self, cursorClass=None):
"""
:type cursorClass: type | None
:rtype: sqlite3.Cursor
"""
return sqlite3.Cursor()
def output_to_sqlite(dict_lists, sqlite_path):
sql_views = [
"""CREATE VIEW APPS_TO_MAPS AS
SELECT *
FROM WEB_APPS WA
LEFT JOIN
WEB_MAPS WM ON WA.WEBMAP_ID = WM.ITEM_ID;""",
"""CREATE VIEW BROKEN_LAYERS AS
SELECT REL.WEBMAP_TITLE,
REL.LAYER_NAME,
FS.ITEM_NAME AS FS_NAME,
WM.SHARED AS WM_SHARED,
FS.SHARED AS FS_SHARED
FROM MAP_FS_REL REL
LEFT JOIN
WEB_MAPS WM ON REL.WEBMAP_ID = WM.ITEM_ID
LEFT JOIN
FEATURE_SERVICES FS ON REL.LAYER_ITEM_ID = FS.ITEM_ID
WHERE WM.SHARED <> FS.SHARED AND
FS.SHARED = 'Private';""", """CREATE VIEW ALL_ITEMS AS
SELECT ITEM_TYPE,
ITEM_NAME,
FOLDER,
SHARED,
EVERYONE,
ORG,
GROUPS,
OWNER,
CREATEDATE,
MODATE,
ITEM_ID,
SIZE,
CONTENT_STATUS
FROM FEATURE_SERVICES
UNION ALL
SELECT ITEM_TYPE,
ITEM_NAME,
FOLDER,
SHARED,
EVERYONE,
ORG,
GROUPS,
OWNER,
CREATEDATE,
MODATE,
ITEM_ID,
SIZE,
CONTENT_STATUS
FROM WEB_MAPS
UNION ALL
SELECT ITEM_TYPE,
ITEM_NAME,
FOLDER,
SHARED,
EVERYONE,
ORG,
GROUPS,
OWNER,
CREATEDATE,
MODATE,
ITEM_ID,
SIZE,
CONTENT_STATUS
FROM WEB_APPS
UNION ALL
SELECT ITEM_TYPE,
ITEM_NAME,
FOLDER,
SHARED,
EVERYONE,
ORG,
GROUPS,
OWNER,
CREATEDATE,
MODATE,
ITEM_ID,
SIZE,
CONTENT_STATUS
FROM OTHER_ITEMS;
""", """CREATE VIEW SHARED_EVERYONE AS
SELECT *
FROM ALL_ITEMS
WHERE EVERYONE = 1
ORDER BY OWNER;""", """CREATE VIEW GROUPS_ZEROMEMBERS AS
SELECT *
FROM GROUPS
WHERE MEMBERCOUNT = 0;""", """CREATE VIEW USERS_NEVERLOGIN AS
SELECT *
FROM USERS
WHERE LAST_LOGIN = -1;""", """CREATE VIEW USERS_VIEW AS
SELECT USERNAME,
FIRSTNAME,
LASTNAME,
LEVEL,
ROLE,
CREATED,
LAST_LOGIN,
ROUND(JULIANDAY({}, 'unixepoch','localtime') - JULIANDAY(LAST_LOGIN),-2) AS DAYS_STAGNANT,
DESCRIPTION
FROM USERS;""".format(time.time()), """CREATE VIEW OTO_FS_MAP AS
SELECT LAYER_ITEM_ID,
WEBMAP_ID
FROM MAP_FS_REL
GROUP BY LAYER_ITEM_ID,
WEBMAP_ID;""", """CREATE VIEW SHARING_GROUPS AS
SELECT
ITEM_ID,
COUNT(*) AS GROUP_COUNT,
GROUP_CONCAT(GROUP_NAME) AS GROUPS
FROM SHARING
GROUP BY ITEM_ID""", """
CREATE VIEW STORAGE_ESTIMATE AS
SELECT
AI.ITEM_ID,
AI.ITEM_NAME,
AI.ITEM_TYPE,
AI.FOLDER,
AI.OWNER,
REPLACE(CATEGORIES, '/Categories/','') AS CATEGORY,
SIZE/1024/1024 AS MB,
CASE WHEN ITEM_TYPE = 'Feature Service' THEN SIZE/1024.0/1024.0 ELSE 0 END AS FS_MB,
CASE WHEN ITEM_TYPE = 'Feature Service' THEN SIZE/1024.0/1024.0*.25 ELSE 0 END AS FS_CPM,
CASE WHEN ITEM_TYPE <> 'Feature Service' THEN SIZE/1024.0/1024.0 ELSE 0 END AS OTHER_MB,
CASE WHEN ITEM_TYPE <> 'Feature Service' THEN SIZE/1024.0/1024.0/1024.0*1.2 ELSE 0 END AS OTHER_CPM,
CASE WHEN ITEM_TYPE <> 'Feature Service' THEN SIZE/1024.0/1024.0/1024.0*1.2 ELSE 0 END +
CASE WHEN ITEM_TYPE = 'Feature Service' THEN SIZE/1024.0/1024.0*.25 ELSE 0 END
AS TOTAL_CPM
FROM
ALL_ITEMS AI
LEFT JOIN
(SELECT
ITEM_ID,
GROUP_CONCAT(CATEGORY) AS CATEGORIES,
COUNT(*) AS TOTALCATS
FROM CATEGORIES
GROUP BY ITEM_ID) CATS
ON AI.ITEM_ID = CATS.ITEM_ID
LEFT JOIN
USERS USERS
ON AI.OWNER = USERS.USERNAME
WHERE USERS.USERNAME IS NOT NULL
""", """CREATE VIEW ITEM_CTGS as
SELECT
AI.ITEM_NAME,
AI.ITEM_TYPE,
AI.OWNER,
AI.FOLDER,
AI.SHARED,
AI.SIZE/1024/1024 AS MB,
TT.TAGS,
SG.GROUPS,
CATS.CATEGORIES,
AI.ITEM_ID
FROM
ALL_ITEMS AI
LEFT JOIN
(SELECT
ITEM_ID,
GROUP_CONCAT(REPLACE(CATEGORY,'/Categories/','')) AS CATEGORIES,
COUNT(*) AS TOTALCATS
FROM CATEGORIES
GROUP BY ITEM_ID) CATS
ON
AI.ITEM_ID = CATS.ITEM_ID
LEFT JOIN
(SELECT
ITEM_ID,
GROUP_CONCAT(TAG) AS TAGS
FROM TAGS
GROUP BY ITEM_ID) TT
ON
AI.ITEM_ID = TT.ITEM_ID
LEFT JOIN
SHARING_GROUPS SG
ON
AI.ITEM_ID = SG.ITEM_ID"""
]
conn = sqlite3.connect(sqlite_path)
for key in dict_lists:
dlist_to_sqlite(dict_lists[key], conn, key)
cursor = sqlite3.Cursor(conn)
for statement in sql_views:
cursor.execute(statement)
conn.close()
def mainfunc():
con = s.connect('contacts.db')
cursur = s.Cursor(con)
cursur.execute(
'CREATE TABLE IF NOT EXISTS contacts(name TEXT NOT NULL,mobile INTEGER NOT NULL,tel INTEGER,email TEXT)'
)
#This is to a view a contact
#----------------------------------------------------------------------------------------------------------
def contact_view_func():
contacts_view_frame = tk.Frame(master=contacts_root, bg='grey')
contacts_view_frame.pack(side='right')
contact_name1 = tk.Label(master=contacts_view_frame,
text='Name :' + contact_name_entry.get(),
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name1.grid(row=0, column=0)
contact_number1 = tk.Label(master=contacts_view_frame,
text='Mobile :' +
contact_number_entry.get(),
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_number1.grid(row=1, column=0)
contact_tel1 = tk.Label(master=contacts_view_frame,
text='Tel :' + contact_tel_entry.get(),
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_tel1.grid(row=2, column=0)
contact_email1 = tk.Label(master=contacts_view_frame,
text='Email :' + contact_email_entry.get(),
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_email1.grid(row=3, column=0)
#This to create a frame for deleting a contact from the list.
#----------------------------------------------------------------------------------------------------------
def contact_delete_form_frame():
contact_delete_form = tk.Frame(master=contacts_root, )
contact_delete_form.pack(side='top')
contact_delete = tk.Label(master=contact_delete_form,
text='Name :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_delete.grid(row=0, column=0)
contact_delete = tk.Entry(master=contact_delete_form,
text='',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_delete.grid(row=0, column=1)
#Contact delete button for deleting a contact from the list...........
contact_dlete_button = tk.Button(master=contacts_delete_form,
text='Delete',
bg='#11c7f9',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'),
command=contact_delete_func)
#creating the list of all contacts....................
#--------------------------------------------------------------------------------------------------------------
def list_of_all_contacts_func():
contact_list_frame = tk.Frame(master=contacts_root, bg='grey')
contact_list_frame.pack(side='right')
listbox = tk.Listbox(
master=contact_list_frame,
bg='grey',
)
listbox.grid(row=0, column=0)
listbox.insert("ID", )
listbox.insert("Contact Name")
cursur.execute('SELECT * FROM contacts')
result = cursur.fetchall()
#value for the calling function
name = contact_name_entry.get()
for (id, name, mobile, tel, email) in result:
listbox.insert(id, )
listbox.insert(name, )
detail_button = tk.Button(master=listbox,
text='Details',
command=show_details_func
and listbox.destroy(),
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
detail_button.pack(side='right')
#---------------------------------------------------------------------------------------------------------------
def update_contact_func():
name = contact_name_entry.get()
mobile = contact_number_entry.get()
tel = contact_tel_entry.get()
email = contact_email_entry.get()
show_details_func(name)
contacts_create_frame = tk.Frame(master=contacts_root, )
contacts_create_frame.pack(side='top', )
contact_name = tk.Label(master=contacts_create_frame,
text='Name :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_name.grid(row=0, column=0)
out_contact_name_entry = tk.Entry(master=contacts_create_frame,
text='Name',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
out_contact_name_entry.grid(row=0, column=1)
contact_number = tk.Label(master=contacts_create_frame,
text='Mobile:',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_number.grid(row=1, column=0)
out_contact_number_entry = tk.Entry(master=contacts_create_frame,
text='Mobile',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15,
'bold italic'))
out_contact_number_entry.grid(row=1, column=1)
contact_tel = tk.Label(master=contacts_create_frame,
text='Tel :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_tel.grid(row=2, column=0)
out_contact_tel_entry = tk.Entry(master=contacts_create_frame,
text='Tel',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
out_contact_tel_entry.grid(row=2, column=1)
contact_email = tk.Label(master=contacts_create_frame,
text='Email :',
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
contact_email.grid(row=3, column=0)
out_contact_email_entry = tk.Entry(master=contacts_create_frame,
text='Email',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
out_contact_email_entry.grid(row=3, column=1)
out_contact_name_entry.set(name)
out_contact_number_entry.set(mobile)
out_contact_tel_entry.set(tel)
out_contact_email_entry.set(email)
def contact_update():
cursur.execute(
'INSERT INTO contacts VALUES(null,?,?,?,?)',
(out_contact_name_entry.get(), out_contact_number_entry.get(),
out_contact_tel_entry.get(), out_contact_email_entry.get()))
con.commit()
update_contact_button = tk.Button(master=listbox,
text='Update',
command=show_details_func
and listbox.destroy(),
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
#--------------------------------------------------------------------------------------------------------------
#These are database functions...............for accessing the database..........................................
#---------------------------------------------------------------------------------------------------------------
def contact_delete_func():
cursur.execute('DELETE FROM contacts WHERE name=?',
(contact_name_entry.get(), ))
con.commit()
def show_details_func(name):
cursur.execute(
'SELECT name,mobile,tel,email FROM contacts WHERE name=?',
(name, ))
one_result = cursur.fetchone()
listboxF = tk.Listbox(master=contacts_root, )
listboxF.pack(side='bottom')
for (id, name, mobile, tel, email) in one_result:
listboxF.insert('ID :', id)
listboxF.insert('Name :', name)
listboxF.insert('Mobile :', mobile)
listboxF.insert('Tel :', tel)
listboxF.insert('Email :', email)
back_button = tk.Button(master=listboxF,
text='Back',
command=list_of_all_contacts_func,
bg='#dec4ef',
fg='black',
bd='2',
relief='sunken',
font=('Verdana', 15, 'bold italic'))
back_button.grid(row=6, column=4)
'''
def contact_list():
cursur.execute('SELECT * FROM contacts')
re=cursur.fetchall()
print(re[1][1])
'''
def contact_insert():
cursur.execute('INSERT INTO contacts VALUES(?,?,?,?)',
(contact_name_entry.get(), contact_number_entry.get(),
contact_tel_entry.get(), contact_email_entry.get()))
con.commit()
#!/usr/bin/python
import sqlite3 as db
import unicodedata
con = db.connect('chinook.db')
cur = db.Cursor(con)
#cur.execute("INSERT INTO genres(Name) VALUES ('Gangnam Style')");
#cur.execute("UPDATE genres SET Name='Ganjam Style' where GenreId=27");
#print(cur.rowcount)
albumid = 65
#cur.execute("select COUNT(*) from albums a inner join tracks t on a.AlbumId=t.AlbumId where a.AlbumId="+str(a))
#print(cur.fetchone()[0])
#
#cur.execute("select Title from albums where AlbumId="+str(a))
#print(cur.fetchone()[0])
#
#cur.execute("select ar.ArtistId,ar.Name from albums a inner join artists ar on a.ArtistId=ar.ArtistId where a.AlbumId="+str(a))
#for i in cur:
# print(i[0])
# print(unicodedata.normalize('NFKD', i[1]).encode('ascii','ignore'))
#
#cur.execute("Select t.TrackId,t.Name,g.Name from albums a inner join artists ar on a.ArtistId=ar.ArtistId inner join tracks t on t.AlbumId=a.AlbumId inner join genres g on t.GenreId=g.GenreId where a.AlbumId="+str(a))
#for i in cur:
# print(i[0])
# print(unicodedata.normalize('NFKD', i[1]).encode('ascii','ignore'))
# print(unicodedata.normalize('NFKD', i[2]).encode('ascii','ignore'))
album = {}
cur.execute("select Title from albums where AlbumId=" + str(albumid))
#album['album_name']=cur.fetchone()[0]
import sqlite3 as s
import os
if(not os.path.isfile('./test.db')):
print('yes')
with s.connect('test.db') as db:
c = s.Cursor(db)
c.execute('CREATE TABLE MEMBERS(name text, hp integer, power integer, SApower integer, speed integer, type text)')
db.commit()
def read():
with s.connect('test.db') as db:
c = s.Cursor(db)
c.execute("SELECT * FROM MEMBERS")
rows = c.fetchall()
for row in rows:
print(row)
db.commit()
def write(op):
with s.connect('test.db') as db:
if op == 'add':
c = s.Cursor(db)
name = 'test2'
hp=32
pwr=42
SA=22
sp=9992
ty='op2'
c.execute(f"INSERT INTO MEMBERS VALUES('{name}', '{hp}', '{pwr}', '{SA}', '{sp}', '{ty}')")
import sqlite3
# we connect to the database file, or create the file if not exists
db = sqlite3.connect('productdb.db')
# sql command to select all
select_all = 'SELECT * FROM products'
# create the cursor object to do the database operation
cursor = sqlite3.Cursor(db)
# let cursor do the command
cursor.execute(select_all)
# get all the data from the cursor operation
products = cursor.fetchall()
print(products[0][1])
print(products[1][1])
print("{0} - IDR. {1}".format(products[0][1], products[0][3]))
for item in products:
print("{0} - IDR. {1} - Stock available: {2}".format(item[1], item[3], item[4]))
def get_product_by_id(id):
# example: SELECT * FROM products WHERE id=1
select_product = 'SELECT * FROM products WHERE id={0}'.format(id)
cursor.execute(select_product)
product = cursor.fetchone()
import sqlite3 as s
con=s.connect('contacts.db')
cursur=s.Cursor(con)
cursur.execute('CREATE TABLE IF NOT EXISTS contacts(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,mobile INTEGER,tel INTEGER,email TEXT)')
def contact_insert(name,mobile,tel,email):
cursur.execute('INSERT INTO contacts VALUES(?,?,?,?,?)',(name,mobile,tel,email))
con.commit()
def contact_delete(name):
cursur.execute('DELETE FROM contacts WHERE name=?',(name,))
con.commit()
def contact_list():
cursur.execute('SELECT * FROM contacts')
re=cursur.fetchall()
print(re[1][1])
def contact_update(**kwargs):
cursur.execute('UPDATE contacts SET name=? email=? mobile=? where id=?',(name,email,mob))
con.commit()
cursur.close()
con.close()
import re
import sqlite3 as sqlt
def add_usuario():
print("Criando novo usuário")
nome = ""
while re.match(r"\w+", nome) is None:
nome = input("Digite o nome: ")
nome.strip
nome.strip()
senha = ""
while re.match(r"\w+", senha) is None:
senha = input("Digite a senha: ")
csr.execute("INSERT INTO usuarios VALUES ('{0}', '{1}')".format(
nome, senha))
print("{0} adicionado".format(nome))
conn.commit()
conn = sqlt.connect("banco.db")
csr = sqlt.Cursor(conn)
csr.execute("CREATE TABLE IF NOT EXISTS usuarios (cadastro text, senha text)")