Documentation

Intended Audience: Web Developers
Programming Language: Python 3.6+,
Frameworks: Flask, Django,
Database: Postgres, MySQL

One of the main design goals of pythonGrid is to make creating datagrid easy for Python web apps, which means easy to install, minimum coding, and good flexibility for customization.

If you are new to Python web development, consider starting with Flask, a micro MVC framework that is easy to learn, and the excellent Flask Mega Tutorial by Miguel Grinberg.

You will need either Postgres or MySQL database, both can be easily set up in a local development environment.

If the local install is not your thing, Docker has you covered.

• Install Docker for Postgres (Tutorial)
• Install Docker for MySQL

Let’s start!

Continue reading →

To install pythonGrid, a couple of quick start options are available:

• Download the latest release (manual install) OR
• Clone the repo (recommended):

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

Folder Structure

Within the download you will see something like this:

├── LICENSE
├── README.md
├── app
│   ├── __init__.py
│   ├── data.py
│   ├── grid.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

Continue reading →

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

For Postgres, PgAdmin is an excellent GUI client. For MySQL, you cannot go wrong with the hand dandy MySQL Workbench.

Steps to create the sample database:

1. Create a new database named sampledb
2. Run the sample SQL script for the database of your choice.

Continue reading →

First of all, if you don’t have Python installed on your computer, download and install it 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.
>>>

Continue reading →

Next, you need to install Flask framework (other frameworks will be supported in the future). You got two options.

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 a different copy of Python without worrying about system permissions.

The following command will create 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:

Continue reading →

pythonGrid uses SQLAlchemy and a few other important modules. They are listed in requirements.txt.

pip install -r requirements.txt

Continue reading →

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 hostname.

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'

Continue reading →

In Flask, it uses view functions to handle 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 require 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 the sample database sampledb.

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

PythonGrid initializer shown above requires 3 parameters, in the order from left to right:

1. A simple SQL SELECT statement. Always include the primary key as one of the columns if not using the wildcard start(*).
2. The name of the database table primary key. 
3. Name of the database table used in the SQL statement.

The view function passes 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 required only 1 parameter, which should be the SAME sql select statement used for PythonGrid.

data = PythonGridDbData('SELECT * FROM orders')

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.   frequently)

Congrats! You just made your first grid.

Continue reading →

• Parameter(s):
  • caption: the text for the grid caption
• Description:
  • When set, this method displays text above the column header as the datagrid caption. When this function is not called, pythonGrid sets the table name as the default caption. For example, the caption will be displayed as “Orders” when the table name is “Orders”.
• Remark:
  • When the caption is set to empty string, e.g. “”, the space used for displaying caption will be hidden, leaves only the column header, grid, and the footer. To display the caption without any text uses ” ” (without the quote).

Example

grid.set_caption('Orders Table')

Continue reading →

• Parameter(s):
  • col_name: Colume name
  • new_title: Display title in grid column header
• Description:
  • Change data grid column header text from the default data field name to a more user-friendly name
• Remark:
  • The default column header text is the database table data field name

Example:

grid.set_col_title('orderNumber', 'Order #')

Continue reading →

• Parameter(s):
  • col_name: Column name
  • new_title: Display title in the grid column header
• Description:
  • Change data grid column header text from the default data field name to a more user-friendly name
• Remark:
  • The default column header text is the database table data field name

Example:

grid.set_col_hidden(['customerNumber, logTime, shippedDate, requiredDate'])

Continue reading →

• Parameter(s):
  • pagesize: An integer value indicates the number of results displaying in a page in the datagrid
• Description:
  • This function changes the default pagination of the datagrid. The default is 20.
• Remark:
  • Pagination is disabled when set_scroll() is set to true. See set_scroll() for more information.

Example

grid.set_pagesize(50)

Continue reading →

• Parameter(s):
  • width: Datagrid width in pixel
  • height: Datagrid height. The default is 100% which expanses grid vertically to the entire browser height in pixel
  • shrinkToFit: true or false. If set to false, a horizontal bar will appear when the total width of the grid is wider than the dimension width. The default is true.
• Description:
  • Set the overall height and width
• Remark:
  • It is recommended to set shrinkToFit to false when there are a large number of columns to display.

Example:

grid.set_dimension(800, 400)

Continue reading →

• Parameter(s):
  • can_search: boolean. When set to true, the search icon displays in the footer, and the integrated search is toggled when the icon is clicked.
• Description:
  • The integrated search provides an intuitive interface for searching data within the datagrid. The user can search one or multiple fields. No additional programming is required. The datagrid is automatically refreshed and repopulated with results returned from the db.
• Remark:
  • The integrated search is toggle off by default. Click on the Search button in the footer to toggle on the integrated search.

Exmaple:

grid.enable_search(True)

Continue reading →

• Parameters:
  • has_rownumbers: boolean value indicating whether row number is displayed.
• Description:
  • Display row numbers before each row.

Example:

grid.enable_rownumbers(True)

Continue reading →

• Parameters:
  • page_count: boolean: true or false.
• Description:
  • Enable page count in the pager. Default to true.
• Remark:
  • Set to false to optimize performance for very large (e.g. millions) database table.

Example:

grid.enable_pagecount(True)

Continue reading →

• Parameters:
  • col_name: column name
  • align: alignment direction: “left”, “center”, or “right”.
• Description:
  • The horizontal alignment of text in a column. The default alignment value is left.

Example:

grid.set_col_align('status', 'center')

Continue reading →

• Parameters:
  • col_name: column name
  • width: width
• Description:
  • Specify column width in pixels. The width should be an integer value.
• Remark:
  •  pythonGrid will automatically resize the column width based on its content during page load. Users can also double-click a column divider to resize a column.

Example:

grid.set_col_width('comments', 600)

Continue reading →

• Parameter(s):
  • type: The only export type is CSV at the moment.
• Description:
  • The export icon appears on the left side of the footer when enabled.

Example:

grid.enable_export()

The export requires PythonGridDbExport class that must be initiated inside export() function in route or controller.

@app.route('/export', methods=['GET', 'POST'])
def export(): 
    exp = PythonGridDbExport('SELECT * FROM orders') 
    return exp.export()

Continue reading →