Create a table in PostgreSQL using Python and Psycopg2

Overview:

A database table in PostgreSQL is created using the SQL Statement CREATE TABLE.
CREATE TABLE is a one of the essential DDL statements supported by PostgreSQL.
A PostgreSQL user, can create a new database in PostgreSQL server and add tables into it or can create tables in an already existing PostgreSQL database.
Inside a database, tables are stored under a schema. When a schema name is not specified tables are created inside a default schema named public.
To create a table from a Python Program, Psycopg2 - the Python client library for PostgreSQL can be used.

The implementation of Psycopg2 adheres to the Python Database Access Specification.
In a Python Program, using the Psycopg2, a developer can create a database connection, a database cursor and call the necessary execute() and fetch() methods to perform any of the required SQL operations on a PostgreSQL server.
The CREATE TABLE statement is as well, executed through a database cursor object obtained using the Psycopg2 module.
The Python example program below, creates a table in PostgreSQL server and prints the list of tables under the default schema, in this case a schema named test,not the standard default schema.

# import the PostgreSQL adapter for Python

import psycopg2

# Connect to the PostgreSQL database server

postgresConnection = psycopg2.connect("dbname=test user=test password='test'")

# Get cursor object from the database connection

cursor = postgresConnection.cursor()

name_Table = "news_stories"

# Create table statement

sqlCreateTable = "create table "+name_Table+" (id bigint, title varchar(128), summary varchar(256), story text);"

# Create a table in PostgreSQL database

cursor.execute(sqlCreateTable)

postgresConnection.commit()

# Get the updated list of tables

sqlGetTableList = "SELECT table_schema,table_name FROM information_schema.tables where table_schema='test' ORDER BY table_schema,table_name ;"

#sqlGetTableList = "\dt"

# Retrieve all the rows from the cursor

cursor.execute(sqlGetTableList)

tables = cursor.fetchall()

# Print the names of the tables

for table in tables:

print(table)

List of tables under current schema

('test', 'news_stories')

('test', 'news_stories_archive')