Re: [Pyparsing] Grouping when using asList()

[Paul M. <pa...@al...>]

Ok, here are the changes I made to your code:

I changed column_def to:

col_width_def = (p.Suppress('(') + 
              p.delimitedList(p.Word(p.nums)).setParseAction( lambda toks:
[int(tok) for tok in toks]) + 
              p.Suppress(')')
              )
              
column_def = (bracket_quoted.setResultsName('column_name') +
              bracket_quoted.setResultsName('column_type') +
 
p.Optional(p.CaselessKeyword('IDENTITY')).setResultsName('identity') +
              #~ p.Optional(paren_quoted(p.nums + p.Optional(',' +
p.nums))).setResultsName('column_width') +
              p.Optional(col_width_def).setResultsName('column_width') +
              nullable
              )

paren_quoted was just not the expression, and the "(p.nums + p.Optional(','
+ p.nums)" argument *actually* created a results name for the column_width
field.


I changed create_table to:

create_table = (p.CaselessKeyword('create').suppress()
                + p.CaselessKeyword('table').suppress()
                + bracket_quoted.setResultsName('schema')
                + "." + bracket_quoted.setResultsName('table_name')
                #~ + p.nestedExpr(content=p.delimitedList(p.Or(
                    #~ [p.Group(column_def.setResultsName('columns')),
                    #~ p.Group(primary_key),
                    #~ p.Group(constraint)])))
                + p.Group(p.Suppress('(') + 
                        p.delimitedList(p.Or(
                    [p.Group(column_def).setResultsName('columns',
listAllMatches=True),
                    p.Group(primary_key).setResultsName('pkeys',
listAllMatches=True),
                    p.Group(constraint).setResultsName('constraints',
listAllMatches=True)]
                    ))
                    + p.Suppress(')')
                    )("defs")
                + p.CaselessKeyword('on').suppress()
                + bracket_quoted.suppress()
                )

There was no need to use nestedExpr for the list of column/key/constraint
items.  Note the use of listAllMatches in the setResultsName calls.

Here is the code to print out the parsed results:

dbData = create_table.parseString(sql)
pprint.pprint(dbData.asList())
print
print dbData.dump()
print
print "Columns"
for c in dbData.defs.columns:
    print "  Column: " + c.column_name
    print c.dump(indent="    ")
print
print "Primary Keys"
for pk in dbData.defs.pkeys:
    print pk.asList()
print
print "Constraints"
for c in dbData.defs.constraints:
    print "Constraint: " + c.constraint_name
    print c.asList()

Note the use of dump() for quick listing of tokens, and any named fields.

The output from this code is:

['dbo',
 '.',
 'auth_login',
 [['login_id', 'int', 'IDENTITY', 1, 1, 'not null'],
  ['login', 'varchar', 255, 'null'],
  ['password_hash', 'varchar', 255, 'null'],
  ['login_name', 'varchar', 255, 'null'],
  ['paid_us_good', 'money', 'null'],
  ['asdfsdafsadf', 'nchar', 10, 'null'],
  ['primary key', 'clustered', ['login_id']],
  ['constraint', 'IX_auth_login', 'unique', 'nonclustered', ['login']]]]

['dbo', '.', 'auth_login', [['login_id', 'int', 'IDENTITY', 1, 1, 'not
null'], ['login', 'varchar', 255, 'null'], ['password_hash', 'varchar', 255,
'null'], ['login_name', 'varchar', 255, 'null'], ['paid_us_good', 'money',
'null'], ['asdfsdafsadf', 'nchar', 10, 'null'], ['primary key', 'clustered',
['login_id']], ['constraint', 'IX_auth_login', 'unique', 'nonclustered',
['login']]]]
- defs: [['login_id', 'int', 'IDENTITY', 1, 1, 'not null'], ['login',
'varchar', 255, 'null'], ['password_hash', 'varchar', 255, 'null'],
['login_name', 'varchar', 255, 'null'], ['paid_us_good', 'money', 'null'],
['asdfsdafsadf', 'nchar', 10, 'null'], ['primary key', 'clustered',
['login_id']], ['constraint', 'IX_auth_login', 'unique', 'nonclustered',
['login']]]
  - columns: [['login_id', 'int', 'IDENTITY', 1, 1, 'not null'], ['login',
'varchar', 255, 'null'], ['password_hash', 'varchar', 255, 'null'],
['login_name', 'varchar', 255, 'null'], ['paid_us_good', 'money', 'null'],
['asdfsdafsadf', 'nchar', 10, 'null']]
  - constraints: [['constraint', 'IX_auth_login', 'unique', 'nonclustered',
['login']]]
  - pkeys: [['primary key', 'clustered', ['login_id']]]
- schema: dbo
- table_name: auth_login

Columns
  Column: login_id
    ['login_id', 'int', 'IDENTITY', 1, 1, 'not null']
    - column_name: login_id
    - column_type: int
    - column_width: [1, 1]
    - identity: IDENTITY
  Column: login
    ['login', 'varchar', 255, 'null']
    - column_name: login
    - column_type: varchar
    - column_width: [255]
  Column: password_hash
    ['password_hash', 'varchar', 255, 'null']
    - column_name: password_hash
    - column_type: varchar
    - column_width: [255]
  Column: login_name
    ['login_name', 'varchar', 255, 'null']
    - column_name: login_name
    - column_type: varchar
    - column_width: [255]
  Column: paid_us_good
    ['paid_us_good', 'money', 'null']
    - column_name: paid_us_good
    - column_type: money
  Column: asdfsdafsadf
    ['asdfsdafsadf', 'nchar', 10, 'null']
    - column_name: asdfsdafsadf
    - column_type: nchar
    - column_width: [10]

Primary Keys
['primary key', 'clustered', ['login_id']]

Constraints
Constraint: IX_auth_login
['constraint', 'IX_auth_login', 'unique', 'nonclustered', ['login']]


-- Paul