Cloud Function Project - Python

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

select project

Create a Cloud Storage Bucket to run the html code associated with the application.

Select Cloud Storage then Buckets

Hit Create

Name Bucket uconn-trading

Uncheck Prevent public access

Go to permissions

Grant access

Allusers - Cloud storage - Storage Object Viewer

Use uconn-trading bucket to develop a front-end system to maintain the uconntrade database.

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

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

Use editor to build html files

Highlight tradeweb directory

Right click and select new file

Front end based upon HTML Forms

Cloud Functions execute SQL commands for Data processing

All files should be placed on Cloud Storage (uconn-trading)

acctmain.html (Menu program for Data functions)

https://storage.googleapis.com/uconn-trading/acctmain.html

accttmain.html

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

<html>

<h1>Account Maintenance</h1>

<br>

<a href="https://storage.googleapis.com/uconn-trading/acctadd.html">Add aa account</a>

<br>

<a href="https://storage.googleapis.com/uconn-trading/acctlist.html">Read an account</a>

<br>

<a href="https://storage.googleapis.com/uconn-trading/acctupdate.html">Update account</a>

<br>

<a href="https://storage.googleapis.com/uconn-trading/acctdel.html">Delete an account</a>

<br>

</html>

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

acctadd.html (executes function that add account records to the SQL table)

function name acctadd

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

<html>

<h1>Add Account Information</h1>

<br>

Account Number: <input type="text" name="myacctnumber" size="6"><br>

Name: <input type="text" name="myname" size="35"><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>

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

Phonel: <input type="text" name="myphone" size="25"><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/acctadd

With the trigger from your function

Search for Cloud Functions

Click CREATE FUNCTION

Function name enter acctadd

select 1ST gen for Environment PYTHON 3.7 FOR VERSION

Select Allow unauthenticated invocations to allow for web traffic access

Hit SAVE

Copy your trigger for

Then hit next to get to code editor

Note: Need to select python3.8 for runtime

Delete code in main.py

Change Entry point to accttadd

Add code below to editor box for main.py

acctadd function

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 = 'account'
db_name = 'uconntrade'
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 acctadd(request):
    request_json = request.get_json()
    request_data = request.values
    acctnum =  request_data['myacctnumber']
    name = request_data['myname']
    addr = request_data['myaddress']
    city = request_data['mycity']
    state = request_data['mystcode']
    zipcode = request_data['myzipcode']
    email = request_data['myemail']
    phone = request_data['myphone']
    stmt = sqlalchemy.text('insert into account (Number, Name, Address, City, StateCode, ZipCode, eMail, Phone) values ("'+acctnum+'","'+name+'","'+addr+'","'+city+'","'+state+'","'+zipcode+'","'+email+'","'+phone+'")')

    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 'Account Successfully Added'