MySQL sang Google Trang tính miễn phí

Giả sử rằng bạn muốn xuất dữ liệu mysql từ cơ sở dữ liệu của mình sang bảng tính vì bạn nghĩ rằng các đồng nghiệp tiếp thị của mình sẽ xử lý tốt hơn với định dạng excel quen thuộc hoặc bạn có thể muốn tạo báo cáo chứa cả thông tin từ các công cụ khác và dữ liệu cơ sở dữ liệu nội bộ

Bạn đã đến đúng nơi, một cách dễ dàng để kết nối MySQL với Google Trang tính là KPIBees. Không cần viết mã hoặc Google Apps Script. Tiện ích bổ sung có thể được cài đặt và bạn có thể kết nối bảng tính với cơ sở dữ liệu mysql của mình chỉ bằng vài cú nhấp chuột – không cần thiết lập khác.  

Với Tiện ích bổ sung KPIBees, bạn có thể tích hợp MySQL với Google Trang tính và chuyển dữ liệu của mình sang định dạng excel ngay lập tức. Nó hoạt động theo cách rất đơn giản, bạn xác định các truy vấn mysql của mình và lưu chúng vào các ô bảng tính mong muốn của bạn. Sau đó, bạn có thể đặt trình kích hoạt làm mới sẽ tự động đưa dữ liệu cơ sở dữ liệu được cập nhật vào bảng tính của bạn để bạn có phiên bản cập nhật mỗi khi quay lại

Hướng dẫn này chỉ cho bạn cách truy vấn cơ sở dữ liệu MySQL từ Google Trang tính bằng Python và xlwings PRO. MySQL là một trong những cơ sở dữ liệu nguồn mở phổ biến nhất và hiện thuộc sở hữu của Oracle. Hướng dẫn này tập trung vào truy vấn cơ sở dữ liệu MySQL trực tiếp từ Google Trang tính. Điều này giúp bạn tránh khỏi các tác vụ thủ công và dễ xảy ra lỗi khi phải xuất dữ liệu từ MySQL sang tệp CSV, chỉ để nhập lại chúng vào Google Trang tính. Bạn cũng có thể cấp cho người dùng quyền truy cập chỉ đọc hoặc ghi vào dữ liệu cụ thể

Tìm kiếm một cơ sở dữ liệu khác nhau?

SQLite. PostgreSQL. MariaDB. Máy chủ SQL. tiên tri

Mục lục

Bắt đầu nhanh

Bạn sẽ sử dụng hàm runPython của xlwings trong Google Apps Script để gọi chương trình phụ trợ Python chạy trên máy chủ hoặc dịch vụ đám mây của bạn. Để biết bạn có thể làm việc này nhanh như thế nào, hãy làm theo các bước sau

1. Đi đến dự án trên GitHub. xlwings-googlesheets-sql
2. Nhấp vào nút Deploy to Render trên README của dự án để tạo ứng dụng Kết xuất miễn phí (trước tiên bạn cũng có thể rẽ nhánh repo trên GitHub để có thể thay đổi nó). Thay vì Render, bạn cũng có thể triển khai ứng dụng cho bất kỳ dịch vụ nào có thể xử lý Python hoặc Docker

3. Điền thông tin sau vào Kết xuất.

   
   (1) Tên, e. g. , xlwings-googlesheets-sql
   (2) (Các) miền không gian làm việc Google của bạn trong ký hiệu của danh sách Python. ["your-domain.com"] hoặc ["your-first-domain.com", "your-other-domain.com"]. Đây là các miền không gian làm việc của Google sẽ được cấp quyền truy cập vào ứng dụng
   (3) Khóa cấp phép xlwings PRO của bạn (nhận khóa dùng thử miễn phí tại đây). Lưu ý rằng xlwings PRO miễn phí cho mục đích sử dụng phi thương mại, xem tài liệu
   (4) Chuỗi kết nối cơ sở dữ liệu của bạn. mysql://user:password@host:port/database. Điều này giả định rằng bạn có một cơ sở dữ liệu hiện có mà bạn có thể sử dụng. Để sử dụng truy vấn từ mẫu, bạn có thể sử dụng tệp create_data.py trong thư mục

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   0 của repo GitHub, tệp này sẽ tạo một bảng

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   1 với dữ liệu từ cơ sở dữ liệu mẫu của AdventureWorks. Dưới đây, chúng ta sẽ xem cách chúng ta có thể tạo một phiên bản MySQL cục bộ bằng cách sử dụng Docker nếu bạn không có sẵn. Hoặc bạn có thể sử dụng phiên bản MySQL được lưu trữ trên máy chủ vì chúng được cung cấp bởi nhiều nhà cung cấp dịch vụ đám mây. Thay vì điền vào bảng mẫu, rõ ràng bạn cũng có thể thay đổi truy vấn SQL để hoạt động với một trong các bảng hiện có của mình, xem bên dưới

   Sau khi nhấn

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   2, sẽ mất một hoặc hai phút cho đến khi ứng dụng của bạn khởi động và chạy. Kiểm tra tiến độ trên Render's Dashboard (nhấp vào biểu tượng Render ở trên cùng bên trái để đến đó)

4. Tạo một bản sao của mẫu Google Trang tính bằng cách nhấp vào

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   3 ở trên cùng bên phải
5. Trong Google Trang tính, hãy mở trình chỉnh sửa Apps Script bằng cách đi tới

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   4 >

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   5, sau đó dán URL của ứng dụng Kết xuất của bạn (bạn sẽ tìm thấy URL này khi nhấp vào dự án của mình trên Bảng điều khiển kết xuất, ở đầu trang)

6. Trong Google Trang tính, nhấp vào nút

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   6 để truy vấn cơ sở dữ liệu, tùy chọn cung cấp các phần của

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   7 ở B5 và (bỏ) chọn hộp kiểm

   const base_url = "https://your-url.com";
   const token = ScriptApp.getOAuthToken();
   
   function selectEmployees() {
     runPython(base_url + "/employees/select", { apiKey: token });
   }
   

   8.

   Ghi chú. khi bạn chạy lần đầu tiên, tập lệnh sẽ yêu cầu các quyền cần thiết, bạn sẽ cần xác nhận quyền này. Sau khi xác nhận, hãy chạy lại tập lệnh

Sau phần giới thiệu nhanh này, chúng ta hãy xem điều gì xảy ra đằng sau hậu trường và cách bạn có thể thay đổi truy vấn SQL

Cho tôi xem mã

Ứng dụng mẫu là một ứng dụng web FastAPI đơn giản và toàn bộ điều kỳ diệu xảy ra ở điểm cuối

const base_url = "https://your-url.com";
const token = ScriptApp.getOAuthToken();

function selectEmployees() {
  runPython(base_url + "/employees/select", { apiKey: token });
}

9 bên dưới

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

0 (hãy kiểm tra repo để biết mã đầy đủ bao gồm cả phần nhập)

@router.post("/select")
def select_employees(
    data: dict = Body,
    current_user: User = Security(authenticate),
    db: Session = Depends(get_db),
):
    # Spreadsheet objects
    book = xw.Book(json=data)
    sheet = book.sheets[0]
    result_cell = sheet["D1"]

    # Get the query parameters as dictionary
    params = sheet["A5:B5"].options(dict, expand="down").value

    # You can log who is running the query
    logger.info(f"Running 'select employees' query for user {current_user.email}")

    # SQL Query using SQLAlchemy placeholders
    sql = """
    SELECT *
    FROM employees
    WHERE salaried_flag = :salaried_flag
    """
    if params["job title"]:
        sql += "AND LOWER(job_title) LIKE LOWER(:job_title)"

    # Execute the query via SQLAlchemy
    result = db.execute(
        text(sql),
        {
            "salaried_flag": params["salaried?"],
            "job_title": f"%{params['job title']}%",
        },
    )

    # Delete existing data in the spreadsheet and write the result back
    result_cell.expand().clear_contents()
    result_cell.value = process_cursor_result(result)

    return book.json()

Ghi chú. trong khi repo sử dụng FastAPI, bạn có thể sử dụng xlwings với bất kỳ khung web Python nào khác, bao gồm Django, Flask, v.v.

Như bạn có thể thấy từ đoạn trích, xlwings giúp bạn dễ dàng đọc các tham số từ Google Trang tính (hộp kiểm tự động trả về là

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

1 hoặc

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

2).

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

3 là phiên SQLAlchemy mà chúng tôi sử dụng để thực hiện truy vấn. Ở cuối đoạn mã, bạn có thể thấy cách xlwings cho phép bạn đẩy kết quả trở lại ô mong muốn trong Google Trang tính

Để gọi điểm cuối này, hãy sử dụng hàm runPython trong Google Apps Script như vậy

const base_url = "https://your-url.com";
const token = ScriptApp.getOAuthToken();

function selectEmployees() {
  runPython(base_url + "/employees/select", { apiKey: token });
}

runPython là một chức năng của mô-đun Tập lệnh ứng dụng

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

6, đã có trong mẫu của hướng dẫn này. Nếu bạn muốn bắt đầu lại từ đầu với Google Trang tính mới, bạn có thể sao chép/dán mô-đun xlwings bằng cách chạy

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

7 trên máy đã cài đặt Python và xlwings

Mặc dù triển khai ứng dụng web cho một dịch vụ như Render là một ý tưởng hay cho sản xuất, nhưng nó sẽ quá cồng kềnh cho mục đích phát triển. Do đó, phần tiếp theo cung cấp cho bạn một cái nhìn tổng quan nhanh về quá trình phát triển trông như thế nào

thiết lập phát triển

Để dễ dàng phát triển, bạn có thể chạy

• ứng dụng từ máy tính xách tay cục bộ của bạn và hiển thị ứng dụng đó bằng cách sử dụng một dịch vụ như ngrok để Google Trang tính có thể truy cập máy chủ web của bạn chạy trên máy chủ cục bộ hoặc
• sử dụng IDE dựa trên web như GitPod hoặc GitHub Workspaces. họ có thể dễ dàng hiển thị cổng ứng dụng web của bạn để Google Trang tính có thể truy cập cổng đó (xem nút GitPod trên README trên GitHub)

Đối với các cơ sở dữ liệu dựa trên máy chủ như MySQL, Docker giúp dễ dàng tạo ra một phiên bản cục bộ cho mục đích phát triển. Repo trên GitHub chứa tệp

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

8 mà bạn chỉ cần bỏ ghi chú dịch vụ

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

9 trước khi chạy runPython0 trong Dấu nhắc Lệnh hoặc Thiết bị đầu cuối. điều này sẽ chạy ứng dụng web trong một vùng chứa và cơ sở dữ liệu MySQL trong một vùng chứa khác. Đảm bảo làm theo các hướng dẫn chi tiết hơn trên README. chẳng hạn, nó sẽ cung cấp cho bạn hướng dẫn về cách cung cấp tệp runPython1 với chuỗi kết nối phù hợp. Trong trường hợp MySQL chạy qua docker-compose, đây là. runPython2. Sau khi bỏ ghi chú dịch vụ

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

9,

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

8 của bạn sẽ trông như thế này (đảm bảo mọi thứ được thụt lề đúng cách. )

version: '3.7'

services:
  app:
    build:
      context: .
    command: uvicorn app.main:app --host 0.0.0.0
    ports:
      - "8000:8000"
    volumes:
      - ./app:/app
      - ./scripts:/scripts
      - ./sqlite-data:/sqlite-data
    env_file:
      - .env

  mysql:
    image: mysql:8
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: xlwings
      MYSQL_ROOT_PASSWORD: MyPassw0rd
    volumes:
      - ./mysql-data:/var/lib/mysql

Để biết thêm chi tiết về quy trình phát triển với Google Trang tính và xlwings, hãy xem tài liệu trình thông dịch từ xa

Nhưng tại sao bạn nên sử dụng Python để kết nối với cơ sở dữ liệu MySQL của mình và so sánh nó với việc sử dụng tiện ích bổ sung Google Trang tính có sẵn như thế nào?

xlwing vs. Tiện ích bổ sung Google Trang tính cho MySQL

Bằng cách sử dụng chương trình phụ trợ Python tùy chỉnh, bạn sẽ nhận được một số lợi thế so với các tiện ích bổ sung Google Trang tính (“không mã”) có sẵn

• Không cần tin tưởng nhà cung cấp bên thứ ba với dữ liệu nhạy cảm của bạn. bạn có thể chạy truy vấn trên cơ sở hạ tầng mong muốn của mình (Kết xuất chỉ là một ví dụ về cách triển khai nó). Bạn có thể chọn nhà cung cấp và vị trí—thậm chí bạn có thể chạy nó trên máy chủ thực của công ty hoặc Raspberry Pi
• Các tiện ích bổ sung của Google thường được định giá cho mỗi người dùng, có thể trở nên đắt đỏ rất nhanh. xlwings PRO chỉ yêu cầu giấy phép trả phí dành cho nhà phát triển và miễn phí sử dụng cho người dùng cuối
• Bạn có thể giữ các chuỗi kết nối và dữ liệu nhạy cảm khác khỏi Google Trang tính của mình—chúng có thể được bảo mật đúng cách dưới dạng bí mật ở phía máy chủ và người dùng bảng tính không thể truy cập được
• Bạn có thể sử dụng một máy chủ thực sự mạnh mẽ nếu bạn muốn thực hiện một số công việc tốn nhiều CPU hoặc bộ nhớ bên cạnh việc truy vấn MySQL
• Bạn có quyền truy cập vào người dùng chạy truy vấn và bạn có thể đăng nhập bất cứ khi nào ai đó thực hiện truy vấn
• Bạn có thể dễ dàng tạo một vài bảng hoặc toàn bộ cơ sở dữ liệu MySQL ở chế độ chỉ đọc cho một số người dùng nhất định trong khi cấp cho người dùng khác quyền ghi. Để biết thêm thông tin về ủy quyền, hãy xem https. //github. com/xlwings/xlwings-googlesheets-auth
• Bạn có thể sử dụng toàn bộ bảng tính để thiết kế giao diện người dùng của riêng mình thay vì phải tuân theo thiết kế của giải pháp không có mã
• Nếu bạn có các nguồn dữ liệu khác mà bạn cần lấy và kết hợp với kết quả SQL, thì bạn có thể thực hiện việc này một cách dễ dàng vì đó chỉ là mã Python
• Bạn muốn làm mới truy vấn hàng ngày hoặc hàng giờ?
• Bạn muốn thay đổi nguồn dữ liệu từ MySQL sang cơ sở dữ liệu SQL khác như PostgreSQL hoặc SQL Server?

Phần kết luận

xlwings cho phép bạn tự động hóa Google Trang tính bằng cách sử dụng Python thay vì Google Apps Script (i. e. , JavaScript). Vì Python có thể kết nối với hầu hết mọi nguồn dữ liệu, nên kết nối với MySQL chỉ cần một vài dòng mã. Điều này cũng đúng với các cơ sở dữ liệu SQL khác như PostgreSQL, SQL Server, Oracle hoặc SQLite—sẽ có các hướng dẫn riêng

Tôi có thể chạy truy vấn SQL trong Google Trang tính không?

Google Trang tính có thể lấy dữ liệu từ SQL Server không?