DATA ANALYTICS REFERENCE DOCUMENT |
|
---|---|
Document Title: | Applied Databases - Python |
Document No.: | 1553629016 |
Author(s): | Gerhard van der Linde, Rita Raher |
Contributor(s): |
REVISION HISTORY
Revision | Details of Modification(s) | Reason for modification | Date | By |
---|---|---|---|---|
0 | Draft release | Applied Databases - Python | 2019/03/26 19:36 | Gerhard van der Linde |
Employee ID | Name | Dept | Salary |
---|---|---|---|
100 | John | HR | 25500 |
101 | Mary | R&D | 44500 |
102 | Bill | R&D | 43000 |
103 | Tom | Sales | 40000 |
SELECT * from employees WHERE Salary > 42000; SELECT * from employees WHERE Salary < 30000;
my1stVariable = "Hello World" my2ndVariable = 1 print(my1stVariable) #Hello World my2ndVariable + 4 print(my2ndVariable) # 1
my2ndVariable = 1 x = my2ndVariable + 4 print(my2ndVariable) # 1 print(x) # 5 age = 21 age = age + 1 print(age) # 22
age = 17 if(age > 17): print("OK") print(finished) # finsied
age = 17 if(age > 17): print("OK") elif(age < 18): print("Nok") print(finished) #Nok # finished
temp = 37 if(temp >37): print("Hot") elif(temp <37): print("Cold") else: print("OK") print("Finished") # ok # finished
name = input("Enter name") # Tom email = name + "@gmit.ie" print(email) # Tom@gmit.ie
salary = input("Enter salary") # 30000 salary = int(salary) salary = salary + 100 print(salary)
i = 1 while(i <=5): print(i) i+=1 # i = i +1 # 1 # 2 # 3 # 4 # 5
answer = "5" while True: guess = input("Pick a number between 1 & 10") if(guess==answer): print("Correct!") break print("end")
myArr = ["Jan", "Feb", "March", "April"] print(myArr) #['Jan', 'Feb', 'March', 'April'] print(myArr[0]) # jan print(len(myArr)) #4
myArr = ["Jan", "Feb", "March", "April"] myArr.append("May") print(myArr) ##['Jan', 'Feb', 'March', 'April', "May"]
name = ["Tom", "John", "Mary", "Bob"] for name in names: print(name + "@gmit.ie") # Tom@gmit.ie # John@gmit.ie # Mary@gmit.ie # Bob@gmit.ie myArr = [1, 5, 12] for x in myArr: print(x+1) # 2 # 6 # 13 print(myArr) #[1, 5, 12]
def printMonths(): print("Jan, Feb, Mar") def printDays(): print("Mon, Tue, Wed") printDays() # Mon, Tue, Wed printMonths() # Jan, Feb, Mar
def printMonths(): print("Jan, Feb, Mar") def main(): printMonths() if __name__ =="__main__": # execute only if run as a script main()
print("Hello World") # Hello World print("Test") # Test s = "This is a string" print(len(s)) # 16
def checkAge(age): if age < 18: return "Too Young" return "Accepted" def main(): name = input("Enter:") age = int(input("Enter Age:")) print(name, "is", checkAge(age)) if __name__="__main__": # execute only if run as a script main()
A local variable is a variable that is given local scope. Local variable references in the function or block in which it is declared override the same variable name in the larger scope.
def checkAge(age): limit = 18 if age < limit: return "Too Young" return "Accepted" def main(): name = input("Enter:") age = int(input("Enter Age:")) print(name, "is", checkAge(age), limit) if __name__="__main__": # execute only if run as a script main()
def checkAge(age): limit = 18 if age < limit: return "Too Young" return "Accepted" def main(): limit= "Finished" name = input("Enter:") age = int(input("Enter Age:")) print(name, "is", checkAge(age), limit) if __name__="__main__": # execute only if run as a script main()
def incrementAge(age): age += 1 print(age) # 25 def main(): age = 24 incrementAge(age) print(age) # 24 if __name__="__main__": # execute only if run as a script main()
Declaring the variable outside of the function and using the keword global to make it a global variable
age = 24 def incrementAge(age): # access using the keyword "Global" global age age += 1 print(age) # 25 def main(): incrementAge(age) print(age) #25 if __name__="__main__": # execute only if run as a script main()
conn = pymysql.connect( "localhost", "root", "root", "school", cursorclass=pysql.cursors.DictCursor)
conn = pymysql.connect( "localhost", "root", "root", "school", cursorclass=pysql.cursors.DictCursor, password="root", host="localhost", db="school", port=3306)
query = "SELECT * FROM subject" with conn: cursor = conn.cursor() cursor.execute(query) subjects = cursor.fetchall() for s in sujects: print(s["Name"])
query = "SELECT * FROM subject WHERE teacher LIKE %s" with conn: cursor = conn.cursor() cursor.execute(query, ("Ms.%")) subjects = cursor.fetchall() for s in subjects: print(s["Name"])
ins = "Insert INTO subject (Name, Teacher, OnLeavingCert) VALUE(%s, %s, %s)" with conn: cursor = conn.cursor() cursor.execute(ins, ("Maths", "Ms.Jones", 1)) conn.commit() # commit to database to make a change
try and except block for error messages
ins = "Insert INTO subject (Name, Teacher, OnLeavingCert) VALUE(%s, %s, %s)" with conn: try: cursor = conn.cursor() cursor.execute(ins, ("Maths", "Ms.Jones", 1)) conn.commit() print("Insert successful") except: print("Maths already exists")
name = "Maths" teacher = "Ms.Jones" lc =1 with conn: try: cursor = conn.cursor() cursor.execute(query, (name, teacher, lc)) conn.commit() print("Insert Successful") except pymysql.err.InternalError as e: print("Internal Error", e) except pymysql.err.IntegrityError as e: print("Error", name, "already exists") except Exception as e: print("error", e)
query = "DELETE FROM subject WHERE name = %s" name = "Maths" with conn: try: cursor = conn.cursor() rowAffected = cursor.execute(query, (name)) conn.commit() if(rowsAffected ==0): print("Nothing deleted - ", name , "never existed") else: print(rowAffected, "row(s) deleted") except Exception as e: print("error", e)
query = "UPDATE subject SET teacher = %s WHERE NAME = %s" subject = "Maths" newTeacher = "Mr.Murphy" with conn: try: cursor = conn.cursor() rowsAffected = cursor.execute(query, (newTeacher, subject)) conn.commit() if(rowsAffected ==0): print(subject, "not updated") else: print(subject, "now taught by", newTeacher) except Exception as e: print("error", e)