How To Create Table And Implement Insert, Update, Delete, Select To Postgresql Table In Python

From previous articles you have learned how to connect to PostgreSQL database server, How To Connect To PostgreSQL Database And Execute SQL Statement Use Psycopg2 In Python, How To Connect PostgreSQL Server Use Configuration File In Python.

This article will tell you how to create tables, and how to implement CRUD ( create, read, update and delete ) actions to the PostgreSQL tables in python source code use the psycopg2 python library. Below example source code is based on the above two article’s examples.

ManagePostgreSQL
   .idea
   venv
     database.ini
     PostgresqLManager.py

1. Create Tables.

  1. Add create_table method in python class PostgresqlManager.py.
    # this method can create multiple tables.    
    def create_table(self, create_table_sql_tuple):
    
        # first get db connection cursor
        self.get_cursor()
    
        # loop in the create table sql command tuple.
        for sql in create_table_sql_tuple:
    
            print(sql)
            # execute the create table sql command.
            self._cursor.execute(sql)
    
        # close the db connection cursor.
        self._cursor.close()
    
        # commit abvoe sql command to postgresql server.
        self._conn.commit()
  2. Invoke the above method to create tables.
    if __name__ == '__main__':
    
        # first create an instance of PostgresqlManager class.
        postgresql_manager = PostgresqlManager()
    
        postgresql_manager.get_connection_by_config('database.ini', 'postgresql_conn_data')
    
        # define create table sql command tuple.
        create_table_sql_tuple = (
    
        '''
        drop table account; 
        
        create table account(
        
            id SERIAL PRIMARY KEY,
            
            user_name VARCHAR(255) NOT NULL,
            
            password  VARCHAR(255) NOT NULL,
            
            email  VARCHAR(255) NOT NULL
        
        )
        ''',
    
        '''
        
        drop table department;
        
        create table department(
        
            id SERIAL PRIMARY KEY,
            
            dept_name VARCHAR(255) NOT NULL,
            
            dept_desc  VARCHAR(255) NOT NULL
        
        )
        
        ''')
    
        # invoke create table method.
        postgresql_manager.create_table(create_table_sql_tuple)
    
        # close the db cursor and connection object.
        postgresql_manager.close_connection()
  3. When you run the above code successfully, you can find the two tables ( account, department ) in the pgAdmin3 GUI manager.

2. Insert Data To Postgresql Table.

2.1 Insert One Row To Postgresql Table.

The python library psycopg2 connection cursor’s execute method will execute one SQL statement include the insert SQL statement.

  1. Add method insert_one_row in PostgresqlManager.py.
    def insert_one_row(self, insert_sql, values_tuple):
    
        self.get_cursor()
    
        # execute insert sql command statement
        self._cursor.execute(insert_sql, values_tuple)
    
        # return the auto generated id back
        account_id = self._cursor.fetchone()[0]
    
        print('The insert row id is ', account_id)
    
        # do not forget commit the insert changes to postgresql db
        self._conn.commit()
    
        # close the cursor object.
        self._cursor.close()
  2. Invoke insert_one_row method.
    if __name__ == '__main__':
    
        # first create an instance of PostgresqlManager class.
        postgresql_manager = PostgresqlManager()
    
        postgresql_manager.get_connection_by_config('database.ini', 'postgresql_conn_data')
    
        # this insert sql statement will insert the values to account table and return the auto generated id value.
        sql = """INSERT INTO account(user_name, password, email)
                     VALUES(%s, %s, %s) RETURNING id;"""
    
        column_values = ('jerry', '888888', '[email protected]')
    
        postgresql_manager.insert_one_row(sql, column_values)
    
        # close the db cursor and connection object.
        postgresql_manager.close_connection()
  3. When you run the above code successfully, you can see the inserted row in the account table in pgAdmin3 GUI manager.
    insert-one-row-in-postgresql-table

2.2 Insert Multiple Rows To Postgresql Table.

The python psycopg2 connection cursor’s executemany method can execute the insert SQL command to insert a list of rows into the PostgreSQL table. The executemany method’s first argument is the SQL statement, the second argument is a list or tuple of row values.

  1. Add insert_multiple_row method in PostgresqlManager.py.
    def insert_multiple_row(self, insert_sql, row_tuple):
    
        self.get_cursor()
    
        # executemany method will execute the insert sql use each element in the row_tuple.
        self._cursor.executemany(insert_sql, row_tuple)
    
        # do not forget commit the insert changes to postgresql db
        self._conn.commit()
    
        # close the cursor object.
        self._cursor.close()
  2. Call the above method with the below code.
    if __name__ == '__main__':
    
        # first create an instance of PostgresqlManager class.
        postgresql_manager = PostgresqlManager()
    
        postgresql_manager.get_connection_by_config('database.ini', 'postgresql_conn_data')
        
        # the insert sql statement.
        sql = """INSERT INTO department(dept_name, dept_desc)
                            VALUES(%s, %s);"""
    
        # the multiple rows data tuple.
        row_tuple = (('Dev', 'Develop department'), ('QA', 'Quality assurance'), ('Market', 'Market analyze'))
    
        postgresql_manager.insert_multiple_row(sql, row_tuple)
    
        # close the db cursor and connection object.
        postgresql_manager.close_connection()
  3. You can see the rows inserted into the department table in pgAdmin3 GUI manager.
    multiple-rows-has-been-inserted-into-postgresql-department-table

3. Update Postgresql Table.

  1. Add update_table method in PostgresqlManager.py.
    def update_table(self, update_sql, update_value_tuple):
    
        self.get_cursor()
    
        # execute update sql command statement
        self._cursor.execute(update_sql, update_value_tuple)
    
        # the execute method will return the udpated rows count number.
        update_row_number = self._cursor.rowcount
    
        print(update_row_number, ' row has been updated. ')
    
        # do not forget commit the insert changes to postgresql db
        self._conn.commit()
    
        # close the cursor object.
        self._cursor.close()
    
  2. Invoke the above method in the main method. Then you will find the data has been updated.
    if __name__ == '__main__':
    
        # first create an instance of PostgresqlManager class.
        postgresql_manager = PostgresqlManager()
    
        postgresql_manager.get_connection_by_config('database.ini', 'postgresql_conn_data')
    
    
        update_sql = ''' update account set password = %s, email = %s where user_name = %s'''
    
        postgresql_manager.update_table(update_sql, ('abcdefg', '[email protected]', 'jerry'))
    
    
        # close the db cursor and connection object.
        postgresql_manager.close_connection()

4. Delete Rows From Postgresql Table.

  1. Add delete_table_rows method in class PostgresqlManager.
    def delete_table_rows(self, delete_sql, delete_value_tuple):
    
        self.get_cursor()
    
        # execute delete sql command statement
        self._cursor.execute(delete_sql, delete_value_tuple)
    
        delete_row_number = self._cursor.rowcount
    
        print(delete_row_number, ' row has been deleted. ')
    
        # do not forget commit the delete changes to postgresql db
        self._conn.commit()
    
        # close the cursor object.
        self._cursor.close()
  2. Invoke delete_table_rows method.
    if __name__ == '__main__':
    
        # first create an instance of PostgresqlManager class.
        postgresql_manager = PostgresqlManager()
    
        # get db connection with provided postgresql database properties.
        postgresql_manager.get_connection('localhost', 5432, 'dev2qa', 'test_user', 'test_user')
    
        delete_sql = '''delete from department where dept_name = %s and dept_desc = %s'''
    
        postgresql_manager.delete_table_rows(delete_sql, ('Market', 'Market analyze'))
    
        # close the db cursor and connection object.
        postgresql_manager.close_connection()

5. Select Rows From Postgresql Table.

You also need to use the psycopg2 connection cursor object’s execute method to run the select SQL statement. And the cursor has three methods for you to fetch rows back, they are:

  1. fetchone() : fetch only one row back. You need to loop the cursor to get all rows.
  2. fetchall() : fetch all rows in the cursor back to a local row list object. This method will be more efficient than fetchone().
  3. fetchmany(size) : fetch specified size rows back.

5.1 fetchone() method example.

  1. Add select_fetch_one method to class PostgresqlManager.
    def select_fetch_one(self, select_sql):
    
        self.get_cursor()
    
        # execute select sql command statement
        self._cursor.execute(select_sql)
    
      
        # get total row number.
        select_row_number = self._cursor.rowcount
    
        print(select_row_number, ' row has been returned. ')
    
    
        start_time = datetime.datetime.now()
    
        print('start_time : ', start_time)
    
        row = self._cursor.fetchone()
    
        while row is not None:
    
            print(row)
    
            row = self._cursor.fetchone()
    
        end_time = datetime.datetime.now()
    
        print('end_time : ', end_time)
    
        delta_time = end_time - start_time
    
        print('delta_time : ', delta_time)
    
        # close the cursor object.
        self._cursor.close()
  2. Invoke select_fetch_one method.
    if __name__ == '__main__':
    
        # first create an instance of PostgresqlManager class.
        postgresql_manager = PostgresqlManager()
    
        postgresql_manager.get_connection_by_config('database.ini', 'postgresql_conn_data')
    
        select_sql = ''' select * from department'''
    
        postgresql_manager.select_fetch_one(select_sql)
    
        # close the db cursor and connection object.
        postgresql_manager.close_connection()
  3. Run above code will get below result. We can see fetch two rows use 0.000041 seconds.
    ******* get postgresql database connection with configuration file ******** 
    
    2  row has been returned. 
    start_time :  2019-05-05 16:36:22.085470
    (1, 'Dev', 'Develop department')
    (2, 'QA', 'Quality assurance')
    end_time :  2019-05-05 16:36:22.085511
    delta_time :  0:00:00.000041
    ******* close postgresql database connection ******** 
    

5.2 fetchall() method example.

  1. Add select_fetch_all method to class PostgresqlManager. This method is similar to the select_fetch_one method.
    def select_fetch_all(self, select_sql):
    
        self.get_cursor()
    
        # execute select sql command statement
        self._cursor.execute(select_sql)
    
        select_row_number = self._cursor.rowcount
    
        print(select_row_number, ' row has been returned. ')
    
        start_time = datetime.datetime.now()
    
        print('start_time : ', start_time)
    
        # call cusor.fetchall() method to return all rows at one time.
        all_rows = self._cursor.fetchall()
    
        for row in all_rows:
            print(row)
    
        end_time = datetime.datetime.now()
    
        print('end_time : ', end_time)
    
        delta_time = end_time - start_time
    
        print('delta_time : ', delta_time)
    
        # close the cursor object.
        self._cursor.close()
  2. Invoke select_fetch_all method.
    if __name__ == '__main__':
    
        # first create an instance of PostgresqlManager class.
        postgresql_manager = PostgresqlManager()
    
        # get db connection with provided postgresql database properties.
        postgresql_manager.get_connection('localhost', 5432, 'dev2qa', 'test_user', 'test_user')
    
        select_sql = ''' select * from department'''
    
        postgresql_manager.select_fetch_all(select_sql)
    
        # close the db cursor and connection object.
        postgresql_manager.close_connection()
  3. Run above code will get below result, you can find that fetchall() method is faster than fetchone() method. Select two rows cost 0.000027 seconds.
    ******* get postgresql database connection with configuration file ******** 
    
    2  row has been returned. 
    start_time :  2019-05-05 16:38:14.421165
    (1, 'Dev', 'Develop department')
    (2, 'QA', 'Quality assurance')
    end_time :  2019-05-05 16:38:14.421192
    delta_time :  0:00:00.000027
    ******* close postgresql database connection ********

5.3 fetchmany() method example.

  1. Add select_fetch_many, call_fetch_many method to class PostgresqlManager.
    def select_fetch_many(self, select_sql):
    
        self.get_cursor()
    
        # execute select sql command statement
        self._cursor.execute(select_sql)
    
        # get total row count.
        select_row_count = self._cursor.rowcount
    
        print(select_row_count, ' row has been returned. ')
    
        start_time = datetime.datetime.now()
    
        print('start_time : ', start_time)
    
        # define current row number.
    
        row_number = 0
    
        # when not reach the lat row then fetch special sized row list.
    
        while row_number < select_row_count:
    
            self.call_fetch_many()
    
            # get current row number.
            row_number = self._cursor.rownumber
    
    
        end_time = datetime.datetime.now()
    
        print('end_time : ', end_time)
    
        delta_time = end_time - start_time
    
        print('delta_time : ', delta_time)
    
        # close the cursor object.
        self._cursor.close()
    
    
    # this method is called in while loop to return diffrent page rows.
    def call_fetch_many(self):
    
        print('start call_fetch_many method. ')
    
        rows = self._cursor.fetchmany(1)
    
        for row in rows:
            print(row)
    
        print('end call_fetch_many method. \n')
  2. Invoke the above method in the main function.
    if __name__ == '__main__':
    
        # first create an instance of PostgresqlManager class.
        postgresql_manager = PostgresqlManager()
    
        postgresql_manager.get_connection_by_config('database.ini', 'postgresql_conn_data')
    
        select_sql = ''' select * from department'''
    
        postgresql_manager.select_fetch_many(select_sql)
    
        # close the db cursor and connection object.
        postgresql_manager.close_connection()
  3. Run the above code will get the below result.
    ******* get postgresql database connection with configuration file ******** 
    
    2  row has been returned. 
    start_time :  2019-05-05 16:57:49.974329
    start call_fetch_many method. 
    (1, 'Dev', 'Develop department')
    end call_fetch_many method. 
    
    start call_fetch_many method. 
    (2, 'QA', 'Quality assurance')
    end call_fetch_many method. 
    
    end_time :  2019-05-05 16:57:49.974389
    delta_time :  0:00:00.000060
    ******* close postgresql database connection ********

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.