Skip to main content

Simple SQL generator.

Project description

FirehSQL

  • URL : Github <https://github.com/dozymoe/firehsql/>_
  • License : GPL v3

Summary

Simple SQL Query Builder, doesn't support CREATE TABLE, field definition and whatnot, the library only observed the table field names.

Example:

.. code:: python

from firehsql import SchemaBase

class UserSchema(SchemaBase):

    TABLE_NAME = 'users'

    FIELDS = ('id', 'username', 'email', 'password', 'is_superuser',
            'is_staff', 'created_at', 'modified_at')

    FILTER_BY_FIELDS = ('id', 'username', 'email', 'created_at')
    ORDER_BY_FIELDS = ('username',)

    # For PostgreSQL to return last inserted id
    RETURNING_FIELDS = ('id',)

.. code:: python

from itertools import chain
import psycopg2

from .schema import UserSchema

def insert():
    sql = UserSchema.create_insert_sql()
    sql.set_values(
            username='User1',
            password='User1Password',
            email='User1@example.com')

    with psycopg2.connect('dbname=testdb') as conn:
        with conn.cursor() as cur:
            # INSERT INTO users (username, password, email)
            #     VALUES (%s, %s, %s)
            #
            # ('User1', 'User1Password', 'User1@example.com')
            cur.execute(str(sql), sql.data)
            
        conn.commit()


def select():
    sql = UserSchema.create_select_sql()

    filter_ = sql.create_or_filter()
    # The word 'LIKE' will not be checked if it was valid or not,
    # too bothersome, just don't put user's input in there.
    filter_.add(('username', 'Ach%', 'LIKE'))
    filter_.add(('username', 'Abd%', 'LIKE'))
    sql.set_filters(filter_)

    # Assumed DESCENDING if it was prefixed with hyphen (-), the target
    # being http query string.
    sql.set_sorting_order('username', '-id')

    page_size = 10
    page = 1
    page_offset = (page - 1) * page_size
    # Both page_size and page_offset tested to be of type integer.
    sql.set_limit(page_size, page_offset)

    with psycopg2.connect('dbname=testdb') as conn:
        with conn.cursor() as cur:
            # SELECT * FROM users WHERE username LIKE %s
            #     OR username LIKE %s ORDER BY username, id DESC
            #     LIMIT 10 OFFSET 0
            #
            # ('Ach%', 'Abd%')
            cur.execute(str(sql), sql.data)


def update():
    sql = UserSchema.create_update_sql()

    sql.set_values(
            is_superuser=True,
            is_staff=True)

    # '=' will not be checked if it was valid operand or not.
    sql.set_filters(
            ('username', 'User1', '='))

    with psycopg2.connect('dbname=testdb') as conn:
        with conn.cursor() as cur:
            # UPDATE users SET is_superuser=%s, is_staff=%s
            #     WHERE username = %s
            #
            # (True, True, 'User1')
            cur.execute(str(sql), sql.data)


def filter_parser():

    # This is targetted at http query string

    sql = UserSchema.create_select_sql()

    data = {
        'filter_by': {
            'username': 'User%', # starts and/or ends with '%'
            'email': '!null',
            'created_at': '>10-2-2017',
        }
    }

    filters = chain(
        sql.find_filters(data['filter_by'],
            'username', 'email'),

        sql.find_datetime_filters(data['filter_by'],
            'created_at'),
    )

    sql.set_filters(*filters)

    with psycopg2.connect('dbname=testdb') as conn:
        with conn.cursor() as cur:
            # SELECT * FROM users WHERE username LIKE %s
            #     AND email IS NOT NULL
            #     AND created_at > %s
            #     LIMIT 10 OFFSET 0
            #
            # ('User%', datetime.datetime(2017, 2, 10, 0, 0, 0, 0,
            #         tzinfo=<UTC>))
            cur.execute(str(sql), sql.data)


def advance_filter_parser():
    sql = UserSchema.create_select_sql()

    data = {
        'filter_by': [
            'AND',
            [
                'OR',
                {'name': 'User%'},
                {'name': '%User'},
            ],
            [
                'OR',
                {'email': '!null'},
                {'email': '=admin@example.com'},
            },
            {'created_at': '>10-2-2017'},
            {
                'id': ['=', 1, 2, 3],
            },
            {
                'id': ['!', 4, 5, 6],
            },
        }
    }

    advanced_filter = sql.parse_adv_filters(
        data['filter_by'],
        (
            ('username', 'name', 'str'),
            ('email', 'str'),
            ('created_at', 'date'),
            ('id', 'int'),
        ))

    sql.set_filters(advanced_filter)

    with psycopg2.connect('dbname=testdb') as conn:
        with conn.cursor() as cur:
            # SELECT * FROM users WHERE
            #     (username LIKE %s OR username LIKE %s)
            #     AND
            #     (email IS NOT NULL OR email = %s)
            #     AND
            #     created_at > %s
            #     AND
            #     id IN (%s, %s, %s)
            #     AND
            #     id NOT IN (%s, %s, %s)
            #     LIMIT 10 OFFSET 0
            #
            # (
            #     'User%', '%User', 'admin@example.com',
            #     datetime(2017, 2, 10, 0, 0, 0, 0, tzinfo=<UTC>),
            #     1, 2, 3, 4, 5, 6,
            # )
            cur.execute(str(sql), sql.data)

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for firehsql, version 1.0.4
Filename, size File type Python version Upload date Hashes
Filename, size firehsql-1.0.4.tar.gz (40.2 kB) File type Source Python version None Upload date Hashes View

Supported by

Pingdom Pingdom Monitoring Google Google Object Storage and Download Analytics Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page