26
Here are the two ways I have successfully used MongoDB with Mathematica. AFAIK much of this would also apply to similar DBs such as CouchDB.
A. Easy way
One working example used a third party Mongo supplier such as 28msec.io and use URLFetch e.g.
With this supplier you can both read from and write to the database using HTTP GET and simple query strings. ...
18
Preamble
As was already hinted in the comments, the new functionality is a much higher level. It is basically a compiler from entity query language to SQL, specialized to a given SQL dialect of the underlying database engine.
You can view the new functionality more or less as ORM for WL. It brings similar benefits as e.g. Django ORM brings to python web ...
17
Mathematica can also connect to Neo4J via the REST API. Instructions for use on OS X:
1. Install Neo4j
2. start the webserver
$ sudo apachectl start
3. start Neo4j (installation in usr/local/bin)
$ neo4j start
4. Make sure all is working
127.0.0.1:7474
Examples:
URLFetch["http://localhost:7474/db/data/node",
{"StatusCode", "Content"},
"Method" -&...
14
For MongoDB instead of using the Rest interface using urls as suggested by Mike, you can use the Java driver.
Here's the proof of concept that should allow a lot of fruitful things ...
I'm still amazed by the possibilities offered by JLink.
Prerequisites
Install MongoDB, for example on Windows http://docs.mongodb.org/manual/tutorial/install-mongodb-on-...
12
I have an open-source MongoDB Mathematica driver here: https://github.com/zbjornson/MongoDBLink. There are quick setup instructions and an overview in the readme.
It's built on the official Java driver, like @faysou's answer, but it's optimized to reduce the JLink overhead when serializing/deserializing many documents. It also transparently maps some of the ...
12
Here is one example of how to create a neo4j connection using JDBC driver from this git.
The steeps are:
1- Put the JAR file (neo4j-jdbc-2.0.1-SNAPSHOT-jar-with-dependencies.jar) together with other jar drivers in: FileNameJoin@{ $InstallationDirectory, "Contents/SystemFiles/Links/DatabaseLink/Java"}
2- Restart you Mathematica
3- Now, using Movie Graph ...
11
After some useless hours in Google trying to understand jTDS, I discovered that DatabaseLink implementation is a open Package, and that SQLInsert implementation is in the file DatabaseLink/Kernel/SQL.m.
Inside It you can check that SQLInsert implementation is just a special application of SQLExecute. And that SQLExecute has two new parameters in V10 called ...
10
I am one of the developers for the new relational database connectivity features.
Very soon, a rather extensive tutorial will be added to the current documentation (via documentation paclet update), which would contain, in particular, examples of the kind you are interested in.
What you need is:
EntityList @ FilteredEntityClass[
"table",
...
10
The issue here is that there are restrictions upon the form of EntityFunction definitions when an entity store is backed by a relational data source. In order to support efficient querying via the entity framework, such functions must be compilable to SQL. The Details section of the EntityFunction documentation lists the forms that the databases framework ...
9
Wolfram Language version 11.3 now has support for MongoDB.
You can connect to a MongoDB database via MongoConnect. For example, connecting to a database running locally:
In[1]:= Needs["MongoLink`"]
client = MongoConnect[];
MongoGetDatabaseNames[client]
Out[2]= {"Test", "admin"}
9
General
The new relational databases functionality does currently support SQLite, MySQL, PostgreSQL, MS SQL, and the support for Oracle is coming very soon. There are a few steps one has to go through to expose the database as a source of entities in WL.
In all cases, the database must be prepared / exist before you try to connect. Please note that having ...
7
Two possible solutions are
Use MySQL, SQLite or any other database that is not an in-memory database. HSQL keeps all its data in the memory at all times. I'm leaving this for future visitors since there is a version of HSQL that is built in that is in-memory, although that was not the case for the OP.
Increase the Java heap size by using the same technique ...
7
Firstly, a bit about SQLite insert performances:
https://stackoverflow.com/questions/1711631/how-do-i-improve-insert-per-second-performance-of-sqlite
https://stackoverflow.com/questions/3852068/sqlite-insert-very-slow
Both of these state the fact that SQLite wraps every insert statement with a transaction and that the run times can be up to 270x faster ...
6
Mathematica 10 ships with version 3.7.2 of the Xerial SQLite JDBC driver. Let me first verify your findings.
SQLiteVersion[] := Module[{v, c = OpenSQLConnection[JDBC["SQLite(Memory)", ""]]},
v = First@First@SQLExecute[c, "SELECT sqlite_version();"];
CloseSQLConnection[c];
v
];
SQLiteVersion[]
3.6.23.1
There are a few SQLite databases in that ...
6
Of course in this case it would be better if you could write a single Table expression:
t3 = Table[10 i + j -> i + j, {i, 5}, {j, 3}]
{{11 -> 2, 12 -> 3, 13 -> 4},
{21 -> 3, 22 -> 4, 23 -> 5},
{31 -> 4, 32 -> 5, 33 -> 6},
{41 -> 5, 42 -> 6, 43 -> 7},
{51 -> 6, 52 -> 7, 53 -> 8}}
If not you can use ...
6
The shortest solution is to set attribute Listable for g:
SetAttributes[g, Listable]
g[a_, b_] := a -> b
t1 = Table[10 i + j, {i, 5}, {j, 3}];
t2 = Table[i + j, {i, 5}, {j, 3}];
g[t1, t2]
{{11 -> 2, 12 -> 3, 13 -> 4}, {21 -> 3, 22 -> 4, 23 -> 5},
{31 -> 4, 32 -> 5, 33 -> 6}, {41 -> 5, 42 -> 6, 43 -> 7},
{51 ->...
6
Wolfram Language version 11.3 now has support for interfacing with MongoDB.
You can connect to a MongoDB database via MongoConnect. For example, connecting to a database running locally:
In[1]:= Needs["MongoLink`"]
client = MongoConnect[];
MongoGetDatabaseNames[client]
Out[2]= {"Test", "admin"}
6
This is not so much a solution as a description of reproduction steps... but the short version of my story was to exit Mathematica, relaunch and try again.
analysis current as of Mathematica v12, *Databases* paclet v0.3.3
There seems to problem in the way that Databases paclet locates a supporting file, distribution.exe on Windows. Starting from a clean ...
5
Import[myfile.ndjson,"JSON"] doesn't work because the file as a whole is not valid JSON. But each line is valid JSON and so we can treat the file as a stream and read it one line at a time. The simplest way to do this would be
stream = OpenRead@"~/test.ndjson";
ClearAll[line];
result = {};
While[line =!= EndOfFile,
line = ReadLine[...
5
Randomly-colored map
Let's start with a colored map of the first level of administrative divisions for a country. We'll use Germany, but any country that has curated data for administrative divisions and their polygons will work. For Germany, these divisions are the Bundesländer.
country = Entity["Country", "Germany"];
regions =
...
4
Using SQLBeginTransaction works fast with the SQLite version 3.7.2 distributed with Mathematica 10 :
Needs["DatabaseLink`"];
testdb = FileNameJoin[{$TemporaryDirectory, "test1.db"}];
If[ FileExistsQ[testdb], DeleteFile[testdb] ];
db = JDBC["SQLite", testdb];
conn = OpenSQLConnection[db];
SQLCreateTable[conn,
SQLTable["TEST"],
{SQLColumn["COL1", "...
4
You can import the prices from the Energy Information Administration.
RCLC1d = Import["http://www.eia.gov/dnav/pet/hist_xls/RCLC1d.xls"];
c1 = RCLC1d[[2, All, {1, 2}]];
c1prices = Cases[Drop[c1, 3], {_, _?NumberQ}];
DateListPlot[c1prices, Joined -> True,
PlotLabel -> "WTI Oil Price since " <>
DateString[
c1prices[[1, 1]], {"Day", " ", ...
4
To pass batch parameters to SQLExecute, we must specify the arguments as a single list-of-lists. Each sublist is expected to contain one value for each parameter placeholder. Thus, there is no need to split Matrix into its component columns using Matrix[[All,1]],Matrix[[All,2]]. It is enough to say:
SQLExecute[con, "INSERT INTO VARIABLE (x,y) VALUES (`1`,...
4
The leading byte sequence 255, 216, 255, 224 looks like the file header that one finds when an image is in JPEG File Interchange Format. To import such an image, we can use:
ImportByteArray[ByteArray[picData], "JPEG"]
ImportByteArray was introduced in version 11.2 of Mathematica. In earlier versions, we can use this instead:
ImportString[...
4
I received an answer from Wolfram Research Technical Support. They say that it is not presently possible to access a file-based relational database (like SQLite) by specifying a URL. You have to get the underlying file at the URL, copy it to a local drive, and then treat it like a file. I have requested that the developers see if this limitation can be ...
4
Yes, but there appears to be a work-around:
Upload the SQLite database to the cloud and make a note of its URL.
ClearAll["Global`*"]
fileSchema = RelationalDatabase[
FindFile["ExampleData/ecommerce-database.sqlite"]];
c = CloudSave[fileSchema];
c[[1]]
(** "https://www.wolframcloud.com/obj/f799c3e1-cdf2-4eff-858c-20fa001afe11" **)
Now the original ...
4
Admittedly the date and time support for the new relational databases is not yet exactly where we would like it to be.
Under the following assumptions
You have already registered the database ( as described e.g. in the tutorial you linked)
Your "SoldDate" column is of the type DATE, not DATETIME
You actually wanted the less, not greater, inequality sign ...
3
In the question you do not specify which database driver you use to open the connection with JDBC. I have been experimenting with Microsoft Access Database drive and I had similar problems in the past.
In that case you must enclose the table parameter of type view in your SQLExecute or SQLSelect query with square brackets, e.g.
SQLExecute[conn, "select * ...
3
If you want to bypass .NET and do something that I personally find more intuitive then run it all through the terminal. I'm running OS X 10.10 and have Mongo 3.2 installed. Create a document:
json = ExportString[{"old mcdonald" -> {"pig" -> 2, "cow" -> 4}}, "JSON"]
"{
\"old mcdonald\": {
\"pig\": 2,
\"cow\": 4
}
}"
Then ...
3
Here is one options:
If[SQLExecute[con, "Select * from TABLE","MaxRows" -> 1] == {},True, False]
If it's just a test, Using MaxRows you can prevent an unnecessary data load.
Only top voted, non community-wiki answers of a minimum length are eligible