HTML output using Python 3/sqlite3

Question

Newbie to Python and SQLLite: @ http://www.sqlite.org/sqlite.html - "Command Line Shell For SQLite" I found this:

Changing Output Formats: The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.

Question: How to do this with the bundled SQLite package that comes with Python 3 - I can open a connection, get a cursor, execute SQL etc but I can't figure out how to use the .mode html command - or any of the dot commands.

Here's an example program - but I want to output the results 'print(l)' on last line in html format using sqlite .mode html

import sqlite3
def main():

    conn = sqlite3.connect('c:\dbTest.dat')
    curs=conn.cursor()
    fs='insert into test(token) values ("%s")'
    i=0
    x=input("Enter a number please: ")
    x.lstrip()
    x=int(x)
    while i<x:
        s=fs % ("ABCD"+str(i/0.999)) 
        curs.execute(s)
        i=i+1
    conn.commit()
    rows=curs.execute('select token from test')
    l=rows.fetchall()
    print(l)

main();

Any help would be appreciated.

TIA

The various dot commands are features of the sqlite3 program, they're not part of the SQLite database library. AFAIK you'll have to produce the HTML yourself if you need HTML output. Or you could talk to sqlite3 using some pipes but that's probably more trouble that it is worth. — mu is too short
– mu is too short, Commented May 23, 2011 at 5:10
@mu is too short, aspw can provide .mode support in python — Mike Pennington
– Mike Pennington, Commented May 23, 2011 at 5:28

Mike Pennington · Accepted Answer · 2011-05-24 07:58:12Z

1

sqlite's html mode is part of a wrapper that is not included in python, but you can use the aspw module to access it...

import apsw
import StringIO as io
output=io.StringIO()
conn = apsw.Connection('dbTest.dat')
shell=apsw.Shell(stdout=output, db=conn)
# How to execute a dot command
shell.process_command(".mode html")
# continue

See the ASPW example for more details

EDIT

If you are using python3...

Python 3.2 (r32:88445, Feb 20 2011, 21:29:02) [MSC v.1500 32 bit (Intel)] on win32
Type "copyright", "credits" or "license()" for more information.
>>> import io
>>> import apsw
>>> output = io.StringIO()
>>> conn = apsw.Connection(":memory:")
>>> shell = apsw.Shell(stdout=output, db=conn)
>>> shell.process_command(".mode html")
>>>

edited May 24, 2011 at 7:58

answered May 23, 2011 at 5:24

Mike Pennington

43.2k22 gold badges140 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vector Over a year ago

@Mike -"part of a wrapper that is not included in python" - I was afraid that might be the problem. Will have to look into the apsw module and then I can accept your answer. Tnx

Mike Pennington Over a year ago

@Mikey, no worries. Take your time

Vector Over a year ago

@Mike - I installed apsw for P 3.2 but can't run your code: where is StringIO module? I don't have it. And on "conn = apsw.Connection('e:\dbTest.dat') I get this error: builtins.AttributeError: 'module' object has no attribute 'Connection'" - tnx

Mike Pennington Over a year ago

@Mikey, please see my edit... try io.StringIO() under python3. I'm not sure why you're having problems with apsw.Connection(). How did you install apsw? I installed with their binary python3.2 installer, and as you can see I am using apsw.Connection() in my IDLE session above.

Vector Over a year ago

@mike - got the edit - understood. I installed apsw with binary installer package they supply on their website - it found my python 3.2 and installed and I was able to import the library.But it didn't recognize Connection() as you can see maybe there is something dependent on io? I'll try again tonight. I am using Wing IDE, which has been solid as rock but I'll try with IDLE. Tnx.

|

Collectives™ on Stack Overflow

HTML output using Python 3/sqlite3

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related