SQL Alchemy Postgres Playground

Requirements

python 2.7
virtualenv
Docker

Install

$ git clone git@github.com:lbenamer/sqla_playground.git sqlapp
$ cd sqlapp
$ bash install.sh

Configure

You will find a .env file at root level to configure sqlapp project

DB_PORT=5532
DB_NAME=sqlapp
PYTHON_BIN=python2.7 # python bin name to create virtual env
DB_URI=postgresql://postgres@localhost:${DB_PORT}/${DB_NAME} 

WORK_ENV= path/to/project/folder # add path to python path to be able to use project import into sqlapp
DEPENDENCIES= path/to/requirement.txt # path of python dependencies to add

Launch

$ bash launch.sh

Uninstall

$ bash uninstall.sh

SqlApp Module

from sqlapp import SqlApp

s = SqlApp()
s.connect('postgresql://postgres@localhost:5532/sqlapp')

Declare and create a model

from sqlalchemy import Column, Integer, String

Base = s.Base

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,
        primary_key=True,
        nullable=False,
    )
    name = Column(String(), nullable=False)
    city = Column(String(), nullable=False)

# Create user table in db
s.create_models(Base)

Create record

user = User(name='player', city='paris')

s.create(user)

You also create multiple records with the populate() method :

ROWS = [
    { 'name': 'Walid', 'city': 'Djerba' },
    { 'name': 'Mathias', 'city': 'Tours' },
]

s.populate(User, ROWS)

Query

player = s.session.query(User).filter_by(name='player').first()

player.city

Delete a record

player = s.session.query(User).filter_by(name='player').first()

s.delete(player)

The SQL inner class

This inner class contain methods to directly interact with the database

List all tables in db

s.sql.tables

Drop Tables

# Drop a table
s.sql.drop_table('user')

## Drop all tables
s.sql.drop_all_table()