I can't connect to MySQL database

[paresh138@…]

Hi

I managed to get this to build with pymills v3.4.2 by chnaging the line from pymills.db import Connections to from pymills.dbapi import Connections

now it seems tp load in trac 1.0.

Now I want to know how to get data from the database.

When I use the trac ticket table, I get an error with the connection string.

To try this, I have the macro enabled and have this setting: uri = mysql://mytracuser:mytracpassword@localhost/trac

This is the same connection I use for this instance of trac database = mysql://mytracuser:mytracpassword@localhost/trac

so, when a page with the macro [[SQL(SELECT * FROM ticket)]] renders, I get this error:

Error: Macro SQL(SELECT * FROM ticket) failed

mysql://tracuser:casandra@localhost/trac

The stack trace in the log file is

2014-10-19 19:55:55,799 Trac[formatter] DEBUG: Executing Wiki macro SQL by provider <sqlquery.macro.SqlQueryMacro object at 0x7f7e2ca556d0>
2014-10-19 19:55:55,799 Trac[formatter] ERROR: Macro SQL(SELECT * FROM ticket) failed:
Traceback (most recent call last):
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0-py2.7.egg/trac/wiki/formatter.py", line 765, in _macro_formatter
return macro.ensure_inline(macro.process(args))
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0-py2.7.egg/trac/wiki/formatter.py", line 356, in process
text = self.processor(text)
File "/usr/local/lib/python2.7/dist-packages/Trac-1.0-py2.7.egg/trac/wiki/formatter.py", line 343, in _macro_processor
text)
File "build/bdist.linux-x86_64/egg/sqlquery/macro.py", line 46, in expand_macro
db = Connection(self.uri)
File "/usr/local/lib/python2.7/dist-packages/pymills-3.4.2-py2.7.egg/pymills/dbapi.py", line 55, in create_connection
return types[type](*args, **kwargs)
KeyError: u'mysql://mytracuser:mytracuser@localhost/trac'

[Ryan J Ollos]

In 14239:

Import of Connection fails, at least in the most recent version of pymills. Refs #12023.

Patch by paresh138@….

[Ryan J Ollos]

More work is needed after [14239], that was just a start. We should probably remove the dependency on PyMills.

[Jun Omae]

The pymills is broken. Also, it is no longer maintained. The project is archived at ​https://github.com/prologic/pymills.

None can fix it (when [sqlquery] uri is configured).

18:10:12 Trac[formatter] ERROR: Processor SQL failed for <Resource 'ticket'>:
Traceback (most recent call last):
  File "/dev/shm/trac-1.4.2/local/lib/python2.7/site-packages/trac/wiki/formatter.py", line 1222, in _exec_processor
    return processor.process(text)
  File "/dev/shm/trac-1.4.2/local/lib/python2.7/site-packages/trac/wiki/formatter.py", line 389, in process
    text = self.processor(text)
  File "/dev/shm/trac-1.4.2/local/lib/python2.7/site-packages/trac/wiki/formatter.py", line 361, in _macro_processor
    text)
  File "/home/jun66j5/src/sqlquerymacro/1.0/sqlquery/macro.py", line 56, in expand_macro
    db = create_connection(schema, self.uri)
  File "/dev/shm/trac-1.4.2/local/lib/python2.7/site-packages/pymills/dbapi.py", line 55, in create_connection
    return types[type](*args, **kwargs)
  File "/dev/shm/trac-1.4.2/local/lib/python2.7/site-packages/pymills/dbapi.py", line 45, in sqlite_session
    except sqlite.Error, e:
NameError: global name 'sqlite' is not defined

[clemens]

Replying to Jun Omae:

The pymills is broken. Also, it is no longer maintained. The project is archived at ​https://github.com/prologic/pymills.

None can fix it (when [sqlquery] uri is configured).

Just to confirm, that I understand correctly. You are saying that SqlQueryMacro (as of today, version 0.2, r17555, 2019-11-15) is unusable with external databases such as MySQL or SQLite i.e. when [sqlquery] uri is configured? And this is because it relies on the (deprecated) pymills dependency?

I have tested SqlQueryMacro with the internal TRAC database i.e. without [sqlquery] uri. This works. However, the major benefit the plugin are external database queries, I would say.

If this is the current state, then I would propose a corresponding note on the plugin wiki page.

[Jun Omae]

You are saying that SqlQueryMacro (as of today, version 0.2, r17555, 2019-11-15) is unusable with external databases such as MySQL or SQLite i.e. when [sqlquery] uri is configured? And this is because it relies on the (deprecated) pymills dependency?

Exactly.

In the t12023-use-trac-dbapi.diff​, uses Trac database api instead of pymills database api.

[clemens]

Replying to Jun Omae:

In the t12023-use-trac-dbapi.diff​, uses Trac database api instead of pymills database api.

This patch works for me. I have tested with an external database. I tested an SQLite trac.db of another repository. I was able to run an SQL query on that external DB. Good!

However, it seems that there is an issue with non-ASCII characters. Running the following query on a ticket with Umlauts (ö):

[[SQL(SELECT summary FROM ticket where id = '1')]]

... yields this error message:

'ascii' codec can't encode character u'\u201c' in position 234: ordinal not in range(128)

[Jun Omae]

Please post entire of the stack trace from log/trac.log.

[clemens]

Replying to Jun Omae:

Please post entire of the stack trace from log/trac.log.

stack trace

2021-03-09 08:38:21,587 Trac[formatter] DEBUG: Executing Wiki macro SQL by provider <Component sqlquery.macro.SqlQueryMacro>
2021-03-09 08:38:21,588 Trac[formatter] ERROR: Macro SQL(SELECT description FROM ticket where id = '1') failed for <Resource u'wiki:WikiStart'>:
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/trac/wiki/formatter.py", line 818, in _macro_formatter
    return macro.ensure_inline(macro.process(args), in_paragraph)
  File "/usr/local/lib/python2.7/dist-packages/trac/wiki/formatter.py", line 389, in process
    text = self.processor(text)
  File "/usr/local/lib/python2.7/dist-packages/trac/wiki/formatter.py", line 361, in _macro_processor
    text)
  File "/usr/local/lib/python2.7/dist-packages/sqlquery/macro.py", line 97, in expand_macro
    t.refresh()
  File "/usr/local/lib/python2.7/dist-packages/pymills/table.py", line 330, in refresh
    header.refresh()
  File "/usr/local/lib/python2.7/dist-packages/pymills/table.py", line 62, in refresh
    self.width = self.getWidth()
  File "/usr/local/lib/python2.7/dist-packages/pymills/table.py", line 71, in getWidth
    max([len(str(x[hIndex])) for x in rows]) + 2,
UnicodeEncodeError: 'ascii' codec can't encode character u'\xfc' in position 417: ordinal not in range(128)

As far as I can see this is an issue of the pymills package. Despite the patch pymills seems to be used for styling the output. I am not sure if it is meaningful to remove dependency from pymills entirely. Currently we still have:

from pymills.datatypes import OrderedDict
from pymills.table import Table, Header, Row, Cell

[Jun Omae]

As far as I can see this is an issue of the pymills package. Despite the patch pymills seems to be used for styling the output. I am not sure if it is meaningful to remove dependency from pymills entirely. Currently we still have:

I think we have only choice to remove use of pymills. t12023-remove-use-of-pymills.diff​.

[clemens]

Replying to Jun Omae:

I think we have only choice to remove use of pymills. t12023-remove-use-of-pymills.diff​.

Great. Now it works.

Should we have a dedicated HTML/CSS class or ID? Even if the macro does not do any CSS styling such class or id would allow users to apply their own styling.

This is the status quo:

<div>
  <table class="wiki">
    <thead>...</thead>
    <tbody>...</tbody>
  </table>
</div>

A proposal could be:

<div class="SqlQuery">
  <table class="wiki">
    <thead>...</thead>
    <tbody>...</tbody>
  </table>
</div>

[clemens]

Another thing I was wondering about is the new SqlQueryDatabaseManager. I am novice to TRAC and Python programming and do not fully understand how this works. SqlQueryDatabaseManager is inherited from trac.db.api.DatabaseManager, right?

It might be a different story but I am interested to query multiple external databases instead just one. The idea is to gather information from multiple sources. I have multiple TRAC repositories and would like to perform multiple external ticket queries on those. Does this sound crazy?

[Ryan J Ollos]

Replying to clemens:

It might be a different story but I am interested to query multiple external databases instead just one.

Something like this might work:

{{{#!SQL env=$envname
SELECT * FROM component
}}}

along with a allowed_env_dir_prefixes option that functions like allowed_repository_dir_prefixes to specify the paths for which $envname is searched.

[clemens]

Replying to Ryan J Ollos...

Your reply indicates that my idea is probably not that crazy. Maybe I should open an enhancement request ticket for SqlQueryMacro, shouldn't I?

[Ryan J Ollos]

Replying to clemens:

Your reply indicates that my idea is probably not that crazy. Maybe I should open an enhancement request ticket for SqlQueryMacro, shouldn't I?

Sounds good. The plugin doesn't have a maintainer so I can't say it would be implemented anytime soon.

[Ryan J Ollos]

Another possible solution would be to allow an arbitrary number of entries in the [sqlquery] section:

[sqlquery]
default = sqlite:///path/to/db
db1 = sqlite:///path/to/db1
db2 = sqlite:///path/to/db2

default would be used unless another entry appeared as an arg:

{{{#!SQL db1
SELECT * FROM component
}}}

Or, use the Environment's DB when there is no WikiProcessor argument.

Edited SqlQueryMacro@29 in preparation for these changes.

[clemens]

I opened a new enhancement ticket #13973 "query multiple external databases".

[Ryan J Ollos]

In 18071:

TracSqlQueryMacro 0.3dev: Remove pymills dependency

Patch by Jun Omae.

Fixes #12023.