You should have created a database when you installed PostGIS. For the examples mentioned as follows, we will use this database.

If you did not create a database during the installation of PostGIS, you can do so using your terminal (command prompt in Windows) and the commands as follows:

createdb -U postgres pythonspatial
 psql -U postgres -d pythonspatial -c "CREATE EXTENSION postgis;" 

You may need to modify your path. On Windows, the command to do so is shown as follows:

set PATH=%PATH%;C:\Program Files\PostgreSQL\10\bin

To connect to your database, use the following code:

import psycopg2

connection = psycopg2.connect(database="pythonspatial",user="postgres", password="postgres")

cursor = connection.cursor()

cursor.execute("CREATE TABLE art_pieces (id SERIAL PRIMARY KEY, code VARCHAR(255), location GEOMETRY)")

connection.commit()

The code mentioned earlier starts by importing psycopg2. It then makes a connection by using the connect() function and passing the parameters for the database name, the user, and the password. It then creates a cursor which allows you to communicate with the database. You can use the execute() method of the cursor to create the table passing SQL statements as strings.

The code executes an SQL command which creates a table named art_pieces with an id of type SERIAL and makes it a PRIMARY KEY, code as type VARCHAR and a length of 255, and the location as the GEOMETRY type. The SERIAL PRIMARY KEY tells PostgreSQL that we want an auto-incremented unique identifier. You can also use the BIGSERIAL type. The other type that is different is the location of the GEOMETRY type. This is the column that will hold the geo portion of our records.

Lastly, you commit() to make sure the changes are saved. You can also close() when you are finished, but we will continue further.