第一次对话后的记忆:
Human: 我姐姐明天要过生日，我需要一束生日花束。
AI: 哦，你姐姐明天要过生日，那太棒了！我可以帮你推荐一些生日花束，你想要什么样的？我知道有很多种，比如玫瑰、康乃馨、郁金香等等。
 
第二次对话后的记忆:
Human: 我姐姐明天要过生日，我需要一束生日花束。
AI: 哦，你姐姐明天要过生日，那太棒了！我可以帮你推荐一些生日花束，你想要什么样的？我知道有很多种，比如玫瑰、康乃馨、郁金香等等。
Human: 她喜欢粉色玫瑰，颜色是粉色的。
AI: 好的，那我可以推荐一束粉色玫瑰的生日花束给你。你想要多少朵？我可以帮你定制一束，比如说十朵、二十朵或者更多？
第三次对话后的记忆：
Human: 我姐姐明天要过生日，我需要一束生日花束。
AI: 哦，你姐姐明天要过生日，那太棒了！我可以帮你推荐一些生日花束，你想要什么样的？我知道有很多种，比如玫瑰、康乃馨、郁金香等等。
Human: 她喜欢粉色玫瑰，颜色是粉色的。
AI: 好的，那我可以推荐一束粉色玫瑰的生日花束给你，你想要多少朵？
Human: 我又来了，还记得我昨天为什么要来买花吗？
AI: 是的，我记得你昨天来买花是因为你姐姐明天要过生日，你想要买一束粉色玫瑰的生日花束给她。

You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question. [背景介绍]
Unless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per SQLite. You can order the results to return the most informative data in the database. 【规则/格式描述】
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use date('now') function to get the current date, if the question involves "today". 【循循善诱】

Use the following format: 【给出信息】

Question: Question here
SQLQuery: SQL Query to run
SQLResult: Result of the SQLQuery
Answer: Final answer here

Only use the following tables:

CREATE TABLE "Flowers" (
        "ID" INTEGER, 
        "Name" TEXT NOT NULL, 
        "Type" TEXT NOT NULL, 
        "Source" TEXT NOT NULL, 
        "PurchasePrice" REAL, 
        "SalePrice" REAL, 
        "StockQuantity" INTEGER, 
        "SoldQuantity" INTEGER, 
        "ExpiryDate" DATE, 
        "Description" TEXT, 
        "EntryDate" DATE DEFAULT CURRENT_DATE, 
        PRIMARY KEY ("ID")
)
【给出表数据样例】
/*
3 rows from Flowers table:
ID      Name    Type    Source  PurchasePrice   SalePrice       StockQuantity   SoldQuantity    ExpiryDate      Description     EntryDate
1       Rose    Flower  France  1.2     2.5     100     10      2023-12-31      A beautiful red rose    2024-01-17
2       Tulip   Flower  Netherlands     0.8     2.0     150     25      2023-12-31      A colorful tulip        2024-01-17
3       Lily    Flower  China   1.5     3.0     80      5       2023-12-31      An elegant white lily   2024-01-17
*/

Question: 有多少种不同的鲜花？
SQLQuery:

SELECT COUNT(DISTINCT \"Name\") AS \"Number of Flowers\" FROM \"Flowers\"

You are a SQLite expert. Given an input question, first create a syntactically correct SQLite query to run, then look at the results of the query and return the answer to the input question.
Unless the user specifies in the question a specific number of examples to obtain, query for at most 5 results using the LIMIT clause as per SQLite. You can order the results to return the most informative data in the database.
Never query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in double quotes (") to denote them as delimited identifiers.
Pay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.
Pay attention to use date('now') function to get the current date, if the question involves "today".

Use the following format:

Question: Question here
SQLQuery: SQL Query to run
SQLResult: Result of the SQLQuery
Answer: Final answer here

Only use the following tables:

CREATE TABLE "Flowers" (
        "ID" INTEGER, 
        "Name" TEXT NOT NULL, 
        "Type" TEXT NOT NULL, 
        "Source" TEXT NOT NULL, 
        "PurchasePrice" REAL, 
        "SalePrice" REAL, 
        "StockQuantity" INTEGER, 
        "SoldQuantity" INTEGER, 
        "ExpiryDate" DATE, 
        "Description" TEXT, 
        "EntryDate" DATE DEFAULT CURRENT_DATE, 
        PRIMARY KEY ("ID")
)

/*
3 rows from Flowers table:
ID      Name    Type    Source  PurchasePrice   SalePrice       StockQuantity   SoldQuantity    ExpiryDate      Description     EntryDate
1       Rose    Flower  France  1.2     2.5     100     10      2023-12-31      A beautiful red rose    2024-01-17
2       Tulip   Flower  Netherlands     0.8     2.0     150     25      2023-12-31      A colorful tulip        2024-01-17
3       Lily    Flower  China   1.5     3.0     80      5       2023-12-31      An elegant white lily   2024-01-17
*/

Question: 有多少种不同的鲜花？
SQLQuery:SELECT COUNT(DISTINCT "Name") AS "Number of Flowers" FROM "Flowers"
SQLResult: [(5,)]
Answer:

有5种不同的鲜花。

from langchain.utilities import SQLDatabase
from langchain.chat_models import ChatOpenAI
from langchain.agents import create_sql_agent
from langchain.agents.agent_toolkits import SQLDatabaseToolkit
from langchain.agents.agent_types import AgentType

# 连接到FlowerShop数据库
db = SQLDatabase.from_uri("sqlite:///FlowerShop.db")
llm = ChatOpenAI(model_name='gpt-3.5-turbo',
                 temperature=0,
                 openai_api_base='http://llm.baidu.com',
                 openai_api_key='')

# 创建SQL Agent
agent_executor = create_sql_agent(
    llm=llm,
    toolkit=SQLDatabaseToolkit(db=db, llm=llm),
    verbose=True,
    agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
)

# 使用Agent执行SQL查询
questions = [
    "哪种鲜花的存货数量最少？",
]

for question in questions:
    response = agent_executor.run(question)
    print(response)