Using Pandas Agent with OpenAI and Langchain for reading CSV

 import pandas as pd

from langchain.chat_models import ChatOpenAI

from langchain.agents import create_pandas_dataframe_agent, AgentType

#Load the .env file

from dotenv import load_dotenv,find_dotenv

load_dotenv(find_dotenv())

customersDf = pd.read_csv('Customers.csv')

salesDf = pd.read_csv('sales.csv')

chat = ChatOpenAI(model_name="gpt-3.5-turbo",temperature=0.0)

#Initialize pandas dataframe agent

agent = create_pandas_dataframe_agent(agent_type=AgentType.OPENAI_FUNCTIONS,

                                      llm=chat,

                                      df=[customersDf,salesDf],

                                      verbose=True)

agent.run("What is the name of the first customer?  using tool python_repl_ast")

agent.run("Has JON placed any orders?")

Read from Postgres DB using langchain and OpenAI

 from langchain import OpenAI, SQLDatabase

from langchain.chat_models import ChatOpenAI

from langchain_experimental.sql import SQLDatabaseChain, SQLDatabaseSequentialChain

import psycopg2

import os

from langchain import PromptTemplate

#Load the .env file

from dotenv import load_dotenv,find_dotenv

load_dotenv(find_dotenv())

# reference https://github.com/bhattbhavesh91/langchain-crashcourse/blob/main/sql-agent-notebook.ipynb

username = "postgres"

password = "postgres"

host = "localhost"

port = "5432"

mydatabase = "adventureworks"

pg_uri = f"postgresql+psycopg2://{username}:{password}@{host}:{port}/{mydatabase}"

input_db = SQLDatabase.from_uri(pg_uri)

# llm = ChatOpenAI(temperature=0, openai_api_key=API_KEY, model_name='gpt-3.5-turbo')

chat = ChatOpenAI(model_name="gpt-3.5-turbo",temperature=0.0, openai_api_key=os.getenv("OPENAI_API_KEY"))

db_agent = SQLDatabaseChain(llm = chat,

                            database = input_db,

                            verbose=True)

db_agent

temmplate = """You are generating queries and results from Postgres Database. The humanresources schema contains employee table. {question}"""

prompt = PromptTemplate(

    input_variables=["question"],

    template = temmplate,

)

db_agent(prompt.format(question ="How many employees are there?"))

# Another method using sql chain:

PROMPT = """

Given an input question, first create a syntactically correct postgresql query to run,  

then look at the results of the query and return the answer.  

The question: {question}

"""

db_chain = SQLDatabaseSequentialChain(llm=llm, database=db_agent, verbose=True, top_k=3)

question = "what is the average rent price in chicago in nov 2022 according to redfin?"

# use db_chain.run(question) instead if you don't have a prompt

db_chain.run(PROMPT.format(question=question))

#https://medium.com/dataherald/how-to-connect-llm-to-sql-database-with-langchain-sqlagent-48635fddaa74

#https://coinsbench.com/chat-with-your-databases-using-langchain-bb7d31ed2e76

Microsoft Autogen with a single agent

Prerequisites:

 pip install pyautogen

Code:

import autogen

config_list =[

    {

        'model': 'gpt-3.5-turbo',

        'api_key': 'XXXXXXXXXXXXX'

    }

]

llm_config = {

    "request_timeout":600,

    "seed": 42,

    "config_list": config_list,

    "temperature": 0

}

assistant = autogen.AssistantAgent(name="assistant",llm_config=llm_config)

user_proxy = autogen.UserProxyAgent(

    name="user_proxy",

    human_input_mode="TERMINATE",

    max_consecutive_auto_reply=10,

    is_termination_msg=lambda x: x.get("content", "").rstrip().endswith("TERMINATE"),

    code_execution_config={"work_dir": "coding"},

    llm_config=llm_config,

    system_message="""Reply TERMINATE if the task has been solved at full satisfaction.

Otherwise, reply CONTINUE, or the reason why the task is not solved yet."""

)

task = """"

Write a python code to find leap year

"""

user_proxy.initiate_chat(assistant,message=task)

LLMs vs chat models in LangChain

LLMs are models string as input and returns a string.

chat models are models which takes a list of messages as input and returns a message.

You will find this generic answer on langchain website.

Let's see what it actually means in code.

In this first example, both of these behave in the exact same way:

LLM:

Chat Model:

Now to explain how chat models are different, look at the below example:

ImportError: No module named openai, name 'ChatOpenAI' is not defined

 There could be many reasons for these error messages related to ChatOpenAI and openai.

If the issue is related to python or pip it will get resolved by following this:

python -m pip uninstall openai

python -m pip install --upgrade pip

python -m pip install openai

Sometimes openai might require a higher version of python then what is installed on your system.

You can check the required version as follows: 

pip show openai

How to Set and Get Environment Variables in Python

Environment variables are a crucial aspect of software development and system configuration. They provide a way to store configuration settings, API keys, and other sensitive information outside of your codebase, making it easier to manage and secure your applications. Python offers built-in modules to interact with these environment variables, allowing you to set and retrieve values as needed. In this blog post, we'll explore how to set and get environment variables in Python.

What Are Environment Variables?

Environment variables are dynamic values that can affect the way running processes behave on a computer. They are part of the environment in which a process runs and can be used to pass configuration information to applications and system utilities.

Common use cases for environment variables include:

Storing sensitive information like API keys and database credentials.

Configuring application settings such as server addresses and log levels.

Specifying system-level variables like the PATH, which tells the system where to find executable files.

Setting Environment Variables

To set an environment variable in Python, you can use the os module. Here's how to do it:

import os

# Set an environment variable

os.environ['MY_VARIABLE'] = 'my_value'

In this example, we set an environment variable named MY_VARIABLE with the value 'my_value'. Once set, this variable will be available to other processes and scripts that run in the same environment.

It's important to note that setting environment variables in a running Python process will only affect that process and its child processes. To make environment variables persist across sessions, you may need to configure them at the system level, depending on your operating system.

Getting Environment Variables

Retrieving the value of an environment variable in Python is just as simple as setting one:

import os

# Get the value of an environment variable

value = os.environ.get('MY_VARIABLE')

if value:

    print(f'The value of MY_VARIABLE is: {value}')

else:

    print('MY_VARIABLE is not set.')

In this code snippet, we use os.environ.get('MY_VARIABLE') to retrieve the value of the environment variable named MY_VARIABLE. If the variable is set, we print its value; otherwise, we indicate that it's not set.

Handling Missing Environment Variables

It's essential to handle cases where an expected environment variable is not set. Failing to do so can result in unexpected errors and application crashes. Using os.environ.get('MY_VARIABLE') allows you to check if the variable exists and provide a default value or handle the absence gracefully.

import os

# Get the value of an environment variable with a default value

value = os.environ.get('MY_VARIABLE', 'default_value')

print(f'The value of MY_VARIABLE is: {value}')

n this example, if MY_VARIABLE is not set, the code will use 'default_value' as a fallback.

How to create a python virtual environment using venv

 Open Powershell:

Navigate to the directory where you want to create a python environment and then type:

python -m venv mynewenv

This command will create a folder named mynewenv in that directory.

Navigate inside this directory by

cd mynewenv

Now to activate the environment type in:

.\Scripts\activate

openai.error.RateLimitError: You exceeded your current quota, please check your plan and billing details.

Error Message: openai.error.RateLimitError: You exceeded your current quota, please check your plan and billing details.

Steps to resolve:

You need to upgrade to a paid plan. Set up a paid account, add a credit or debit card. Add some credit, you can you Pay as you Go method.

After 10-15 minutes generate a new API key and use that in the code

modularize your PySpark code that includes SQL queries based on createOrReplaceTempView

 To modularize your PySpark code that includes SQL queries based on createOrReplaceTempView, you can follow a few steps:

Create separate Python modules for different components of your code.

Define functions in each module to encapsulate the logic related to specific SQL queries and transformations.

Import the necessary modules and call the functions in your main code.

In this example, the create_temp_views function in queries.py encapsulates the logic to read CSV files, create temporary views using createOrReplaceTempView, and perform a join operation. By separating the SQL-related code into a separate module, you can easily reuse and maintain your queries.

Note that you need to replace the file paths and SQL queries in the example with your specific requirements. Additionally, you might need to adjust the imports and customize the functions according to your actual codebase.

Create a module named queries.py to define functions for your SQL queries:

# queries.py

from pyspark.sql import SparkSession

def create_temp_views(spark):

    # Register temp views using createOrReplaceTempView

    df1 = spark.read.csv("data/file1.csv", header=True)

    df1.createOrReplaceTempView("table1")

    df2 = spark.read.csv("data/file2.csv", header=True)

    df2.createOrReplaceTempView("table2")

    df3 = spark.sql("SELECT * FROM table1 JOIN table2 ON table1.id = table2.id")

    df3.createOrReplaceTempView("table3")

    # Return the SparkSession for further use, if needed

    return spark

Create your main script and import the queries module to call the functions:

# main.py

from pyspark.sql import SparkSession

import queries

# Create a SparkSession

spark = SparkSession.builder.appName("Modularized PySpark").getOrCreate()

# Call the create_temp_views function from queries module

spark = queries.create_temp_views(spark)

# Use the registered temp views for further processing

result = spark.sql("SELECT * FROM table3")

result.show()

# Stop the SparkSession

spark.stop()

Best practices for organizing PySpark code with SQL usage

Example illustrating the best practices for organizing PySpark code with extensive SQL usage:

In this example, the SQL code is stored in separate SQL files within the sql/ directory. The data_preprocessing.sql and feature_engineering.sql files encapsulate the respective SQL operations.

The feature_engineering.sql file imports common functions from common_functions.sql within the utils/ subdirectory. This approach promotes code reusability and separates the common functions from the main SQL code.

The main.py script reads the SQL files and executes the SQL code using spark.sql(). By following this approach, you maintain a clean separation between the PySpark code and SQL logic, making it easier to manage and maintain your project.

Directory Structure:

project/

├── main.py

└── sql/

    ├── data_preprocessing.sql

    ├── feature_engineering.sql

    └── utils/

        ├── common_functions.sql

        └── feature_functions.sql

data_preprocessing.sql:

-- data_preprocessing.sql

-- Define the SQL operations for data preprocessing

CREATE OR REPLACE TEMPORARY VIEW cleaned_data AS

SELECT

    column1,

    column2,

    -- Perform data preprocessing operations here

FROM

    input_data;

feature_engineering.sql:

-- feature_engineering.sql

-- Import common functions

:load sql/utils/common_functions.sql

-- Define the SQL operations for feature engineering

CREATE OR REPLACE TEMPORARY VIEW transformed_data AS

SELECT

    column1,

    column2,

    -- Perform feature engineering operations here

    -- Example using a custom feature function

    feature_function(column1) AS new_feature

FROM

    cleaned_data;

common_functions.sql (in sql/utils/):

-- common_functions.sql

-- Define common SQL functions used across multiple SQL files

CREATE OR REPLACE FUNCTION feature_function(column)

RETURNS feature

BEGIN

    -- Implement the feature function logic here

    -- ...

    RETURN feature_value;

END;

main.py:

from pyspark.sql import SparkSession

# Create SparkSession

spark = SparkSession.builder.getOrCreate()

# Load input data

input_data = spark.read.csv("data.csv", header=True, inferSchema=True)

input_data.createOrReplaceTempView("input_data")

# Load and execute SQL scripts

with open("sql/data_preprocessing.sql") as f:

    data_preprocessing_sql = f.read()

spark.sql(data_preprocessing_sql)

with open("sql/feature_engineering.sql") as f:

    feature_engineering_sql = f.read()

spark.sql(feature_engineering_sql)

# Retrieve the transformed data

transformed_data = spark.table("transformed_data")

# Perform further operations on transformed_data or train models, etc.