read_csv from HTTPs + basic-auth + custom port throws an error (urlopen error)

[skynss]

Code Sample, a copy-pastable example if possible

import pandas as pd
df = pd.read_csv('https://user-name:pwd@my.fqdn.com:8080/get_content.csv')
# fails with urlopen error [Errno 11003] getaddrinfo failed Python2.7 as well as Python 3.x

Problem description

HTTPS basic auth is very common. This URL format works in Excel, other text editors, etc. This url works in requests library. It seems like the scenario doesnt work because the underlying urlopen doesnt work

# I think it fails because the underlying code here fails in pandas/io/common.py:
from urllib2 import urlopen as _urlopen
b = _urlopen('https://un-name:pwd@my.fqdn.com:8080/get_content.csv')
# fails with urlopen error [Errno 11003] getaddrinfo failed

see stackoverflow issue urllib basic auth
Only way to overcome this is using requests + StringIO ?

Expected Output

be able to get a CSV loaded dataframe

Output of pd.show_versions()

python: 2.7.13.final.0 python-bits: 64 pandas: 0.20.1

[gfyoung]

HTTPS basic auth is very common

Apparently not with pandas 😉

But joking aside, that's an awkward problem to have (can't replicate since I have no endpoint against which to test this). Also, this is not easy to test without publicly providing credentials to an endpoint.

I read the SO post, and I was wondering: can you confirm that requests was the only way for you to get the request to work? I read a bunch of solutions in that discussion, so I just wanted to check.

If that is the case, I suppose we could implement a wrapper that makes the request and then returns the response content in place of urlopen, though doing it in a clean way will be the challenge. Not to mention, we would need to explicitly add requests as a Python 2.x dependency.

[skynss]

With requests and StringIO ot works well. I am travelling today but over next 1-2 days i can post a reproducable test url or simple reprocode. I suspect it repros with http and no real auth.. because i suspect this is simply a parsing error in hostname and port even before outbound call initiates. Can confirm in next 1-2 days when i am back unless you beat me to it

[gfyoung]

Yeah if you could provide repro code that would be useful since it is hard to reproduce without our own endpoint. However, if you could confirm none of the other solutions proposed in the SO work besides the requests ones, that would also be useful.

[skynss]

It appears to me that an existing url is not needed, because the issue is just in the parsing of the URL. eg:

df = pd.read_csv('http://username:pwd@cnn.com:8080/get_content.csv')
# returns URLError: <urlopen error [Errno 11003] getaddrinfo failed>
df = pd.read_csv('http://username:pwd@cnn.com/get_content.csv')
# returns InvalidURL: nonnumeric port: 'pwd@cnn.com' ( failing to find port number)

However, simplest way to fake it is:
Create a text file: aaa.csv and put some csv contents

with open('/path1/aaa.csv', 'w') as f:
  f.write( 'animal,bird\ncat,pigeon\nmonkey,swan')

cd /path1
run

python -m SimpleHTTPServer 8080

Now the url http://localhost:8080/aaa.csv should exist

import requests
import pandas as pd
u1 = 'http://localhost:8080/aaa.csv'
print( requests.get(u1).text) # as expected prints contents of aaa.csv
df = pd.read_csv(u1)  # as expected, loads contents of aaa.csv into df

u2 = 'http://uname:pwd@localhost:8080/aaa.csv'
print( requests.get(u2).text) # as expected prints contents of aaa.csv (ignores unnecessary uname pwd)
df = pd.read_csv(u2)  # URLError: <urlopen error [Errno 11003] getaddrinfo failed>

I'll provide a working positive test case url later tomorrow once I set it up. It would be good to engineer a fix to allow self-signed certs for testing too - eg: requests has verify=False. Helpful for testing.

[TomAugspurger]

So since we can't use requests, we would follow https://stackoverflow.com/a/4188709/1889400

1. detect and extract the username / password in the url
2. setup a urllib.Request context for the (stripped) url in

   pandas/pandas/io/common.py

   Line 197 in 125c414

   if _is_url(filepath_or_buffer):

3. encode the user / password and add to the Authorization header
4. urlopen the request context

[gfyoung]

@TomAugspurger :

1. Why can't we use requests ?

2. Does this work with the localhost repro example that @skynss provided?

[TomAugspurger]

Why can't we use requests ?

This alone isn't worth adding it as a dependency I think.

[gfyoung]

This alone isn't worth adding it as a dependency I think.

That's very different from "we can't use requests"

For starters, it's builtin with Python 3.x, so we would only be adding it as a dependency for Python 2.x. However, if we could find a way to use just urlopen, that would be nice though.

[TomAugspurger]

Does this work with the localhost repro example that @skynss provided?

Can't really test that since I'd have to write the implementation first :) I think it'll work though.

For starters, it's builtin with Python 3.x,

I don't think it is... It's documented as the recommended way for making high-level http calls though (but still requires a separate install).

[gfyoung]

I don't think it is... It's documented as the recommended way for making high-level http calls though (but still requires a separate install).

Oh right, I stand corrected. 😄

[skynss]

Here are working urls using self signed certs. Would be good to get it to work with self-signed certs too as that can be very useful especially in testing. Please let me know once you are done testing.. so I can shut down the demo because thats all it is up for.

import pandas as pd
import requests
from io import StringIO

# both urls use self signed cert. Both will remain working for few days
u1 = 'https://pandasusr:pandaspwd@pandastest.mooo.com:5000/aaa.csv' # non default ssl port 
u2 = 'https://pandasusr:pandaspwd@pandastest.mooo.com/aaa.csv' # default ssl port


r1 = requests.get(u1, verify=False)
print(r1.text) # prints ok
df1 = pd.read_csv(StringIO(r1.text))  # works

r2 = requests.get(u1, verify=False)
print(r2.text) # prints ok
df2 = pd.read_csv(StringIO(r2.text)) # works

# without requests
df1 = pd.read_csv(u1) # URLError: <urlopen error [Errno 11003] getaddrinfo failed>

df2 = pd.read_csv(u2)  # InvalidURL: nonnumeric port: 'pandaspwd@pandastest.mooo.com'