sqlite errors in docker image?

[Mist-Hunter]

After spending a couple hours looking for an image that plots speed, it looked like your image was the best, light weight etc. Thank you for your work and making this public!

The error below was produced with:

docker run -d -p 8000:8000 ansemjo/speedtest

I am, however, having an issue. The image seems to be doing this:

eated directory: '/data',

• Connected database: sqlite:////data/speedtests.db,
• Serving Flask app "speedtest" (lazy loading),
• Environment: development,
• Debug mode: off,
• Running on http://0.0.0.0:8000/ (Press CTRL+C to quit),
  [2020-03-26 13:39:04,885] ERROR in app: Exception on / [GET],
  Traceback (most recent call last):,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context,
  self.dialect.do_execute(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute,
  cursor.execute(statement, parameters),
  sqlite3.OperationalError: no such table: speedtest,
  ,
  The above exception was the direct cause of the following exception:,
  ,
  Traceback (most recent call last):,
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 2446, in wsgi_app,
  response = self.full_dispatch_request(),
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1951, in full_dispatch_request,
  rv = self.handle_user_exception(e),
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1820, in handle_user_exception,
  reraise(exc_type, exc_value, tb),
  File "/usr/local/lib/python3.8/site-packages/flask/_compat.py", line 39, in reraise,
  raise value,
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1949, in full_dispatch_request,
  rv = self.dispatch_request(),
  File "/usr/local/lib/python3.8/site-packages/flask/app.py", line 1935, in dispatch_request,
  return self.view_functionsrule.endpoint,
  File "./speedtest-plotter", line 112, in home,
  rows = retrieve_measurements(limit, after),
  File "./speedtest-plotter", line 82, in retrieve_measurements,
  return db.query("SELECT * FROM speedtest WHERE "Timestamp" IS NOT NULL ORDER BY "Timestamp" DESC LIMIT :li", li=limit),
  File "/usr/local/lib/python3.8/site-packages/dataset/database.py", line 253, in query,
  rp = self.executable.execute(query, *args, **kwargs),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 988, in execute,
  return meth(self, multiparams, params),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection,
  return connection._execute_clauseelement(self, multiparams, params),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement,
  ret = self._execute_context(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1252, in _execute_context,
  self._handle_dbapi_exception(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception,
  util.raise_from_cause(sqlalchemy_exception, exc_info),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause,
  reraise(type(exception), exception, tb=exc_tb, cause=cause),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise,
  raise value.with_traceback(tb),
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context,
  self.dialect.do_execute(,
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute,
  cursor.execute(statement, parameters),
  sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: speedtest,
  [SQL: SELECT * FROM speedtest WHERE "Timestamp" IS NOT NULL ORDER BY "Timestamp" DESC LIMIT ?],
  [parameters: (1344,)],
  (Background on this error at: http://sqlalche.me/e/e3q8),

[ansemjo]

Does this happen by trying to look at the graph in a browser directly after starting the container or all by itself?

I'm using dataset to connect to the database and retrieve data. It is stupid simple to use but it also doesn't create the tables etc. before it is instructed to store the first row in the database. I.e. what you are probably seeing is the attempt to select rows from a database where none exist because no measurements have been taken yet.

Afaik it should go away after two measurements have been taken. You can force taking a measurement before the cron schedule with:

docker exec <container-id> speedtest-plotter measure

[Mist-Hunter]

Thanks for the fast reply, I'll test further :)

[Mist-Hunter]

I was probably not giving it enough time, it seemed to be doing it on it's own. Currently that error has cleared after the first speed test was logged.

The page is now showing a partial result, but no image yet (probably doesn't plot with just one point?).

I did get the following when trying to runt he command above:

docker run -d -p 8000:8000 ansemjo/speedtest
bab6e8985d5017edc3f05bd7150432cc5e8eefcdeec0d3be4d95be28389d743b

docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bab6e8985d50 ansemjo/speedtest "/bin/ash /entrypoin…" 38 seconds ago Up 37 seconds 0.0.0.0:8000->8000/tcp clever_benz

docker exec bab6e8985d50 speedtest-plotter measure
OCI runtime exec failed: exec failed: container_linux.go:349: starting container process caused "exec: "speedtest-plotter": executable file not found in $PATH": unknown

docker exec bab6e8985d50 measure
OCI runtime exec failed: exec failed: container_linux.go:349: starting container process caused "exec: "measure": executable file not found in $PATH": unknown

I hope this isn't my environment, I'll close this for now and give it some time.

[ansemjo]

Oh. No that was my bad. It should have been docker exec ... ./speedtest-plotter measure.

And yes, it needs two measurements until the plot appears because gnuplot is instructed to apply some smoothing -- which only works on two or more datapoints.

[ansemjo]

I've pushed a few commits just now:

• d6e618b display a message about number of measurements if no plot can be created
• ffa91b3 amend PATH variable in docker container with script workdir
• 9384e6b add simple exception handling on main flask route

This should display a more useful message when you're trying to display results with an empty database or too few rows to generate a graph.

The automated Dockerhub build is currently pending but should be done in a few minutes.

[Mist-Hunter]

Nice! Thanks again for your work.

Is it possible your speed test data string has new elements in it, it almost looks like they are offset slightly or are breaking at the wrong point in my case, maybe near "Seattle WA". Maybe you are breaking elements on "," and the sponsors are returning "Seattle, WA".

{'Server ID': '6199', 'Sponsor': 'Wowrack', 'Server Name': '"Seattle', 'Timestamp': ' WA"', 'Distance': '2020-03-26T14:46:57.495763Z', 'Ping': '3.901019554563146', 'Download': '16.944', 'Upload': '86800814.61200348', 'Share': '6072783.422078191', 'IP Address': ''}

[ansemjo]

Aw, crap. Yes, I'm just using a .split(',') in my result parser ... I'll have a look if I can quickly plug in a proper CSV parser.

[ansemjo]

This should be fixed in d391d19 now. Again, the automated Dockerhub build will take a moment to complete.

---

Some trivia: what I'm doing here is super inefficient .. I am using the subprocess module to execute speedtest-cli, wrap its output in a io.StringIO object and then use a csv.DictReader to parse the result. This has grown historically because originally this was intended to be an experiment in using gnuplot to create pretty graphs from text files .. the database and the Flask webserver only came later.

By now it's probably time for a proper "v2" where the speedtest library is used directly from Python -- no CSV parsing necessary -- and the graphs are rendered client-side with some Javascript.

I still hope it can be useful as is. :)

[Mist-Hunter]

Thanks! Verified fixed {'Server ID': '8864', 'Sponsor': 'CenturyLink', 'Server Name': 'Seattle, WA', 'Timestamp': '2020-03-26T15:31:10.383180Z', 'Distance': '3.901019554563146', 'Ping': '42.382', 'Download': '48027924.148877', 'Upload': '4967379.51062998', 'Share': '', 'IP Address': <REMOVED>'} I know enough coding to be helpful here and there, but not enough to do what you are doing. Most people doing this speed plotting are using multiple containers and allot more resources, so you may think yours is simple, but I think it's better than the others. Thanks again. [image: image.png]

…

On Thu, Mar 26, 2020 at 8:26 AM ansemjo ***@***.***> wrote: This should be fixed in d391d19 <d391d19> now. Again, the automated Dockerhub build will take a moment to complete. ------------------------------ Some trivia: what I'm doing here is super inefficient .. I am using the subprocess module to execute speedtest-cli, wrap its output in a io.StringIO object and then use a csv.DictReader to parse the result. This has grown historically because originally this was intended to be an experiment in using gnuplot to create pretty graphs from text files .. the database and the Flask webserver only came later. By now it's probably time for a proper "v2" where the speedtest library is used directly from Python -- no CSV parsing necessary -- and the graphs are rendered client-side with some Javascript. I still hope it can be useful as is. :) — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub <#2 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQ7RTCHL6TYRMZ7AQUZ35TRJNX25ANCNFSM4LUHVMGA> .