Modify

Opened 9 years ago

Closed 3 years ago

Last modified 3 years ago

#12023 closed defect (fixed)

I can't connect to MySQL database

Reported by: paresh138@… Owned by: Jun Omae
Priority: normal Component: SqlQueryMacro
Severity: normal Keywords:
Cc: Trac Release: 1.0

Description

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'

Attachments (2)

t12023-use-trac-dbapi.diff (2.5 KB) - added by Jun Omae 3 years ago.
t12023-remove-use-of-pymills.diff (6.0 KB) - added by Jun Omae 3 years ago.

Download all attachments as: .zip

Change History (21)

comment:1 Changed 9 years ago by Ryan J Ollos

In 14239:

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

Patch by paresh138@….

comment:2 Changed 9 years ago by Ryan J Ollos

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

comment:3 Changed 4 years ago by Ryan J Ollos

Owner: Ryan J Ollos deleted

comment:4 Changed 3 years ago by 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

comment:5 in reply to:  4 ; Changed 3 years ago by 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.

Changed 3 years ago by Jun Omae

Attachment: t12023-use-trac-dbapi.diff added

comment:6 in reply to:  5 ; Changed 3 years ago by 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.

comment:7 in reply to:  6 Changed 3 years ago by 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)

comment:8 Changed 3 years ago by Jun Omae

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

comment:9 in reply to:  8 ; Changed 3 years ago by 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

Changed 3 years ago by Jun Omae

comment:10 in reply to:  9 ; Changed 3 years ago by 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.

Last edited 3 years ago by Jun Omae (previous) (diff)

comment:11 in reply to:  10 Changed 3 years ago by 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>

comment:12 Changed 3 years ago by 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?

comment:13 in reply to:  12 ; Changed 3 years ago by 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.

comment:14 in reply to:  13 ; Changed 3 years ago by 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?

comment:15 in reply to:  14 Changed 3 years ago by 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.

comment:16 Changed 3 years ago by 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.

Last edited 3 years ago by Ryan J Ollos (previous) (diff)

comment:17 Changed 3 years ago by clemens

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

comment:18 Changed 3 years ago by Ryan J Ollos

Owner: set to Ryan J Ollos
Resolution: fixed
Status: newclosed

In 18071:

TracSqlQueryMacro 0.3dev: Remove pymills dependency

Patch by Jun Omae.

Fixes #12023.

comment:19 Changed 3 years ago by Ryan J Ollos

Owner: changed from Ryan J Ollos to Jun Omae

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


E-mail address and name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.