How to create or alter a DB schema dynamically (at run time) using Gramex FormHandler-CodePudding

I want to be able to (at run time) create or alter a DB schema dynamically on a particular event (e.g. click of a button) using FormHandler microservice of Gramex.

CodePudding user response：

You can do it using queryfunction of FormHandler which can modify the query based on the query parameters passed in the url.

Refer the link below for more https://gramener.com/gramex/guide/formhandler/#formhandler-queryfunction

CodePudding user response：

FormHandler supports defining columns in the spec.

For example, this configuration creates a table called profile with 4 columns: user, password, age and id.

url:
    handler: FormHandler
    kwargs:
      url: 'postgresql://$USER:$PASS@server/db'       # Pick any database
      table: profile              # Pick any table name to create
      id: id                      # The "id" column is primary key
      # Define your table's columns
      columns:
        user: TEXT                # Use any SQL type allowed by DB
        password: VARCHAR(40)     # including customizations
        age:
          type: INTEGER           # You can also specify as a dict
          nullable: true          # Allows NULL values for this field
          default: 0              # that default to zero
        id:
          type: INTEGER           # Define an integer ID column
          primary_key: true       # as a primary key
          autoincrement: true     # that auto-increments

But if this needs to be changed at runtime, e.g. when a user clicks on a button, you can use a FunctionHandler with gramex.data.alter()

For example, add this to your gramex.yaml:

url:
  alter:
    pattern: /alter
    handler: FunctionHandler
    kwargs:
      # You can decide the columns dynamically here
      function: >
        gramex.data.alter(url, table, columns={
           col: 'TEXT' for col in handler.args.get('col', [])
        })

When /alter?col=email is called, the function adds an email column as text.

NOTE: There's no option to DELETE columns.

CodePudding user response：

You should try this:

In your yaml handler:

queryfunction: mymodule.sales_query(args)

In your python code:

def sales_query(args):
    cities = args.get('ct', [])
    if len(cities) > 0:
        vals = ', '.join("'%s'" % pymysql.escape_string(v) for v in cities)
        return 'SELECT * FROM sales WHERE city IN (%s)' % vals
    else:
        return 'SELECT * FROM sales'

Reference from: https://gramener.com/gramex/guide/formhandler/#formhandler-queryfunction