Cloud Function Project - Python

How much does a Oracle Pl Sql Developer make in USA?

$117,000 / Annual

Based on 1244 salaries

The average oracle pl sql developer salary in the USA is $117,000 per year or $56.25 per hour. Entry level positions start at $104,314 per year while most experienced workers make up to $136,500 per year.

Greed

Note: Everyone will use the project I have shared with the class called ‘uconn customers’

select project

Need to create your own bucket on this project I used ‘jiacovacci’ and own functions e.g. jiacovacci-add

Use your existing customer structure to develop a front-end system to maintain your customer database.

The linux shell should be used to create the html files in a directory called custweb.

john_iacovacci1@cloudshell:~ (uconn-customers)$ mkdir custweb

Front end based upon HTML Forms

Cloud Functions execute SQL commands for Data processing

All files should be placed on your Cloud Storage (mine is jiacovacci)

custmain.html (Menu program for Data functions)

Note: replace jiacovacci with your cloud storage bucket

https://storage.googleapis.com/jiacovacci/custmain.html

custmain.html

=========================================================

<html>

<h1>Customer Maintenance</h1>

<br>

<a href="https://storage.googleapis.com/jiacovacci/custadd.html">Add a customer</a>

<br>

<a href="https://storage.googleapis.com/jiacovacci/custlist.html">Read a customer</a>

<br>

<a href="https://storage.googleapis.com/jiacovacci/custupdate.html">Update customer</a>

<br>

<a href="https://storage.googleapis.com/jiacovacci/custdel.html">Delete a customer</a>

<br>

</html>

=========================================================

custadd.html (executes function that add customer records to the SQL table)

Your function name needs to be first initial and last name suffixed by -add e.g. jiacovacci-add

=========================================================

<html>

<h1>Customer Add records Information</h1>

<br>

Email: <input type="text" name="myemail" size="35"><br>

Last Name: <input type="text" name="mylastname" size="25"><br>

First Name: <input type="text" name="myfirstname" size="25"><br>

Address: <input type="text" name="myaddress" size="35"><br>

City: <input type="text" name="mycity" size="30"><br>

ST Code: <input type="text" name="mystcode" size="2"><br>

ZIP Code: <input type="text" name="myzipcode" size="5"><br>

</form>

</html>

=========================================================

Note: input variables are set for each field in our database using my prefix

We will use these variables to populate the SQL Table fields

In all HTML files that access SQL you need to replace my trigger function

https://us-central1-uconn-customers.cloudfunctions.net/jiacovacci-add

With the trigger from your function

Search for Cloud Functions

Click CREATE FUNCTION

For Function name enter jiacovacci-add (yourname)

Make sure you select 1st gen for Environment

Select Allow unauthenticated invocations to allow for web traffic access

Hit SAVE

Copy your trigger for later use in your custadd.html form action

https://us-central1-uconn-customers.cloudfunctions.net/jiacovacci-add

Then hit next to get to code editor

Note: Need to select python3.7 for runtime

Delete code in main.py

Change Entry point to custadd

Add code below to editor box for main.py

custadd function

Cloud function jiacovacci-add main.py

=========================================================

# This file contains all the code used in the codelab.

import sqlalchemy

# Depending on which database you are using, you'll set some variables differently.

# In this code we are inserting only one field with one value.

# Feel free to change the insert statement as needed for your own table's requirements.

connection_name = 'uconn-customers:us-central1:customer'

table_name = 'customer'

db_name = 'customer'

db_user = 'root'

db_password = 'uconnstamford'

# If your database is MySQL, uncomment the following two lines:

driver_name = 'mysql+pymysql'

query_string = dict({"unix_socket": "/cloudsql/{}".format(connection_name)})

# If your database is PostgreSQL, uncomment the following two lines:

#driver_name = 'postgres+pg8000'

#query_string = dict({"unix_sock": "/cloudsql/{}/.s.PGSQL.5432".format(connection_name)})

# If the type of your table_field value is a string, surround it with double quotes.

def custadd(request):

request_json = request.get_json()

request_data = request.values

email = request_data['myemail']

lname = request_data['mylastname']

fname = request_data['myfirstname']

addr = request_data['myaddress']

city = request_data['mycity']

state = request_data['mystcode']

zipcode = request_data['myzipcode']

stmt = sqlalchemy.text('insert into customer (email, LastName, FirstName, Address, City, StateCode, ZipCode) values ("'+email+'","'+lname+'","'+fname+'","'+addr+'","'+city+'","'+state+'","'+zipcode+'")')

db = sqlalchemy.create_engine(

sqlalchemy.engine.url.URL(

drivername=driver_name,

username=db_user,

password=db_password,

database=db_name,

query=query_string,

pool_size=5,

max_overflow=2,

pool_timeout=30,

pool_recycle=1800

)

try:

with db.connect() as conn:

conn.execute(stmt)

except Exception as e:

return 'Error: {}'.format(str(e))

return 'Customer Successfully Added'

=========================================================

The SQL Statement embedded in the function INSERTS data into the customer table

insert into customer (email, LastName, FirstName, Address, City, StateCode, ZipCode) values ("'+email+'","'+lname+'","'+fname+'","'+addr+'","'+city+'","'+state+'","'+zipcode+'")')

Next select requirements.txt

Replace the requirements.txt file, delete code, paste code from blog and deploy

Put statements below in editor

requirements.txt needs the libraries the function uses

=========================================================

# Function dependencies, for example:

# package>=version

# This file tells Python which modules it needs to import

SQLAlchemy==1.3.12

# If your database is MySQL, uncomment the following line:

PyMySQL==0.9.3

# If your database is PostgreSQL, uncomment the following line:

#pg8000==1.13.2

=========================================================

Implementation steps

When finished hit deploy

Note: the trigger url for html

https://us-central1-uconn-customers.cloudfunctions.net/jiacovacci-add

Change Runtime to python 3.7, change entry point to custadd (name of function)

Delete code in text editor and copy and paste custadd function from blog.

Note: we are all using the same SQL instance on the project uconn customers.

This is to make sure that no one gets charged for SQL because it can become expensive.

All charges will go under my account for that project

Drill into function and copy trigger.

Click into TRIGGER

Copy theTrigger URL

Insert Trigger into custadd.html

Create directory for HTML files

Create new file custadd.html with custweb highlighted so file is created within directory

Replace the Trigger on the blog with the Trigger URL from your function

(after action within quotes)

Save file

Change into custweb directory

Copy the file to your bucket

Welcome to Cloud Shell! Type "help" to get started.

Your Cloud Platform project in this session is set to uconn-engr.

Use “gcloud config set project [PROJECT_ID]” to change to a different project.

john_iacovacci1@cloudshell:~ (uconn-customers)$ cd custweb

$ gsutil cp custadd.html gs://jiacovacci/

Copying file://custadd.html [Content-Type=text/html]...

/ [1 files][ 639.0 B/ 639.0 B]

Operation completed over 1 objects/639.0 B.

john_iacovacci1@cloudshell:~/custweb (uconn-customers)$

Note: gsutil cp custadd.html gs://jiacovacci/

replace jiacovacci with your bucket name

Test the function by accessing the custadd.html via your storage bucket

Click into the bucket

Click into custadd.html

Click into Public URL

Enter information (email needs to be unique for each entry)

Hit Submit Form

Should get a successful message

Note: if you get this message

Error: Forbidden

Your client does not have permission to get URL /jiacovacci-add2 from this server.

You will need to permission your function

Go into PERMISSIONS

Then Grant Access to your function to allUsers and Cloud Function Invoker

Repeat for custlist, custupdate and custdel below

=========================================================custlist.html - reads the customer record via key from customer table

=========================================================

<html>

<h1>List Customer Information</h1>

<br>

Email: <input type="text" name="myemail" SIZE=35><br>

</form>

</html>

=========================================================

Note: Make sure you change the entry point

custlist function main.py

==========================================================

# This file contains all the code used in the codelab.

import sqlalchemy

connection_name = 'uconn-customers:us-central1:customer'

table_name = 'customer'

db_name = 'customer'

db_user = 'root'

db_password = 'uconnstamford'

# If your database is MySQL, uncomment the following two lines:

driver_name = 'mysql+pymysql'

query_string = dict({"unix_socket": "/cloudsql/{}".format(connection_name)})

# If the type of your table_field value is a string, surround it with double quotes.

def custlist(request):

request_json = request.get_json()

request_data = request.values

remail = request_data['myemail']

stmt = sqlalchemy.text('SELECT email, LastName, FirstName FROM customer WHERE email = "'+remail+'"')

db = sqlalchemy.create_engine(

sqlalchemy.engine.url.URL(

drivername=driver_name,

username=db_user,

password=db_password,

database=db_name,

query=query_string,

pool_size=5,

max_overflow=2,

pool_timeout=30,

pool_recycle=1800

)

try:

with db.connect() as conn:

results = conn.execute(stmt).fetchone()

except Exception as e:

return 'Error: {}'.format(str(e))

dname = "<!DOCTYPE html><html><h1>Customer Info</h1><br>" + results[0] + "<br>" + results[1] + "<br>" + results[2]

return dname

=========================================================

Read the LastName field from the customer table using email as a key

requirements.txt needs the libraries the function uses

=========================================================

# Function dependencies, for example:

# package>=version

# This file tells Python which modules it needs to import

SQLAlchemy==1.3.12

# If your database is MySQL, uncomment the following line:

PyMySQL==0.9.3

# If your database is PostgreSQL, uncomment the following line:

#pg8000==1.13.2

=========================================================

I had permission issue

Had to add Cloud Functions Invoker to allusers

custupdate.html updates customer fields

=========================================================

<html>

<h1>Customer Update Information</h1>

<br>

Email: <input type="text" name="myemail" size="35"><br>

Last Name: <input type="text" name="mylastname" size="25"><br>

First Name: <input type="text" name="myfirstname" size="25"><br>

Address: <input type="text" name="myaddress" size="35"><br>

City: <input type="text" name="mycity" size="30"><br>

ST Code: <input type="text" name="mystcode" size="2"><br>

ZIP Code: <input type="text" name="myzipcode" size="5"><br>

</form>

</html>

==========================================================

custupdate function

==========================================================

# This file contains all the code used in the codelab.

import sqlalchemy

# Depending on which database you are using, you'll set some variables differently.

# In this code we are inserting only one field with one value.

# Feel free to change the insert statement as needed for your own table's requirements.

connection_name = 'uconn-customers:us-central1:customer'

table_name = 'customer'

db_name = 'customer'

db_user = 'root'

db_password = 'uconnstamford'

# If your database is MySQL, uncomment the following two lines:

driver_name = 'mysql+pymysql'

query_string = dict({"unix_socket": "/cloudsql/{}".format(connection_name)})

# If your database is PostgreSQL, uncomment the following two lines:

#driver_name = 'postgres+pg8000'

#query_string = dict({"unix_sock": "/cloudsql/{}/.s.PGSQL.5432".format(connection_name)})

# If the type of your table_field value is a string, surround it with double quotes.

def custupdate(request):

request_json = request.get_json()

request_data = request.values

email = request_data['myemail']

lname = request_data['mylastname']

fname = request_data['myfirstname']

addr = request_data['myaddress']

city = request_data['mycity']

state = request_data['mystcode']

zipcode = request_data['myzipcode']

stmt = sqlalchemy.text('UPDATE customer SET LastName = "'+lname+'", FirstName = "'+fname+'", Address = "'+addr+'", City = "'+city+'", StateCode = "'+state+'", ZipCode = "'+zipcode+'" WHERE email = "'+email+'"')

db = sqlalchemy.create_engine(

sqlalchemy.engine.url.URL(

drivername=driver_name,

username=db_user,

password=db_password,

database=db_name,

query=query_string,

pool_size=5,

max_overflow=2,

pool_timeout=30,

pool_recycle=1800

)

try:

with db.connect() as conn:

conn.execute(stmt)

except Exception as e:

return 'Error: {}'.format(str(e))

return 'Customer Successfully Updated'

=========================================================

Updates customer fields from html input form

requirements.txt

=========================================================

# Function dependencies, for example:

# package>=version

# This file tells Python which modules it needs to import

SQLAlchemy==1.3.12

# If your database is MySQL, uncomment the following line:

PyMySQL==0.9.3

# If your database is PostgreSQL, uncomment the following line:

#pg8000==1.13.2

==========================================================

custdel.html

=========================================================

<html>

<h1>Delete Customer Information</h1>

<br>

Email: <input type="text" name="myemail" SIZE=35><br>

</form>

</html>

=========================================================

custdel function main.py

==========================================================

# This file contains all the code used in the codelab.

import sqlalchemy

connection_name = 'uconn-customers:us-central1:customer'

table_name = 'customer'

db_name = 'customer'

db_user = 'root'

db_password = 'uconnstamford'

# If your database is MySQL, uncomment the following two lines:

driver_name = 'mysql+pymysql'

query_string = dict({"unix_socket": "/cloudsql/{}".format(connection_name)})

# If the type of your table_field value is a string, surround it with double quotes.

def custdel(request):

request_json = request.get_json()

request_data = request.values

email = request_data['myemail']

stmt = sqlalchemy.text('DELETE FROM customer WHERE email = "'+email+'"')

db = sqlalchemy.create_engine(

sqlalchemy.engine.url.URL(

drivername=driver_name,

username=db_user,

password=db_password,

database=db_name,

query=query_string,

pool_size=5,

max_overflow=2,

pool_timeout=30,

pool_recycle=1800

)

try:

with db.connect() as conn:

conn.execute(stmt)

except Exception as e:

return 'Error: {}'.format(str(e))

return 'Customer Successfully Deleted'

=========================================================

Deletes customer where email is entered in form

requirements.txt

=========================================================

# Function dependencies, for example:

# package>=version

# This file tells Python which modules it needs to import

SQLAlchemy==1.3.12

# If your database is MySQL, uncomment the following line:

PyMySQL==0.9.3

# If your database is PostgreSQL, uncomment the following line:

#pg8000==1.13.2

=========================================================

UCONN Stamford Google Cloud Development Platform

UCONN

Cloud Function Project - Python

How much does a Oracle Pl Sql Developer make in USA?

Error: Forbidden

Your client does not have permission to get URL /jiacovacci-add2 from this server.

You will need to permission your function

No comments:

Post a Comment

Disable Billing

Report Abuse