~~NOTOC~~
|<100% 25% - >|
^  \\ DATA ANALYTICS REFERENCE DOCUMENT\\ \\   ^^
^  Document Title:|Applied Databases - Python|
^  Document No.:|1553629016|
^  Author(s):|Gerhard van der Linde, Rita Raher|
^  Contributor(s):|  |



**REVISION HISTORY**
|< 100% 10% - - 10% 17% 10% >|
^  \\  Revision\\  \\  ^\\ Details of Modification(s)^\\ Reason for modification^  \\ Date  ^  \\ By  ^
|  [[:doku.php?id=modules:52553_python&do=revisions|0]]  |Draft release|Applied Databases - Python|  2019/03/26 19:36  |  Gerhard van der Linde  |


----

====== Topic8 - Python I ======

===== Databases vs Program =====

^Employee ID^Name^Dept^Salary^
^100|John|HR|25500|
^101|Mary|R&D|44500|
^102|Bill|R&D|43000|
^103|Tom|Sales|40000|

<code mysql>

SELECT * from employees WHERE Salary > 42000;
SELECT * from employees WHERE Salary < 30000;

</code>


===== Variables =====

  * Variables are names areas in the computer's memory that store values.

<code python variables.py>
my1stVariable = "Hello World"
my2ndVariable = 1

print(my1stVariable)
#Hello World

my2ndVariable + 4

print(my2ndVariable)
# 1


</code>

  * Variables are named areas in the computer's memory that store values.

<code python variables2.py>

my2ndVariable = 1
x = my2ndVariable + 4


print(my2ndVariable)
# 1
print(x)
# 5

age = 21
age = age + 1

print(age) # 22
</code>



===== IF Statements =====

<code python ifstatements.py>

age = 17

if(age > 17):
   print("OK")
   
print(finished)
# finsied
</code>



<code python ifstatements1.py>

age = 17

if(age > 17):
   print("OK")
elif(age < 18):
   print("Nok")
print(finished)
#Nok
# finished
</code>

<code python ifstatements2.py>

temp = 37 

if(temp >37):
   print("Hot")
elif(temp <37):
   print("Cold")
else:
   print("OK")
print("Finished")
# ok
# finished

</code>

===== input =====

<code python input.py>

name = input("Enter name") # Tom
email = name + "@gmit.ie"
print(email)
# Tom@gmit.ie
</code>


<code python salary.py>

salary = input("Enter salary") # 30000
salary = int(salary)
salary = salary + 100

print(salary)

</code>

===== WHILE statement =====

<code python while.py>

i = 1
while(i <=5):
   print(i)
   i+=1
   # i = i +1
# 1  
# 2 
# 3 
# 4 
# 5 
</code>

<code python whilebreak.py>

answer = "5"

while True:
   guess = input("Pick a number between 1 & 10")
   if(guess==answer):
     print("Correct!")
     break
 
print("end")
    
</code>

===== Arrays =====

<code python array.py>
myArr = ["Jan", "Feb", "March", "April"]
print(myArr)
#['Jan', 'Feb', 'March', 'April']

print(myArr[0])
# jan

print(len(myArr))
#4


</code>


===== Append() =====

<code python append.py>
myArr = ["Jan", "Feb", "March", "April"]
myArr.append("May")

print(myArr)
##['Jan', 'Feb', 'March', 'April', "May"]

</code>

===== FOR Statement =====

<code python forloop.py>
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]

</code>

===== User-defined functions =====

<code python userfunctions.py>
def printMonths():
   print("Jan, Feb, Mar")
   
def printDays():
   print("Mon, Tue, Wed")

printDays()
# Mon, Tue, Wed
   
printMonths()
# Jan, Feb, Mar
</code>

===== name =====

<code python userfunctions.py>
def printMonths():
   print("Jan, Feb, Mar")
   
def main():
   printMonths()
   
if __name__ =="__main__":
   # execute only if run as a script
   main() 
</code>

===== Parameters =====

<code python parameters.py>

print("Hello World")
# Hello World

print("Test")
# Test


s = "This is a string"

print(len(s))
# 16


</code>

<code python parameters1.py>

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()
   

</code>

{{:modules:screenshot_2019-03-29_at_17.36.57.png?200|}}


===== Local Variables =====
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.

<code python localvariables.py>

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()
   
</code>

<code python localvariables1.py>

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()
   
</code>

===== Global Variables =====


<code python globalvariables.py>

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()
   
</code>

Declaring the variable outside of the function and using the keword global to make it a global variable

<code python globalvariables.py>
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()
   
</code>

====== Topic9 - Python II ======

===== PyMySQL =====

  * MySQLdb
  * mysql.connector
  * PyMySQL

==== connect() ====

  * The connect() function connects to a MySQL database.
  * **host** - host where the database server is located
  * **user** - username to log in as
  * **password** - Password to use
  * **db** - Database to use
  * **port** - Port to use
  * **cursorclass**- Custom cursor class to use

==== Connecting to the Database ====
<code python>
conn = pymysql.connect( "localhost", "root", "root", "school", 
                 cursorclass=pysql.cursors.DictCursor)
</code>

<code python>
conn = pymysql.connect( "localhost", "root", "root", "school", 
                 cursorclass=pysql.cursors.DictCursor,
                 password="root", 
                 host="localhost",
                 db="school",
                 port=3306)
</code>

{{:modules:screenshot_2019-04-13_at_12.13.38.png?400|}}

==== Executing a query ====

<code python>
query = "SELECT * FROM subject"

with conn: 
   cursor = conn.cursor()
   cursor.execute(query)
   subjects = cursor.fetchall()
   for s in sujects:
      print(s["Name"])
      
</code>


{{:modules:screenshot_2019-04-13_at_12.18.40.png?400|}}
<code python query.py>
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"])
</code>

==== Inserting new data ====
<code python insertquery.py>
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   
</code>
try and except block for error messages
<code python insertquery2.py>
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")
</code>

==== Exceptions ====

<code python>

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)

</code>

==== Deleting Data ====

<code python>

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)
</code>

==== Updating Data ====


<code python>

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)
</code>

==== Installing PyMySQL ====

  * open command prompt or terminal 
  * type conda install pymysql
  * create a new py file

<code>
</code>

====== Topic10 - Python III ======

{{:modules:52553:pdf:topic_10_-_python_iii.pdf|}}

===== pymongo =====
  * client = pymongo.MongoClient()
  * client = pymongo.MongoClient(host="localhost", port="27017")
  * try
    * client.admin.command('ismaster')
===== Database and Collections =====
  * mydb = myclient["cars"]
  * cols = mydb.list_collection_names()
  * docs = mydb["docs"]
===== find() =====
  * people = docs.find({"age":{"$gt":18}})
  * for person in people:
    *print(person["Name"])
===== find() =====
  * people = docs.find({"age":{"$gt":18}}, {"_id":0})
  * people = docs.find({"age":{"$gt":18}}, limit=2)
===== insert_one() =====
  * newDoc = {"_id":991, "name":"John", "age":44}
  * mycol.insert_one(doc)
===== insert_many() =====
  * newDocs = [{"_id":991, "name":"John", "age":44},
    *{"_id":992, "name":"Mary", "age":24},
    *{"_id":992, "name":"Mary", "age":35}]
  * mycol.insert_many(newDocs)
===== Exceptions =====
  * pymongo.errors.ConnectionFailure
  * pymongo.errors.DuplicateKeyError
  * newDocs = [{"_id":991, "name":"John", "age":44}, 
    * {"_id":992, "name":"Mary", "age":24}, 
    * {"_id":992, "name":"Mary", "age":35}]
  * mycol.insert_many(newDocs) mycol.insert_many(newDocs, ordered=False)
===== delete_one() =====
  * filter = {"age":{"$gt":44}}
  * mycol.delete_one(filter)
  * mycol.delete_one({"age":{"$gt":44}})
===== delete_many() =====
  * filter = {"age":{"$gt":44}}
  * result = mycol.delete_many(filter)
  * __DeleteResult__
  * print(result.deleted_count)
===== update_one() =====
  * filter = {"age":{"$gt":44}}
  * update = {"$inc":{"age":1}}
  * mycol.update_one(filter, update)
===== update_many() =====
  * filter = {"age":{"$gt":44}}
  * update = {"$inc":{"age":1}}
  * result = mycol.update_many(filter, update)
  * __UpdateResult__
  * print(result.modified_count)
===== Review =====
  * MySQL
  * MongoDB
  * Python