librelist archives

« back to archive

flask-sqlalchemy with pre-existing database

flask-sqlalchemy with pre-existing database

From:
Carlos Gonzales
Date:
2012-05-23 @ 02:17
So I'm pretty comfortable with SQL and so far I've been building an
application for the company I work for using just psycopg2 and a simple
wrapper class I made. All the database and table definitions I do in SQL
along with related views, functions and triggers.

It's getting to a point where the application is growing faster than before
and I'm curious about cutting down on lines of code (common sql patterns)
for things such as retrieving records based on simple criteria or
automatically inserting or updating a records, etc. These things become a
bit tedious after a while, so I'm considering using flask-sqlalchemy.

I still want to do most queries by hand and I already have the database set
up, so I don't really need to redifine models, for example. I need a way to
import the schema and just work with that. Any suggestions are appreciated.

FYI, I'm building an application that will soon completely replace a
Perl-written one I inherited when I started working here. It's built on
Catalyst with DBIx that does include utilites to do what I need.

Saludos,
Carlos R. Gonzales

Re: [flask] flask-sqlalchemy with pre-existing database

From:
Shaun Zinck
Date:
2012-05-23 @ 05:01
On Tue, May 22, 2012 at 9:17 PM, Carlos Gonzales <carlos.rgn@gmail.com>wrote:

> So I'm pretty comfortable with SQL and so far I've been building an
> application for the company I work for using just psycopg2 and a simple
> wrapper class I made. All the database and table definitions I do in SQL
> along with related views, functions and triggers.
>
> It's getting to a point where the application is growing faster than
> before and I'm curious about cutting down on lines of code (common sql
> patterns) for things such as retrieving records based on simple criteria or
> automatically inserting or updating a records, etc. These things become a
> bit tedious after a while, so I'm considering using flask-sqlalchemy.
>
> I still want to do most queries by hand and I already have the database
> set up, so I don't really need to redifine models, for example. I need a
> way to import the schema and just work with that. Any suggestions are
> appreciated.
>
> FYI, I'm building an application that will soon completely replace a
> Perl-written one I inherited when I started working here. It's built on
> Catalyst with DBIx that does include utilites to do what I need.
>
> Saludos,
> Carlos R. Gonzales
>

Hi Carlos

I don't know about flask-sqlalchemy, but here's a pure sqlalchemy
approach.  This uses reflection to read the schema.  You get tables and
their columns for free, but you have to declare relationships between
tables yourself. (gist here: https://gist.github.com/2773350)


# database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("postgresql://user:@host/schema")
Base = declarative_base()
Base.metadata.reflect(engine)


# models.py
from sqlalchemy.orm import relationship, backref

class Bar(Base):
    __table__ = Base.metadata.tables['bars']

class Foo(Base):
    __table__ = Base.metadata.tables['foos']

    bar = relationship(Bar, primaryjoin='Foo.bar_id == Bar.bar_id')


# Example usage (assuming foo has a column named foo_column1)

foo = db.query(Foo).filter(Foo.foo_id = 1).one()
print foo.foo_column1 = 1
foo.foo_string_column = "foo"
foo.bar.bar_column1 = 1

-Shaun