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

From previous articles you have learnt 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 psycopg2 library. Below example source code is based on above two article’s example.

use configuration file saved connection data to connect to postgresql database server

1. Create Table.

  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 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 above code successfully, you can find the two tables in pgAdmin3 gui.
    create two table in postgresql database server

2. Insert Data To Postgresql Table.

2.1 Insert One Row To Postgresql Table.

psycopg2 connection cursor’s execute method will execute one sql statement include 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 above code successfully, you can see the inserted row in pgAdmin3.
    insert one row in postgresql table

2.2 Insert Multiple Rows To Postgresql Table.

psycopg2 connection cursor’s executemany method can execute insert sql command to insert a list of rows into postgresql table. The executemany 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 above method with 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 department table.
    multiple rows has been inserted into postgresql department table
READ :   How To Successfully Install Django On Windows

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 above method in 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 select sql statement. And the cursor has three method for you to fetch rows back, they are:

  1. fetchone() : fetch only one row back. You need 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 with 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 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 above method in 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 above code will get 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 Reply

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.