root/livinglogic.python.xist/src/ll/scripts/db2ul4.py @ 5366:fb609031902a

Revision 5366:fb609031902a, 5.7 KB (checked in by Walter Doerwald <walter@…>, 6 years ago)

Revert db2ul4 docstring.

Line 
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4"""
5``db2ul4`` is a script that can be used for rendering database content into
6a UL4 template.
7
8
9Options
10-------
11
12``db2ul4`` supports the following options:
13
14    ``templates``
15        One or more template files. A file named ``-`` will be treated as
16        standard input. The first file in the list is the main template, i.e. the
17        one that gets rendered. All templates will be available in the main
18        template as the ``templates`` dictionary. The keys are the base names
19        of the files (i.e. ``foo.ul4`` will be ``templates.foo``; stdin will be
20        ``templates.stdin``).
21
22    ``-i``, ``--inputencoding``
23        The encoding of the templates files (default ``utf-8``)
24
25
26Template variables
27------------------
28
29Inside the template the following variables are available:
30
31    ``templates``
32        A dictionary containing all the templates specified on the command line.
33
34    ``encoding``
35        The output encoding.
36
37    ``system``
38        A dict-like object that maps system commands to their output, e.g. the
39        template::
40
41            <?print system["whoami"]?>
42
43        will output the user name.
44
45    ``oracle``
46        A dict-like object that maps Oracle connect strings to connection objects.
47
48    ``mysql``
49        A dict-like object that maps MySQL connect strings to connection objects.
50        A MySQL connect string is a string of the form ``user/pwd@host/db``.
51
52    ``sqlite``
53        A dict-like object that maps SQLite connect strings to connection objects.
54        The connect string will be passed directly to :func:`sqlite3.connect`.
55
56All connection objects are itself dict-like objects providing two keys:
57
58    ``iter``
59        A dict-like object that maps SQL queries to iterators over the
60        result records.
61
62    ``list``
63        A dict-like object that maps SQL queries to a list of result records.
64
65A record in turn is a dict-like object mapping field names to field values.
66
67
68Example
69-------
70
71This example shows how to connect to an Oracle database and output the content
72of a ``person`` table into an XML file.
73
74Suppose we have a database table that looks like this::
75
76    create table person
77    (
78        id integer not null,
79        firstname varchar2(200),
80        lastname varchar2(200)
81    );
82
83Then we can use the following template to output the table into an XML file::
84
85    <?xml version='1.0' encoding='<?print encoding?>'?>
86    <?code db = oracle["user/pwd@db"]?>
87    <persons>
88        <?for p in db.iter["select id, firstname, lastname from person order by 2, 1"]?>
89            <person id="<?printx p.id?>">
90                <firstname><?printx p.firstname?></firstname>
91                <lastname><?printx p.lastname?></lastname>
92            </person>
93        <?end for?>
94    </persons>
95
96If we put the template into the file ``person.ul4`` we can call ``db2ul4`` like
97this::
98
99    db2ul4 -o=utf-8 person.ul4 >person.xml
100"""
101
102
103import sys, os, argparse, codecs, keyword
104
105from ll import ul4c
106
107
108__docformat__ = "reStructuredText"
109
110
111class System(object):
112    def __getitem__(self, cmd):
113        return os.popen(cmd).read()
114
115
116class QueryList(object):
117    def __init__(self, connection):
118        self.connection = connection
119
120    def __getitem__(self, query):
121        c = self.connection.cursor()
122        c.execute(query)
123        return list(c)
124
125
126class QueryIter(object):
127    def __init__(self, connection):
128        self.connection = connection
129
130    def __getitem__(self, query):
131        c = self.connection.cursor()
132        c.execute(query)
133        return c
134
135
136class Connection(object):
137    def __init__(self, connection):
138        self.connection = connection
139
140    def __getitem__(self, key):
141        if key == "iter":
142            return QueryIter(self.connection)
143        elif key == "list":
144            return QueryList(self.connection)
145        else:
146            raise KeyError(key)
147
148
149class Oracle(object):
150    def __getitem__(self, connectstring):
151        from ll import orasql
152        return Connection(orasql.connect(connectstring, readlobs=True))
153
154
155class SQLite(object):
156    def __getitem__(self, connectstring):
157        import sqlite3
158        connection = sqlite3.connect(connectstring)
159        class Row(sqlite3.Row):
160            def __getitem__(self, key):
161                if isinstance(key, str):
162                    key = key.encode("ascii")
163                return sqlite3.Row.__getitem__(self, key)
164        connection.row_factory = Row
165        return Connection(connection)
166
167
168class MySQL(object):
169    def __getitem__(self, connectstring):
170        import MySQLdb
171        from MySQLdb import cursors
172        (user, host) = connectstring.split("@")
173        (user, passwd) = user.split("/")
174        (host, db) = host.split("/")
175        return Connection(MySQLdb.connect(user=user, passwd=passwd, host=host, db=db, use_unicode=True, cursorclass=cursors.DictCursor))
176
177
178def fixname(name):
179    newname = "".join(c for (i, c) in enumerate(name) if (c.isalnum() if i else c.isalpha()))
180    while keyword.iskeyword(newname):
181        newname += "_"
182    return newname
183
184
185def main(args=None):
186    p = argparse.ArgumentParser(description="render UL4 templates containing SQL statements", epilog="For more info see http://www.livinglogic.de/Python/scripts/db2ul4.html")
187    p.add_argument("templates", metavar="template", help="templates to be used", nargs="+")
188    p.add_argument("-e", "--encoding", dest="encoding", help="Encoding for template sources (default %(default)s)", default="utf-8", metavar="ENCODING")
189
190    args = p.parse_args(args)
191
192    templates = {}
193    maintemplate = None
194    for templatename in args.templates:
195        if templatename == "-":
196            templatestream = sys.stdin
197            templatename = "stdin"
198        else:
199            templatestream = open(templatename, "r", encoding=args.encoding)
200            templatename = os.path.basename(templatename)
201            if os.path.extsep in templatename:
202                templatename = templatename.rpartition(os.extsep)[0]
203        template = ul4c.Template(templatestream.read(), fixname(templatename))
204        # The first template is the main template
205        if maintemplate is None:
206            maintemplate = template
207        templates[templatename] = template
208
209    vars = dict(
210        oracle=Oracle(),
211        sqlite=SQLite(),
212        mysql=MySQL(),
213        system=System(),
214        templates=templates,
215        encoding=sys.stdout.encoding,
216    )
217    for part in maintemplate.render(**vars):
218        sys.stdout.write(part)
219
220
221if __name__ == "__main__":
222    sys.exit(main())
Note: See TracBrowser for help on using the browser.