curl -s https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sql | sqlite3 Chinook.db

import os from langchain_community.agent_toolkits import create_sql_agentfrom langchain_community.utilities import SQLDatabasefrom langchain_openai import ChatOpenAI os.environ["OPENAI_API_KEY"] = "sk-proj-xxx" if __name__ == "__main__":    db = SQLDatabase.from_uri("sqlite:///Chinook.db")    llm = ChatOpenAI(model="gpt-4o-mini")     agent_executor = create_sql_agent(llm, db=db, agent_type="openai-tools", verbose=True)    r = agent_executor.invoke("How many employees are there?")     print(r) 

{'input': 'How many employees are there?', 'output': 'There are 8 employees in the database.'}

(1, 'Adams', 'Andrew', 'General Manager', None, '1962-02-18 00:00:00', '2002-08-14 00:00:00', '11120 Jasper Ave NW', 'Edmonton', 'AB', 'Canada', 'T5K 2N1', '+1 (780) 428-9482', '+1 (780) 428-3457', 'andrew@chinookcorp.com')
(2, 'Edwards', 'Nancy', 'Sales Manager', 1, '1958-12-08 00:00:00', '2002-05-01 00:00:00', '825 8 Ave SW', 'Calgary', 'AB', 'Canada', 'T2P 2T3', '+1 (403) 262-3443', '+1 (403) 262-3322', 'nancy@chinookcorp.com')
(3, 'Peacock', 'Jane', 'Sales Support Agent', 2, '1973-08-29 00:00:00', '2002-04-01 00:00:00', '1111 6 Ave SW', 'Calgary', 'AB', 'Canada', 'T2P 5M5', '+1 (403) 262-3443', '+1 (403) 262-6712', 'jane@chinookcorp.com')
(4, 'Park', 'Margaret', 'Sales Support Agent', 2, '1947-09-19 00:00:00', '2003-05-03 00:00:00', '683 10 Street SW', 'Calgary', 'AB', 'Canada', 'T2P 5G3', '+1 (403) 263-4423', '+1 (403) 263-4289', 'margaret@chinookcorp.com')
(5, 'Johnson', 'Steve', 'Sales Support Agent', 2, '1965-03-03 00:00:00', '2003-10-17 00:00:00', '7727B 41 Ave', 'Calgary', 'AB', 'Canada', 'T3B 1Y7', '1 (780) 836-9987', '1 (780) 836-9543', 'steve@chinookcorp.com')
(6, 'Mitchell', 'Michael', 'IT Manager', 1, '1973-07-01 00:00:00', '2003-10-17 00:00:00', '5827 Bowness Road NW', 'Calgary', 'AB', 'Canada', 'T3B 0C5', '+1 (403) 246-9887', '+1 (403) 246-9899', 'michael@chinookcorp.com')
(7, 'King', 'Robert', 'IT Staff', 6, '1970-05-29 00:00:00', '2004-01-02 00:00:00', '590 Columbia Boulevard West', 'Lethbridge', 'AB', 'Canada', 'T1K 5N8', '+1 (403) 456-9986', '+1 (403) 456-8485', 'robert@chinookcorp.com')
(8, 'Callahan', 'Laura', 'IT Staff', 6, '1968-01-09 00:00:00', '2004-03-04 00:00:00', '923 7 ST NW', 'Lethbridge', 'AB', 'Canada', 'T1H 1Y8', '+1 (403) 467-3351', '+1 (403) 467-8772', 'laura@chinookcorp.com')

SQL_PREFIX = """You are an agent designed to interact with a SQL database.Given an input question, create a syntactically correct {dialect} query to run, then look at the results of the query and return the answer.Unless the user specifies a specific number of examples they wish to obtain, always limit your query to at most {top_k} results.You can order the results by a relevant column to return the most interesting examples in the database.Never query for all the columns from a specific table, only ask for the relevant columns given the question.You have access to tools for interacting with the database.Only use the below tools. Only use the information returned by the below tools to construct your final answer.You MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again. DO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database. If the question does not seem related to the database, just return "I don't know" as the answer.""" SQL_SUFFIX = """Begin! Question: {input}Thought: I should look at the tables in the database to see what I can query.  Then I should query the schema of the most relevant tables.{agent_scratchpad}""" SQL_FUNCTIONS_SUFFIX = """I should look at the tables in the database to see what I can query.  Then I should query the schema of the most relevant tables."""

"Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track”

Then I should query the schema of the most relevant tables.\nAI: \nTool: Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track

"CREATE TABLE "Employee" (
	"EmployeeId" INTEGER NOT NULL,
	"LastName" NVARCHAR(20) NOT NULL,
	"FirstName" NVARCHAR(20) NOT NULL,
	"Title" NVARCHAR(30),
	"ReportsTo" INTEGER,
	"BirthDate" DATETIME,
	"HireDate" DATETIME,
	"Address" NVARCHAR(70),
	"City" NVARCHAR(40),
	"State" NVARCHAR(40),
	"Country" NVARCHAR(40),
	"PostalCode" NVARCHAR(10),
	"Phone" NVARCHAR(24),
	"Fax" NVARCHAR(24),
	"Email" NVARCHAR(60),
	PRIMARY KEY ("EmployeeId"),
	FOREIGN KEY("ReportsTo") REFERENCES "Employee" ("EmployeeId")
)

/*
3 rows from Employee table:
EmployeeId	LastName	FirstName	Title	ReportsTo	BirthDate	HireDate	Address	City	State	Country	PostalCode	Phone	Fax	Email
1	Adams	Andrew	General Manager	None	1962-02-18 00:00:00	2002-08-14 00:00:00	11120 Jasper Ave NW	Edmonton	AB	Canada	T5K 2N1	+1 (780) 428-9482	+1 (780) 428-3457	andrew@chinookcorp.com
2	Edwards	Nancy	Sales Manager	1	1958-12-08 00:00:00	2002-05-01 00:00:00	825 8 Ave SW	Calgary	AB	Canada	T2P 2T3	+1 (403) 262-3443	+1 (403) 262-3322	nancy@chinookcorp.com
3	Peacock	Jane	Sales Support Agent	2	1973-08-29 00:00:00	2002-04-01 00:00:00	1111 6 Ave SW	Calgary	AB	Canada	T2P 5M5	+1 (403) 262-3443	+1 (403) 262-6712	jane@chinookcorp.com
*/"

System: You are an agent designed to interact with a SQL database.\nGiven an input question, create a syntactically correct sqlite query to run, then look at the results of the query and return the answer.\nUnless the user specifies a specific number of examples they wish to obtain, always limit your query to at most 10 results.\nYou can order the results by a relevant column to return the most interesting examples in the database.\nNever query for all the columns from a specific table, only ask for the relevant columns given the question.\nYou have access to tools for interacting with the database.\nOnly use the below tools. Only use the information returned by the below tools to construct your final answer.\nYou MUST double check your query before executing it. If you get an error while executing a query, rewrite the query and try again.\n\nDO NOT make any DML statements (INSERT, UPDATE, DELETE, DROP etc.) to the database.\n\nIf the question does not seem related to the database, just return \"I don't know\" as the answer.\n\nHuman: How many employees are there?\nAI: I should look at the tables in the database to see what I can query.  Then I should query the schema of the most relevant tables.\nAI: \nTool: Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track\nAI: \nTool: \nCREATE TABLE \"Employee\" (\n\t\"EmployeeId\" INTEGER NOT NULL, \n\t\"LastName\" NVARCHAR(20) NOT NULL, \n\t\"FirstName\" NVARCHAR(20) NOT NULL, \n\t\"Title\" NVARCHAR(30), \n\t\"ReportsTo\" INTEGER, \n\t\"BirthDate\" DATETIME, \n\t\"HireDate\" DATETIME, \n\t\"Address\" NVARCHAR(70), \n\t\"City\" NVARCHAR(40), \n\t\"State\" NVARCHAR(40), \n\t\"Country\" NVARCHAR(40), \n\t\"PostalCode\" NVARCHAR(10), \n\t\"Phone\" NVARCHAR(24), \n\t\"Fax\" NVARCHAR(24), \n\t\"Email\" NVARCHAR(60), \n\tPRIMARY KEY (\"EmployeeId\"), \n\tFOREIGN KEY(\"ReportsTo\") REFERENCES \"Employee\" (\"EmployeeId\")\n)\n\n/*\n3 rows from Employee table:\nEmployeeId\tLastName\tFirstName\tTitle\tReportsTo\tBirthDate\tHireDate\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\n1\tAdams\tAndrew\tGeneral Manager\tNone\t1962-02-18 00:00:00\t2002-08-14 00:00:00\t11120 Jasper Ave NW\tEdmonton\tAB\tCanada\tT5K 2N1\t+1 (780) 428-9482\t+1 (780) 428-3457\tandrew@chinookcorp.com\n2\tEdwards\tNancy\tSales Manager\t1\t1958-12-08 00:00:00\t2002-05-01 00:00:00\t825 8 Ave SW\tCalgary\tAB\tCanada\tT2P 2T3\t+1 (403) 262-3443\t+1 (403) 262-3322\tnancy@chinookcorp.com\n3\tPeacock\tJane\tSales Support Agent\t2\t1973-08-29 00:00:00\t2002-04-01 00:00:00\t1111 6 Ave SW\tCalgary\tAB\tCanada\tT2P 5M5\t+1 (403) 262-3443\t+1 (403) 262-6712\tjane@chinookcorp.com\n*/

You are a {dialect} expert.

Please help to generate a {dialect} query to answer the question. Your response should ONLY be based on the given context and follow the response guidelines and format instructions.

===Tables
{table_schemas)

===Original Query
{original_query}

===Response Guidelines
1. If the provided context is sufficient, please generate a valid query without any explanations for the question. The query should start with a comment containing the question being asked.
2. If the provided context is insufficient, please explain why it can't be generated.
3. Please use the most relevant table(s).
5. Please format the query before responding.
6. Please always respond with a valid well-formed JSON object with the following format

===Response Format
{{
    "query": "A generated SQL query when context is sufficient.",
    "explanation": "An explanation of failing to generate the query."
}}

===Question
{question}

import contextlibimport jsonimport os import openaiimport pydanticimport sqlalchemyimport sqlparsefrom sqlalchemy import ormfrom sqlalchemy.schema import CreateTablefrom sqlparse.tokens import DML, DDL engine = sqlalchemy.create_engine("sqlite:///Chinook.db")Session = orm.sessionmaker(bind=engine) @contextlib.contextmanagerdef session_scope():    session = Session()    try:        yield session        session.commit()    except:        session.rollback()        raise    finally:        session.close() def is_safe_sql(sql_statement: str) -> bool:    # 本番稼働にはさらに厳密なチェックが必要    if sql_statement == "わかりません。":        return False     disallowed_types = (DML, DDL)    parsed = sqlparse.parse(sql_statement)     for statement in parsed:        for token in statement.tokens:            if token.ttype in disallowed_types:                if token.value.upper() in [                    "INSERT",                    "UPDATE",                    "DELETE",                    "CREATE",                    "ALTER",                    "DROP",                    "TRUNCATE",                    "REPLACE",                    "GRANT",                    "REVOKE",                    "MERGE",                    "CALL",                    "EXPLAIN",                    "LOCK",                    "UNLOCK",                ]:                    return False  # 不正なSQL文    return True  # 安全なSQL文 class SQLOutput(pydantic.BaseModel):    sql_statement: str if __name__ == "__main__":    metadata = sqlalchemy.MetaData()    metadata.reflect(bind=engine)    table_names: set[str] = set(metadata.tables.keys())  # テーブル名の一覧     # 各テーブルのCREATE文を生成    create_statements = {}    for table_name, table in metadata.tables.items():        # CreateTableオブジェクトを生成し、SQL文字列を取得        create_statement = str(CreateTable(table).compile(engine))        create_statements[table_name] = create_statement     system_prompt: str = f"""    あなたはSQLデータベースと対話するように設計されたエージェントです。    入力された質問に対して、構文的に正しいSQLiteクエリを作成してください。     データベースに対してDML文(INSERT、UPDATE、DELETE、DROPなど)を作成しないでください。     質問がデータベースと関係なさそうであれば、「わかりません。」を回答として返してください。     このデータベースのテーブルの一覧: {table_names}    各テーブルのスキーマ: {create_statements}    """     user_prompt: str = "従業員は何名いますか？"     os.environ["OPENAI_API_KEY"] = "sk-proj-xxx"    client = openai.OpenAI()    messages = [        {"role": "system", "content": system_prompt},        {"role": "user", "content": user_prompt},    ]    r = client.beta.chat.completions.parse(        model="gpt-4o-mini",        messages=messages,        response_format=SQLOutput,    )     response = json.loads(r.choices[0].message.content)    messages.append({"role": "system", "content": response["sql_statement"]})    print(f"ユーザークエリ: {user_prompt}")    print(f"生成されたSQL文: {response['sql_statement']}")     if not is_safe_sql(response["sql_statement"]):        print("不正なSQL文です。")        exit(1)     with session_scope() as session:        r = session.execute(sqlalchemy.text(response["sql_statement"])).fetchall()        print(f"SQL実行結果: {r}")     analysis_prompt: str = f"""    あなたはユーザーのクエリから、データベースの情報に基づき、適切な情報分析を行い、その報告を行うように設計されたエージェントです。    ユーザーのクエリに対して、データベースの情報を分析し、その結果を報告してください。     質問: {user_prompt}    質問に対するSQLクエリ: {response['sql_statement']}    データベースの情報: {r}    """    messages.append({"role": "user", "content": analysis_prompt})     r = client.beta.chat.completions.parse(        model="gpt-4o-mini",        messages=messages,    )    print(f"分析結果: {r.choices[0].message.content}") 

$ rye run python src/text2sql.pyユーザークエリ: 従業員は何名いますか？生成されたSQL文: SELECT COUNT(*) FROM Employee;SQL実行結果: [(8,)]分析結果: 従業員の人数は8名です。

user_prompt: str = "今日の昼ご飯はなんですか。"

$ rye run python src/text2sql.pyユーザークエリ: 今日の昼ご飯はなんですか。生成されたSQL文: わかりません。不正なSQL文です。