Creating Random PostgreSQL Tables with Inserted Data

This function connects to a PostgreSQL database, creates a random table, and inserts random data. The number of columns and their types are specified by the caller.

Technology Stack : psycopg2

Code Type : Database operation

Code Difficulty : Intermediate

                
                    
import psycopg2
import random

def create_random_table_and_insert_data(db_name, user, password, host, port, table_name, columns):
    """
    This function creates a random table with given columns and inserts random data into it.
    """
    # Connect to the PostgreSQL database
    conn = psycopg2.connect(dbname=db_name, user=user, password=password, host=host, port=port)
    cursor = conn.cursor()

    # Create a random table
    create_table_query = f"CREATE TABLE {table_name} ({columns})"
    cursor.execute(create_table_query)

    # Insert random data into the table
    for _ in range(5):  # Inserting 5 rows of random data
        insert_query = f"INSERT INTO {table_name} VALUES ({', '.join(['%s'] * len(columns)))"
        random_data = [random.randint(1, 100) for _ in range(len(columns))]
        cursor.execute(insert_query, random_data)

    # Commit the changes and close the connection
    conn.commit()
    cursor.close()
    conn.close()

# Example usage
create_random_table_and_insert_data('mydatabase', 'user', 'password', 'localhost', '5432', 'random_table', 'int, varchar, float')