This is a simple graph database in SQLite, inspired by

About

This is a simple graph database in SQLite, inspired by "SQLite as a document database".

Structure

The schema consists of just two structures:

  • Nodes - these are any json objects, with the only constraint being that they each contain a unique id value
  • Edges - these are pairs of node id values, specifying the direction, with an optional json object as connection properties

Applications

Usage

Installation

Basic Functions

The python database script provides convenience functions for atomic transactions to add, delete, connect, and search for nodes.

Any single node or path of nodes can also be depicted graphically by using the visualize function within the database script to generate dot files, which in turn can be converted to images with Graphviz.

Testing

There will be more robust and dedicated unit tests with pytest soon, but in the meantime, running the example locally will do in a pinch.

This bit of shell magic will pull out the commands from this document:

grep ">>> " README.md | grep -v "grep" | sed -e 's/>>> //'

Use a final | clip (Windows), | pbcopy (macOS), or | xclip -selection clipboard (most linuxes) to copy all the commands into your clipboard.

If you have the correct version of SQLite installed, everything should just work without errors.

Example

Dropping into a python shell, we can create, upsert, and connect people from the early days of Apple Computer. The resulting database will be saved to a SQLite file named apple.sqlite:

>>> apple = "apple.sqlite"
>>> import database as db
>>> db.initialize(apple)
>>> db.atomic(apple, db.add_node({'name': 'Apple Computer Company', 'type':['company', 'start-up'], 'founded': 'April 1, 1976'}, 1))
>>> db.atomic(apple, db.add_node({'name': 'Steve Wozniak', 'type':['person','engineer','founder']}, 2))
>>> db.atomic(apple, db.add_node({'name': 'Steve Jobs', 'type':['person','designer','founder']}, 3))
>>> db.atomic(apple, db.add_node({'name': 'Ronald Wayne', 'type':['person','administrator','founder']}, 4))
>>> db.atomic(apple, db.add_node({'name': 'Mike Markkula', 'type':['person','investor']}, 5))
>>> db.atomic(apple, db.connect_nodes(2, 1, {'action': 'founded'}))
>>> db.atomic(apple, db.connect_nodes(3, 1, {'action': 'founded'}))
>>> db.atomic(apple, db.connect_nodes(4, 1, {'action': 'founded'}))
>>> db.atomic(apple, db.connect_nodes(5, 1, {'action': 'invested', 'equity': 80000, 'debt': 170000}))
>>> db.atomic(apple, db.connect_nodes(1, 4, {'action': 'divested', 'amount': 800, 'date': 'April 12, 1976'}))
>>> db.atomic(apple, db.connect_nodes(2, 3))
>>> db.atomic(apple, db.upsert_node(2, {'nickname': 'Woz'}))

The nodes can be searched by their ids or any other combination of attributes (either as strict equality, or using _search_like in combination with _search_starts_with or _search_contains):

>>> db.atomic(apple, db.find_node(1))
{'name': 'Apple Computer Company', 'type': ['company', 'start-up'], 'founded': 'April 1, 1976', 'id': 1}
>>> db.atomic(apple, db.find_nodes({'name': 'Steve'}, db._search_like, db._search_starts_with))
[{'name': 'Steve Wozniak', 'type': ['person', 'engineer', 'founder'], 'id': 2, 'nickname': 'Woz'}, {'name': 'Steve Jobs', 'type': ['person', 'designer', 'founder'], 'id': 3}]

Paths through the graph can be discovered with a starting node id, and an optional ending id; the default neighbor expansion is nodes connected nodes in either direction, but that can changed by specifying either find_outbound_neighbors or find_inbound_neighbors instead:

>>> db.traverse(apple, 2, 3)
[2, 3]
>>> db.traverse(apple, 4, 5)
[4, 1, 5]
>>> db.traverse(apple, 5, neighbors_fn=db.find_inbound_neighbors)
[5]
>>> db.traverse(apple, 5, neighbors_fn=db.find_outbound_neighbors)
[5, 1, 4]
>>> db.traverse(apple, 5, neighbors_fn=db.find_neighbors)
[5, 1, 4, 3, 2]

Any path or list of nodes can rendered graphically by using the visualize function. This command produces dot files, which are also rendered as images with Graphviz:

>>> db.visualize(apple, 'apple.dot', [4, 1, 5])

The resulting text file also comes with an associated image (the default is png, but that can be changed by supplying a different value to the format parameter)

The default options include every key/value pair (excluding the id) in the node and edge objects:

Basic visualization

There are display options to help refine what is produced:

>>> db.visualize(apple, 'apple.dot', [4, 1, 5], exclude_node_keys=['type'], hide_edge_key=True)

More refined visualization

The resulting dot file can be edited further as needed; the dot guide has more options and examples.

Comments
  • Find Node

    Find Node

    Why do you use fetchall() and not fetchone()?

    https://github.com/dpapathanasiou/simple-graph/blob/be41db28995f745a4f85677bcade7f0471984848/python/database.py#L112

  • Ambiguous nature of ID

    Ambiguous nature of ID

    Id's are numerical in the tests which JSONDecoder is happy with despite the unnecessary need to decode the IDs as JSON.

    If you create a key as a string for instance this will work but will not be possible to decode as the json library in python expects strings to be explicitly quoted. Thus you cannot traverse or visualise if edges aren't double quoted strings.

    Should I be double quoting my strings for IDs?

    I'll post a coded example later on if this isn't clear

  •  support a uuid as node.id

    support a uuid as node.id

    • support a string (e.g. uuid '5b86cc504bda4c7e94d47d00a7696bde') as node.id

    • int64 as node.id are not supported. I tried to us a long int

    uuid.UUID('5b86cc504bda4c7e94d47d00a7696bde').int

    P.S. Thank you for your work. This library has made my day.

  • Error initializing database

    Error initializing database

    Context: MacOS Mojave, SQLite3, Python 3.8.5

    The default sqlite3 binary in MacOS Mojave does not have the JSON extensions enabled.

    Fix: Install sqlite via brew Add to shell initialization file: export PATH="/usr/local/opt/sqlite/bin:$PATH"

  • Searching for nodes where query must match a value in an array in the body

    Searching for nodes where query must match a value in an array in the body

    I'm looking to do something like this

    from simple_graph_sqlite import database as db 
    dbfile = "db.sqlite"
    
    db.initialize(dbfile)
    
    db.atomic(dbfile, db.add_node({'name': 'foo', 'type':['company', 'start-up']}, 1))
    db.atomic(dbfile, db.add_node({'name': 'bar', 'type':['cat', 'apple']}, 1))
    
    db.atomic(apple, db.find_nodes({'type': 'apple'}, db._search_like, db._search_starts_with))
    

    But I don't think the last statement works

  • Multiple Node Connections in visualize

    Multiple Node Connections in visualize

    If I am having a graph with multiple nodes attached to a single node (e.g. node 7), the command db.visualize(graph, 'example.dot', db.traverse(graph, 7, neighbors_fn=db.find_outbound_neighbors)) only visualizes the first connection found in db.find_outbound_neighbors. Is there a way to automatically visualize all connections?

  • Thank You

    Thank You

    Hey,

    This isn't an issue, just wanted to say thanks for creating simple-graph!

    I used it as the basis for a Julia package (https://github.com/joshday/SQLiteGraph.jl), since as far as I know, there's no graph database that has a Julia interface. I made some minor changes tailored to my specific use case, but I wouldn't have been able to put it together so easily without the help of your work, so thanks again!

  • No Traverse with TEXT ID

    No Traverse with TEXT ID

    When I use IDs like meta6 the traverse is not working.

    print(db.traverse(db_file_name, 'meta6', neighbors_fn=db.find_neighbors))
    

    This only prints ['"meta6"'].

    In the atomic(), I enabled tracing via:

    connection.set_trace_callback(print)
    

    and the query is incorrect:

    PRAGMA foreign_keys = TRUE;
    WITH RECURSIVE traverse(id) AS (
      SELECT '"meta6"'
      UNION
      SELECT source FROM edges JOIN traverse ON target = id
      UNION
      SELECT target FROM edges JOIN traverse ON source = id
    ) SELECT id FROM traverse;
    

    Changing it to

    PRAGMA foreign_keys = TRUE;
    WITH RECURSIVE traverse(id) AS (
      SELECT 'meta6'
      UNION
      SELECT source FROM edges JOIN traverse ON target = id
      UNION
      SELECT target FROM edges JOIN traverse ON source = id
    ) SELECT id FROM traverse;
    

    makes it work.

  • SQL errors result in database being locked

    SQL errors result in database being locked

    Hi - very handy package!

    I've been playing with it and Ive noticed that if I try to insert a duplicate node, it throws the expected IntegrityError. But after that, subsequent operations fail due to the db being locked.

    Looking at atomic I think this is because the db is not being closed. I modified it to wrap it in try/finally and it seems to solve the problem.

    def atomic(db_file, cursor_exec_fn):
        connection = None
        try:
            connection = sqlite3.connect(db_file)
            cursor = connection.cursor()
            cursor.execute("PRAGMA foreign_keys = TRUE;")
            results = cursor_exec_fn(cursor)
            connection.commit()        
        finally:
            if connection: 
                connection.close()
        return results
    
Manage your sqlite database very easy (like django) ...

Manage your sqlite database very easy (like django) ...

Feb 9, 2022
AWS Tags As A Database is a Python library using AWS Tags as a Key-Value database.

AWS Tags As A Database is a Python library using AWS Tags as a Key-Value database. This database is completely free* ??

Nov 25, 2022
Mongita is to MongoDB as SQLite is to SQL
Mongita is to MongoDB as SQLite is to SQL

Mongita is a lightweight embedded document database that implements a commonly-used subset of the MongoDB/PyMongo interface. Mongita differs from MongoDB in that instead of being a server, Mongita is a self-contained Python library. Mongita can be configured to store its documents either on disk or in memory.

Jan 7, 2023
Python function to query SQLite files stored on S3

sqlite-s3-query Python function to query a SQLite file stored on S3. It uses multiple HTTP range requests per query to avoid downloading the entire fi

Dec 27, 2022
Tools for analyzing Git history using SQLite

git-history Tools for analyzing Git history using SQLite Installation Install this tool using pip: $ pip install git-history Usage This tool can be r

Jan 2, 2023
Decentralised graph database management system

Decentralised graph database management system To get started clone the repo, and run the command below. python3 database.py Now, create a new termina

Apr 18, 2022
A Persistent Embedded Graph Database for Python
A Persistent Embedded Graph Database for Python

Cog - Embedded Graph Database for Python cogdb.io New release: 2.0.5! Installing Cog pip install cogdb Cog is a persistent embedded graph database im

Dec 30, 2022
HTTP graph database built in Python 3

KiwiDB HTTP graph database built in Python 3. Reference Format References are strings in the format: {[email protected]} Authentication Currently, t

Dec 17, 2021
A simple GUI that interacts with a database to keep track of a collection of US coins.

CoinCollectorGUI A simple gui designed to interact with a database. The goal of the database is to make keeping track of collected coins simple. The G

Nov 9, 2021
A Simple , โ˜๏ธ Lightweight , ๐Ÿ’ช Efficent JSON based database for ๐Ÿ Python.
A Simple , โ˜๏ธ Lightweight ,  ๐Ÿ’ช Efficent JSON based database for ๐Ÿ Python.

A Simple, Lightweight, Efficent JSON based DataBase for Python The current stable version is v1.6.1 pip install pysondb==1.6.1 Support the project her

Jan 7, 2023
A Painless Simple Way To Create Schema and Do Database Operations Quickly In Python
A Painless Simple Way To Create Schema and Do Database Operations Quickly In Python

PainlessDB - Taking Your Pain away to the moon ?? Contribute ยท Community ยท Documentation ?? Introduction : PainlessDB is a Python-based free and open-

Jul 15, 2022
ClutterDB - Extremely simple JSON database made for infrequent changes which behaves like a dict

extremely simple JSON database made for infrequent changes which behaves like a dict this was made for ClutterBot

Jan 12, 2022
A very simple document database

DockieDb A simple in-memory document database. Installation Build the Wheel Fork or clone this repository and run python setup.py bdist_wheel in the r

Jan 16, 2022
Simpledb-py: Simple JSON database

Simpledb-py: Simple JSON database

Feb 9, 2022
Simple json type database for python3

What it is? Simple json type database for python3! What about speed? The speed is great! All data is stored in RAM until saved. How to install? pip in

Feb 11, 2022
TinyDB is a lightweight document oriented database optimized for your happiness :)
TinyDB is a lightweight document oriented database optimized for your happiness :)

Quick Links Example Code Supported Python Versions Documentation Changelog Extensions Contributing Introduction TinyDB is a lightweight document orien

Dec 30, 2022
Python object-oriented database

ZODB, a Python object-oriented database ZODB provides an object-oriented database for Python that provides a high-degree of transparency. ZODB runs on

Dec 31, 2022
Elara DB is an easy to use, lightweight NoSQL database that can also be used as a fast in-memory cache.
Elara DB is an easy to use, lightweight NoSQL database that can also be used as a fast in-memory cache.

Elara DB is an easy to use, lightweight NoSQL database written for python that can also be used as a fast in-memory cache for JSON-serializable data. Includes various methods and features to manipulate data structures in-memory, protect database files and export data.

Jan 4, 2023
LightDB is a lightweight JSON Database for Python

LightDB What is this? LightDB is a lightweight JSON Database for Python that allows you to quickly and easily write data to a file Installing pip3 ins

Oct 1, 2022