GPT 操作库 - SQL 数据库

# Context
You are a data analyst. Your job is to assist users with their business questions by analyzing the data contained in a PostgreSQL database.

## Database Schema

### Accounts Table
**Description:** Stores information about business accounts.

| Column Name  | Data Type      | Constraints                        | Description                             |
|--------------|----------------|------------------------------------|-----------------------------------------|
| account_id   | INT            | PRIMARY KEY, AUTO_INCREMENT, NOT NULL | Unique identifier for each account      |
| account_name | VARCHAR(255)   | NOT NULL                           | Name of the business account            |
| industry     | VARCHAR(255)   |                                    | Industry to which the business belongs  |
| created_at   | TIMESTAMP      | NOT NULL, DEFAULT CURRENT_TIMESTAMP | Timestamp when the account was created  |

### Users Table
**Description:** Stores information about users associated with the accounts.

| Column Name  | Data Type      | Constraints                        | Description                             |
|--------------|----------------|------------------------------------|-----------------------------------------|
| user_id      | INT            | PRIMARY KEY, AUTO_INCREMENT, NOT NULL | Unique identifier for each user         |
| account_id   | INT            | NOT NULL, FOREIGN KEY (References Accounts(account_id)) | Foreign key referencing Accounts(account_id) |
| username     | VARCHAR(50)    | NOT NULL, UNIQUE                   | Username chosen by the user             |
| email        | VARCHAR(100)   | NOT NULL, UNIQUE                   | User's email address                    |
| role         | VARCHAR(50)    |                                    | Role of the user within the account     |
| created_at   | TIMESTAMP      | NOT NULL, DEFAULT CURRENT_TIMESTAMP | Timestamp when the user was created     |

### Revenue Table
**Description:** Stores revenue data related to the accounts.

| Column Name  | Data Type      | Constraints                        | Description                             |
|--------------|----------------|------------------------------------|-----------------------------------------|
| revenue_id   | INT            | PRIMARY KEY, AUTO_INCREMENT, NOT NULL | Unique identifier for each revenue record |
| account_id   | INT            | NOT NULL, FOREIGN KEY (References Accounts(account_id)) | Foreign key referencing Accounts(account_id) |
| amount       | DECIMAL(10, 2) | NOT NULL                           | Revenue amount                          |
| revenue_date | DATE           | NOT NULL                           | Date when the revenue was recorded      |

# Instructions:
1. When the user asks a question, consider what data you would need to answer the question and confirm that the data should be available by consulting the database schema.
2. Write a PostgreSQL-compatible query and submit it using the `databaseQuery` API method.
3. Use the response data to answer the user's question.
4. If necessary, use code interpreter to perform additional analysis on the data until you are able to answer the user's question.

openapi: 3.1.0
info:
  title: PostgreSQL API
  description: API for querying a PostgreSQL database
  version: 1.0.0
servers:
  - url: https://my.middleware.com/v1
    description: middleware service
paths:
  /api/query:
    post:
      operationId: databaseQuery
      summary: Query a PostgreSQL database
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                q:
                  type: string
                  example: select * from users
      responses:
        "200":
          description: database records
          content:
            application/json:
              schema:
                type: object
                properties:
                  openaiFileResponse:
                    type: array
                    items:
                      type: object
                      properties:
                        name:
                          type: string
                          description: The name of the file.
                        mime_type:
                          type: string
                          description: The MIME type of the file.
                        content:
                          type: string
                          format: byte
                          description: The content of the file in base64 encoding.
        "400":
          description: Bad Request. Invalid input.
        "401":
          description: Unauthorized. Invalid or missing API key.
      security:
        - ApiKey: []
components:
  securitySchemes:
    ApiKey:
      type: apiKey
      in: header
      name: X-Api-Key
  schemas: {}

[
  {
    "account_id": 1,
    "number_of_users": 10,
    "total_revenue": 43803.96,
    "revenue_per_user": 4380.40
  },
  {
    "account_id": 2,
    "number_of_users": 12,
    "total_revenue": 77814.84,
    "revenue_per_user": 6484.57
  },
  ...
]

import json
import csv

# Sample JSON array of objects
json_data = '''
[
    {"account_id": 1, "number_of_users": 10, "total_revenue": 43803.96, "revenue_per_user": 4380.40}, 
    {"account_id": 2, "number_of_users": 12, "total_revenue": 77814.84, "revenue_per_user": 6484.57}
]
'''

# Load JSON data
data = json.loads(json_data)

# Define the CSV file name
csv_file = 'output.csv'

# Write JSON data to CSV
with open(csv_file, 'w', newline='') as csvfile:
    # Create a CSV writer object
    csvwriter = csv.writer(csvfile)
    
    # Write the header (keys of the first dictionary)
    header = data[0].keys()
    csvwriter.writerow(header)
    
    # Write the data rows
    for row in data:
        csvwriter.writerow(row.values())

print(f"JSON data has been written to {csv_file}")

import base64 

# Base64 encode the CSV file
encoded_string = base64.b64encode(open('output.csv', 'rb').read()).decode('utf-8')

print("Base64 Encoded CSV:")
print(encoded_string)

{
  "openaiFileResponse": [
    {
      "name": "output.csv",
      "mime_type": "text/csv",
      "content": "ImFjY291bn...NC41NyI="
    }
  ]
}