genSql() error with MySQL

[Karen K]

A user of my extension weewx-GTS reported an error in a call of genSql() from manager.py. The original line is:

for _result in db_manager.genSql(
        "SELECT `dateTime`,`usUnits`,"
        "`outTemp`,`outHumidity`,`pressure`"
        "from %s WHERE dateTime>? AND dateTime<=?"
        "ORDER BY `dateTime`"
        % db_manager.table_name,timespan):

    ...

He claimed that he had to change that line for use with MySQL to:

for _result in db_manager.genSql( "SELECT `dateTime`,`usUnits`,`outTemp`,`outHumidity`,`pressure` from %s WHERE dateTime> %d AND dateTime<= %d ORDER BY `dateTime`" % (db_manager.table_name,timespan.start,timespan.stop)):

Those 2 question marks are always used throughout WeeWX. So I do not understand why it causes an error in this case. 

Some hints?

[Vince Skahan]

what did the error transcript with the exact error look like ?

[Cameron D]

The problem is you are combining a python formatted string with text for a parameterised query.

For whatever bizarre reason, the python sql specs allow different database interfaces to use different parameter indicators. Mysql uses "%s" while sqlite uses "?".

For a mysql query you obviously can't also add a python string  format in the old (%) style, as the "%s" will be grabbed early.

I don't know how weewx handles the different  query parameters.

[Cameron D]

OK, so , as I might have expected,  the weewx mysql layer replaces '?' with the '%s' parameter marker. 

And there is a specific comment about handling timespan tuples (weedb/mysql.py) just before it calls cursor.execute.

So, back to what Vince said - you'll need the original error message. The suggested replacement is certainly the wrong way to go.

[Karen K]

I have got an idea: There is no space between the last question mark and the word "ORDER". By now I have got no reply from the user for that point.

That is the error log the user provided:
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: Caught unrecoverable exception in generator 'weewx.imagegenerator.ImageGenerator'
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY dateTime' at line 1")
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** Traceback (most recent call last):
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weedb/mysql.py", line 54, in guarded_fn
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** return fn(*args, **kwargs)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weedb/mysql.py", line 266, in execute
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** self.cursor.execute(mysql_string, tuple(sql_tuple))
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 250, in execute
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** self.errorhandler(self, exc, value)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** raise errorvalue
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 247, in execute
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** res = self._query(query)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 411, in _query
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** rowcount = self._do_query(q)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 374, in _do_query
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** db.query(q)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 292, in query
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** _mysql.connection.query(self, query)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY dateTime' at line 1")
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: ****
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** During handling of the above exception, another exception occurred:
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: ****
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** Traceback (most recent call last):
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weewx/reportengine.py", line 197, in run
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** obj.start()
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weewx/reportengine.py", line 385, in start
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** self.run()
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weewx/imagegenerator.py", line 42, in run
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** self.gen_images(self.gen_ts)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weewx/imagegenerator.py", line 109, in gen_images
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** self.image_dict[timespan][plotname])
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weewx/imagegenerator.py", line 249, in gen_plot
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** **option_dict)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weewx/xtypes.py", line 101, in get_series
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** aggregate_interval, **option_dict)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weewx/xtypes.py", line 167, in get_series
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** agg_vt = get_aggregate(obs_type, stamp, do_aggregate, db_manager)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weewx/xtypes.py", line 128, in get_aggregate
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** **option_dict)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/user/GTS.py", line 1039, in get_aggregate
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** return self.calc_derived(obs_type,timespan,aggregate_type,db_manager)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/user/GTS.py", line 885, in calc_derived
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** % db_manager.table_name,timespan):
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weewx/manager.py", line 473, in genSql
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** for _row in _cursor.execute(sql, sqlargs):
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** File "/usr/share/weewx/weedb/mysql.py", line 63, in guarded_fn
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** raise klass(e)
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** weedb.DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY dateTime' at line 1")
2022-08-22 22:00:40 weewx[15344] ERROR weewx.reportengine: **** Generator terminated

[Cameron D]

that would make sense.  perhaps the sqlite library "helpfully" inserts the space.  Not what you requested but probably what you intended.  The python concatenation does not insert a space.

[Karen K]

The user reported: inserting the space resolved the issue.