pg_query 0.19

Introduction

At the lower level the module exposes two libpg_query functions, parse_sql() and parse_plpgsql(), that take respectively an SQL statement and a PLpgSQL statement and return a parse tree as a hierarchy of Python dictionaries, lists and scalar values. In some cases these scalars correspond to some C typedef enums, that are automatically extracted from the PostgreSQL headers and are available as pg_query.enums.

At a higher level that tree is represented by three Python classes, a Node that represents a single node, a List that wraps a sequence of nodes and a Scalar for plain values such a strings, integers, booleans or none.

Every node is identified by a tag, a string label that characterize its content that is exposed as a set of attributes as well as with a dictionary-like interface (technically they implements both a __getattr__ method and a __getitem__ method). When asked for an attribute, the node returns an instance of the base classes, i.e. another Node, or a List or a Scalar, depending on the data type of that item. When the node does not contain the requested attribute it returns a singleton Missing marker instance.

A List wraps a plain Python list and may contains a sequence of Node instances, or in some cases other sub-lists, that can be accessed with the usual syntax, or iterated.

Finally, a Scalar carries a single value of some type, accessible through its value attribute.

On top of that, the module implements two serializations, one that transforms a Node into a raw textual representation and another that returns a prettified representation. The latter is exposed by the __main__ entry point of the package, see below for an example.

Installation

As usual, the easiest way is with pip:

$ pip install pg_query

Alternatively you can clone the repository:

$ git clone https://github.com/lelit/pg_query.git --recursive

and install from there:

$ pip install ./pg_query

Development

There is a set of makefiles implementing the most common operations, a make help will show a brief table of contents. A comprehensive test suite, based on pytest, covers 98% of the source lines.

Examples of usage

• Parse an SQL statement and get its AST root node:

  >>> from pg_query import Node, parse_sql
  >>> root = Node(parse_sql('SELECT foo FROM bar'))
  >>> print(root)
  None=[1*{RawStmt}]

• Recursively traverse the parse tree:

  >>> for node in root.traverse():
  ...   print(node)
  ...
  None[0]={RawStmt}
  stmt={SelectStmt}
  fromClause[0]={RangeVar}
  inh=<True>
  location=<16>
  relname=<'bar'>
  relpersistence=<'p'>
  op=<0>
  targetList[0]={ResTarget}
  location=<7>
  val={ColumnRef}
  fields[0]={String}
  str=<'foo'>
  location=<7>

  As you can see, the representation of each value is mnemonic: {some_tag} means a Node with tag some_tag, [X*{some_tag}] is a List containing X nodes of that particular kind[†] and <value> is a Scalar.

• Get a particular node:

  >>> from_clause = root[0].stmt.fromClause
  >>> print(from_clause)
  fromClause=[1*{RangeVar}]

• Obtain some information about a node:

  >>> range_var = from_clause[0]
  >>> print(range_var.node_tag)
  RangeVar
  >>> print(range_var.attribute_names)
  dict_keys(['relname', 'inh', 'relpersistence', 'location'])
  >>> print(range_var.parent_node)
  stmt={SelectStmt}

• Iterate over nodes:

  >>> for a in from_clause:
  ...     print(a)
  ...     for b in a:
  ...         print(b)
  ...
  fromClause[0]={RangeVar}
  inh=<True>
  location=<16>
  relname=<'bar'>
  relpersistence=<'p'>

• Reformat a SQL statement[‡] from the command line:

  $ echo "select a,b,c from sometable" | pgpp
  SELECT a
       , b
       , c
  FROM sometable
  
  $ echo 'update "table" set value=123 where value is null' | pgpp
  UPDATE "table"
  SET value = 123
  WHERE value IS NULL
  
  $ echo "
  insert into t (id, description)
  values (1, 'this is short enough'),
         (2, 'this is too long, and will be splitted')" | pgpp -s 20
  INSERT INTO t (id, description)
  VALUES (1, 'this is short enough')
       , (2, 'this is too long, an'
             'd will be splitted')

• Programmatically reformat a SQL statement:

  >>> from pg_query import prettify
  >>> print(prettify('delete from sometable where value is null'))
  DELETE FROM sometable
  WHERE value IS NULL

Documentation

Latest documentation is hosted by Read the Docs at http://pg-query.readthedocs.io/en/latest/

Changes