How to store spanish characters into postgresql database?

[Velu Narasimman]

I am getting some input from user and that input has some non ASCII characters. The user is using some Spanish characters like 'ó', 'é' etc., when I save his input to database I face UnicodeEncodeError.

Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.15-py2.7.egg/trac/web/main.py", line 624, in _dispatch_request
    dispatcher.dispatch(req)
  File "/usr/local/lib/python2.7/dist-packages/Trac-1.0.15-py2.7.egg/trac/web/main.py", line 302, in dispatch
    resp = chosen_handler.process_request(req)
  File "/usr/local/lib/python2.7/dist-packages/timesheet-1.0-py2.7.egg/timesheet/form_ui.py", line 1173, in process_request
    timeEntry.create_weekly(project_env,timesheet_result,project)
  File "/usr/local/lib/python2.7/dist-packages/timesheet-1.0-py2.7.egg/timesheet/model.py", line 289, in create_weekly
    str(detail['comments']).replace('"', '').replace("'", '').replace('\n', ' ').replace('\r', ' '),\
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 37: ordinal not in range(128)

If there is any support page for unicode usage in Trac it would be better. Please help me how I can get some randome language characters input like this and store it to db without issues.

[RjOllos]

Try:

str(detail['comments']) -> detail['comments'] 

Or 

str(detail['comments']) -> unicode(detail['comments'])

- Ryan

[Velu Narasimman]

Method: 1

When I just used the below,
detail['comments']

Query is read as
insert into ticket_time ( exclusion, time_submitted, seconds_worked, submitter, worker, modified, comments, ticket_time_status, reason, time_started, ticket) values ('on-premise', 1583650057, 7200.0, 'sergio.martinez', 'sergio.martinez', 1583650057, u'BRD_PAG_009 Pago Establecimientos BRD_ACT_015 Maduraci\\xf3n de Operaciones de Cr\\xe9dito', 0, 'mentoring/review', 1583346600, 1693) returning id,ticket,exclusion,reason,time_started

And I am getting below error in log file
ProgrammingError: type "u" does not exist
LINE 1: ...'sergio.martinez', 'sergio.martinez', 1583649598, u'BRD_PAG_...


the original value of the chunk
detail['comments']
is "BRD_PAG_009 Pago Establecimientos BRD_ACT_015 Maduración de Operaciones de Crédito".


METHOD:2

this time I used like this,


and the error is same as above.


    I am trying a lot and struggling with this! I don't know how to safely insert it into db.

[Dmitri Maziuk]

On 3/8/2020 1:08 AM, Velu Narasimman wrote:
...

> And I am getting below error in log file

> ProgrammingError: type "u" does not existt

> LINE 1: ...'sergio.martinez', 'sergio.martinez', 1583649598, u'BRD_PAG_...
>
>
> the original value of the chunk
> detail['comments']
> is "BRD_PAG_009 Pago Establecimientos BRD_ACT_015 Maduración de Operaciones
> de Crédito".

What is the data type of the comments column (`\dt ticket_time` in psql
should tell you). (The wisdom of storing unicode in database column aside.)

Dima

[Velu Narasimman]

data type of comments column in table is "text". I am using postgresql database.

[Jun Omae]

On Sun, Mar 8, 2020 at 4:08 PM Velu Narasimman <velava...@gmail.com> wrote:
>
> Method: 1
>
> When I just used the below,
> detail['comments']
>
> Query is read as
> insert into ticket_time ( exclusion, time_submitted, seconds_worked, submitter, worker, modified, comments, ticket_time_status, reason, time_started, ticket) values ('on-premise', 1583650057, 7200.0, 'sergio.martinez', 'sergio.martinez', 1583650057, u'BRD_PAG_009 Pago Establecimientos BRD_ACT_015 Maduraci\\xf3n de Operaciones de Cr\\xe9dito', 0, 'mentoring/review', 1583346600, 1693) returning id,ticket,exclusion,reason,time_started
>

Instead of constructing SQL statements, you should use DB API.
Removing single-quote, double-quote and control characters to
construct SQL statements is bad practice.

See also https://trac.edgewall.org/wiki/TracDev/DatabaseApi


--
Jun Omae <jun...@gmail.com> (大前 潤)

[Velu Narasimman]

@Jun Omae, Yes I understand. I explore the database api document and change my code accordingly. And about the single and double quotes, actually we faced some issue when inserting some text with single quotes to postgresql database in past. So from that experience, we started avoiding the quotes. But let me retry the right way to store them to db instead of removing them.

    And about the \n and \r, frankly I removed them because if many newlines are there in user's input it occupies the database and makes it very big in size(not sure it really occupies spaces in terms of Bytes but I am talking about the look in terminal, it looks huge in terminal when selected). I welcome your opinion and the good practices about handling them.

    Coming to my above problem, I altered the way the query is prepared now I tried the below code and it worked.

            query = "insert into ticket_time ( exclusion, time_submitted, seconds_worked, submitter, worker, modified, comments, ticket_time_status, reason, time_started, ticket) values %s returning id,ticket,exclusion,reason,time_started"
            cursor.execute(query, (values,))

In the above chunk, values is a string and I am making passing it into a tuple and that in turn is passed to execute statement. Now the I did no conversion to unicodes. Input from my user is encoded to unicode when accessing the request arguments( say req.args.get("comments")). Then I am just passing that value to execute statement directly so it is stored into database. Retrieval also works well without issues.

[Jun Omae]

On Mon, Mar 9, 2020 at 3:44 PM Velu Narasimman <velava...@gmail.com> wrote:
> Coming to my above problem, I altered the way the query is prepared now I tried the below code and it worked.
>
> query = "insert into ticket_time ( exclusion, time_submitted, seconds_worked, submitter, worker, modified, comments, ticket_time_status, reason, time_started, ticket) values %s returning id,ticket,exclusion,reason,time_started"
> cursor.execute(query, (values,))

I think the code should be:

values = ... # should be tuple or list which has 11 items
query = """
INSERT INTO ticket_time (

exclusion, time_submitted, seconds_worked, submitter,
worker, modified,
comments, ticket_time_status, reason, time_started, ticket)

VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
cursor.execute(query, values)
id_ = cursor.get_last_id('ticket_time') # retrieve ticket_time.id


See also:
- https://www.edgewall.org/docs/branches-1.2-stable/html/api/trac_db_api.html?highlight=execute#trac.db.api.DbContextManager.execute
- https://www.edgewall.org/docs/branches-1.2-stable/html/api/trac_db_api.html?highlight=execute#trac.db.api.ConnectionBase.get_last_id

[RjOllos]

On Friday, March 6, 2020 at 6:26:06 AM UTC-8, Velu Narasimman wrote:

Please consider using the trac-dev mailing list in the future for development related questions:

https://groups.google.com/forum/#!forum/trac-dev

 

[Dmitri Maziuk]

On 3/8/2020 11:08 PM, Velu Narasimman wrote:
> data type of comments column in table is "text". I am using postgresql
> database.
>

Right. Sorry, brain fart: `psql -l` and look at encoding column.

Dima