sql2dot.py 2.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596
  1. #!/usr/bin/python
  2. # sql2dot.py
  3. #
  4. # Creates table graphics by parsing SQL table DML commands and
  5. # generating DOT language output.
  6. #
  7. # Adapted from a post at https://energyblog.blogspot.com/2006/04/blog-post_20.html.
  8. #
  9. sampleSQL = """
  10. create table student
  11. (
  12. student_id integer primary key,
  13. firstname varchar(20),
  14. lastname varchar(40),
  15. address1 varchar(80),
  16. address2 varchar(80),
  17. city varchar(30),
  18. state varchar(2),
  19. zipcode varchar(10),
  20. dob date
  21. );
  22. create table classes
  23. (
  24. class_id integer primary key,
  25. id varchar(8),
  26. maxsize integer,
  27. instructor varchar(40)
  28. );
  29. create table student_registrations
  30. (
  31. reg_id integer primary key,
  32. student_id integer,
  33. class_id integer
  34. );
  35. alter table only student_registrations
  36. add constraint students_link
  37. foreign key
  38. (student_id) references students(student_id);
  39. alter table only student_registrations
  40. add constraint classes_link
  41. foreign key
  42. (class_id) references classes(class_id);
  43. """.upper()
  44. from pyparsing import Literal, Word, delimitedList \
  45. , alphas, alphanums \
  46. , OneOrMore, ZeroOrMore, CharsNotIn \
  47. , replaceWith
  48. skobki = "(" + ZeroOrMore(CharsNotIn(")")) + ")"
  49. field_def = OneOrMore(Word(alphas,alphanums+"_\"':-") | skobki)
  50. def field_act(s,loc,tok):
  51. return ("<"+tok[0]+"> " + " ".join(tok)).replace("\"","\\\"")
  52. field_def.setParseAction(field_act)
  53. field_list_def = delimitedList( field_def )
  54. def field_list_act(toks):
  55. return " | ".join(toks)
  56. field_list_def.setParseAction(field_list_act)
  57. create_table_def = Literal("CREATE") + "TABLE" + Word(alphas,alphanums+"_").setResultsName("tablename") + \
  58. "("+field_list_def.setResultsName("columns")+")"+ ";"
  59. def create_table_act(toks):
  60. return """"%(tablename)s" [\n\t label="<%(tablename)s> %(tablename)s | %(columns)s"\n\t shape="record"\n];""" % toks
  61. create_table_def.setParseAction(create_table_act)
  62. add_fkey_def=Literal("ALTER")+"TABLE"+"ONLY" + Word(alphanums+"_").setResultsName("fromtable") + "ADD" \
  63. + "CONSTRAINT" + Word(alphanums+"_") + "FOREIGN"+"KEY"+"("+Word(alphanums+"_").setResultsName("fromcolumn")+")" \
  64. +"REFERENCES"+Word(alphanums+"_").setResultsName("totable")+"("+Word(alphanums+"_").setResultsName("tocolumn")+")"+";"
  65. def add_fkey_act(toks):
  66. return """ "%(fromtable)s":%(fromcolumn)s -> "%(totable)s":%(tocolumn)s """ % toks
  67. add_fkey_def.setParseAction(add_fkey_act)
  68. other_statement_def = ( OneOrMore(CharsNotIn(";") ) + ";")
  69. other_statement_def.setParseAction( replaceWith("") )
  70. comment_def = "--" + ZeroOrMore(CharsNotIn("\n"))
  71. comment_def.setParseAction( replaceWith("") )
  72. statement_def = comment_def | create_table_def | add_fkey_def | other_statement_def
  73. defs = OneOrMore(statement_def)
  74. print("""digraph g { graph [ rankdir = "LR" ]; """)
  75. for i in defs.parseString(sampleSQL):
  76. if i!="":
  77. print(i)
  78. print("}")