To create our database and the tables that will hold the application data, we will use the SQLAlchemy and GeoAlchemy2 classes and methods. The following code is in the script called Chapter11_0.py. This code will allow us to connect to a PostgreSQL data server to create a database and data tables that will form the backend of the web application. Import these libraries:

from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database,
                             drop_database
from sqlalchemy import Column, Integer, String, ForeignKey, Float
from sqlalchemy.orm import relationship
from geoalchemy2 import Geometry
from sqlalchemy.ext.declarative import declarative_base

Connecting the database server to both generate and query the data tables is achieved using the create_engine function and the connection string format, demonstrated as follows:

conn_string = '{DBtype}://{user}:{pword}@{instancehost}:{port}/{database}'
engine = create_engine(conn_string, echo=True)

Connection strings are used throughout all Python database modules. They usually include a specification of the relational database management system (RDBMS) type, the username, the password, the instance host (that is the IP address or localhost for a database server installed on the local machine), an optional port number, and a database name. For example, a connection string might look like this:

connstring = 'postgresql://postgres:bond007@localhost:5432/chapter11'
engine = create_engine(connstring, echo=True)

In this example, postgresql is the RDBMS type, postgres is the user, bond007 is the password, localhost is the instance host, 5432 is the port (and the default port for PostgreSQL installations; if the port wasn't changed on installation, it can be left out of the connection string), and chapter11 is the name of the database. The echo=True statement is used to generate logs of the database interactions to the standard output window. To turn these messages off, change the echo value to False.

For our database, we can use the following format. Replace {user} and {pword} (including the brackets) with your PostgreSQL server username and password:

conn_string ='postgresql://{user}:{pword}@localhost:5432/chapter11'
engine = create_engine(conn_string, echo=True)

If the connection string is valid, the create_engine function will return an object to the engine variable, which will be used to perform database interactions throughout the script.

The code in the comment (#drop_database(engine.url)) is commented out but can be uncommented if the database needs to be dropped and then recreated using the script. It calls the SQLAlchemy create_engine's url property, which is a reference to the connection string:

# Uncomment the line below if you need to recreate the database.
#drop_database(engine.url)

The database and the data tables it will contain is created within an if not conditional that relies on the database_exists function. If the conditional returns True (indicating that the database does not exist), the engine variable is passed to the create_database function:

# Check to ensure that the database doesn't exist
# If it doesn't, create it and generate the PostGIS extention and tables
if not database_exists(engine.url):
    create_database(engine.url)