Dataset: Databases for lazy people
dataset.readthedocs.org
dataset.readthedocs.org
Few questions:
- what's the performance like? is there any overhead to alchemy, eg comparing schema every time you do an insert?
- no way to specify primary keys as an alternative to the auto-generated id column?
- no table.remove()?
- what about inserting more complicated data structures, eg a dict with a nested dict or a list? would be great if those were serialized auto-magically into a blob type (or used to create another table with a foreign key?)
- would be nice to be able to freeze a schema with table.freeze() for example: from then on new columns don't get created automatically, or get stored in an extra blob column (this is a very common scenario for python devs where non-indexable columns just get stuck in a blob)
- let me optionally define a schema and specify defaults with table.schema(name='', price=0.0')
- would love to see table.ensureIndex('column', 'unique') similar to mongo for quickly creating indices
- db = Dataset() should do dataset.create('sqlite:///:memory:') for me - would be nice to have that as the default connector, so that Dataset() acts as LINQ for Python by default
- dataset.freeze is nice but I'd rather have dataset.export() & dataset.import() letting me easily copy rows from one db to another (after inspection for example)
Thanks for creating this!
1. What size limits/practical constraints are there on freezefiles (and accompanying JSON files)?
2. Are there any code samples for consuming freezefiles, or should I just assume it's simple JSON/YML parsing?
3. Has there been any thought in using this to expose database contents via static REST API?
Final thought: this seems like a great step towards solving the age-old version controlling data problem.
From what I can read, it seems that this cool looking tool allows you to use SQL as a kind of object free data store, maybe not unlike a NoSQL DB python wrapper (freeing you from first defining your models, and then ensuring that the SQLAlchemy functions have updated your DB).
As for 1.: CSV files are encoded as a stream, so they can be as large as needed. JSON is dumped as a whole from memory, I'd be keen to see if someone has written a streaming JSON encoder.
2.: Consuming, no. I normally load them in a browser with D3 or jQuery to feed them into a graphic or other interface.
3.: I'd argue this is out of scope for dataset, but simpler REST API makers would definietly be cool. Check https://github.com/okfn/webstore - this is what dataset came out of, and it makes somewhat RESTish APIs.
This looks interesting: https://gist.github.com/akaihola/1415730
Edit: dataset looks like a really interesting library!
For datasets that fit in memory, Pandas seems like the best bet. Good I/O functions (JSON, CSV), easy slicing (numpy array-like syntax), and some sql-like operations (groupby, join).
For large datasets, you'd need a proper db.
So is Dataset then useful for datasets that cannot fit in memory but aren't too large?
Recently, I've been tasked with mapping all of our clients addresses to lat/long. I could've read the CSV and appended the results to each line. Or used a JSON file. That I would have to read/write every time.
Instead, I wrote some pseudo-helper to dump all the CSV data into a SQLite DB. Then I ran my script. Every time I found a lat/long, I could mark the client as "done" and add the lat/long for that client and every client that shared this address. When I had to cut my script because I saw one result from Google Maps was wrong, I could just edit it straight in SQL, mark it as "invalid" and relaunch my script: it started right back at the first undone row. Then I just had to select all the "invalid" results and search them manually or refine them so Google Maps would give me a proper result.
Dataset is useful for small data that is constantly being worked on.
(This answer is from a Ruby POV and the dataset I was working on had about 4K rows, which explains why a) some Python magic wasn't available to me, maybe it would have been perfect in Python world and b) I didn't want to play with streams on my files)
Of course I still need some automation to correctly use my "DataMiner" (as I called it) to the fullest. I'll use Dataset's API as a basis to rewite it correctly.
Also, it looks like it is a proper DB (access layer), point it at postgres or something and take away it's ALTER and CREATE permissions and you're good to go.
Sure the mocks had to do some work, but a simple cache allowed me to perform all of the CRUD operations in memory. I can see doing something similar with Mongo/Couch, but having done the DAL with a pure mock set injected via Spring, I don't really see the point. The same goes for HQL or another lightweight in memory DB + Hibernate/JPA. I assume the model of interaction would work with Python or similar languages too.
It's kinda of the opposite of the delivery-date-and-it's-done style of project.
Hard is stuff like some very obscure sort algorithm which is mysterious but once you figure it out, you can apply the sort.
Big and interconnected is what RDBMS is where knowing only one or a couple topics in isolation makes the whole thing appear useless... if you all you know about is normalization, or the idea of foreign keys, or the idea of indexes, or the idea of transactions, individually it all seems like a waste of time lets just use CSV files. But once you know a critical mass of the (simple) parts, its becomes a valuable tool.
If sorts were like RDBMS then once you understood the quicksort you'd still be inherently unable to ever apply a quicksort unless you also knew the radix sort. But they're not like that.
Namely, when doing ETL you don't want to have to map all your tables and relationships into models that an ORM likes to have. IMO, there is such a dearth of tools in this space of "quick and dirty" database work. People are either using highly custom scripts on one end or things like Kettle or commercial analogs for "big serious work" on the other. There's almost no in-between.
Having something at a slightly higher level of abstraction than the database driver itself is really, really nice and makes for cleaner, more readable code. Makes me wonder about my continued work on DataExpress!
Typical example, "Say what, who decided the name column is now two columns first name and last name ?"
And sometimes there's absolutely nothing wrong with that, if the natural demarc point in a project isn't the database and its schema.
For more power, drop down to SQLAlchemy.
[1]: https://github.com/pudo/dataset/blob/dc144a27b01ff404a528275... [2]: https://dataset.readthedocs.org/en/latest/quickstart.html#ru...
That's more than a bit unsatisfactory if I'm using a query builder or ORM to avoid writing custom SQL queries.
> For more power, drop down to SQLAlchemy.
It's closer to stepping sideways, even the expression language is at a similar level of abstraction.
in <module> import dataset File "C:\Python33\lib\site-packages\dataset\__init__.py", line 7, in <module> from dataset.persistence.database import Database File "C:\Python33\lib\site-packages\dataset\persistence\database.py", line 3, in <module> from urlparse import parse_qs ImportError: No module named 'urlparse'
I'll have to look into it more in-depth later, but I love the idea behind it.
Sometimes it's useful to persist a mass of crap, without thinking through the format at all. Webscraping is a good example offered by the project author.
http://sequel.jeremyevans.net/ https://github.com/jeremyevans/sequel