CODEWITHSUNDEEP
pythongoogle-app-engineflasksqlalchemygoogle-cloud-sql
Hews
Hews

Reputation: 581

Connecting to google-sql with Python3 flexible engine through Flask/SQLAlchemy

I've upgraded my app engine to flexible and am now refactoring code. I haven't worked with Flask besides in standard and haven't used SQLAlchemy. I've set up my databases and have had valid, functioning connections before in standard environment. I'm now trying to execute a simple SQL in Python3 flexible environment:

SELECT id, latitude, longitude FROM weatherData

I now have a valid connection to the database through the following:

    app = Flask(__name__)
    app.config['WEATHER_DATABASE_URI'] = os.environ['WEATHER_DATABASE_URI']
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

    db = SQLAlchemy(app)

The respective environment variables are in my app.yaml file.

I understand that SQLAlchemy uses ORMs but in all the examples I've seen they've created a class as 'buffer' between the client and database to firstly create the table, and then perform CRUD operations. Eg.

engine = create_engine('sqlite:///student.db', echo=True)
Base = declarative_base()

class Student(Base):
""""""
__tablename__ = "student"

id = Column(Integer, primary_key=True)
username = Column(String)
firstname = Column(String)
lastname = Column(String)
university = Column(String)

#----------------------------------------------------------------------
def __init__(self, username, firstname, lastname, university):
    """"""
    self.username = username
    self.firstname = firstname
    self.lastname = lastname
    self.university = university

 # create tables
 Base.metadata.create_all(engine)

I notice that in this case they're using engine which doesn't seem relevant to me. In short, how can I perform the aforementioned SQL query?

Thanks :)

Upvotes: 0

Views: 59

Answers (1)

kurtisvg
kurtisvg

Reputation: 3565

SQLAlchemy uses it's engine class to control interactions with the database. First, you create an engine specifying how you want to connect:

db = sqlalchemy.create_engine(
    # Equivalent URL:
    # mysql+pymysql://<db_user>:<db_pass>@/<db_name>?unix_socket=/cloudsql/<cloud_sql_instance_name>
    sqlalchemy.engine.url.URL(
        drivername='mysql+pymysql',
        username=db_user,
        password=db_pass,
        database=db_name,
        query={
            'unix_socket': '/cloudsql/{}'.format(cloud_sql_instance_name)
        }
    )
}

Second, you use the engine to retrieve a connection to the instance and perform your actions:

with db.connect() as conn:
     recent_votes = conn.execute(
            "SELECT candidate, time_cast FROM votes "
            "ORDER BY time_cast DESC LIMIT 5"
     ).fetchall()

This allows SQLAlchemy to manage your connections in a more efficient way. If you want to see these snippets in the context of an application, take a look at this example application.

Upvotes: 1

Related Questions