root/livinglogic.python.xist/src/ll/scripts/db2ul4.py @ 4437:6f3d4e845072

Revision 4437:6f3d4e845072, 5.8 KB (checked in by Walter Doerwald <walter@…>, 8 years ago)

Fix typos in script documentation. Add examples. Bump version number.

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