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

Question

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 :)

Hey @IljaEverilä thanks for your comment. But if I don't want to create a database as described in that link, do I need to create the User class? It just seems strange that I have to write out the structure of my table to connect to it. — Hews
– Hews, Commented Dec 12, 2018 at 5:01
You do not have to use the ORM, if you don't need it. SQLAlchemy is in a way 2 libraries in 1: the Core and the ORM, built atop the former. You can work in a more traditional SQL way, if using the Core. — Ilja Everilä
– Ilja Everilä, Commented Dec 12, 2018 at 5:58

kurtisvg · Accepted Answer · 2018-12-13 17:10:47Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related