Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit e461e9a

Browse filesBrowse files
chenyumicandrewsg
chenyumic
authored and
andrewsg
committed
Added samples for using Cloud SQL with App Engine Python 3.7 Standard (GoogleCloudPlatform#1672)
1 parent dacf009 commit e461e9a
Copy full SHA for e461e9a

File tree

Expand file treeCollapse file tree

7 files changed

+333
-0
lines changed
Filter options
Expand file treeCollapse file tree

7 files changed

+333
-0
lines changed

‎appengine/standard_python37/cloudsql/app.yaml

Copy file name to clipboard
+9Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
# [START gae_python37_cloudsql_config]
2+
runtime: python37
3+
4+
env_variables:
5+
CLOUDSQL_USERNAME: YOUR-USERNAME
6+
CLOUDSQL_PASSWORD: YOUR-PASSWORD
7+
CLOUDSQL_DATABASE_NAME: YOUR-DATABASE
8+
CLOUDSQL_CONNECTION_NAME: YOUR-CONNECTION-NAME
9+
# [END gae_python37_cloudsql_config]

‎appengine/standard_python37/cloudsql/main_mysql.py

Copy file name to clipboard
+56Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
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 gae_python37_cloudsql_mysql]
16+
import os
17+
18+
from flask import Flask
19+
import pymysql
20+
21+
db_user = os.environ.get('CLOUD_SQL_USERNAME')
22+
db_password = os.environ.get('CLOUD_SQL_PASSWORD')
23+
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
24+
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')
25+
26+
app = Flask(__name__)
27+
28+
29+
@app.route('/')
30+
def main():
31+
# When deployed to App Engine, the `GAE_ENV` environment variable will be
32+
# set to `standard`
33+
if os.environ.get('GAE_ENV'):
34+
# If deployed, use the local socket interface for accessing Cloud SQL
35+
host = '/cloudsql/{}'.format(db_connection_name)
36+
else:
37+
# If running locally, use the TCP connections instead
38+
# Set up Cloud SQL Proxy (cloud.google.com/sql/docs/mysql/sql-proxy)
39+
# so that your application can use 127.0.0.1:3306 to connect to your
40+
# Cloud SQL instance
41+
host = '127.0.0.1'
42+
43+
cnx = pymysql.connect(user=db_user, password=db_password,
44+
host=host, db=db_name)
45+
with cnx.cursor() as cursor:
46+
cursor.execute('SELECT NOW() as now;')
47+
result = cursor.fetchall()
48+
current_time = result[0][0]
49+
cnx.close()
50+
51+
return str(current_time)
52+
# [END gae_python37_cloudsql_mysql]
53+
54+
55+
if __name__ == '__main__':
56+
app.run(host='127.0.0.1', port=8080, debug=True)

‎appengine/standard_python37/cloudsql/main_mysql_pooling.py

Copy file name to clipboard
+63Lines changed: 63 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,63 @@
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 gae_python37_cloudsql_mysql_pooling]
16+
import os
17+
18+
from flask import Flask
19+
import sqlalchemy
20+
21+
db_user = os.environ.get('CLOUD_SQL_USERNAME')
22+
db_password = os.environ.get('CLOUD_SQL_PASSWORD')
23+
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
24+
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')
25+
26+
# When deployed to App Engine, the `GAE_ENV` environment variable will be
27+
# set to `standard`
28+
if os.environ.get('GAE_ENV'):
29+
# If deployed, use the local socket interface for accessing Cloud SQL
30+
host = '/cloudsql/{}'.format(db_connection_name)
31+
else:
32+
# If running locally, use the TCP connections instead
33+
# Set up Cloud SQL Proxy (cloud.google.com/sql/docs/mysql/sql-proxy)
34+
# so that your application can use 127.0.0.1:3306 to connect to your
35+
# Cloud SQL instance
36+
host = '127.0.0.1'
37+
38+
# The Engine object returned by create_engine() has a QueuePool integrated
39+
# See https://docs.sqlalchemy.org/en/latest/core/pooling.html for more
40+
# information
41+
engine = sqlalchemy.create_engine('mysql+pymysql://{}:{}@{}/{}'.format(
42+
db_user, db_password, host, db_name
43+
), pool_size=3)
44+
45+
app = Flask(__name__)
46+
47+
48+
@app.route('/')
49+
def main():
50+
cnx = engine.connect()
51+
cursor = cnx.execute('SELECT NOW() as now;')
52+
result = cursor.fetchall()
53+
current_time = result[0][0]
54+
# If the connection comes from a pool, close() will send the connection
55+
# back to the pool instead of closing it
56+
cnx.close()
57+
58+
return str(current_time)
59+
# [END gae_python37_cloudsql_mysql_pooling]
60+
61+
62+
if __name__ == '__main__':
63+
app.run(host='127.0.0.1', port=8080, debug=True)

‎appengine/standard_python37/cloudsql/main_postgres.py

Copy file name to clipboard
+57Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,57 @@
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 gae_python37_cloudsql_psql]
16+
import os
17+
18+
from flask import Flask
19+
import psycopg2
20+
21+
db_user = os.environ.get('CLOUD_SQL_USERNAME')
22+
db_password = os.environ.get('CLOUD_SQL_PASSWORD')
23+
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
24+
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')
25+
26+
app = Flask(__name__)
27+
28+
29+
@app.route('/')
30+
def main():
31+
# When deployed to App Engine, the `GAE_ENV` environment variable will be
32+
# set to `standard`
33+
if os.environ.get('GAE_ENV'):
34+
# If deployed, use the local socket interface for accessing Cloud SQL
35+
host = '/cloudsql/{}'.format(db_connection_name)
36+
else:
37+
# If running locally, use the TCP connections instead
38+
# Set up Cloud SQL Proxy (cloud.google.com/sql/docs/mysql/sql-proxy)
39+
# so that your application can use 127.0.0.1:3306 to connect to your
40+
# Cloud SQL instance
41+
host = '127.0.0.1'
42+
43+
cnx = psycopg2.connect(dbname=db_name, user=db_user,
44+
password=db_password, host=host)
45+
with cnx.cursor() as cursor:
46+
cursor.execute('SELECT NOW() as now;')
47+
result = cursor.fetchall()
48+
current_time = result[0][0]
49+
cnx.commit()
50+
cnx.close()
51+
52+
return str(current_time)
53+
# [END gae_python37_cloudsql_psql]
54+
55+
56+
if __name__ == '__main__':
57+
app.run(host='127.0.0.1', port=8080, debug=True)

‎appengine/standard_python37/cloudsql/main_postgres_pooling.py

Copy file name to clipboard
+66Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
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 gae_python37_cloudsql_psql_pooling]
16+
import os
17+
18+
from flask import Flask
19+
import psycopg2.pool
20+
21+
db_user = os.environ.get('CLOUD_SQL_USERNAME')
22+
db_password = os.environ.get('CLOUD_SQL_PASSWORD')
23+
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
24+
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')
25+
26+
# When deployed to App Engine, the `GAE_ENV` environment variable will be
27+
# set to `standard`
28+
if os.environ.get('GAE_ENV'):
29+
# If deployed, use the local socket interface for accessing Cloud SQL
30+
host = '/cloudsql/{}'.format(db_connection_name)
31+
else:
32+
# If running locally, use the TCP connections instead
33+
# Set up Cloud SQL Proxy (cloud.google.com/sql/docs/mysql/sql-proxy)
34+
# so that your application can use 127.0.0.1:3306 to connect to your
35+
# Cloud SQL instance
36+
host = '127.0.0.1'
37+
38+
db_config = {
39+
'user': db_user,
40+
'password': db_password,
41+
'database': db_name,
42+
'host': host
43+
}
44+
45+
cnxpool = psycopg2.pool.ThreadedConnectionPool(minconn=1, maxconn=3,
46+
**db_config)
47+
48+
app = Flask(__name__)
49+
50+
51+
@app.route('/')
52+
def main():
53+
cnx = cnxpool.getconn()
54+
with cnx.cursor() as cursor:
55+
cursor.execute('SELECT NOW() as now;')
56+
result = cursor.fetchall()
57+
current_time = result[0][0]
58+
cnx.commit()
59+
cnxpool.putconn(cnx)
60+
61+
return str(current_time)
62+
# [END gae_python37_cloudsql_psql_pooling]
63+
64+
65+
if __name__ == '__main__':
66+
app.run(host='127.0.0.1', port=8080, debug=True)

‎appengine/standard_python37/cloudsql/main_test.py

Copy file name to clipboard
+78Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,78 @@
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+
from unittest.mock import MagicMock
16+
17+
import psycopg2.pool
18+
import sqlalchemy
19+
20+
21+
def test_main():
22+
import main_mysql
23+
main_mysql.pymysql = MagicMock()
24+
fetchall_mock = main_mysql.pymysql.connect().cursor().__enter__().fetchall
25+
fetchall_mock.return_value = [['0']]
26+
27+
main_mysql.app.testing = True
28+
client = main_mysql.app.test_client()
29+
30+
r = client.get('/')
31+
assert r.status_code == 200
32+
assert '0' in r.data.decode('utf-8')
33+
34+
35+
def test_main_pooling():
36+
sqlalchemy.create_engine = MagicMock()
37+
38+
import main_mysql_pooling
39+
40+
cnx_mock = main_mysql_pooling.sqlalchemy.create_engine().connect()
41+
cnx_mock.execute().fetchall.return_value = [['0']]
42+
43+
main_mysql_pooling.app.testing = True
44+
client = main_mysql_pooling.app.test_client()
45+
46+
r = client.get('/')
47+
assert r.status_code == 200
48+
assert '0' in r.data.decode('utf-8')
49+
50+
51+
def test_main_postgressql():
52+
import main_postgres
53+
main_postgres.psycopg2.connect = MagicMock()
54+
mock_cursor = main_postgres.psycopg2.connect().cursor()
55+
mock_cursor.__enter__().fetchall.return_value = [['0']]
56+
57+
main_postgres.app.testing = True
58+
client = main_postgres.app.test_client()
59+
60+
r = client.get('/')
61+
assert r.status_code == 200
62+
assert '0' in r.data.decode('utf-8')
63+
64+
65+
def test_main_postgressql_pooling():
66+
psycopg2.pool.ThreadedConnectionPool = MagicMock()
67+
68+
import main_postgres_pooling
69+
70+
mock_pool = main_postgres_pooling.psycopg2.pool.ThreadedConnectionPool()
71+
mock_pool.getconn().cursor().__enter__().fetchall.return_value = [['0']]
72+
73+
main_postgres_pooling.app.testing = True
74+
client = main_postgres_pooling.app.test_client()
75+
76+
r = client.get('/')
77+
assert r.status_code == 200
78+
assert '0' in r.data.decode('utf-8')

‎appengine/standard_python37/cloudsql/requirements.txt

Copy file name to clipboard
+4Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
psycopg2==2.7.5
2+
psycopg2-binary==2.7.5
3+
PyMySQL==0.9.2
4+
SQLAlchemy==1.2.12

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.