How to use execute_values() function In SQL

The execute_values() function from the psycopg2 library is used to get the postgres table of the given data frame.

Syntax:

psycopg2.extras.execute_values(cur, sql, argslist, template=None, page_size=100, fetch=False)

Parameters:

cur – the cursor that will be used to run the query.
sql – the query that will be run. A single percent s placeholder must be present, which will be substituted by a VALUES list.
argslist — a list of series or dictionaries containing the query’s arguments. The template must be followed in terms of font and content.
template – the snippet that will be merged into each item in the argslist to form the query
page size – the maximum amount of argslist items that each statement can have.
fetch – it’s similar to fetchall. the values can be “True” or “False”

Example:

In this example, the same CSV file is used in this method. code begins with importing packages, then we form a custom function execute_values, where the given dataframe, connection, and table name are given as arguments. The dataframe rows and values are updated into the PostgreSQL table using the execute_values() method. The defined method contains an exception handling block, if there’s no exception “execute_values() done” is printed.

Python3

# import packages 
import psycopg2 
import psycopg2.extras as extras 
import pandas as pd 
  
  
def execute_values(conn, df, table): 
  
    tuples = [tuple(x) for x in df.to_numpy()] 
  
    cols = ','.join(list(df.columns)) 
  
    # SQL query to execute 
    query = "INSERT INTO %s(%s) VALUES %%s" % (table, cols) 
    cursor = conn.cursor() 
    try: 
        extras.execute_values(cursor, query, tuples) 
        conn.commit() 
    except (Exception, psycopg2.DatabaseError) as error: 
        print("Error: %s" % error) 
        conn.rollback() 
        cursor.close() 
        return 1
    print("execute_values() done") 
    cursor.close() 
  
  
# establishing connection 
conn = psycopg2.connect( 
    database="Airlines_Database", 
    user='postgres', 
    password='sherlockedisi', 
    host='127.0.0.1', 
    port='5432'
) 
sql = '''CREATE TABLE airlines_final1(id int ,day 
char(20) ,airline char(20),destination char(20));'''
  
# creating a cursor 
cursor = conn.cursor() 
cursor.execute(sql) 
data = pd.read_csv("airlines_final.csv") 
  
data = data[["id", "day", "airline", "destination"]] 
  
# using the function defined 
execute_values(conn, data, 'airlines_final1') 

Output:

execute_values() done

How to write Pandas DataFrame to PostgreSQL table?

In this article, we will be looking at some methods to write Pandas dataframes to PostgreSQL tables in the Python.

Tags:

#Python pandas-dataFrame #Python PostgreSQL #Python-pandas #Python #python

Method 1: Using to_sql() function

How to use execute_values() function In SQL

Python3

How to write Pandas DataFrame to PostgreSQL table?

Similar Reads

Contact Us