8000 Add GCF Python SQL samples (#1655) · rlkelly/python-docs-samples@0eb0ebd · GitHub
[go: up one dir, main page]
Skip to content

Commit 0eb0ebd

Browse files
author
Ace Nassri
authored
Add GCF Python SQL samples (GoogleCloudPlatform#1655)
* Add GCF Python SQL samples Change-Id: Ib60712a5f3808b1fb99088e5e637304ef21e98c7 * Update test SQL instance name Change-Id: I0c6ca84695a1a593f46ddd922507eb0cb1e56879 * Add SQL Kokoro test files Change-Id: Ia59f803bb2bd50842f047a6570ebbc86171d2898 * Address comments Change-Id: I0bad3298fa138430a4073ce108412fd4a6af5854 * Switch python samples to pools Change-Id: Ibf5a90b3b8c02596f441b7d77cdf11b416260437 * Use connections for Python MySQL, as libraries don't support pooling Change-Id: I3efeabd192be9c2c987846b0df7dcced016ddc49 * Fix lint + tests Change-Id: I9d2901a737ab0bb7d74edefa69876704846fdf10 * Fix nits Change-Id: I0bbcf8e32e6fd2f8fa8ef942d3b089418f01f502 * Make Python config consistent with Node samples Change-Id: Ibb0341f51861ec5adcba136903c1df0567e97398
1 parent 2dfacb6 commit 0eb0ebd

File tree

9 files changed

+206
-0
lines changed

9 files changed

+206
-0
lines changed

‎.kokoro/presubmit_tests_functions_sql.cfg

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
# Format: //devtools/kokoro/config/proto/build.proto
2+
3+
# Download secrets from Cloud Storage.
4+
gfile_resources: "/bigstore/cloud-devrel-kokoro-resources/python-docs-samples"
5+
6+
# Tell the trampoline which build file to use.
7+
env_vars: {
8+
key: "TRAMPOLINE_BUILD_FILE"
9+
value: "github/python-docs-samples/.kokoro/system_tests.sh"
10+
}
11+
12+
env_vars: {
13+
key: "NOX_SESSION"
14+
value: "functions and sql and py36 and not venv"
15+
}

‎.kokoro/system_tests.sh

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,9 +26,19 @@ source ./testing/test-env.sh
2626
export GOOGLE_APPLICATION_CREDENTIALS=$(pwd)/testing/service-account.json
2727
export GOOGLE_CLIENT_SECRETS=$(pwd)/testing/client-secrets.json
2828

29+
# Run Cloud SQL proxy, if required
30+
if [ -n "${CLOUD_SQL_PROXY}" ]; then
31+
cloud_sql_proxy -instances="${MYSQL_INSTANCE}"=tcp:3306 &
32+
cloud_sql_proxy -instances="${POSTGRES_INSTANCE}"=tcp:5432 &
33+
fi
34+
2935
# Run tests
3036
nox -k "${NOX_SESSION}" || ret_code=$?
3137

38+
if [ -n "${CLOUD_SQL_PROXY}" ]; then
39+
killall cloud_sql_proxy || true
40+
fi
41+
3242
# Workaround for Kokoro permissions issue: delete secrets
3343
rm testing/{test-env.sh,client-secrets.json,service-account.json}
3444

‎functions/sql/mysql_sample.py

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
# Copyright 2018 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the 'License');
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an 'AS IS' BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
# [START functions_sql_mysql]
16+
from os import getenv
17+
18+
import pymysql
19+
20+
is_production = getenv('SUPERVISOR_HOSTNAME') is not None
21+
22+
# TODO(developer): specify SQL connection details
23+
CONNECTION_NAME = getenv(
24+
'INSTANCE_CONNECTION_NAME',
25+
'<YOUR INSTANCE CONNECTION NAME>')
26+
DB_USER = getenv('MYSQL_USER', '<YOUR DB USER>')
27+
DB_PASSWORD = getenv('MYSQL_PASSWORD', '<YOUR DB PASSWORD>')
28+
DB_NAME = getenv('MYSQL_DATABASE', '<YOUR DB NAME>')
29+
30+
mysql_config = {
31+
'user': DB_USER,
32+
'password': DB_PASSWORD,
33+
'db': DB_NAME,
34+
'charset': 'utf8mb4',
35+
'cursorclass': pymysql.cursors.DictCursor,
36+
'autocommit': True
37+
}
38+
39+
if is_production:
40+
mysql_config['unix_socket'] = \
41+
'/cloudsql/' + CONNECTION_NAME
42+
43+
# Create SQL connection globally to enable reuse
44+
# PyMySQL does not include support for connection pooling
45+
mysql_conn = pymysql.connect(**mysql_config)
46+
47+
48+
def __get_cursor():
49+
"""
50+
Helper function to get a cursor
51+
PyMySQL does NOT automatically reconnect,
52+
so we must reconnect explicitly using ping()
53+
"""
54+
try:
55+
return mysql_conn.cursor()
56+
except Exception:
57+
mysql_conn.ping(reconnect=True)
58+
return mysql_conn.cursor()
59+
60+
61+
def mysql_demo(request):
62+
# Remember to close SQL resources declared while running this function.
63+
# Keep any declared in global scope (e.g. mysql_conn) for later reuse.
64+
with __get_cursor() as cursor:
65+
cursor.execute('SELECT NOW() as now')
66+
results = cursor.fetchone()
67+
return str(results['now'])
68+
# [END functions_sql_mysql]

‎functions/sql/mysql_test.py

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
# Copyright 2018 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the 'License');
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an 'AS IS' BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
import mysql_sample
16+
17+
18+
def test_mysql():
19+
mysql_sample.mysql_demo(None)

‎functions/sql/postgres_sample.py

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
1+
# Copyright 2018 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the 'License');
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an 'AS IS' BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
# [START functions_sql_postgres]
16+
from os import getenv
17+
18+
from psycopg2.pool import SimpleConnectionPool
19+
20+
is_production = getenv('SUPERVISOR_HOSTNAME') is not None
21+
22+
# TODO(developer): specify SQL connection details
23+
CONNECTION_NAME = getenv(
24+
'INSTANCE_CONNECTION_NAME',
25+
'<YOUR INSTANCE CONNECTION NAME>')
26+
DB_USER = getenv('POSTGRES_USER', '<YOUR DB USER>')
27+
DB_PASSWORD = getenv('POSTGRES_PASSWORD', '<YOUR DB PASSWORD>')
28+
DB_NAME = getenv('POSTGRES_DATABASE', '<YOUR DB NAME>')
29+
30+
pg_config = {
31+
'user': DB_USER,
32+
'password': DB_PASSWORD,
33+
'dbname': DB_NAME,
34+
}
35+
36+
if is_production:
37+
pg_config['host'] = '/cloudsql/' + CONNECTION_NAME
38+
else:
39+
pg_config['host'] = 'localhost'
40+
41+
# Connection pools reuse connections between invocations,
42+
# and handle dropped or expired connections automatically.
43+
pg_pool = None
44+
45+
46+
def postgres_demo(request):
47+
global pg_pool
48+
49+
# Initialize the pool lazily, in case SQL access isn't needed for this
50+
# GCF instance. Doing so minimizes the number of active SQL connections,
51+
# which helps keep your GCF instances under SQL connection limits.
52+
if not pg_pool:
53+
pg_pool = SimpleConnectionPool(1, 1, **pg_config)
54+
55+
# Remember to close SQL resources declared while running this function.
56+
# Keep any declared in global scope (e.g. pg_pool) for later reuse.
57+
with pg_pool.getconn().cursor() as cursor:
58+
cursor.execute('SELECT NOW() as now')
59+
results = cursor.fetchone()
60+
return str(results[0])
61+
# [END functions_sql_postgres]

‎functions/sql/postgres_test.py

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
# Copyright 2018 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the 'License');
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an 'AS IS' BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
import postgres_sample
16+
17+
18+
def test_postgres():
19+
postgres_sample.postgres_demo(None)

‎functions/sql/requirements.txt

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
psycopg2==2.7.5
2+
PyMySQL==0.9.2

‎testing/secrets.tar.enc

7.02 KB
Binary file not shown.

‎testing/test-env.tmpl.sh

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,25 @@
11
# Environment variables for system tests.
22
export GCLOUD_PROJECT=your-project-id
33
export GOOGLE_CLOUD_PROJECT=$GCLOUD_PROJECT
4+
export FIRESTORE_PROJECT=
5+
46
export CLOUD_STORAGE_BUCKET=$GCLOUD_PROJECT
57
export API_KEY=
68
export BIGTABLE_CLUSTER=bigtable-test
79
export BIGTABLE_ZONE=us-central1-c
810
export SPANNER_INSTANCE=
911
export COMPOSER_LOCATION=us-central1
1012
export COMPOSER_ENVIRONMENT=
13+
export CLOUD_KMS_KEY=
14+
15+
export MYSQL_INSTANCE=
16+
export MYSQL_USER=
17+
export MYSQL_PASSWORD=
18+
export MYSQL_DATABASE=
19+
export POSTGRES_INSTANCE=
20+
export POSTGRES_USER=
21+
export POSTGRES_PASSWORD=
22+
export POSTGRES_DATABASE=
1123

1224
# Environment variables for App Engine Flexible system tests.
1325
export GA_TRACKING_ID=

0 commit comments

Comments
 (0)
0