Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #95515 > unrolled thread

SQLite3 and web server

Back to article view | Back to comp.lang.python

Contents

  SQLite3 and web server Cecil Westerhof <Cecil@decebal.nl> - 2015-08-21 08:11 +0200
    Re: SQLite3 and web server Steven D'Aprano <steve@pearwood.info> - 2015-08-21 16:50 +1000
      Re: SQLite3 and web server Cecil Westerhof <Cecil@decebal.nl> - 2015-08-21 09:13 +0200
        Re: SQLite3 and web server Chris Angelico <rosuav@gmail.com> - 2015-08-21 17:27 +1000
          Re: SQLite3 and web server Cecil Westerhof <Cecil@decebal.nl> - 2015-08-21 11:44 +0200
            Re: SQLite3 and web server Michael Torrie <torriem@gmail.com> - 2015-08-21 08:19 -0600
              Re: SQLite3 and web server Cecil Westerhof <Cecil@decebal.nl> - 2015-08-21 17:23 +0200
            Re: SQLite3 and web server Michael Torrie <torriem@gmail.com> - 2015-08-21 08:57 -0600
              Re: SQLite3 and web server Cecil Westerhof <Cecil@decebal.nl> - 2015-08-22 13:38 +0200
    Re: SQLite3 and web server Peter Otten <__peter__@web.de> - 2015-08-21 11:32 +0200
      Re: SQLite3 and web server Cecil Westerhof <Cecil@decebal.nl> - 2015-08-21 17:12 +0200
        Re: SQLite3 and web server Peter Otten <__peter__@web.de> - 2015-08-22 09:51 +0200
          Re: SQLite3 and web server Cecil Westerhof <Cecil@decebal.nl> - 2015-08-22 13:41 +0200
            Re: SQLite3 and web server Peter Otten <__peter__@web.de> - 2015-08-22 14:23 +0200

#95515 — SQLite3 and web server

At the moment I serve a AngularJS web application with:
    python3 -m http-server

This only servers static html pages with the data contained in js
files, like:
        $scope.links = [
            { desc: 'Album',            url: 'https://plus.google.com/collection/MuwPX'                  },
            { desc: 'Heron Sunbathing', url: 'https://plus.google.com/+CecilWesterhof/posts/bHvSzBGobEj' },
            { desc: 'Heron Fishing',    url: 'https://plus.google.com/+CecilWesterhof/posts/TY3asc5oCnB' },
            { desc: 'Water Lily',       url: 'https://plus.google.com/+CecilWesterhof/posts/AtTwhL8SdnH' },
            { desc: 'Tree at Pond',     url: 'https://plus.google.com/+CecilWesterhof/posts/TyiZbUWdnrm' },
            { desc: 'Fish',             url: 'https://plus.google.com/+CecilWesterhof/posts/MoQ7vXs8HqP' },
            { desc: 'Fountain',         url: 'https://plus.google.com/+CecilWesterhof/posts/BDYkPKSMUwZ' },
            { desc: 'Digitalis',        url: 'https://plus.google.com/+CecilWesterhof/posts/ed3ZGNzb8kM' },
            { desc: 'Sunset',           url: 'https://plus.google.com/+CecilWesterhof/posts/DPbHHSFXBY4' },
            { desc: 'Digitalis 2',      url: 'https://plus.google.com/+CecilWesterhof/posts/ZZtSUwNb6RC' },
            { desc: 'Water Lilies',     url: 'https://plus.google.com/+CecilWesterhof/posts/LY62DqLEJhG' },
            { desc: 'Flower',           url: 'https://plus.google.com/+CecilWesterhof/posts/XFKyTcoakcy' },
            { desc: 'Waterfalls',       url: 'https://plus.google.com/+CecilWesterhof/posts/bfg5irDAn2T' },
            { desc: 'Frogs',            url: 'https://plus.google.com/+CecilWesterhof/posts/jKr5B6EQyo1' },
            { desc: 'Flowers',          url: 'https://plus.google.com/+CecilWesterhof/posts/iPQbBrTbcnm' },
            { desc: 'Sheep',            url: 'https://plus.google.com/+CecilWesterhof/posts/3a2mBo7om4H' },
            { desc: 'Beetle',           url: 'https://plus.google.com/+CecilWesterhof/posts/KnNtis2Gqxf' },
            { desc: 'Dove',             url: 'https://plus.google.com/+CecilWesterhof/posts/XA5RcC2Cxbv' },
            { desc: 'City Walk',        url: 'https://plus.google.com/+CecilWesterhof/posts/R9me9AKQC6n' },
            { desc: 'Boar',             url: 'https://plus.google.com/+CecilWesterhof/posts/9bfpBiQPYen' },
            { desc: 'Bird',             url: 'https://plus.google.com/+CecilWesterhof/posts/X6gFE3oxXLY' },
            { desc: 'Goose',            url: 'https://plus.google.com/+CecilWesterhof/posts/H4w6JvnQkcU' },
            { desc: 'On mothers wings', url: 'https://plus.google.com/+CecilWesterhof/posts/PY4Nm1TASvx' },
            { desc: 'Flowers',          url: 'https://plus.google.com/+CecilWesterhof/posts/9o1i2NgoSfV' },
        ]

I would like to retrieve the information out a SQLite3 database. I did
some Googling, but until now I did not find something useful. How
would I implement this?

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [next] | [standalone]

#95516

On Fri, 21 Aug 2015 04:11 pm, Cecil Westerhof wrote:

> At the moment I serve a AngularJS web application with:
>     python3 -m http-server
> 
> This only servers static html pages with the data contained in js
> files, 


Ah, so you're one of *them*. People who serve static content out of
Javascript, instead of HTML, so that the website is broken and unusable in
browsers without JS, and those with JS turned off.


> I would like to retrieve the information out a SQLite3 database. I did
> some Googling, but until now I did not find something useful. How
> would I implement this?

I feel that by answering this question, I'm in the same position as someone
telling terrorists how best to build a dirty bomb. Oh well.

https://docs.python.org/3/library/sqlite3.html

But surely you need to find out how to access Sqlite from Javascript, not
Python? This doesn't seem to be related to Python in any way. Just because
the web server is written in Python doesn't mean that it's a Python
problem. Your web page is generated from Javascript, not Python.




Making-the-Internet-a-worse-place-one-piece-of-Javascript-at-a-time-ly y'rs,

-- 
Steven

[toc] | [prev] | [next] | [standalone]

#95517

On Friday 21 Aug 2015 08:50 CEST, Steven D'Aprano wrote:

>> I would like to retrieve the information out a SQLite3 database. I
>> did some Googling, but until now I did not find something useful.
>> How would I implement this?
>
> I feel that by answering this question, I'm in the same position as
> someone telling terrorists how best to build a dirty bomb. Oh well.
>
> https://docs.python.org/3/library/sqlite3.html
>
> But surely you need to find out how to access Sqlite from
> Javascript, not Python? This doesn't seem to be related to Python in
> any way. Just because the web server is written in Python doesn't
> mean that it's a Python problem. Your web page is generated from
> Javascript, not Python.

I know how to work with SQLite. What I do not know how to make a
python web-server that accepts a request from the JavaScript code and
responds with data from the SQLite3 database.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#95518

On Fri, Aug 21, 2015 at 5:13 PM, Cecil Westerhof <Cecil@decebal.nl> wrote:
> I know how to work with SQLite. What I do not know how to make a
> python web-server that accepts a request from the JavaScript code and
> responds with data from the SQLite3 database.

The request from JS will be some sort of HTTP transaction. It'll be
AJAX or similar, and to your web server, it'll simply be another page
request. (You might be using a method other than GET/POST (which are
the only two that regular page loads use), but it's still a standard
HTTP request.) You then respond to that request with data that you got
from the database.

Check out this video from PyCon. Its main thrust is "here's why
websockets are great, and why other technologies are insufficient",
but the background info is a tidy summary of how the web works.

https://www.youtube.com/watch?v=u5QT3luWx7w

ChrisA

[toc] | [prev] | [next] | [standalone]

#95520

On Friday 21 Aug 2015 09:27 CEST, Chris Angelico wrote:

> On Fri, Aug 21, 2015 at 5:13 PM, Cecil Westerhof <Cecil@decebal.nl> wrote:
>> I know how to work with SQLite. What I do not know how to make a
>> python web-server that accepts a request from the JavaScript code
>> and responds with data from the SQLite3 database.
>
> The request from JS will be some sort of HTTP transaction. It'll be
> AJAX or similar, and to your web server, it'll simply be another
> page request. (You might be using a method other than GET/POST
> (which are the only two that regular page loads use), but it's still
> a standard HTTP request.) You then respond to that request with data
> that you got from the database.
>
> Check out this video from PyCon. Its main thrust is "here's why
> websockets are great, and why other technologies are insufficient",
> but the background info is a tidy summary of how the web works.
>
> https://www.youtube.com/watch?v=u5QT3luWx7w

Interesting, but it does not help me. I need to know how to server
data from SQLite instead of static files.

I understood that CGI is old school and should not be used anymore,
but I tried it anyway. (Because that is the only thing I found.)

I use the following script:
========================================================================
#!/usr/bin/env python3

import http.server
import os
import socketserver
import sys

os.chdir(os.path.dirname(os.path.abspath(sys.argv[0])))

PORT = 8000
Handler = http.server.CGIHTTPRequestHandler
httpd   = socketserver.TCPServer(('', PORT), Handler)
print('serving at port {0}'.format(PORT))
httpd.serve_forever()
========================================================================

In cgi-bin I have the script helloTxt.py, which contains:
========================================================================
#!/usr/bin/env python3

print('Content-type: text/plain\n\n')
print ('Hello World!\n')
========================================================================

My static files are handled correctly, but when I enter:
    localhost:8000/cgi-bin/helloTxt.py
I get:
----------------------------------------
Exception happened during processing of request from ('127.0.0.1', 49380)
Traceback (most recent call last):
  File "/usr/lib64/python3.4/socketserver.py", line 305, in _handle_request_noblock
    self.process_request(request, client_address)
  File "/usr/lib64/python3.4/socketserver.py", line 331, in process_request
    self.finish_request(request, client_address)
  File "/usr/lib64/python3.4/socketserver.py", line 344, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/usr/lib64/python3.4/socketserver.py", line 665, in __init__
    self.handle()
  File "/usr/lib64/python3.4/http/server.py", line 398, in handle
    self.handle_one_request()
  File "/usr/lib64/python3.4/http/server.py", line 386, in handle_one_request
    method()
  File "/usr/lib64/python3.4/http/server.py", line 677, in do_GET
    f = self.send_head()
  File "/usr/lib64/python3.4/http/server.py", line 961, in send_head
    return self.run_cgi()
  File "/usr/lib64/python3.4/http/server.py", line 1051, in run_cgi
    env['SERVER_NAME'] = self.server.server_name
AttributeError: 'TCPServer' object has no attribute 'server_name'
----------------------------------------


-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#95521

On 08/21/2015 03:44 AM, Cecil Westerhof wrote:
> Interesting, but it does not help me. I need to know how to server
> data from SQLite instead of static files.

Is your program already working with data coming from your Python dict?
 If so, you would just replace the python dict lookups with a call to
SQLite3's function to perform a query on the database. You say you
already know how to work with SQLite, so I assume this is what you're doing.

http://raspberrywebserver.com/sql-databases/accessing-an-sqlite-database-with-python.html

As to the traceback you reported, I don't know enough about using
http.server and socketserver to answer.  Typically web development in
Python is done via WSGI protocol, in conjunction with a web server like
Apache using mod_wsgi[1], which is available in most distro's repos.

But of course you can use straight CGI with Apache too.  Probably this
is the easiest way for you to get started.  Don't try to run your own
http server.  That's just a waste of time and effort. Run the CGIs under
Apache.  Google for Apache CGI to get info on how to set up apache to
execute CGI scripts (in any language).

[1] https://code.google.com/p/modwsgi/wiki/QuickConfigurationGuide

[toc] | [prev] | [next] | [standalone]

#95526

On Friday 21 Aug 2015 16:19 CEST, Michael Torrie wrote:

> On 08/21/2015 03:44 AM, Cecil Westerhof wrote:
>> Interesting, but it does not help me. I need to know how to server
>> data from SQLite instead of static files.
>
> Is your program already working with data coming from your Python
> dict? If so, you would just replace the python dict lookups with a
> call to SQLite3's function to perform a query on the database. You
> say you already know how to work with SQLite, so I assume this is
> what you're doing.
>
> http://raspberrywebserver.com/sql-databases/accessing-an-sqlite-database-with-python.html
>
> As to the traceback you reported, I don't know enough about using
> http.server and socketserver to answer. Typically web development in
> Python is done via WSGI protocol, in conjunction with a web server
> like Apache using mod_wsgi[1], which is available in most distro's
> repos.
>
> But of course you can use straight CGI with Apache too. Probably
> this is the easiest way for you to get started. Don't try to run
> your own http server. That's just a waste of time and effort. Run
> the CGIs under Apache. Google for Apache CGI to get info on how to
> set up apache to execute CGI scripts (in any language).
>
> [1] https://code.google.com/p/modwsgi/wiki/QuickConfigurationGuide

I did manage to get something working:
    https://github.com/CecilWesterhof/PublishedPhotos

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#95522

On 08/21/2015 08:19 AM, Michael Torrie wrote:
> But of course you can use straight CGI with Apache too.  Probably this
> is the easiest way for you to get started.  Don't try to run your own
> http server.  That's just a waste of time and effort. Run the CGIs under
> Apache.  Google for Apache CGI to get info on how to set up apache to
> execute CGI scripts (in any language).

On second thought, maybe your original course of action is better,
though I don't know the cause of the errors you are seeing.

[toc] | [prev] | [next] | [standalone]

#95554

On Friday 21 Aug 2015 16:57 CEST, Michael Torrie wrote:

> On 08/21/2015 08:19 AM, Michael Torrie wrote:
>> But of course you can use straight CGI with Apache too. Probably
>> this is the easiest way for you to get started. Don't try to run
>> your own http server. That's just a waste of time and effort. Run
>> the CGIs under Apache. Google for Apache CGI to get info on how to
>> set up apache to execute CGI scripts (in any language).
>
> On second thought, maybe your original course of action is better,
> though I don't know the cause of the errors you are seeing.

I switched to bottle.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#95519

Cecil Westerhof wrote:

> At the moment I serve a AngularJS web application with:
>     python3 -m http-server
> 
> This only servers static html pages with the data contained in js
> files, like:
>         $scope.links = [
>             { desc: 'Album',            url:
>             { 'https://plus.google.com/collection/MuwPX'                 
>             { }, desc: 'Heron Sunbathing', url:

> I would like to retrieve the information out a SQLite3 database. I did
> some Googling, but until now I did not find something useful. How
> would I implement this?

I like bottle for (not much) dabbling around with generated html.
Assuming there is an sqlite database "links.sqlite" with a table "links" 
featuring columns "desc" and "url" you get a traditional html page built on 
the fly from the data in that table with the code shown below. Producing 
JSON is also demonstrated.

Integrating the JSON data is a matter of the javascript framework; google 
found http://api.jquery.com/jquery.getjson/.

$ cat serve.py
#!/usr/bin/env python3

import bottle
import json
import sqlite3
from contextlib import contextmanager


@contextmanager
def open_db():
    db = sqlite3.connect("links.sqlite")
    cs = db.cursor()
    try:
        yield cs
    finally:
        db.close()


def read_links():
    with open_db() as cs:
        return cs.execute("select desc, url from links;").fetchall()


TEMPLATE = """
<html>
<head>
<title>"Links as found in the db</title>
</head>
<body>
  % for desc, url in rows:
    <a href={{url}}>{{desc}}</a><br/>
  % end
</body></html>
"""


@bottle.route("/links")
def links_as_html():
    rows = read_links()
    return bottle.template(TEMPLATE, rows=rows)


@bottle.route("/links/data")
def links_as_json():
    bottle.response.content_type = "application/json"
    return json.dumps([
        {"desc": desc, "url": url}
        for desc, url in read_links()], indent=4)


if __name__ == "__main__":
    bottle.run(host="localhost", port=8080)

[Instead of the following commandline gymnastics you can just invoke the 
script with 
$ python3 serve.py
and then point your browser to
http://localhost:8080/links]

$ python3 serve.py 2>/dev/null &
[1] 8418
$ curl http://localhost:8080/links 2>/dev/null | head

<html>
<head>
<title>"Links as found in the db</title>
</head>
<body>
    <a href=https://plus.google.com/collection/MuwPX>Album</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/bHvSzBGobEj>Heron 
Sunbathing</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/TY3asc5oCnB>Heron 
Fishing</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/AtTwhL8SdnH>Water 
Lily</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/TyiZbUWdnrm>Tree 
at Pond</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/MoQ7vXs8HqP>Fish</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/BDYkPKSMUwZ>Fountain</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/ed3ZGNzb8kM>Digitalis</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/DPbHHSFXBY4>Sunset</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/ZZtSUwNb6RC>Digitalis 
2</a><br/>
    <a href=https://plus.google.com/+CecilWesterhof/posts/LY62DqLEJhG>Water 
Lilies</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/XFKyTcoakcy>Flower</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/bfg5irDAn2T>Waterfalls</a><br/>
    <a 
href=https://plus.google.com/+CecilWesterhof/posts/jKr5B6EQyo1>Frogs</a><br/>
$ curl http://localhost:8080/links/data 2>/dev/null | head
[
    {
        "desc": "Album",
        "url": "https://plus.google.com/collection/MuwPX"
    },
    {
        "desc": "Heron Sunbathing",
        "url": "https://plus.google.com/+CecilWesterhof/posts/bHvSzBGobEj"
    },
    {
        "desc": "Heron Fishing",
        "url": "https://plus.google.com/+CecilWesterhof/posts/TY3asc5oCnB"
    },
    {
        "desc": "Water Lily",
        "url": "https://plus.google.com/+CecilWesterhof/posts/AtTwhL8SdnH"
    },
    {
        "desc": "Tree at Pond",
        "url": "https://plus.google.com/+CecilWesterhof/posts/TyiZbUWdnrm"

[toc] | [prev] | [next] | [standalone]

#95523

On Friday 21 Aug 2015 11:32 CEST, Peter Otten wrote:

> Cecil Westerhof wrote:
>
>> At the moment I serve a AngularJS web application with:
>> python3 -m http-server
>>
>> This only servers static html pages with the data contained in js
>> files, like:
>> $scope.links = [
>> { desc: 'Album',            url:
>> { 'https://plus.google.com/collection/MuwPX'                 
>> { }, desc: 'Heron Sunbathing', url:
>
>> I would like to retrieve the information out a SQLite3 database. I
>> did some Googling, but until now I did not find something useful.
>> How would I implement this?
>
> I like bottle for (not much) dabbling around with generated html.
> Assuming there is an sqlite database "links.sqlite" with a table
> "links" featuring columns "desc" and "url" you get a traditional
> html page built on the fly from the data in that table with the code
> shown below. Producing JSON is also demonstrated.

Thanks. I made a first try:
    https://github.com/CecilWesterhof/PublishedPhotos

The thing I do not like is that all my static files have to be put in
/static/, but I think I can life with it.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#95548

Cecil Westerhof wrote:

> Thanks. I made a first try:
> https://github.com/CecilWesterhof/PublishedPhotos
> 
> The thing I do not like is that all my static files have to be put in
> /static/, but I think I can life with it.

Is that really required by bottle? Where would you like to put the files? 

[toc] | [prev] | [next] | [standalone]

#95555

On Saturday 22 Aug 2015 09:51 CEST, Peter Otten wrote:

> Cecil Westerhof wrote:
>
>> Thanks. I made a first try:
>> https://github.com/CecilWesterhof/PublishedPhotos
>>
>> The thing I do not like is that all my static files have to be put
>> in /static/, but I think I can life with it.
>
> Is that really required by bottle? Where would you like to put the
> files?

That is how I interpret this:
    http://bottlepy.org/docs/dev/tutorial.html#routing-static-files

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#95557

Cecil Westerhof wrote:

> On Saturday 22 Aug 2015 09:51 CEST, Peter Otten wrote:
> 
>> Cecil Westerhof wrote:
>>
>>> Thanks. I made a first try:
>>> https://github.com/CecilWesterhof/PublishedPhotos
>>>
>>> The thing I do not like is that all my static files have to be put
>>> in /static/, but I think I can life with it.
>>
>> Is that really required by bottle? Where would you like to put the
>> files?
> 
> That is how I interpret this:
>     http://bottlepy.org/docs/dev/tutorial.html#routing-static-files

No, /static/ is in no way special. To drive the point home here's a bogus 
example that gives you a random file from the ~/.thumbnails/large folder:
 
@bottle.route("/<path:path>")
def random_thumbnail(path):
    root = os.path.expanduser("~/.thumbnails/large")
    name = random.choice(os.listdir(root))
    return bottle.static_file(name, root=root)

The path is not even taken into account.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web