Cloud Function Project - Python
Use your existing customer structure to develop a front-end system to maintain your customer database
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 dollarsforstuff)
Custmain.html (Menu program for Data functions)
Note: replace dollarsforstuff with your cloud storage bucket
https://storage.googleapis.com/dollarsforstuff/custmain.html
custmain.html
========================================================================
<html>
<h1>Customer Maintenance</h1>
<br>
<a href="https://storage.googleapis.com/dollarsforstuff/custadd.html">Add a customer</a><br>
<a href="https://storage.googleapis.com/dollarsforstuff/custlist.html">Read a customer</a><br>
<a href="https://storage.googleapis.com/dollarsforstuff/custupdate.html">Update customer</a><br>
<a href="https://storage.googleapis.com/dollarsforstuff/custdel.html">Delete a customer</a><br>
<br>
<br>
</html>
=============================================================
Custadd.html (executes function that add customer records to the SQL table)
My function is custadd based upon the project I’m using uconn-engr yours will be based upon the project you are using
========================================================================
<html>
<h1>Customer Add records Information</h1>
<br>
<br>
<form method="POST" action="https://us-central1-uconn-engr.cloudfunctions.net/custadd">
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>
<input type="submit" value="Submit form">
</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-engr.cloudfunctions.net/custadd
With the trigger from your function
Custadd function
Cloud function custadd 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.
# Uncomment and set the following variables depending on your specific instance and database:
connection_name = 'uconn-engr: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+'")')
Note: You need to replace my sql Connection name with your sql connection name
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
Step 1. Build the function - Cloud functions create function
Step 2. Name function and allow unauthenticated invocations
Click save and hit next
Step 3. 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.
Step 4. Change the connection name inside python code to represent your SQL connection name
connection_name = 'uconn-engr:us-central1:customer'
Copy the Connection name and replace that name inside the custadd function
connection_name = 'INSERT YOUR SQL CONNECTION NAME'
Step 5. Replace the requirements.txt file, delete code, paste code from blog and deploy
PASTE
DEPLOY FUNCTION
Step 6. Drill into function and copy trigger.
Click into TRIGGER
Copy theTrigger URL
Step 7. 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
Note: gsutil cp custadd.html gs://dollarsforstuff/
replace dollarsforstuff with your bucket name
Custlist.html - reads the customer record via key from customer table
======================================================================
<html>
<h1>List Customer Information</h1>
<br>
<br>
<form method="POST" action="https://us-central1-uconn-engr.cloudfunctions.net/custlist">
Email: <input type="text" name="myemail" SIZE=35><br>
<input type="submit" value="Submit form">
</form>
</html>
==============================================================
Custlist function main.py
============================================================
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
======================================================================
Custdel.html deletes the customer
======================================================================
custdel function main.py
======================================================================
# This file contains all the code used in the codelab.
import sqlalchemy
# Uncomment and set the following variables depending on your specific instance and database:
connection_name = 'uconn-engr: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
======================================================================
Custupdate.html updates customer fields
======================================================================
<html>
<h1>Customer Update Information</h1>
<br>
<br>
<form method="POST" action="https://us-central1-uconn-engr.cloudfunctions.net/custupdate">
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>
<input type="submit" value="Submit form">
</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.
# Uncomment and set the following variables depending on your specific instance and database:
connection_name = 'uconn-engr: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 function
===================================================================
# This file contains all the code used in the codelab.
import sqlalchemy
# Uncomment and set the following variables depending on your specific instance and database:
connection_name = 'uconn-engr: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'
No comments:
Post a Comment