google cloud Flask SQL

Question

I am developing a website to make my own portfolio. I wanted to switch to google cloud service. I got my app working and a database up. I am able to connect via ip but if a an other instance get's create the ip has to be whitelisted first.

SQLAlchemly url is what i saw was the anwser. But i want to keep working with peewee if possible.

Can someone explain to me why my code is not connect from the app engine to the cloud sql.

My code

    """Main script for the blog website."""
from flask import Flask
from peewee import *
from livereload import Server, shell
from flask_uploads import UploadSet, IMAGES, configure_uploads
from flask_login import LoginManager, login_user
from jinja2 import Environment
import os
from playhouse.db_url import connect
import pymysql

# db = connect(host='127.0.0.1', port=3306, user='root', password='fakepassword')
# db = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='fakepassword')
try:
    db = connect('sqlite:///root:fakepassword@/DATABASE?unix_socket=/cloudsql/austinbakkerblog:us-west1:database')
except Exception:
    print('did not connect to database')
# 'mysql+mysqldb://%s:%s@/blog?unix_socket=/cloudsql/%s'


app = Flask(__name__)
app.config.from_pyfile('config.py', silent=False)
DEBUG = app.debug


# db = MySQLDatabase('database')
# db.connect()

# db = connect('mysql://root:[email protected]:3306/database')
# db = connect('mysql://root:fakepassword@/DATABASE?unix_socket=/cloudsql/austinbakkerblog:us-west1:database')
# db = connect('mysql://root:fakepassword@/cloudsql/austinbakkerblog:us-west1:database')
# db = connect('mysql://root:fakepassword@cloudsql/austinbakkerblog:us-west1:database')
# print(db.connect())


login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'


photos = UploadSet('photos', IMAGES)
configure_uploads(app, photos)

import models
from views import *


    if __name__ == '__main__':
        models.initialize()
        print('=' * 100)
        app.run()

If anyone is willing to help that would amazing, i spendt the last to day's trying to get it work but no success.

What your setup is about is not exactly clear but I assume you are attempting to set up a connection between your App Engine Instance and the CloudSQL instance. There are a number of ways to implement such connections, which include using the Service account, or by using a Cloud SQL Proxy. Have you tried these methods? — oakinlaja
– oakinlaja, Commented May 21, 2018 at 22:23
I tried both options but couldn't get it to work. I was wondering if you had a peewee example or could help me formulate it because i am having no success. @oakinlaja — austin
– austin, Commented May 22, 2018 at 11:16
db = MySQLDatabase('database', user='root', password='##############', host='35.233.225.232', port=3306) I can use this to connect but then i have to whitelist the instance. I but that means my app won't scale good — austin
– austin, Commented May 22, 2018 at 13:23

austin · Accepted Answer · 2018-05-23 15:03:18Z

1

So after a few days of grinding i got it to work. Not sure exactly how but it

app.yaml

works. runtime: python
env: flex
entrypoint: gunicorn -b :$PORT app:app

runtime_config:
    python_version: 3


env_variables:

    SQL_USER: root
    SQL_PASSWORD: test
    SQL_DATABASE: database
    INSTANCE_CONNECTION_NAME: austinbakkerblog:europe-west2:mydatabase1


beta_settings:
    cloud_sql_instances: "austinbakkerblog:europe-west2:mydatabase1"

automatic_scaling:
  min_num_instances: 1
  max_num_instances: 2
  cpu_utilization:
    target_utilization: 0.5

app.py

"""Main script for the blog's website."""
from flask import Flask
from peewee import *
from livereload import Server, shell
from flask_uploads import UploadSet, IMAGES, configure_uploads
from flask_login import LoginManager, login_user
from jinja2 import Environment
import os
import pymysql


db = MySQLDatabase(host='127.0.0.1', user='root', password='test', unix_socket='/cloudsql/austinbakkerblog:europe-west2:mydatabase1', database='database')

print(db.connect())
print(db.close())

answered May 23, 2018 at 15:03

austin

1512 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

coleifer Over a year ago

Glad to hear you're unblocked.

coleifer · Accepted Answer · 2018-05-22 01:12:25Z

0

Rather than using a connection URL, it might be easier to debug if you just setup the database connection directly.

For example,

db = MySQLDatabase('db_name', user='root', password='dbpassword',
                   host='googlecloud.whatever.com', port=31337)

Can you get pymysql to connect?

answered May 22, 2018 at 1:12

coleifer

26.4k7 gold badges67 silver badges81 bronze badges

4 Comments

austin Over a year ago

if i would do this i would still have to whitelist the ip that it is comming from, don't i ?

austin Over a year ago

db = MySQLDatabase('database', user='root', password='##############', host='35.233.225.232', port=3306) I got that to work. But the problem is not i have to whitelist every instance on my sql.

coleifer Over a year ago

Presumably you would have to whitelist them regardless of what client library you're using, though? i.e., a connection is a connection, whether peewee, sqlalchemy, jdbc...?

austin Over a year ago

On google cloud service all google app engines have access to it without whitelisting. So if i sure a ip im am rerouting the connection while that is not needed.

Collectives™ on Stack Overflow

google cloud Flask SQL

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related