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.

Creating a database table in PostgreSQL using Python and Psycopg2:

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

Example:

# 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)

 

Output:

List of tables under current schema

('test', 'news_stories')

('test', 'news_stories_archive')

 


Copyright 2020 © pythontic.com