Hướng dẫn how do you pass a parameter in sql query using python? - làm thế nào để bạn chuyển một tham số trong truy vấn sql bằng cách sử dụng python?

cur.execute(""" INSERT INTO some_table (id, created_at, last_name) VALUES (%s, %s, %s); """, (10, datetime.date(2020, 11, 18), "O'Reilly")) 4 Đối số#

Chuyển các tham số cho câu lệnh SQL xảy ra trong các chức năng như

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

gần tương đương với lệnh SQL:

INSERT INTO some_table (id, created_at, last_name)
VALUES (10, '2020-11-18', 'O''Reilly');

Lưu ý rằng các tham số sẽ không thực sự được hợp nhất với truy vấn: truy vấn và các tham số được gửi riêng đến máy chủ: xem ràng buộc phía máy chủ để biết chi tiết.Server-side binding for details.

Các đối số được đặt tên cũng được hỗ trợ bằng cách sử dụng trình giữ chỗ

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

cur.execute("""
    INSERT INTO some_table (id, created_at, updated_at, last_name)
    VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
    """,
    {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})

Sử dụng các ký tự

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

INSERT INTO some_table (id, created_at, last_name)
VALUES (10, '2020-11-18', 'O''Reilly');

Khi các tham số được sử dụng, để bao gồm một

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

INSERT INTO some_table (id, created_at, last_name)
VALUES (10, '2020-11-18', 'O''Reilly');

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

Mặc dù cơ chế giống như thao tác chuỗi python thông thường, có một vài sự khác biệt tinh tế mà bạn nên quan tâm khi chuyển các tham số cho một truy vấn.

• Toán tử chuỗi Python

  cur.execute("""
      INSERT INTO some_table (id, created_at, last_name)
      VALUES (%s, %s, %s);
      """,
      (10, datetime.date(2020, 11, 18), "O'Reilly"))
  

  8 không được sử dụng: Phương thức

  cur.execute("""
      INSERT INTO some_table (id, created_at, last_name)
      VALUES (%s, %s, %s);
      """,
      (10, datetime.date(2020, 11, 18), "O'Reilly"))
  

  4 chấp nhận một tuple hoặc từ điển của các giá trị làm tham số thứ hai. Không bao giờ sử dụng

  cur.execute("""
      INSERT INTO some_table (id, created_at, last_name)
      VALUES (%s, %s, %s);
      """,
      (10, datetime.date(2020, 11, 18), "O'Reilly"))
  

  8 hoặc

  INSERT INTO some_table (id, created_at, last_name)
  VALUES (10, '2020-11-18', 'O''Reilly');
  

  6 để hợp nhất các giá trị thành các truy vấn:Never use

  cur.execute("""
      INSERT INTO some_table (id, created_at, last_name)
      VALUES (%s, %s, %s);
      """,
      (10, datetime.date(2020, 11, 18), "O'Reilly"))
  

  8 or

  INSERT INTO some_table (id, created_at, last_name)
  VALUES (10, '2020-11-18', 'O''Reilly');
  

  6 to merge values into queries:

  cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # WRONG
  cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # correct
  

• Đối với các biến vị trí ràng buộc, đối số thứ hai phải luôn luôn là một chuỗi, ngay cả khi nó chứa một biến duy nhất (hãy nhớ rằng Python yêu cầu dấu phẩy để tạo một bộ phần tử duy nhất):

  cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # WRONG
  cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # WRONG
  cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
  cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # correct
  

• Người giữ chỗ không được trích dẫn:

  cur.execute("INSERT INTO numbers VALUES ('%s')", ("Hello",)) # WRONG
  cur.execute("INSERT INTO numbers VALUES (%s)", ("Hello",))   # correct
  

• Các chất giữ chỗ của các biến phải luôn là

  cur.execute("""
      INSERT INTO some_table (id, created_at, last_name)
      VALUES (%s, %s, %s);
      """,
      (10, datetime.date(2020, 11, 18), "O'Reilly"))
  

  6, ngay cả khi một trình giữ chỗ khác (chẳng hạn như

  INSERT INTO some_table (id, created_at, last_name)
  VALUES (10, '2020-11-18', 'O''Reilly');
  

  8 cho số nguyên hoặc

  INSERT INTO some_table (id, created_at, last_name)
  VALUES (10, '2020-11-18', 'O''Reilly');
  

  9 cho phao) có thể trông phù hợp hơn với loại. Bạn có thể tìm thấy những người giữ chỗ khác được sử dụng trong các truy vấn PSYCOPG (

  cur.execute("""
      INSERT INTO some_table (id, created_at, updated_at, last_name)
      VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
      """,
      {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})
  

  0 và

  cur.execute("""
      INSERT INTO some_table (id, created_at, updated_at, last_name)
      VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
      """,
      {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})
  

  1) nhưng chúng không liên quan đến loại đối số: xem các tham số và kết quả nhị phân nếu bạn muốn đọc thêm:Binary parameters and results if you want to read more:

  cur.execute("INSERT INTO numbers VALUES (%d)", (10,))   # WRONG
  cur.execute("INSERT INTO numbers VALUES (%s)", (10,))   # correct
  

• Chỉ các giá trị truy vấn nên được ràng buộc thông qua phương thức này: nó không nên được sử dụng để hợp nhất các tên trường hoặc trường vào truy vấn. Nếu bạn cần tạo các truy vấn SQL một cách linh hoạt (ví dụ như chọn tên bảng trong thời gian chạy), bạn có thể sử dụng các chức năng được cung cấp trong mô -đun

  cur.execute("""
      INSERT INTO some_table (id, created_at, updated_at, last_name)
      VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
      """,
      {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})
  

  2:

  cur.execute("INSERT INTO %s VALUES (%s)", ('numbers', 10))  # WRONG
  cur.execute(                                                # correct
      SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')),
      (10,))
  

Nguy hiểm: SQL Injection#

Biểu diễn SQL của nhiều loại dữ liệu thường khác với biểu diễn chuỗi python của chúng. Ví dụ điển hình là với các trích dẫn đơn trong chuỗi: Trong các trích dẫn đơn SQL được sử dụng làm trình phân cách theo nghĩa đen, do đó, các trích dẫn xuất hiện bên trong chuỗi phải được thoát ra, trong khi trong các trích dẫn đơn Python có thể được để lại .

Do sự khác biệt, đôi khi tinh tế, giữa các biểu diễn các loại dữ liệu, cách tiếp cận ngây thơ đối với thành phần chuỗi truy vấn, chẳng hạn như sử dụng kết nối chuỗi Python, là một công thức cho các vấn đề khủng khiếp:

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

Nếu các biến chứa dữ liệu để gửi đến cơ sở dữ liệu đến từ một nguồn không đáng tin cơ sở dữ liệu. Hình thức tấn công này được gọi là tiêm SQL và được biết đến là một trong những hình thức tấn công phổ biến nhất trên các hệ thống cơ sở dữ liệu. Trước khi tiếp tục, xin vui lòng in trang này dưới dạng bản ghi nhớ và treo nó lên bàn của bạn.

PSYCOPG có thể tự động chuyển đổi các đối tượng Python thành các giá trị SQL: Sử dụng tính năng này, mã của bạn sẽ mạnh mẽ và đáng tin cậy hơn. Chúng ta phải nhấn mạnh điểm này:automatically convert Python objects to SQL values: using this feature your code will be more robust and reliable. We must stress this point:

Cảnh báo

• Don lồng hợp nhất các giá trị hợp nhất cho một truy vấn: tin tặc từ nước ngoài sẽ đột nhập vào máy tính của bạn và đánh cắp không chỉ các đĩa của bạn, mà cả đĩa CD của bạn, chỉ khiến bạn có ba hồ sơ đáng xấu hổ nhất mà bạn từng mua. Trên băng cassette.

• Nếu bạn sử dụng nhà điều hành

  cur.execute("""
      INSERT INTO some_table (id, created_at, last_name)
      VALUES (%s, %s, %s);
      """,
      (10, datetime.date(2020, 11, 18), "O'Reilly"))
  

  8 để hợp nhất các giá trị cho một truy vấn, Con nghệ sĩ sẽ quyến rũ mèo của bạn, người sẽ chạy trốn lấy thẻ tín dụng của bạn và kính râm của bạn với họ.

• Nếu bạn sử dụng

  INSERT INTO some_table (id, created_at, last_name)
  VALUES (10, '2020-11-18', 'O''Reilly');
  

  6 để hợp nhất giá trị văn bản thành một chuỗi, kẻ xấu ở Balaclava sẽ tìm đường đến tủ lạnh của bạn, uống tất cả bia của bạn và để chỗ ngồi vệ sinh của bạn lên và giấy vệ sinh của bạn theo hướng sai.

• Bạn không muốn hợp nhất các giá trị theo cách thủ công với một truy vấn: sử dụng các phương thức được cung cấp thay thế.use the provided methods instead.

Cách chính xác để truyền các biến trong lệnh SQL là sử dụng đối số thứ hai của phương thức

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

Ghi chú

Trình kiểm tra mã tĩnh Python chưa hoàn toàn ở đó, nhưng trong tương lai, có thể kiểm tra mã của bạn để sử dụng không đúng các biểu thức chuỗi trong các truy vấn. Xem kiểm tra các chuỗi theo nghĩa đen trong các truy vấn để biết chi tiết.Checking literal strings in queries for details.

Thông số nhị phân và kết quả#

PostgreSQL có hai cách khác nhau để truyền dữ liệu giữa máy khách và máy chủ:

cur.execute("""
    INSERT INTO some_table (id, created_at, updated_at, last_name)
    VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
    """,
    {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})

cur.execute("""
    INSERT INTO some_table (id, created_at, updated_at, last_name)
    VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
    """,
    {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})

PSYCOPG có thể hỗ trợ cả hai định dạng cho từng loại dữ liệu. Bất cứ khi nào một giá trị được chuyển cho một truy vấn bằng cách sử dụng trình giữ chỗ

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

Nếu bạn có lý do để chọn rõ ràng định dạng nhị phân hoặc định dạng văn bản cho một giá trị bạn có thể sử dụng tương ứng là trình giữ chỗ

cur.execute("""
    INSERT INTO some_table (id, created_at, updated_at, last_name)
    VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
    """,
    {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})

cur.execute("""
    INSERT INTO some_table (id, created_at, updated_at, last_name)
    VALUES (%(id)s, %(created)s, %(created)s, %(name)s);
    """,
    {'id': 10, 'name': "O'Reilly", 'created': datetime.date(2020, 11, 18)})

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

Hai định dạng tương tự, văn bản hoặc nhị phân, được PostgreSQL sử dụng để trả lại dữ liệu từ truy vấn cho máy khách. Không giống như với các tham số, trong đó bạn có thể chọn định dạng giá trị theo giá trị, tất cả các cột được trả về bởi một truy vấn sẽ có cùng định dạng. Mỗi loại được trả về bởi truy vấn phải có cấu hình

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

Ghi chú

Bảng PG_TYPE xác định định dạng nào được hỗ trợ cho từng loại dữ liệu PostgreSQL. Đầu vào/đầu ra văn bản được quản lý bởi các hàm được khai báo trong các trường

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

Bởi vì không phải mọi loại PostgreSQL đều hỗ trợ đầu ra nhị phân, theo mặc định, dữ liệu sẽ được trả về ở định dạng văn bản. Để trả về dữ liệu ở định dạng nhị phân, bạn có thể tạo con trỏ bằng cách sử dụng

cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # WRONG
cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # correct

cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # WRONG
cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # correct

cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # WRONG
cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # correct

cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # WRONG
cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # correct

cur.execute("""
    INSERT INTO some_table (id, created_at, last_name)
    VALUES (%s, %s, %s);
    """,
    (10, datetime.date(2020, 11, 18), "O'Reilly"))