Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gaKkx-0006zh-Gg for pgadmin-hackers@arkaria.postgresql.org; Fri, 21 Dec 2018 13:22:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gaKku-0007Xu-UD for pgadmin-hackers@arkaria.postgresql.org; Fri, 21 Dec 2018 13:22:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gaKku-0007Xn-6z for pgadmin-hackers@lists.postgresql.org; Fri, 21 Dec 2018 13:22:20 +0000 Received: from mail-wr1-x429.google.com ([2a00:1450:4864:20::429]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gaKkq-0004fT-9m for pgadmin-hackers@postgresql.org; Fri, 21 Dec 2018 13:22:19 +0000 Received: by mail-wr1-x429.google.com with SMTP id j2so5269227wrw.1 for ; Fri, 21 Dec 2018 05:22:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin-org.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=auyL+YSliLJ20HkJHxWL/txIp0Ncx6qDR622D0fsK9w=; b=vd6vkndr/vPodNXkCxtsO7mAt6Ra+Lr7rDeGyOTy5rOfi9/phtlVW/di8f6NMqMmO+ QBmqD4y/OILR3Ke6cO88dVQ6hRt7kP9S3QehzPzt7VrEkGgsAxp18CPOMdlJUTKQMl4z Z4H4lLnM/HAvZKBPz3lsknuMcsO5ppw/ACZUbIJs8F4y9wJus/ymLZc4fs6PdXPpWdUp 0UQ0G5zY+OQYf6G8GSFPCPDqBdRiuFmlf6xWJ5W4SB6ICpyRElX/BmVJPCE2pTSkY2GF mCUia95i01h5mAMoTrBJZ5IBpTEmpt9WKvxJQgJ2ATYD+wrwPAFAsQ9nzSN/nmqv+NwZ A25Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=auyL+YSliLJ20HkJHxWL/txIp0Ncx6qDR622D0fsK9w=; b=kGLZRrgQS5SJusb1KoKKvOKVr6vzeYvNg/hnh4UzrqSjMSAPqxd3DQqeOMRo//MtFl 4lF2qT30TKbbaHf+QoC09lAOF1w6u5pCeD1STwLxiDmXTxck36vIvWGGmEZ5OXe0xTgr UnsiX7yUct9LXoFZzRz4M3bw1xKUQ2hHx4kX9O9hn0kDyqzVa8hXfTcwVmDWZ4jW37UD oc8WNdB2Yg44yQI8siIRyDFrlKi/rSZ8R1d+D2VztP3uPnEYOHjtg4rL0OTVPzwx1yAa sbPbjHc8oLoqPYYm9suyNYgKd3W4u/v2/n8thAx7AsJB3t0BzZ3fk39IB0eYYEmhKJjh ra4Q== X-Gm-Message-State: AJcUukd6lZAlfkpGsuxrvl/jK3twgmd4sA3mnBs4KaCMKgWLS0CcLiBq 1ZyZRikClmMASFj+LHpy2YKMvC9zjMz0WL1yhSPiEA== X-Google-Smtp-Source: ALg8bN7gNKWmwgzP4OJBis3Q5Qbjn9KCyURLx7zhYkIPgaVR6noReUqliBahRdEkKzC0LDeod0Wiqfsr+Gz6P2RU1AY= X-Received: by 2002:adf:8264:: with SMTP id 91mr2547383wrb.312.1545398534458; Fri, 21 Dec 2018 05:22:14 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Fri, 21 Dec 2018 13:22:02 +0000 Message-ID: Subject: Re: [pgAdmin4][Patch] - RM 3780 pgAdmin4 lacks ability to specify NULL values in CSV export To: Akshay Joshi Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000aee324057d8822b1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000aee324057d8822b1 Content-Type: text/plain; charset="UTF-8" Hi Looks good - I just found one issue; please go ahead and commit (with a release notes update) when fixed. - If I set the NULL replacement value to an empty string, it defaults back to NULL. It's therefore not possible to replace null values with an empty value. Thanks! On Fri, Dec 21, 2018 at 12:57 PM Akshay Joshi wrote: > Hi Dave > > I have modified the condition, it won't through any exception. Attached is > the updated patch, please review it. > > On Fri, Dec 21, 2018 at 4:22 AM Dave Page wrote: > >> Sorry! Here is is. >> >> On Fri, Dec 21, 2018 at 12:12 PM Akshay Joshi < >> akshay.joshi@enterprisedb.com> wrote: >> >>> Hi Dave >>> >>> Can you please attached the updated patch with your changes. I'll try to >>> fix the exception. >>> >>> On Fri, Dec 21, 2018 at 3:57 AM Dave Page wrote: >>> >>>> Hi >>>> >>>> Here's an updated patch as I've tweaked some of the wording. The >>>> screenshot probably isn't the right resolution, but as we're replacing them >>>> anyway it doesn't seem overly important. Feel free to fix if you like :-) >>>> >>>> With quoting set to either All or Strings, everything looks good. With >>>> it set to None, I still get an exception (below). The query I'm using is >>>> this: >>>> >>>> SELECT NULL::text, 1234::int, 'Foo bar'::text, E'Foo\nBar'::text >>>> >>>> Field separator: , >>>> Quote character: " >>>> Replace null's with: NULL >>>> >>>> Steps: >>>> >>>> 1) Run pgAdmin in Desktop mode. I'm running from within PyuCharms, >>>> using the venv detailed below. >>>> 2) Open the Query Tool on a PostgreSQL 9.6.10 database, running on >>>> MacOS 10.14.1 >>>> 3) Run the above query, wit quoting set to All and check the result in >>>> the grid. >>>> 4) Download the CSV file and check. >>>> 5) Open Preferences and set quoting to Strings. >>>> 6) Download the CSV file and check. >>>> 7) Open Preferences and set quoting to None. >>>> 8) Download the CSV file *exception occurs*. >>>> >>>> >>>> System info: >>>> >>>> (pgadmin4) dpage@hal:*~/git/pgadmin4*$ python --version >>>> >>>> Python 3.6.7 >>>> >>>> (pgadmin4) dpage@hal:*~/git/pgadmin4*$ pip freeze >>>> >>>> alabaster==0.7.11 >>>> >>>> alembic==1.0.0 >>>> >>>> asn1crypto==0.24.0 >>>> >>>> Babel==2.6.0 >>>> >>>> bcrypt==3.1.4 >>>> >>>> blinker==1.4 >>>> >>>> certifi==2018.8.24 >>>> >>>> cffi==1.11.5 >>>> >>>> chardet==3.0.4 >>>> >>>> chromedriver-installer==0.0.6 >>>> >>>> click==6.7 >>>> >>>> cryptography==2.3 >>>> >>>> docutils==0.14 >>>> >>>> extras==1.0.0 >>>> >>>> fixtures==3.0.0 >>>> >>>> Flask==0.12.4 >>>> >>>> Flask-BabelEx==0.9.3 >>>> >>>> Flask-Gravatar==0.5.0 >>>> >>>> Flask-HTMLmin==1.3.2 >>>> >>>> Flask-Login==0.3.2 >>>> >>>> Flask-Mail==0.9.1 >>>> >>>> Flask-Migrate==2.1.1 >>>> >>>> Flask-Paranoid==0.2.0 >>>> >>>> Flask-Principal==0.4.0 >>>> >>>> Flask-Security==3.0.0 >>>> >>>> Flask-SQLAlchemy==2.3.2 >>>> >>>> Flask-WTF==0.14.2 >>>> >>>> html5lib==1.0.1 >>>> >>>> htmlmin==0.1.12 >>>> >>>> idna==2.7 >>>> >>>> imagesize==1.1.0 >>>> >>>> itsdangerous==0.24 >>>> >>>> Jinja2==2.10 >>>> >>>> linecache2==1.0.0 >>>> >>>> Mako==1.0.7 >>>> >>>> MarkupSafe==1.0 >>>> >>>> packaging==18.0 >>>> >>>> paramiko==2.4.1 >>>> >>>> passlib==1.7.1 >>>> >>>> pbr==3.1.1 >>>> >>>> psutil==5.4.8 >>>> >>>> psycopg2==2.7.5 >>>> >>>> pyasn1==0.4.4 >>>> >>>> pycodestyle==2.3.1 >>>> >>>> pycparser==2.18 >>>> >>>> pycrypto==2.6.1 >>>> >>>> Pygments==2.2.0 >>>> >>>> PyNaCl==1.2.1 >>>> >>>> pyparsing==2.2.2 >>>> >>>> pyperclip==1.6.4 >>>> >>>> pyrsistent==0.14.2 >>>> >>>> python-dateutil==2.7.3 >>>> >>>> python-editor==1.0.3 >>>> >>>> python-mimeparse==1.6.0 >>>> >>>> pytz==2018.3 >>>> >>>> requests==2.19.1 >>>> >>>> selenium==3.14.1 >>>> >>>> simplejson==3.13.2 >>>> >>>> six==1.11.0 >>>> >>>> snowballstemmer==1.2.1 >>>> >>>> speaklater==1.3 >>>> >>>> Sphinx==1.8.2 >>>> >>>> sphinxcontrib-websupport==1.1.0 >>>> >>>> SQLAlchemy==1.2.10 >>>> >>>> sqlparse==0.2.4 >>>> >>>> sshtunnel==0.1.4 >>>> >>>> testscenarios==0.5.0 >>>> >>>> testtools==2.3.0 >>>> >>>> traceback2==1.4.0 >>>> >>>> unittest2==1.1.0 >>>> >>>> urllib3==1.23 >>>> >>>> webencodings==0.5.1 >>>> >>>> Werkzeug==0.14.1 >>>> >>>> WTForms==2.1 >>>> >>>> Exception: >>>> >>>> 2018-12-21 11:47:28,995: SQL pgadmin: Execute (with server cursor) for >>>> server #2 - CONN:8760231 (Query-id: 8649354): >>>> SELECT NULL::text, 1234::int, 'Foo bar'::text, E'Foo\nBar'::text >>>> 2018-12-21 11:47:29,001: INFO werkzeug: 127.0.0.1 - - [21/Dec/2018 >>>> 11:47:29] "GET >>>> /sqleditor/query_tool/download/2133388?query=SELECT%20NULL%3A%3Atext%2C%201234%3A%3Aint%2C%20%27Foo%20bar%27%3A%3Atext%2C%20E%27Foo%5CnBar%27%3A%3Atext&filename=data-1545392848979.csv >>>> HTTP/1.1" 500 - >>>> 2018-12-21 11:47:29,003: ERROR werkzeug: Error on request: >>>> Traceback (most recent call last): >>>> File >>>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/serving.py", >>>> line 270, in run_wsgi >>>> execute(self.server.app) >>>> File >>>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/serving.py", >>>> line 260, in execute >>>> for data in application_iter: >>>> File >>>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/wsgi.py", >>>> line 870, in __next__ >>>> return self._next() >>>> File >>>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/wrappers.py", >>>> line 82, in _iter_encoded >>>> for item in iterable: >>>> File >>>> "/Users/dpage/git/pgadmin4/web/pgadmin/utils/driver/psycopg2/connection.py", >>>> line 848, in gen >>>> csv_writer.writerows(results) >>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line 761, >>>> in writerows >>>> return self.writer.writerows(map(self._dict_to_list, rowdicts)) >>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line 268, >>>> in writerows >>>> self.writerow(row) >>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line 261, >>>> in writerow >>>> row = [self.strategy.prepare(field, only=only) for field in row] >>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line 261, >>>> in >>>> row = [self.strategy.prepare(field, only=only) for field in row] >>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line 142, >>>> in prepare >>>> raise Error('No escapechar is set') >>>> _csv.Error: No escapechar is set >>>> >>>> On Thu, Dec 20, 2018 at 1:05 PM Akshay Joshi < >>>> akshay.joshi@enterprisedb.com> wrote: >>>> >>>>> Hi Dave >>>>> >>>>> On Thu, Dec 20, 2018 at 5:12 PM Akshay Joshi < >>>>> akshay.joshi@enterprisedb.com> wrote: >>>>> >>>>>> >>>>>> >>>>>> On Thu, Dec 20, 2018 at 4:48 PM Dave Page wrote: >>>>>> >>>>>>> Hi >>>>>>> >>>>>>> On Thu, Dec 20, 2018 at 10:09 AM Akshay Joshi < >>>>>>> akshay.joshi@enterprisedb.com> wrote: >>>>>>> >>>>>>>> Hi Dave >>>>>>>> >>>>>>>> On Thu, Dec 20, 2018 at 3:08 PM Dave Page >>>>>>>> wrote: >>>>>>>> >>>>>>>>> Hi >>>>>>>>> >>>>>>>>> When testing with quoting set to None, quote = " and delimiter = , >>>>>>>>> I get the following exception when I try to download: >>>>>>>>> >>>>>>>>> 2018-12-20 09:34:02,547: SQL pgadmin: Execute (with server >>>>>>>>> cursor) for server #2 - CONN:354106 (Query-id: 4121147): >>>>>>>>> SELECT NULL::text, 1234::int, 'Foo bar'::text, E'Foo\nBar'::text >>>>>>>>> 2018-12-20 09:34:02,570: INFO werkzeug: 127.0.0.1 - - >>>>>>>>> [20/Dec/2018 09:34:02] "GET >>>>>>>>> /sqleditor/query_tool/download/5610522?query=SELECT%20NULL%3A%3Atext%2C%201234%3A%3Aint%2C%20%27Foo%20bar%27%3A%3Atext%2C%20E%27Foo%5CnBar%27%3A%3Atext&filename=data-1545298442530.csv >>>>>>>>> HTTP/1.1" 500 - >>>>>>>>> 2018-12-20 09:34:02,572: ERROR werkzeug: Error on request: >>>>>>>>> Traceback (most recent call last): >>>>>>>>> File >>>>>>>>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/serving.py", >>>>>>>>> line 270, in run_wsgi >>>>>>>>> execute(self.server.app) >>>>>>>>> File >>>>>>>>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/serving.py", >>>>>>>>> line 260, in execute >>>>>>>>> for data in application_iter: >>>>>>>>> File >>>>>>>>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/wsgi.py", >>>>>>>>> line 870, in __next__ >>>>>>>>> return self._next() >>>>>>>>> File >>>>>>>>> "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/wrappers.py", >>>>>>>>> line 82, in _iter_encoded >>>>>>>>> for item in iterable: >>>>>>>>> File >>>>>>>>> "/Users/dpage/git/pgadmin4/web/pgadmin/utils/driver/psycopg2/connection.py", >>>>>>>>> line 820, in gen >>>>>>>>> csv_writer.writerows(results) >>>>>>>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line >>>>>>>>> 748, in writerows >>>>>>>>> return self.writer.writerows(map(self._dict_to_list, rowdicts)) >>>>>>>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line >>>>>>>>> 256, in writerows >>>>>>>>> self.writerow(row) >>>>>>>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line >>>>>>>>> 249, in writerow >>>>>>>>> row = [self.strategy.prepare(field, only=only) for field in >>>>>>>>> row] >>>>>>>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line >>>>>>>>> 249, in >>>>>>>>> row = [self.strategy.prepare(field, only=only) for field in >>>>>>>>> row] >>>>>>>>> File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line >>>>>>>>> 136, in prepare >>>>>>>>> raise Error('No escapechar is set') >>>>>>>>> _csv.Error: No escapechar is set >>>>>>>>> >>>>>>>> >>>>>>>> Not able to reproduce the above issue. I have tested it with >>>>>>>> the same setting as you mentioned. Please refer all the attached >>>>>>>> screenshots. Please specify the steps if they are different. >>>>>>>> >>>>>>>>> >>>>>>>>> When I have quoting set to All, the first column is returned as "" >>>>>>>>> >>>>>>>>> dpage@hal:*~/Downloads*$ more data-1545298598112.csv >>>>>>>>> >>>>>>>>> "text","int4","text-2","text-3" >>>>>>>>> >>>>>>>>> "","1234","Foo bar","Foo >>>>>>>>> >>>>>>>>> Bar" >>>>>>>>> >>>>>>>>> Isn't the point for it to be NULL? >>>>>>>>> >>>>>>>> >>>>>>>> while quoting is set to ALL, all the data types has been >>>>>>>> quoted, so I thought null values should be replaced by "" instead of blank. >>>>>>>> But if you think null values shouldn't be quoted even if user select quote >>>>>>>> ALL, I'll fix it and resend the patch. >>>>>>>> >>>>>>> >>>>>>> So how would you distinguish NULL from an empty string? Isn't that >>>>>>> exactly what the bug is about? >>>>>>> >>>>>>> I still think we need a "Replace NULLs with" config option, and >>>>>>> regardless of quoting settings we always replace NULL values with whatever >>>>>>> that is set to - for which the user could then choose options like: >>>>>>> >>>>>>> NULL >>>>>>> "NULL" >>>>>>> "" >>>>>>> '' >>>>>>> >>>>>>> >>>>>>> We would never quote the NULL replacement value - if the user wanted >>>>>>> it to be quoted, they would include the quotes in the configured string. >>>>>>> >>>>>> >>>>>> >>>>>> OK, Will work on it and send the modified patch again. >>>>>> >>>>> >>>>> Attached is the modified patch as per your suggestion. >>>>> >>>>>> >>>>>>> >>>>>>>> >>>>>>>>> On Tue, Dec 18, 2018 at 11:13 AM Akshay Joshi < >>>>>>>>> akshay.joshi@enterprisedb.com> wrote: >>>>>>>>> >>>>>>>>>> Hi Dave >>>>>>>>>> >>>>>>>>>> Attached is the modified patch to fix review comments. >>>>>>>>>> >>>>>>>>>> On Tue, Dec 18, 2018 at 3:00 PM Akshay Joshi < >>>>>>>>>> akshay.joshi@enterprisedb.com> wrote: >>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Tue, Dec 18, 2018 at 2:49 PM Dave Page >>>>>>>>>>> wrote: >>>>>>>>>>> >>>>>>>>>>>> Hi >>>>>>>>>>>> >>>>>>>>>>>> On Tue, Dec 18, 2018 at 3:45 AM Akshay Joshi < >>>>>>>>>>>> akshay.joshi@enterprisedb.com> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Hi Hackers, >>>>>>>>>>>>> >>>>>>>>>>>>> Attached is the patch to fix RM #3780 pgAdmin4 lacks ability >>>>>>>>>>>>> to specify NULL values in CSV export. >>>>>>>>>>>>> >>>>>>>>>>>>> Please review it. >>>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> A few points; >>>>>>>>>>>> >>>>>>>>>>>> - You've included code from backports.csv, but per the licence >>>>>>>>>>>> you need to include a description of the changes made. >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Sure. In that case I'll copy the complete file and will do >>>>>>>>>>> my changes which is of two lines only. With my patch I have remove all the >>>>>>>>>>> unwanted code from backport.csv. >>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> - Shouldn't backports.csv be removed from requirements.txt, or >>>>>>>>>>>> is it used elsewhere? >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Yes. Will do that. >>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> - If the previous point is true, then I'm fairly sure there is >>>>>>>>>>>> code in one or more of the many package build scripts that adds an >>>>>>>>>>>> __init__.py file to backports.csv in the venv that's created. >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> I'll remove that code as well. >>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> -- >>>>>>>>>>>> Dave Page >>>>>>>>>>>> Blog: http://pgsnake.blogspot.com >>>>>>>>>>>> Twitter: @pgsnake >>>>>>>>>>>> >>>>>>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>>>>>>>>> The Enterprise PostgreSQL Company >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> -- >>>>>>>>>>> *Akshay Joshi* >>>>>>>>>>> >>>>>>>>>>> *Sr. Software Architect * >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> *Akshay Joshi* >>>>>>>>>> >>>>>>>>>> *Sr. Software Architect * >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >>>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> Dave Page >>>>>>>>> Blog: http://pgsnake.blogspot.com >>>>>>>>> Twitter: @pgsnake >>>>>>>>> >>>>>>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>>>>>> The Enterprise PostgreSQL Company >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> *Akshay Joshi* >>>>>>>> >>>>>>>> *Sr. Software Architect * >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >>>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> Dave Page >>>>>>> Blog: http://pgsnake.blogspot.com >>>>>>> Twitter: @pgsnake >>>>>>> >>>>>>> EnterpriseDB UK: http://www.enterprisedb.com >>>>>>> The Enterprise PostgreSQL Company >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> *Akshay Joshi* >>>>>> >>>>>> *Sr. Software Architect * >>>>>> >>>>>> >>>>>> >>>>>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >>>>>> >>>>> >>>>> >>>>> -- >>>>> *Akshay Joshi* >>>>> >>>>> *Sr. Software Architect * >>>>> >>>>> >>>>> >>>>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >>>>> >>>> >>>> >>>> -- >>>> Dave Page >>>> Blog: http://pgsnake.blogspot.com >>>> Twitter: @pgsnake >>>> >>>> EnterpriseDB UK: http://www.enterprisedb.com >>>> The Enterprise PostgreSQL Company >>>> >>> >>> >>> -- >>> *Akshay Joshi* >>> >>> *Sr. Software Architect * >>> >>> >>> >>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >>> >> >> >> -- >> Dave Page >> Blog: http://pgsnake.blogspot.com >> Twitter: @pgsnake >> >> EnterpriseDB UK: http://www.enterprisedb.com >> The Enterprise PostgreSQL Company >> > > > -- > *Akshay Joshi* > > *Sr. Software Architect * > > > > *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company --000000000000aee324057d8822b1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

Looks good - I just found one issue;= please go ahead and commit (with a release notes update) when fixed.
=

- If I set the NULL replacement value to an empty strin= g, it defaults back to NULL. It's therefore not possible to replace nul= l values with an empty value.

Thanks!
<= br>
On Fri, Dec 21, 2018 at 12:5= 7 PM Akshay Joshi <aksh= ay.joshi@enterprisedb.com> wrote:
Hi Dave

=
I have modified the condition, it won't through any exception. Att= ached is the updated patch, please review it.

On Fri, Dec 21, 2018 at 4:22 AM Dave Pag= e <dpage@pgadmin.= org> wrote:
Sorry! Here is is.

=
On Fri, Dec 21, 2018 at 12:12 PM Akshay Joshi <akshay.joshi@en= terprisedb.com> wrote:
Hi Dave

C= an you please attached the updated patch with your changes. I'll try to= fix the exception.

On Fri, Dec 21, 2018 at 3:57 AM Dave Page <dpage@pgadmin.org> wrote:
Hi

Here'= ;s an updated patch as I've tweaked some of the wording. The screenshot= probably isn't the right resolution, but as we're replacing them a= nyway it doesn't seem overly important. Feel free to fix if you like :-= )

With quoting set to either All or Strings, every= thing looks good. With it set to None, I still get an exception (below). Th= e query I'm using is this:

SELECT NULL::text, = 1234::int, 'Foo bar'::text, E'Foo\nBar'::text

Field separator: ,
Quote character: "
<= div>Replace null's with: NULL

Steps:

1) Run pgAdmin in Desktop mode. I'm running from within= PyuCharms, using the venv detailed below.
2) Open the Query Tool= on a PostgreSQL 9.6.10 database, running on MacOS 10.14.1
3) Run= the above query, wit quoting set to All and check the result in the grid.<= /div>
4) Download the CSV file and check.
5) Open Preferences= and set quoting to Strings.
6) Download the CSV file and check.<= /div>
7) Open Preferences and set quoting to None.
8) Downloa= d the CSV file *exception occurs*.


= System info:

(pgadmin4) dpage@hal:~/git/pgadmin4$ python --version

Python 3.6.7

(pgadmin4) dpage@hal:~/git/pgadmin4$ pip freeze

alabaster=3D=3D0.7.11

alembic=3D=3D1.0.0

asn1crypto=3D=3D0.24.0

Babel=3D=3D2.6.0

bcrypt=3D=3D3.1.4

blinker=3D=3D1.4

certifi=3D=3D2018.8.24

cffi=3D=3D1.11.5

chardet=3D=3D3.0.4

chromedriver-installer=3D=3D0.0.6

click=3D=3D6.7

cryptography=3D=3D2.3

docutils=3D=3D0.14

extras=3D=3D1.0.0

fixtures=3D=3D3.0.0

Flask=3D=3D0.12.4

Flask-BabelEx=3D=3D0.9.3

Flask-Gravatar=3D=3D0.5.0

Flask-HTMLmin=3D=3D1.3.2

Flask-Login=3D=3D0.3.2

Flask-Mail=3D=3D0.9.1

Flask-Migrate=3D=3D2.1.1

Flask-Paranoid=3D=3D0.2.0

Flask-Principal=3D=3D0.4.0

Flask-Security=3D=3D3.0.0

Flask-SQLAlchemy=3D=3D2.3.2

Flask-WTF=3D=3D0.14.2

html5lib=3D=3D1.0.1

htmlmin=3D=3D0.1.12

idna=3D=3D2.7

imagesize=3D=3D1.1.0

itsdangerous=3D=3D0.24

Jinja2=3D=3D2.10

linecache2=3D=3D1.0.0

Mako=3D=3D1.0.7

MarkupSafe=3D=3D1.0

packaging=3D=3D18.0

paramiko=3D=3D2.4.1

passlib=3D=3D1.7.1

pbr=3D=3D3.1.1

psutil=3D=3D5.4.8

psycopg2=3D=3D2.7.5

pyasn1=3D=3D0.4.4

pycodestyle=3D=3D2.3.1

pycparser=3D=3D2.18

pycrypto=3D=3D2.6.1

Pygments=3D=3D2.2.0

PyNaCl=3D=3D1.2.1

pyparsing=3D=3D2.2.2

pyperclip=3D=3D1.6.4

pyrsistent=3D=3D0.14.2

python-dateutil=3D=3D2.7.3

python-editor=3D=3D1.0.3

python-mimeparse=3D=3D1.6.0

pytz=3D=3D2018.3

requests=3D=3D2.19.1

selenium=3D=3D3.14.1

simplejson=3D=3D3.13.2

six=3D=3D1.11.0

snowballstemmer=3D=3D1.2.1

speaklater=3D=3D1.3

Sphinx=3D=3D1.8.2

sphinxcontrib-websupport=3D=3D1.1.0

SQLAlchemy=3D=3D1.2.10

sqlparse=3D=3D0.2.4

sshtunnel=3D=3D0.1.4

testscenarios=3D=3D0.5.0

testtools=3D=3D2.3.0

traceback2=3D=3D1.4.0

unittest2=3D=3D1.1.0

urllib3=3D=3D1.23

webencodings=3D=3D0.5.1

Werkzeug=3D=3D0.14.1

WTForms=3D=3D2.1


Exceptio= n:

2018-12-21 11:47:28,995: SQL pgadmin: Execute (with server cursor) for server #2 - CONN:8760231 (Query-i= d: 8649354):
SELECT NULL::text, 1234::int, 'Foo bar'::tex= t, E'Foo\nBar'::text
2018-12-21 11:47:29,001: INFO werkzeug: 127.0.0.1 - - [21/Dec/2018 11:47:29] "GET /sqleditor/= query_tool/download/2133388?query=3DSELECT%20NULL%3A%3Atext%2C%201234%3A%3A= int%2C%20%27Foo%20bar%27%3A%3Atext%2C%20E%27Foo%5CnBar%27%3A%3Atext&fil= ename=3Ddata-1545392848979.csv HTTP/1.1" 500 -
2018-12-21 11= :47:29,003: ERROR werkzeug: Error on request:
Traceb= ack (most recent call last):
=C2=A0 File "/Users/dpage/.virt= ualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/serving.py", lin= e 270, in run_wsgi
=C2=A0 =C2=A0 execute(self.server.app)
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-= packages/werkzeug/serving.py", line 260, in execute
=C2=A0 = =C2=A0 for data in application_iter:
=C2=A0 File "/Users/dpa= ge/.virtualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/wsgi.py"= , line 870, in __next__
=C2=A0 =C2=A0 return self._next()
=C2=A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-= packages/werkzeug/wrappers.py", line 82, in _iter_encoded
= =C2=A0 =C2=A0 for item in iterable:
=C2=A0 File "/Users/dpag= e/git/pgadmin4/web/pgadmin/utils/driver/psycopg2/connection.py", line = 848, in gen
=C2=A0 =C2=A0 csv_writer.writerows(results)
=C2=A0 File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py"= , line 761, in writerows
=C2=A0 =C2=A0 return self.writer.writero= ws(map(self._dict_to_list, rowdicts))
=C2=A0 File "/Users/dp= age/git/pgadmin4/web/pgadmin/utils/csv.py", line 268, in writerows
=C2=A0 =C2=A0 self.writerow(row)
=C2=A0 File "/Users/= dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line 261, in writerow
=C2=A0 =C2=A0 row =3D [self.strategy.prepare(field, only=3Donly) fo= r field in row]
=C2=A0 File "/Users/dpage/git/pgadmin4/web/p= gadmin/utils/csv.py", line 261, in <listcomp>
=C2=A0 = =C2=A0 row =3D [self.strategy.prepare(field, only=3Donly) for field in row]=
=C2=A0 File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/cs= v.py", line 142, in prepare
=C2=A0 =C2=A0 raise Error('N= o escapechar is set')
_csv.Error: No escapechar is set
<= /div>
On Thu, Dec 20, 2018 a= t 1:05 PM Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
Hi=C2=A0Dave

= On Thu, Dec 20, 2018 at 5:12 PM Akshay Joshi <akshay.joshi@enterprisedb.com&= gt; wrote:


On Thu, Dec 20, 2018 at 4:48 PM Dave Page <dpage@pgadmin.org> wrote:
<= /div>
Hi<= br>
On Thu, Dec 20, 2018 at = 10:09 AM Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
Hi Dave

On Thu, = Dec 20, 2018 at 3:08 PM Dave Page <dpage@pgadmin.org> wrote:
Hi
When testing with quoting set to None, quote =3D " and= delimiter =3D , I get the following exception when I try to download:

2018-12-20 09:34:02,547: SQL pgadmin: Execute (with server cursor) for server #2 - CONN:354106 (Query-id: 41211= 47):
SELECT NULL::text, 1234::int, 'Foo bar'::text, E'= ;Foo\nBar'::text
2018-12-20 09:34:02,570: INFO werkzeug: 127.0.0.1 - - [20/Dec/2018 09:34:02] "GET /sqleditor/query_to= ol/download/5610522?query=3DSELECT%20NULL%3A%3Atext%2C%201234%3A%3Aint%2C%2= 0%27Foo%20bar%27%3A%3Atext%2C%20E%27Foo%5CnBar%27%3A%3Atext&filename=3D= data-1545298442530.csv HTTP/1.1" 500 -
2018-12-20 09:34:02,5= 72: ERROR werkzeug: Error on request:
Traceback (mo= st recent call last):
=C2=A0 File "/Users/dpage/.virtualenvs= /pgadmin4/lib/python3.6/site-packages/werkzeug/serving.py", line 270, = in run_wsgi
=C2=A0 =C2=A0 execute(self.server.app)
=C2= =A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packag= es/werkzeug/serving.py", line 260, in execute
=C2=A0 =C2=A0 = for data in application_iter:
=C2=A0 File "/Users/dpage/.vir= tualenvs/pgadmin4/lib/python3.6/site-packages/werkzeug/wsgi.py", line = 870, in __next__
=C2=A0 =C2=A0 return self._next()
=C2= =A0 File "/Users/dpage/.virtualenvs/pgadmin4/lib/python3.6/site-packag= es/werkzeug/wrappers.py", line 82, in _iter_encoded
=C2=A0 = =C2=A0 for item in iterable:
=C2=A0 File "/Users/dpage/git/p= gadmin4/web/pgadmin/utils/driver/psycopg2/connection.py", line 820, in= gen
=C2=A0 =C2=A0 csv_writer.writerows(results)
=C2=A0= File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py", line = 748, in writerows
=C2=A0 =C2=A0 return self.writer.writerows(map(= self._dict_to_list, rowdicts))
=C2=A0 File "/Users/dpage/git= /pgadmin4/web/pgadmin/utils/csv.py", line 256, in writerows
= =C2=A0 =C2=A0 self.writerow(row)
=C2=A0 File "/Users/dpage/g= it/pgadmin4/web/pgadmin/utils/csv.py", line 249, in writerow
=C2=A0 =C2=A0 row =3D [self.strategy.prepare(field, only=3Donly) for field= in row]
=C2=A0 File "/Users/dpage/git/pgadmin4/web/pgadmin/= utils/csv.py", line 249, in <listcomp>
=C2=A0 =C2=A0 r= ow =3D [self.strategy.prepare(field, only=3Donly) for field in row]
=C2=A0 File "/Users/dpage/git/pgadmin4/web/pgadmin/utils/csv.py&quo= t;, line 136, in prepare
=C2=A0 =C2=A0 raise Error('No escape= char is set')
_csv.Error: No escapechar is set

=C2=A0 =C2=A0 Not able to reprodu= ce the above issue. I have tested it with the same setting as you mentioned= . Please refer all the attached screenshots. Please specify the steps if th= ey are different.

When I have quotin= g set to All, the first column is returned as ""

dp= age@hal:= ~/Downloads$ more data-1545298598112.c= sv=C2=A0<= /span>

"text","i= nt4","text-2","text-3"

"","1234&= quot;,"Foo bar","Foo

Bar"


Isn't the = point for it to be NULL?
=C2=A0 =C2=A0 while quoting is set to ALL, all the data types h= as been quoted, so I thought null values should be replaced by ""= instead of blank. But if you think null values shouldn't be quoted eve= n if user select quote ALL, I'll fix it and resend the patch.

So how would you distinguish NULL = from an empty string? Isn't that exactly what the bug is about?

I still think we need a "Replace NULLs with" co= nfig option, and regardless of quoting settings we always replace NULL valu= es with whatever that is set to=C2=A0 - for which the user could then choos= e options like:

NULL
"NULL"
""
''
<empty string>

We would never quote the NULL replacement value - if= the user wanted it to be quoted, they would include the quotes in the conf= igured string.


=
=C2=A0 =C2=A0OK, Will work on it and send the modified patch again.=C2= =A0

=C2=A0 =C2=A0 =C2=A0 = Attached is the modified patch as per your suggestion.=C2=A0
=C2=A0
<= div>

= On Tue, Dec 18, 2018 at 11:13 AM Akshay Joshi <akshay.joshi@enterprisedb.com= > wrote:
Hi=C2=A0Dave

Attached is the modified patch= to fix review comments.

On Tue, Dec 18, 2018 at 3:00 PM Akshay Joshi <akshay.joshi@enterprisedb= .com> wrote:


On Tue, Dec 18, 2018 at 2:49 PM Dave Page <dpage@pgadmin.org> w= rote:
Hi

On Tue, Dec = 18, 2018 at 3:45 AM Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
=
Hi Hackers,

Attached is the patch to fi= x RM #3780=C2=A0pgAdmin4 lacks ability to specify NULL values in CSV export.

Please review it.

A few points;

- You= 've included code from backports.csv, but per the licence you need to i= nclude a description of the changes made.

=C2=A0 =C2=A0 =C2=A0 Sure. In that case I'll copy the = complete file and will do my changes which is of two lines only. With my pa= tch I have remove all the unwanted code from backport.csv.=C2=A0 =C2=A0 =C2= =A0=C2=A0

- Shouldn't bac= kports.csv be removed from requirements.txt, or is it used elsewhere?
=

=C2=A0 =C2=A0 =C2=A0Yes. Will = do that.=C2=A0

- If the previou= s point is true, then I'm fairly sure there is code in one or more of t= he many package build scripts that adds an __init__.py file to backports.cs= v in the venv that's created.

=C2=A0 =C2=A0 I'll remove that code as well.=C2=A0=C2=A0
=
=C2=A0
--
Dave Page
Blog: http://pgsnake.blogspot.com=
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Ent= erprise PostgreSQL Company


--
Akshay Joshi<= /font>
Sr. = Software Architect


Phone: +91 20-3058-9517
Mobile: +91 976-788-8246=


--
Akshay Joshi
Sr. Software Architect

<= font color=3D"#3333FF">
=
Phone: +91= 20-3058-9517
Mobile: +91 976-788-8246


--
Dave Page
Blog: http://pgsnake.blogspot.= com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The = Enterprise PostgreSQL Company


--
Akshay Joshi<= /div>
Sr. Softwar= e Architect

Phone: +91 20-3058-9517
Mobile: +91 976-788-8246


--
Dave = Page
Blog: htt= p://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: <= a href=3D"http://www.enterprisedb.com" target=3D"_blank">http://www.enterpr= isedb.com
The Enterprise PostgreSQL Company


--
Akshay Jos= hi
Sr. Software Architect


Phone: +91 20-3058-9517
Mobile: +91 976-7= 88-8246


--
Akshay Joshi
<= div>Sr. Software Arch= itect

=

Phone: +91 20-3058-9517
Mobile: +91 976-788-8246


--
Dave Page
= Blog: http://pgsn= ake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.= com
The Enterprise PostgreSQL Company


--
Akshay Joshi
Sr. Softw= are Architect
<= span style=3D"color:rgb(0,0,0);font-family:arial,sans-serif;font-size:13px;= border-collapse:collapse">
<= /b>
=
Phone: +91 20-3058-9517
Mobile: +91 976-788-8246<= /font>


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

Enterpr= iseDB UK: http://= www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Akshay = Joshi
Sr. Software Architect


Phone: +91 20-3058-9517
Mobile: +91 97= 6-788-8246


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @p= gsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL = Company
--000000000000aee324057d8822b1--