API or example of frictionless usage

[essenciary]

Sorry to be a pain but I'm not sure how to handle the new API. I'm probably missing something but it seems to me that the results of both query(...) and Query(...) are pretty much unusable directly. When I'm at the REPL and run a query I expect something that I can understand and further work with.

Now MySQL.query(conn, "SHOW COLUMNS FROM tweets") returns something unusable for a normal human being:

(Field = String["id", "tweet_id", "text", "user_name", "screen_name", "avatar_url", "favorite_count", "retweet_count", "polarity", "subjective"], Type = Array{UInt8,1}[UInt8[0x69, 0x6e, 0x74, 0x28, 0x31, 0x31, 0x29], UInt8[0x76, 0x61, 0x72, 0x63, 0x68, 0x61, 0x72, 0x28, 0x32, 0x34, 0x29], UInt8[0x76, 0x61, 0x72, 0x63, 0x68, 0x61, 0x72, 0x28, 0x35, 0x31, 0x32, 0x29], UInt8[0x76, 0x61, 0x72, 0x63, 0x68, 0x61, 0x72, 0x28, 0x33, 0x32, 0x29], UInt8[0x76, 0x61, 0x72, 0x63, 0x68, 0x61, 0x72, 0x28, 0x33, 0x32, 0x29], UInt8[0x76, 0x61, 0x72, 0x63, 0x68, 0x61, 0x72, 0x28, 0x35, 0x31, 0x32, 0x29], UInt8[0x69, 0x6e, 0x74, 0x28, 0x31, 0x31, 0x29], UInt8[0x69, 0x6e, 0x74, 0x28, 0x31, 0x31, 0x29], UInt8[0x69, 0x6e, 0x74, 0x28, 0x31, 0x31, 0x29], UInt8[0x74, 0x69, 0x6e, 0x79, 0x69, 0x6e, 0x74, 0x28, 0x31, 0x29]], Null = String["NO", "YES", "YES", "YES", "YES", "YES", "YES", "YES", "YES", "YES"], Key = String["PRI", "UNI", "MUL", "", "", "", "", "", "MUL", "MUL"], Default = Union{Array{UInt8,1}, Missings.Missing}[missing, missing, missing, missing, missing, missing, missing, missing, missing, missing], Extra = String["auto_increment", "", "", "", "", "", "", "", "", ""])

Which is the representation of a NamedTuples._NT_Field_Type_Null_Key_Default_Extra{Array{String,1},Array{Array{UInt8,1},1},Array{String,1},Array{String,1},Array{Union{Array{UInt8,1}, Missings.Missing},1},Array{String,1}} -- oh my!

MySQL.Query(...) results in something similar...

Despite googling, I couldn't find any good example of converting one of these into a DataFrame in a straightforward way (without having to manually create a DataFrame and then manually iterate over the result).

(in a tiny voice: Help? 😨 )

[quinnj]

From the docs

MySQL.query(conn, sql, sink=Data.Table; append::Bool=false) => sink
Execute an SQL statement and return the results in sink, which can be any valid Data.Sink

So for sink, just put any valid Data.Sink, including DataFrame, like MySQL.query(conn, sql, DataFrame).

[essenciary]

Thanks - sorry, as I expected, I was missing something :) Cheers!

[quinnj]

Also, I would hope that a NamedTuple of Vectors wouldn't be too scary for you. It's actually one of the simplest possible things you could return for a query result. I.e. a NamedTuple is just a struct with fields, and in the case of a query result, each column in the resultset is a field. So in you're case, you could access each column of your resultset like nt = MySQL.query(conn, sql); nt.Field # get the entire Field column. This becomes even nicer in 0.7 where NamedTuple is builtin to the language.

[essenciary]

I was adding support for the new MySQL.jl API into Searchlight.jl - an ORM that provides a unifying API for MySQL.jl, SQLite.jl and Postgres.jl, allowing to swap DB backends in Genie. I'm also planning on adding support for ODBC soon, to cover cases where native Julia support is lacking.

The problem was that each library had its own API, which made a unifying approach very difficult. The only common thing was the DataFrame result, so I'm using that.

Now it seems that the DB access libraries that are actively developed and maintained (MySQL.jl, SQLite.jl and in some measure ODBC.jl) are moving towards a common API that wraps DataStreams.jl. I'd certainly appreciate a common API as it would make things simple in the ORM layer -- and would allow me to look at potentially more performant data structures, like the NamedTuple.