License: MIT

Experimental package to expose an SQL database through a JSON-RPC endpoint (for read-only access at the moment). Supports:

• MS SQL
• MySQL
• Postgres
• SQLite

The only reason I made this is to access MSSQL data from a Cloud Foundry Python app without creating a custom buildpack. In particular, this means I can't install FreeTDS. As I want to use this with the Pandas package without modifying any of my existing code, the return type of the API is a top-level array, so that the read_json method will work with it out of the box.

Use go get github.com/michaelbironneau/jsql, followed by go install. Then you can run

jsql --port 1234 --password scrambled_eggs

You can now use the client:

import (
	jsql "github.com/michaelbironneau/jsql/client"
	"fmt"
)

func main() {
	c := &jsql.Client{Password: "scrambled_eggs"}

	if err := c.Dial("127.0.0.1:1234"); err != nil {
		panic("Failed to dial server!")
	}

	defer c.Close()

	result, err := c.Query("mssql", "server=localhost;user id=sa", "SELECT 1 AS 'Answer'")

	if err != nil {
		fmt.Println(err)
		return
	}

	fmt.Println("Answer: %v\n", result[0]["Answer"])
}

You can optionally specify a password using the --password flag.

You can optionally specify a server certificate and key file using the --cert and --key flags. The client should then set TLS to true:

c := &client.JSQLClient{TLS: true}

See jsql.py. Usage examples:

from jsql import Database

db = Database("127.0.0.1", 1234, "sqlite3", "./1.db")

print(db.sql("select 1 as 'Answer'"))  # prints [{"Answer": 1}]

print(db.sql("select * from test"))

Importing the data into Pandas:

from pandas import DataFrame
from jsql import Database

db = Database("127.0.0.1", 1234, "sqlite3", "./1.db")

df = DataFrame.from_dict(db.sql("select * from test"))