pycr / pythongrid

Easy datagrid for Python Flask web framework

pythonGrid

Quick Demo

pythonGrid is an easy way to create a fully working datagrid for Python web framework that connects to a relation database such as Postgres or MySql/MariaDB database. Currently only Flask framework is supported. More frameworks support are coming.

Requirements

• Python 3.6+
• Flask (Django, Web2Py, CherryPy and other major web frameworks support are coming)
• SQLAlchemy

Quick Start

A couple quick start options are available:

• Download the latest release
• Clone the repo (recommended):

git clone https://github.com/pycr/pythongrid.git

Files included

Within the download you will see something like this:

├── LICENSE
├── README.md
├── app
│   ├── __init__.py
│   ├── data.py
│   ├── grid.py
│   ├── export.py
│   ├── routes.py
│   ├── static
│   └── templates
│       ├── 404.html
│       ├── base.html
│       ├── grid.html
│       └── index.html
├── sample
│   ├── sampledb_postgres.sql
│   ├── sampledb_mysql.sql
├── config.py
├── index.py
└── requirements.txt

pythonGrid current has two main files in grid.py and data.py in app folder.

• grid.py is the main Python class that is responsible for creating the datagrid table. It relies on jqGrid, a popular jQuery datagrid plugin, to render grids in the browser.

• data.py is a Python class that returns the data via AJAX to populate the grid from a database.

• static contains all of the client side Javascript and CSS files used for rendering.

Creating the Database

Find the sample database in folder sampledb. Using your favorite MySQL os Postgres client (more database supports are coming).

1. Create a new database named sampledb
2. Run the sample sql script.

Install Python

First of all, if you don't have Python installed on your computer, download and install from the Python official website now.

To make sure your Python is functional, type python3 in a terminal window, or just python if that does not work. Here is what you should expect to see:

Python 3.6.3 (v3.6.3:2c5fed86e0, Oct  3 2017, 00:32:08)
[GCC 4.2.1 (Apple Inc. build 5666) (dot 3)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>>

Next, you need to install Flask framework. You got two options.

Install Flask Framework via Virtual Environment

It is highly recommended to use Python virtual environment. Basically, a Python virtual environment is a self-contained separate copy of Python installation. Different applications can then use different virtual environments with different copy of Python without worrying about system permissions.

The following command will creates a virtual environment named venv stored in a directory also named venv.

python3 -m venv venv

Activate the new virtual environment:

source venv/bin/activate

Now the terminal prompt is modified to include the name of the activated virtual environment

(venv) $ _

With a new virtual environment created and activated, finally let's install dependents:

Install Dependents

pythonGrid uses SQLAlchemy to support different types of database.

pip install -r requirements.txt

Configuration

Find file config.py, and set the database connection properties according to your environment. The demo uses MySQL database.

You can also use a socket to connect to your database without specifying a database host name.

PYTHONGRID_DB_HOSTNAME = 'mysqldatabase.example.com'
PYTHONGRID_DB_NAME = 'sampledb'
PYTHONGRID_DB_USERNAME = 'root'
PYTHONGRID_DB_PASSWORD = 'root'
PYTHONGRID_DB_TYPE = 'mysql+pymysql'

For Postgres set database type to postgres+psycopg2

PYTHONGRID_DB_TYPE = 'postgres+psycopg2'

Initialize Grid

Flask uses view functions to handle for the application routes. View functions are mapped to one or more route URLs so that Flask knows what logic to execute when a client requests a given URL such as "https://example.com/grid".

We have two view functions that need initialization.

index()

The file routes.py contains our def index() view functions associate with root URL /. This means that when a web browser requests the URL, Flask is going to invoke this function and pass the return value of it back to the browser as a response.

Inside the function, it creates a new instance of the PythonGrid class and assigns this object to the local variable grid. Note orders is a table from sample database sampledb.

grid = PythonGrid('SELECT * FROM orders', 'orderNumber', 'orders')

PythonGrid initializer shown above requires 3 parameters:

1. A simple SQL SELECT statement
2. The database table primary key
3. The database table name

The view function pass the grid object into the rendered template from grid.html template.

return render_template('grid.html', title='GRID', grid=grid)

data()

Next, we need the data for the grid (thus the datagrid :-)

In the next view function data(), we create a new instance for PythonGridDbData class that is responsible for retrieve data from the database.

It has requires only 1 parameter, which should be the SAME Sql Select statement used for PythonGrid.

data = PythonGridDbData('SELECT * FROM orders')

Hello, Grid

At this point, we can run our program with the command below

flask run

It should give you a beautiful datagrid with data come from the table orders.

The pythonGrid supports

• Sort
• Row number
• Toolbar search
• Pagination
• Page size
• Column title
• Hide columns
• Datagrid dimension
• Column width
• Column text alignment
• CSV export

Run Demo

Troubleshoot

1. Flask Debugger Toolbar is enabled by default in demo. Disable it by commenting the following in app/init.py

toolbar = DebugToolbarExtension(app)

2. When launching application, you might get a server error

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.

This is due to Flask session caching. Simply refresh the browser should fix the issue.

pythongrid's Issues

AttributeError: 'Engine' object has no attribute 'open'

Hi,
I has cloned your repo and followed the Readme for the setup. When I run the application, loading the page the grid never appear and in the console I can see this error:

Exception ignored in: <function PythonGrid.__del__ at 0x74b72268>
Traceback (most recent call last):
  File "/home/pi/WebServer/FlaskWebProject1/grid.py", line 177, in __del__
    if self.db.open:
AttributeError: 'Engine' object has no attribute 'open'

here the code at line 177:

def __del__(self):
        if self.db.open:
            self.db.close()

The object self.db is assigned at line 28:

self.db = create_engine(app.config['PYTHONGRID_DB_TYPE']+'://'+app.config['PYTHONGRID_DB_USERNAME']+':'+app.config['PYTHONGRID_DB_PASSWORD']+'@'+app.config['PYTHONGRID_DB_HOSTNAME']+'/'+app.config['PYTHONGRID_DB_NAME']+'?unix_socket='+app.config['PYTHONGRID_DB_SOCKET'], encoding=app.config['PYTHONGRID_DB_CHARSET'])

Looking on google I saw that sqlalchemy.create_engine don't have the attribute open.
Any tips on how to solve this?

Thank you for your time.

edit.py

Hello, thx for your nice effort.

I was wondering if you made any progress on the edit.py module?

still alive?

I see all the demos and domain are gone.. did this die out? Getting ready for a project and hate to go down the wrong path.

Seems like a wonderful thing. Will be a shame.

CRUD includes UPDATE, CREATE/INSERT and DELETE

While you did a nice job in your demo showing how to view, search and order a database table in a generic fashion, you did not include the ability to update records and fields, create and insert new rows and delete them. Looking thru the code it doesn't seem that you have included any support for those capabilities. Are you working on a version that has that functionality or should we implement that for ourselves.

With those capabilities and few more adjustments this would probably make a popular python package...

Does it support virtual rows

If i load there 1 milliopn of rows doesthis makes not visible rows virtual to safe the time on demand to see other rows when needed?