jeffknupp / sandman2

Automatically generate a RESTful API service for your legacy database. No code required!

sandman2

sandman2 documentation

[ ~ Dependencies scanned by PyUp.io ~ ]

sandman2 automagically generates a RESTful API service from your existing database, without requiring you to write a line of code. Simply point sandman2 to your database, add salt for seasoning, and voila!, a fully RESTful API service with hypermedia support starts running, ready to accept HTTP requests.

This is a big deal. It means every single database you interact with, from the SQLite database that houses your web browser's data up to your production PostgreSQL server can be endowed with a REST API and accessed programmatically, using any number of HTTP client libraries available in every language. sandman2 frees your data.

For developers:

Imagine you're working for AnonymousCorp and need to access Group Y's data, which is presented to you through some horrible API or GUI. Wouldn't it be nice if you could just interact with that database through a REST API?

More than that, imagine if you could interact with the database through a REST API and no one had to write any code. Not you. Not Group Y. No one. That means no boilerplate ORM code, no database connection logic. Nothing. sandman2 can be run as a command-line tool (sandman2ctl) that just takes your database information as parameters and connects to it, introspects the schema, generates a RESTful API, and starts the server.

What Happened to Sandman (1)?

sandman, the precursor to sandman2, is no longer being maintained. sandman had almost identical functionality but had an architecture that reflected the capabilities of the underlying ORM, SQLAlchemy. As of the 0.9 release, SQLAlchemy introduced the automap construct. This fundamentally changed the way that sandman could interact with the underlying database in a way that greatly simplified things. All that was needed was the actual effort to rewrite sandman from scratch...

sandman2 has since surpassed the functionality of the original sandman and the latter should be considered deprecated/obsolete.

Quickstart

Install sandman2 using pip: $ pip install sandman2. This provides the script sandman2ctl, which just takes the database URI string, described here. For example, to connect to a SQLite database in the same directory you're running the script, you would run:

$ sandman2ctl sqlite+pysqlite:///database_file_name

To connect to a PostgreSQL database, make sure you install a driver like psycopg2 using pip, then use the following connection string:

$ sandman2ctl postgresql+psycopg2://scott:tiger@localhost/mydatabase

Again, see the SQLAlchemy documentation for a more comprehensive discussion of connection strings.

Supported Databases

sandman2 supports all databases that the underlying ORM, SQLAlchemy, supports. Presently, that includes:

• MySQL
• PostgreSQL
• Oracle
• Microsoft SQL Server
• SQLite
• Sybase
• Drizzle
• Firebird

Third-party packages extend support to:

• IBM DB2
• Amazon Redshift
• SQL Anywhere
• MonetDB

Admin Interface

One of the best things about the original sandman was the Admin Interface. Not only does sandman2 include the Admin Interface, but it modernizes it as well. The layout has been greatly improved, especially when dealing with larger numbers of tables. All of the original functionality of the Admin Interface remains unchanged.

Here's a shot of the new look:

Customizing

If sandman2ctl doesn't give you fine-grained enough control over your REST endpoints, or you'd like to restrict the set of tables made available via sandman2ctl, you can easily integrate sandman2 into your application. See the documentation for more info.

Running sandman2 under Docker

sandman2 has an official docker image at Docker Hub. Simply docker pull jeffknupp/sandman2 to get the latest version. It supports the most popular database engines, but not all that sandman2 currently natively supports. If you'd like to see support for your RDBMS, either add a pull request on this repo (if possible) or create a new issue with the details of your database's Python driver.

Example

Here's how one would run sandman2 to connect to a PostgreSQL database running on one's host machine (i.e. not a remote database, which is far simpler) under Docker (on a Mac, explained below):

1. $ docker pull jeffknupp/sandman2
2. $ docker run -d -e DB_TYPE=postgres -e DB_DRIVER=psycopg2 -e USERNAME=jknupp -e DB_HOST=host.docker.internal -e DATABASE=jknupp -e DB_PORT=5432 -p 9000:5000 sandman2
3. $ curl localhost:9000/meta or open a browser to http://localhost:9000/admin/

Note, DB_HOST=host.docker.internal is only necessary for databases that reside on the host system (and the value only works on macOS). To connect to a database on a remote machine, simply replace that value with the machine's IP or hostname.

Parameters

Here are the parameters available to specify your connection information and their meaning:

• $DB_TYPE - The type of RDBMS to connect to (e.g. postgres or mysql)
• $DB_DRIVER - The name of the Python library to use as a driver (e.g. psycopg2 or pymysql)
• $USERNAME - Database username
• $PASSWORD - Database password
• $DB_HOST - Database IP or hostname
• $DB_PORT - Database port
• $DATABASE - Name of database to connect to

Pass each value separately to the docker run command with -e <VARIABLE>=<VALUE>. Not all are required, but which ones are required differs based on your target RDBMS.

sandman2's Issues

Documentation missing

Hi,

Could it be possible to have some basic doc samples on?

• Reverse proxy NGINX conf (everything I tried was breaking the URLs on proxy_pass)
• HTTP auth currently doing this on NGINX (just redirecting though)

Documentation for defining foreign key strings in the admin site is soooo close...

In the admin interface, I need the foreign keys to show something more informative than <flask_sqlalchemy.users object at 0x7fdec43e6c90>.

Chapter 4 of the latest docs describes a fix for this problem. Unfortunately, the docs are incomplete.

I've tried to implement the fix, but haven't been able to get it to work. Editing models.py is not enough, and I'm not deeply familiar with the abstractions for SQLAlchemy and Flask-SQLAlchemy. Even after a couple hours of probing, I haven't been able to make sense of where to inject my edits.

• Where should models.py be implemented? There's no real description of how to set up a sandman2 project when the CTL alone isn't enough.
• Do I need to replace sandman2ctl with a tool that uses user_model when calling get_app?
• Is there a good way to programmatically populate the unicode method across multiple classes? (e.g. use the value of the primary key as the default)

Thanks!

Authentication is no longer available

I noticed that Sandman2 does not allow to import auth as Sandman1 did.
Was it your intention to remove authentication support?

Including relational data?

I was messing around with PostgREST before this and they have the concept of resource embedding to pull in related data. Is there anything similar here? It also seems like https://github.com/pyeve/eve-sqlalchemy supports this.

With sandman(1), it seems like the docs suggest that there should be links - altho there is a field named links, it's not clear how that relates to items:

With sandman2, I don't even see a link:

Schema constructed with a simple many-to-many relationship:

users_items_assoc = Table('users_items_assoc', Base.metadata,
                          Column('users_items_assoc_id', Integer, primary_key=True),
                          Column('user_id', Integer, ForeignKey('users.id')),
                          Column('item_id', Integer, ForeignKey('items.id'))
                          )

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    nickname = Column(String)

    items = relationship('Item', back_populates='users', secondary=users_items_assoc)

    def __repr__(self):
        return f'{self.name}'


class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    description = Column(String)

    users = relationship('User', back_populates='items', secondary=users_items_assoc)
    # TODO: add image, upc, and so on

    def __repr__(self):
        return f'{self.name}'

sqlite db displays on {} on http://0.0.0.0:5000/

Following the instructions on the main githiub page I attempt to connect to a small db that I have started creating. However I am receiving only when I open the localhost

{}

I am in the same directory and connect to the db race.db you can see here in this structure, I connect no errors just am not receiving the expected output.

sayth@linux-2sg8:~/xml/XML_race_dict> ll
total 104
-rw-r--r-- 1 sayth users 86434 Mar  6 13:33 mycsv.csv
-rw-r--r-- 1 sayth users     0 Mar  6 13:38 race
-rw-r--r-- 1 sayth users  8192 Mar  6 13:33 race.db
-rw-r--r-- 1 sayth users  4228 Mar  6 13:33 xml_race.py
sayth@linux-2sg8:~/xml/XML_race_dict> sandman2ctl sqlite+pysqlite:///race.db
 * Running on http://0.0.0.0:5000/ (Press CTRL+C to quit)
127.0.0.1 - - [06/Mar/2017 13:40:28] "GET /race HTTP/1.1" 404 -
127.0.0.1 - - [06/Mar/2017 13:40:33] "GET / HTTP/1.1" 200 -

Not sure what would help so to show db has valid data I pulled it console using sql2csv.

sayth@linux-2sg8:~/xml/XML_race_dict> sql2csv --db "sqlite:///race.db" --query "select * from race"
R_Number,R_KEY,R_NAME,R_AGE,R_DIST,R_CLASS,M_ID
1,227392,BENCHMARK 85 HCP,3U,2000,BM85,46295
2,227393,TAB HIGHWAY PLATE,3U,1600,CL3,46295
3,227394,SWEET EMBRACE STAKES,2,1200,~,46295
4,227395,SKYLINE STAKES,2,1200,~,46295
5,227396,LIVERPOOL CITY CUP,3U,1300,~,46295
6,227397,SURROUND STAKES,3,1400,~,46295
7,227398,CHIPPING NORTON STAKES,3U,1600,~,46295
8,227399,GUY WALTER STAKES,4U,1400,~,46295
9,227400,BENCHMARK 85 HCP,3U,1200,BM85,46295
sayth@linux-2sg8:~/xml/XML_race_dict> 

standalone license file?

most projects have a LICENSE file in their root folder. Sandman 1 had this. Is the absence here intentional? If so, the reason for this should be noted in README.md. I'm genuinely interested -- Thanks!

Paging does not work with MSSQL

http://localhost:5000/bigtable/?page=1 works
http://localhost:5000/bigtable/?page=2 gives 500 Internal Server Error

....
sql/base.py", line 1169, in visit_select
raise exc.CompileError('MSSQL requires an order_by when '
CompileError: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause

I am using sandman2 v1.0.6 and Python 2.7.12 on Linux
Connection string starts with mssql+pymssql://

Document further query parameters

Thanks for making such great code. I've got an instance running and am trying to document the ways that I can query the API.

So far, I've found:

• ?page=2
• ?[columnheader]=[value]
• ?[columnheader1]=[value1]&[columnheader3]=[value4] (returns results that have both value1 and value2)
• ?[columnheader1]=[value1]&[columnheader1]=[value2] (returns results that have value1 OR value2)

I've been trying to figure these but haven't had any luck:

• set number of resources per page (e.g. ?limit=30)
• search (e.g. ?q=value5)
• sort (e.g. ?sort=-[columnhearder2])
• exclude (e.g. ?[columnheader1]=[value1]&[columnheader3]=-[value4]) (return all entries that have value1 in columnheader1 but exclude those that have value4 in columnheader3).
• return only certain fields (e.g. _?field=columnheader2)

I've looked pretty hard but sorry if I missed these being explained elsewhere. Thanks for any help with this.

Including views along with tables

How can Sandman2 include both views and tables in the API?

-- can views be auto-discovered the same way tables are?

-- is there an example of a user-defined model for a view?

does update_version.sh update setup.py?

setup.py has a plain text version string which I don't think will be updated by update_version.sh. It is very different in sandman2 vs sandman(1).

in the update script I wonder if the -r in the line does this somehow:
python setup.py sdist bdist_wheel upload -r pypi
but the pypi page lists the version in setup.py so I'm not sure.

This update script looks very important and convenient! A line or two of documentation on what it does would help a lot.

Counter-intuitive naming

Hi, sorry for the brevity - just something I noticed that irked me:

error_message = is_valid_method(self.__model__, resource)

I would expect a functioned named is_valid_method(...) to return True if the endpoint usage was valid. Instead, if it returns an error_message (which evaluates to True), that indicates it wasn't a valid usage.

Maybe a name like validation_errors or validate_method would be more intuitive.

Secret key not being set; can't create new records in admin

See stacktrace:

127.0.0.1 - - [30/Jul/2016 20:10:12] "GET /admin/proposals/new/?url=%2Fadmin%2Fproposals%2F HTTP/1.1" 200 -
[2016-07-30 20:10:38,291] ERROR in app: Exception on /admin/proposals/new/ [POST]
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask_admin/contrib/sqla/view.py", line 1026, in create_model
    self.session.commit()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 811, in commit
    self.transaction.commit()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 398, in commit
    self._prepare_impl()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 378, in _prepare_impl
    self.session.flush()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2065, in flush
    self._flush(objects)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2183, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2147, in _flush
    flush_context.execute()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 386, in execute
    rec.execute(self)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 545, in execute
    uow
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 176, in save_obj
    mapper, table, insert)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 812, in _emit_insert_statements
    execute(statement, params)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 947, in execute
    return meth(self, multiparams, params)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 262, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1040, in _execute_clauseelement
    if not self.schema_for_object.is_default else None
  File "<string>", line 1, in <lambda>
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 433, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 439, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 421, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 208, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 231, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 1927, in visit_insert
    self, insert_stmt, crud.ISINSERT, **kw)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/crud.py", line 56, in _setup_crud_params
    return _get_crud_params(compiler, stmt, **kw)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/crud.py", line 135, in _get_crud_params
    _col_bind_name, check_columns, values, kw)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/crud.py", line 280, in _scan_cols
    compiler, stmt, c, values, kw)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/crud.py", line 380, in _append_param_insert_pk_returning
    _raise_pk_with_no_anticipated_value(c)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/crud.py", line 681, in _raise_pk_with_no_anticipated_value
    raise exc.CompileError(msg)
sqlalchemy.exc.CompileError: Column 'proposals.id' is marked as a member of the primary key for table 'proposals', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed.  Primary key columns typically may not store NULL.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask_admin/base.py", line 69, in inner
    return self._run_view(f, *args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask_admin/base.py", line 368, in _run_view
    return fn(self, *args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask_admin/model/base.py", line 1920, in create_view
    model = self.create_model(form)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask_admin/contrib/sqla/view.py", line 1029, in create_model
    flash(gettext('Failed to create record. %(error)s', error=str(ex)), 'error')
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask/helpers.py", line 386, in flash
    session['_flashes'] = flashes
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/werkzeug/local.py", line 346, in __setitem__
    self._get_current_object()[key] = value
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask/sessions.py", line 126, in _fail
    raise RuntimeError('The session is unavailable because no secret '
RuntimeError: The session is unavailable because no secret key was set.  Set the secret_key on the application to something unique and secret.

Not sure if the secret key wasn't set because of sqlalchemy's CompileError or what, but it's unclear how I can get around this currently.

Automapper gives errors on database unless prepare() is configured differently

When running sandman2ctl, I got the following errors:

sqlalchemy.exc.ArgumentError: WARNING: when configuring property 'coding_system' on Mapper|category|category, column 'coding_system' conflicts with property '<RelationshipProperty at 0x2c49c987908; coding_system>'.

Which I could fix, but then I also got (and fixed) this:

sqlalchemy.exc.ArgumentError: Error creating backref 'organisation_collection' on relationship 'organisation.product_collection': property of that name exists on mapper 'Mapper|product|product'

While SQLAlchemy's automapper is probably capable of mapping any type of schema, it seems that some weird edge cases can cause issues when only using AutomapModel's default naming conventions.

For our Postgresql database it seems there were issues when:

• a table contains two columns with foreign keys that both reference the same table
• a table contains a foreign key to a table and also has a column with the same name as the referenced table

To get it working in our case I had to change app._reflect_all() to use the following:

    # http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html#sqlalchemy.ext.automap.name_for_scalar_relationship
    def prepend_name(base, local_cls, referred_cls, constraint):
        return 'tbl_' + referred_cls.__name__.lower()
    # https://stackoverflow.com/questions/37797140/sqlalchemy-automap-backref-errors
    def _name_for_collection_relationship(base, local_cls, referred_cls, constraint):
        if constraint.name:
            return constraint.name.lower() + '_' + referred_cls.__name__.lower()
        # if this didn't work, revert to the default behavior
        return name_for_collection_relationship(base, local_cls, referred_cls, constraint)

    AutomapModel.prepare(  # pylint:disable=maybe-no-member
        db.engine, reflect=True, schema=schema,
        name_for_scalar_relationship=prepend_name,
        name_for_collection_relationship=_name_for_collection_relationship)

This does have some side effects such as a "Tbl" prefix for foreign key columns in the admin interface, but at least it works.

Hopefully there's a cleaner way to handle this use case automatically, but even an option with some configuration through the CLI would be helpful.

Does `sandman2` require pytest?

I found interesting requirements during

$ pip install sandman2
...
Collecting pytest>=2.4 (from pytest-flask>=0.4.0->sandman2)
  Downloading pytest-3.3.1-py2.py3-none-any.whl (184kB)****

IMHO: pytest is development requirement. Is it possible to remove it?

Versioning & Anaconda

Would it be possible to label the versions in git? This would really help in creating a nice build for conda package manager, which integrates quite well with labels.

While I'm on that topic: would you be open to a PR that includes a conda recipe? This would allow someone to easily package up this library into an Anaconda package, which adds some meta-data on-top of what's already in setup.py?

It consists of a single file (meta.yaml) in the root.

500 Inernal Server Error while inserting into a table via admin interface

Hi,

I'm using sandman2 with PostgreSQL.
Every time inserting data into a table using admin interface I'm receiving this page:

Internal Server Error

The server encountered an internal error and was unable to complete your request. Either the server is overloaded or there is an error in the application.

However, table becomes updated and selects in psql shows new inserted data.

Question: How to integrate Sandman2 with Flask?

I would like to use Sandman2's features in my Flask application, instead of using it as a standalone application. How to integrate it with Flask?

sqlalchemy_utils.PasswordType makes JSONEncoder's serialization barf

Here's my stacktrace:

  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/flask/json.py", line 83, in default
    return _json.JSONEncoder.default(self, o)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/json/encoder.py", line 173, in default
    raise TypeError(repr(o) + " is not JSON serializable")
TypeError: b'$2a$12$0rN/M0JPI3ChHNlxBnhoqeNyaC95otDUbflNsjY5O9XvEAlLiUETi' is not JSON serializable

You just might wanna make the api machinery a little more robust, to handle bytes serialization.

Out of the box, sandman2 should have sane defaults for serializing common sqlalchemy types

Trying to get json from sandman2, and the automap table has a date-time column, this causes jsonify to blow up as it doesn't know how to handle datetime.

Sandman2 should define some default json mappings for common sqlalchemy types.

How would one add authentication to sandman2 (either basic or oauth) (feature request)

How would you add authentication to sandman2?
I only want to allow one particular user with a token or huge password to be able to update or access the database.

---TODDL

layout not found

I am getting this error on sandman2ctl with postgresql

jinja2.exceptions.TemplateNotFound: layout.html

sdist published on pypi lacks template files

The current sdist distribution on pypi (0.0.6) has a bug where it lacks the template files needed for the admin interface. It was built without the necessary "setuptools-git" package installed on the machine that built it.

Until a new build is released, a workaround for end-users is:

pip install setuptools-git
git clone https://github.com/jeffknupp/sandman2.git
cd sandman2
python setup.py sdist
cd dist
pip install --force sandman2-0.0.6.tar.gz

Admin interface not working

It seems the built-in admin interface is not working with current versions of Flask-Admin. My REST endpoints are working fine (cool, many thanks for that!!!), but when I call /admin, I get:

[2016-11-09 12:46:30,369] ERROR in app: Exception on /admin/ [GET]
Traceback (most recent call last):
File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1988, in wsgi_app
response = self.full_dispatch_request()
File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1641, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1544, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/usr/local/lib/python3.5/dist-packages/flask/_compat.py", line 33, in reraise
raise value
File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1639, in full_dispatch_request
rv = self.dispatch_request()
File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1625, in dispatch_request
return self.view_functionsrule.endpoint
File "/usr/local/lib/python3.5/dist-packages/flask_admin/base.py", line 69, in inner
return self._run_view(f, *args, **kwargs)
File "/usr/local/lib/python3.5/dist-packages/flask_admin/base.py", line 368, in _run_view
return fn(self, *args, **kwargs)
File "/usr/local/lib/python3.5/dist-packages/flask_admin/base.py", line 452, in index
return self.render(self._template)
File "/usr/local/lib/python3.5/dist-packages/flask_admin/base.py", line 308, in render
return render_template(template, **kwargs)
File "/usr/local/lib/python3.5/dist-packages/flask/templating.py", line 134, in render_template
context, ctx.app)
File "/usr/local/lib/python3.5/dist-packages/flask/templating.py", line 116, in _render
rv = template.render(context)
File "/usr/local/lib/python3.5/dist-packages/jinja2/environment.py", line 989, in render
return self.environment.handle_exception(exc_info, True)
File "/usr/local/lib/python3.5/dist-packages/jinja2/environment.py", line 754, in handle_exception
reraise(exc_type, exc_value, tb)
File "/usr/local/lib/python3.5/dist-packages/jinja2/_compat.py", line 37, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.5/dist-packages/flask_admin/templates/bootstrap3/admin/index.html", line 1, in top-level template code
{% extends 'admin/master.html' %}
File "/usr/local/lib/python3.5/dist-packages/flask_admin/templates/bootstrap3/admin/master.html", line 1, in top-level template code
{% extends admin_base_template %}
File "/usr/local/lib/python3.5/dist-packages/flask/templating.py", line 57, in get_source
return self._get_source_fast(environment, template)
File "/usr/local/lib/python3.5/dist-packages/flask/templating.py", line 85, in _get_source_fast
raise TemplateNotFound(template)
jinja2.exceptions.TemplateNotFound: layout.html
127.0.0.1 - - [09/Nov/2016 12:46:30] "GET /admin/ HTTP/1.1" 500 -

I also tried using virtualenv to install sandman, but no success.

Here is what I got installed on my system:

ls /usr/local/lib/python3.5/dist-packages/ | grep -e ".[0-9]."
click-6.6-py3.5.egg-info
configparser-3.5.0-py3.5.egg-info
configparser-3.5.0-py3.5-nspkg.pth
Flask-0.11.1.dist-info
Flask_Admin-1.4.2-py3.5.egg-info
Flask_GraphQL-1.3.0-py3.5.egg-info
Flask_HTTPAuth-3.2.1-py3.5.egg-info
Flask_SQLAlchemy-2.1-py3.5.egg-info
graphene-1.0.2-py3.5.egg-info
graphene_sqlalchemy-1.0-py3.5.egg-info
graphql_core-1.0-py3.5.egg-info
graphql_relay-0.4.4-py3.5.egg-info
itsdangerous-0.24-py3.5.egg-info
jinja2
Jinja2-2.8.dist-info
MarkupSafe-0.23-py3.5.egg-info
mysql_connector-2.1.4-py3.5.egg-info
pip-9.0.1.dist-info
promise-1.0.0-py3.5.egg-info
py-1.4.31.dist-info
pytest-3.0.3.dist-info
pytest_flask-0.10.0.dist-info
sandman2
sandman2-1.0.4-py3.5.egg-info
setuptools-18.1-py3.5.egg
singledispatch-3.4.0.3.dist-info
SQLAlchemy-1.1.0b3-py3.5.egg-info
typing-3.5.2.2-py3.5.egg-info
virtualenv-15.0.3.dist-info
Werkzeug-0.11.11.dist-info
WTForms-2.1-py3.5.egg-info

How can I fix that?

Any support for mongodb in future?

Missing "admin interface awesomesauce screenshot" in the readme

See title.

broken image

The awesomesauce screenshots aren't quite doing it:

• https://github.com/jeffknupp/sandman2#admin-interface
• https://github.com/jeffknupp/sandman#behind-the-scenes

sqlacodegen

Any experience with the above library, in preference to Automap? It would allow, at least, a bit more customisation - plus no start-up processing cost for reflection of the db. Any tips on how the above can be used with sandman2 today, would be appreciated.

Doesn't support binary types in db

If table has binary column type then it raises typerror exception when tried serialize to json

Flask-admin version

We seem to be using an old flask-admin version, is this intentional? I am finding the admin page looks very strange, and wondering if I am using a vastly newer version - with regressions.

Jeff, what are you on?

Non-int primary key

Any tips on how to implement allowing the above? Currently it's hard-coded to int, looking for an easy way to reflect this from the metadata.

Composite primary keys

As far as I can tell there is no support for these (multiple columns in the primary key). @jeffknupp can you outline your view on this - are they supported, to what extent, do you think they should be etc.?

Can Not Connect To Sql server

Hey , i am not bale to connect to Sql server database , i am new to python . I am getting this error

File "pymssql.pyx", line 639, in pymssql.connect (pymssql.c:10246)
sqlalchemy.exc.InterfaceError: (pymssql.InterfaceError) Connection to the database failed for an unknown reason.

I am using this commmand to connect to sql server
sandmanctl mssql+pymssql://username:password@servername/database

Unable to run sandman2ctl

Getting the following error:

@ubuntu:~$ sandman2ctl
Traceback (most recent call last):
File "/usr/local/bin/sandman2ctl", line 9, in
load_entry_point('sandman2==0.0.3', 'console_scripts', 'sandman2ctl')()
File "/usr/local/lib/python2.7/dist-packages/setuptools-1.1-py2.7.egg/pkg_resources.py", line 357, in load_entry_point
return get_distribution(dist).load_entry_point(group, name)
File "/usr/local/lib/python2.7/dist-packages/setuptools-1.1-py2.7.egg/pkg_resources.py", line 2394, in load_entry_point
return ep.load()
File "/usr/local/lib/python2.7/dist-packages/setuptools-1.1-py2.7.egg/pkg_resources.py", line 2108, in load
entry = import(self.module_name, globals(),globals(), ['name'])
ImportError: No module named sandman2ctl

Here after the "sandman2ctl" installed script:

!/usr/bin/python

EASY-INSTALL-ENTRY-SCRIPT: 'sandman2==0.0.3','console_scripts','sandman2ctl'

requires = 'sandman2==0.0.3'
import sys
from pkg_resources import load_entry_point

if name == 'main':
sys.exit(
load_entry_point('sandman2==0.0.3', 'console_scripts', 'sandman2ctl')()
)

Best regards.

maturity stage of sandman2

Hi there,

What is the current maturity level of sandman2? Can it be used in production or should be better to use sandman1?

AttributeError: Invalid query produces an exception

Consider a following query:

 GET /resource/?day=6

If the column day exists, everything goes fine.
But if I try with non-existent column:

GET /resource/?f=6
AttributeError: type object 'Resource' has no attribute 'f'

It blows an exception to the client.

display primary keys by default - option to display primary keys

Sandman2 by default hides primary keys.
I think it should be default show all data and give the user an option to hide the primary keys in admin.
the --show-pk flags seem to be no longer available in Sandman2

No tables read from sqlite3 file

Hello,

On some sqlite3 tables, it seems sandman2 is unable to discover the names of the tables, and presents empty list on the admin page (and returns 'not found' for all tables).

Example:

$ echo "create table foo(a integer); insert into foo values (1),(2),(3);" | sqlite3  bar.sqlite3
$ echo "select * from foo;" | sqlite3 bar.sqlite3 
1
2
3
$ sandman2ctl sqlite+pysqlite:///bar.sqlite3

Visiting "localhost:5000/admin" shows no tables at all.
No errors are displayed on the terminal, either.

Any ideas how to solve this?

The installed versions are:

$ pip freeze | grep -Ei 'sandman|sql|flask'
Flask==0.11.1
Flask-Admin==1.4.2
Flask-HTTPAuth==3.1.2
Flask-SQLAlchemy==2.1
SQLAlchemy==1.0.14
pysqlite==2.6.3
sandman2==0.0.7

Thanks!

More REST query patterns

Just wondering what people's thoughts are on implementing more complex patterns for reading (joins and filters), along the lines of things like:

• joins: /table1/id1/table2 - returns all the table2's related to table1 key id1
• filters: /table1?id=id1 - alternative to /table1/id1
• sort: /table1?_orderby=field1,field2
• pagination: /table1?_size=100&page=1

(http://sailsjs.org/documentation/reference/blueprint-api for some ideas)

At some point it is easier to parse the URL, rather than prepare every conceivable pattern. Thoughts?

Sandman2 does not show endpoints on root

When going to root / sandman 2 does not who the endpoints and meta links. I get a 404 error.
The index.html template appears to be missing, perhaps also the route.

running sandman2 on virtualenv on ubuntu

when I run on sqlite db file I get this error fro admin page:

Traceback (most recent call last):
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/app.py", line 1836, in call
return self.wsgi_app(environ, start_response)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/app.py", line 1820, in wsgi_app
response = self.make_response(self.handle_exception(e))
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/app.py", line 1403, in handle_exception
reraise(exc_type, exc_value, tb)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/app.py", line 1817, in wsgi_app
response = self.full_dispatch_request()
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/app.py", line 1477, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/app.py", line 1381, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/app.py", line 1475, in full_dispatch_request
rv = self.dispatch_request()
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/app.py", line 1461, in dispatch_request
return self.view_functionsrule.endpoint
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask_admin/base.py", line 68, in inner
return self._run_view(f, _args, *_kwargs)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask_admin/base.py", line 359, in _run_view
return fn(self, _args, *_kwargs)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask_admin/base.py", line 443, in index
return self.render(self._template)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask_admin/base.py", line 299, in render
return render_template(template, **kwargs)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/templating.py", line 128, in render_template
context, ctx.app)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/templating.py", line 110, in _render
rv = template.render(context)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/jinja2/environment.py", line 989, in render
return self.environment.handle_exception(exc_info, True)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/jinja2/environment.py", line 754, in handle_exception
reraise(exc_type, exc_value, tb)
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask_admin/templates/bootstrap2/admin/index.html", line 1, in top-level template code
{% extends 'admin/master.html' %}
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask_admin/templates/bootstrap2/admin/master.html", line 1, in top-level template code
{% extends admin_base_template %}
File "/home/vagrant/sandman2/venv/lib/python2.7/site-packages/flask/templating.py", line 64, in get_source
raise TemplateNotFound(template)
TemplateNotFound: layout.html

running multiple sandman2 apps under a single wsgi

Hi Jeff. We have many legacy databases we are trying to do some data extraction on. We initially set up our approach using a single db/wsgi.py which does something like:

import os
import sys

app_dir = os.path.abspath(os.path.dirname(__file__))
project_dir = os.path.abspath(os.path.join(app_dir, '..' ))

activate_this = "%s/_env/bin/activate_this.py" % app_dir
execfile(activate_this, dict(__file__=activate_this))
sys.path.insert(0, project_dir)

from werkzeug.wsgi import DispatcherMiddleware

from foo import app as foo_app
from foo.services.goop import app as goop _db
from foo.services.blah import app as blah_db

application = DispatcherMiddleware(foo_app, {
    '/goop ': goop_db,
    '/blah ': blah_db
})

in foo/services/goop.py and foo/services/blah.py we have

app = sandman2.get_app(
    driver_uri, #driver_uri unique to each file
    read_only=True
)

where the idea is we can hit paths like:

• /foo/ -> general stuff for our project
• /foo/goop/admin/ -> sandman2 admin for goop database
• /foo/blah/admin/ -> sandman2 admin for blah database

The problem is we see the table for both databases in both admin panels! And we get a lot of errors like:
SAWarning: This declarative base already contains a class with the same class name and module name...

The gist appears to be that there can only be one call to sandman2.get_app per wsgi as there is some class level state being set/stored and thus duplicating the information across the admin pages.

Thoughts? Is there just a better way we are missing or does it boil down to a separate wsgi per database?

Thanks!
Thatcher

How do I setup sandman for read-only access?

Could you post in the readme an example for that? I think this is a very common use case...

Missing example

Hello,

Sandman2 is a great piece of software for those with little knowledge in programming like myself. However, it's sometime necessary to change the default behavior of the software. Sandman1 documentation came with some examples for that. Is there something as good as that for sandman2 ?

Thank you very much

how to install it without pip

how to install it on hosts where the flying dependency https://pypi.python.org/simple/Flask-HTTPAuth/ ( when executing python setup.py install ) can't be accessed due to network administration ?

thanks

Example of user-defined models

Is there an example of user-defined models that can be used in place of auto-discovery?

Auto-discovery is wonderful, but in cases where column names, formats etc. aren't as clean as you'd like for your API, it may be necessary to adjust those items via models instead.

Is there a way to call a stored procedure in database?

I 've searched the documentation, but have not find an API for that.

ways to create new routes

Dear Developers/Moderators,

I want to create multiple routes(which will filter information from a table or will join two tables). Is there a way specified for such requirement.

For example: I have a table score(id is primary key)

id,name,marks
1,Ajay,100
2,Jay,99
3,Ram,90
4,Jay,55

currently
the route is /score/1 or /score/2 to get each of the marks

I want to run something like

/my-score?name=Jay to respond with both rows matching the result or
my-score?marks=100 to respond row where marks=100

Is there a specified way by which this can be achieved?

SQLAlchemy hard dependency vs cx_oracle

Hello

SQLAlchemy required is SQLAlchemy==1.1.0b3

Any version prior to 1.1.1 is not compatible with "latest" cx_oracle - see https://bitbucket.org/zzzeek/sqlalchemy/issues/3997/twophase-is-an-invalid-keyword

Would it be possible to upgrade the required version of SQLAlchemy ?

FYI I forced sqlalchemy-1.2.12 and so far I didn't encounter issues.

Different Schema?

@jeffknupp - Is it possible to use sandman2 with a schema other than public?

Add repository topic "automatic-api"

Could you add the topic automatic-api to your repository? Software that automatically exposes APIs to databases isn't well-cataloged. (There didn't even seem to be a list on GitHub, so I started one.) It will be easier to discover if there is a standard GitHub topic, and automatic-api seems as good a candidate as any. Three projects already use it.