· 4 years ago · Feb 20, 2021, 04:44 AM
1
2import sqlite3
3from sqlite3 import Error
4
5# connection example
6"""
7def create():
8 mydb = connector(
9 host = "localhost",
10 user = "root",
11 passwd = "pass",
12 database = "mailing_list"
13 )
14
15 cursor = mydb.cursor() """
16
17
18def create_db(file):
19 conn = None
20 try:
21 conn = sqlite3.connect(file)
22 print(sqlite3.version)
23 except Error as e:
24 print(e)
25 finally:
26 if conn:
27 conn.close()
28
29
30def create_table(file, table_name):
31 conn = sqlite3.connect(file)
32 c = conn.cursor()
33 c.execute(''' CREATE TABLE IF NOT EXISTS {}
34 (employeeID integer PRIMARY KEY NOT NULL,
35 lastName varchar(255),
36 firstName varchar(255),
37 address varchar(255),
38 salary integer)'''.format(table_name))
39 conn.commit()
40 conn.close()
41
42
43def add_employee(conn, employeeID, lastName, firstName, address, salary):
44 c = conn.cursor()
45 # emoployeeID has to be unique, so error occurs when you try to create another
46 # try and except blocks are an easy system
47 # the extra quotes is because each parameter has to be a string
48 c.execute(r"INSERT INTO employees VALUES ({0}, '{1}', '{2}', '{3}', {4})".format(
49 employeeID, lastName, firstName, address, salary))
50 conn.commit()
51
52
53def read_employees(conn):
54 print("\nCurrent Employees: ")
55 c = conn.cursor()
56 # cursor c becomes an interator
57 # alternatively you can use "c.fetchone()" to get the next, and keep calling it
58 for row in c.execute("SELECT * FROM employees"):
59 print(row)
60
61
62def remove_employee(conn, employeeID):
63 c = conn.cursor()
64 # no error if no employee matches the condition, just nothing happens
65 c.execute(r"DELETE from employees WHERE employeeID = '{}'".format(employeeID))
66 conn.commit()
67
68
69def adjust_salary(conn, employeeID, salary):
70 c = conn.cursor()
71 # remember that if you don't add the WHERE condition, all rows will be updated
72 c.execute(r"UPDATE employees SET salary = '{0}' WHERE employeeID = '{1}'".format(salary, employeeID))
73 conn.commit()
74
75
76def main():
77 #rawstring format incase you make it a directory and have like /.../.../ it will ignore it"
78 file = r'test.db'
79 create_db(file)
80 create_table(file, 'employees')
81
82 conn = sqlite3.connect(file)
83
84 add_employee(conn, 1000, 'last', 'first', 'place', 29324)
85 read_employees(conn)
86
87 remove_employee(conn, 1000)
88 read_employees(conn)
89
90 adjust_salary(conn, 999, 1000000)
91 read_employees(conn)
92
93 #close connection after everything
94 conn.close()
95
96
97if __name__ == '__main__':
98 main()
99
100
101
102
103
104# why sql? big storage, better than a file if you want to keep track of
105#like all scores or something idk
106