{"action":"created","issue":{"url":"https://api.github.com/repos/pysal/pysal/issues/692","labels_url":"https://api.github.com/repos/pysal/pysal/issues/692/labels{/name}","comments_url":"https://api.github.com/repos/pysal/pysal/issues/692/comments","events_url":"https://api.github.com/repos/pysal/pysal/issues/692/events","html_url":"https://github.com/pysal/pysal/pull/692","id":105728871,"number":692,"title":"Conditional Database Import / Docos","user":{"login":"jlaura","id":2105468,"avatar_url":"https://avatars.githubusercontent.com/u/2105468?v=3","gravatar_id":"","url":"https://api.github.com/users/jlaura","html_url":"https://github.com/jlaura","followers_url":"https://api.github.com/users/jlaura/followers","following_url":"https://api.github.com/users/jlaura/following{/other_user}","gists_url":"https://api.github.com/users/jlaura/gists{/gist_id}","starred_url":"https://api.github.com/users/jlaura/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jlaura/subscriptions","organizations_url":"https://api.github.com/users/jlaura/orgs","repos_url":"https://api.github.com/users/jlaura/repos","events_url":"https://api.github.com/users/jlaura/events{/privacy}","received_events_url":"https://api.github.com/users/jlaura/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-09-10T04:17:00Z","updated_at":"2015-09-10T06:35:50Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/pysal/pysal/pulls/692","html_url":"https://github.com/pysal/pysal/pull/692","diff_url":"https://github.com/pysal/pysal/pull/692.diff","patch_url":"https://github.com/pysal/pysal/pull/692.patch"},"body":"Two parter, so feel free to request that I break apart.\r\n\r\n1. Trivial fixes to ESDA documentation.\r\n2. I added a database driver to the current FileIO module.  This uses a conditional import  (in core/IOHandlers/__init__.py) and depends on both sqlalchemy (ships with anaconda) and geomet.  \r\n\r\nThe main reason for the PR is to continue the conversation re: FileIO / Database access / Conditional imports.\r\n\r\nTo test (`conda install gdal` - only to get the test data generated, not necessary for the PR):\r\n\r\n```python\r\n#Slurp all the pysal example files into a single sqlite database\r\nimport glob\r\nimport subprocess\r\npsexamples = glob.glob('/Users/jay/github/pysal/pysal/examples/*/*.shp')\r\nfor i, s in enumerate(psexamples):\r\n    cmd = 'ogr2ogr -f SQLite -append /Users/jay/Desktop/psexamples.sqlite {}'.format(s)\r\n    subprocess.check_call(cmd.split())\r\n```\r\n\r\nThen\r\n\r\n```python\r\nimport pysal as ps\r\ndb = ps.open('sqlite:///psexamples.db') # /// is a relative sqlite open and //// is an absolute path\r\ndb.tables #property to list available tables\r\n\r\n# List of tables printed to screen \r\n\r\nvirginia_geojson = ps.read('virginia', attributes=True)  #Defaults to attributes=False, or just the \r\n```\r\n\r\nSome things to discuss (maybe in a hangout?)\r\n1. FileIO looks like it would be much more streamlined using ABC.  Having said that, it works and adding a new driver is trivial once the learning curve is stumbled up.\r\n2. FileIO docos claim to require `read`, `seek`, and `next` methods, but a DB is not a file object.  How can the interface be either generalized or the differences obfuscated (the current hack feels like a hack, so zero love from here)\r\n3. Conditional import a la @ljwolf suggestion via gitter.  Thoughts?  It is throwing a proper error on your side if you do not have Geomet?\r\n4. What about passing an SQL statement to the reader - totally possible - but do we want to support that?\r\n5. The user is required to know the input data type with respect to identifying `sqlite:///` (or postgresql with username/password/port).  Do we want to try and abstract that from the user or just accept that this is the way SQLAlchemy manages interfacing with databases?\r\n"},"comment":{"url":"https://api.github.com/repos/pysal/pysal/issues/comments/139130460","html_url":"https://github.com/pysal/pysal/pull/692#issuecomment-139130460","issue_url":"https://api.github.com/repos/pysal/pysal/issues/692","id":139130460,"user":{"login":"ljwolf","id":2250995,"avatar_url":"https://avatars.githubusercontent.com/u/2250995?v=3","gravatar_id":"","url":"https://api.github.com/users/ljwolf","html_url":"https://github.com/ljwolf","followers_url":"https://api.github.com/users/ljwolf/followers","following_url":"https://api.github.com/users/ljwolf/following{/other_user}","gists_url":"https://api.github.com/users/ljwolf/gists{/gist_id}","starred_url":"https://api.github.com/users/ljwolf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ljwolf/subscriptions","organizations_url":"https://api.github.com/users/ljwolf/orgs","repos_url":"https://api.github.com/users/ljwolf/repos","events_url":"https://api.github.com/users/ljwolf/events{/privacy}","received_events_url":"https://api.github.com/users/ljwolf/received_events","type":"User","site_admin":false},"created_at":"2015-09-10T06:35:50Z","updated_at":"2015-09-10T06:35:50Z","body":"This looks awesome! Cloning now. \r\n\r\nWhat you've put are great starting points for a discussion. That said, I may be a bit more outspoken about FileIO...\r\n\r\n1. This is a great implementation of a db interface leaning on the current mature standard for database interaction in python. Awesome. IMHO, we should extend this approach: FileIO started before a \"good\" python wrapper around OGR IO stuff. Moving forward, we should take advantage of new IO packages with soft dependencies while providing ways to access [old behavior](https://github.com/ljwolf/pysal/blob/pandasal/pysal/__init__.py#L92). Our comparative advantage is analytics, not feature-complete python clones of OGR. Using SQLalchemy here is definitely :+1: \r\n\r\n2. True... also applies more broadly to streams of GeoJSON/semistructured data. No clue how to do this. Ties to 4 quite strongly.\r\n\r\n3. freaking awesome, exactly how this should work @ module level! Now, how can we get nose to acknowledge this and avoid running tests on disabled modules?\r\n![2015-09-09-230942_817x266_scrot](https://cloud.githubusercontent.com/assets/2250995/9781326/d9b28360-5747-11e5-8f1d-61d068a7c91f.png)\r\n\r\n4. So, if the connection is opened as `conn`, then, a `conn.read(<SQL>)` would just pass the query string and kwargs down to the sqlalchemy engine? Seems fine in theory.\r\n\r\n5. I've seen some solutions to make it simple to connect to databases (and to avoid using plaintext passwords in demo code), but they involved a persistent local store of hashed credentials, and a set of \"accessor\" functions to connect to a database using the stored creds. So, like `pysal.db.add_database('USCensus', 'username', 'password', 'host', 'port', 'protocol'))` would store and `pysal.db.connect('USCensus')` would attach. Not the best, but definitely should be discussed. I have code to do this laying around if a prototype is wanted. \r\n"}}