Thực hành tốt nhất trong python sql

Tất cả các trình kết nối bao gồm một hàm thực thi giả định một câu lệnh SQL dưới dạng tham số chuỗi và có thể được thực thi trên một phần của cơ sở dữ liệu. Tuy nhiên, việc sử dụng Python không thực sự có ý nghĩa cho đến khi SQL được tạo động và theo hướng dữ liệu

Tại thời điểm này, tôi muốn ngắt lời và trình bày các phương án thay thế khác nhau – bắt đầu bằng các phương pháp đơn giản nhất nhưng cũng kém thông minh nhất – và kết thúc bằng một phương pháp hay nhất về cách truyền các chuỗi SQL

Để bắt đầu, trước tiên tôi phải làm rõ rằng mọi phương án ngoại trừ phương án cuối cùng đều tạo thành những lỗ hổng bảo mật nguy hiểm tiềm ẩn. Có thể, nếu không có biện pháp phòng ngừa bảo mật nào khác được thực hiện, dữ liệu nhạy cảm đó có thể được truy xuất hoặc thậm chí bị xóa

Cách tiếp cận ngây thơ nhất và nguy hiểm nhất. nối chuỗi

Trước hết, chúng tôi tạo một cơ sở dữ liệu thử nghiệm trong SQLite. Lý do tôi đang sử dụng SQLite để trình diễn là vì SQLite đi kèm với Python, có thể tạo cơ sở dữ liệu trực tiếp trong bộ nhớ cho thời gian chạy tập lệnh, nghĩa là các ví dụ có thể được sao chép cho từng cái. Tuy nhiên, tôi chỉ có thể đảm bảo việc thực thi các ví dụ bắt đầu bằng Python 3 mà không gặp lỗi. x

 import sqlite3 
db = sqlite3.connect[':memory:'] 
db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
db.execute["INSERT INTO staff [person_id, lastname] VALUES [1, 'Pavlov'] "] 
db.execute["INSERT INTO staff [person_id, lastname] VALUES [2, 'Skinner'] "] 

Trong mã mẫu, cơ sở dữ liệu không tên được khởi tạo trong bộ nhớ bằng cách nhập '. kỉ niệm. ' làm vị trí lưu trữ trong lệnh kết nối sau khi nhập mô-đun.
Sau đó, một bảng tính nhân viên có tên "nhân viên" được tạo và điền vào tập dữ liệu đầu tiên.

Cho đến nay, rất tốt. Nhưng chúng tôi không muốn phải viết toàn bộ lệnh chèn cho từng nhân viên mà chúng tôi sẽ thêm vào bảng tính.
Nếu tên của nhân viên đã có sẵn dưới dạng danh sách, thì điều này thực sự phù hợp với việc sử dụng vòng lặp.

nỗ lực đầu tiên

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [" + person_id + ", '" + lastname + "'] "]

Mặc dù tất cả các ý định tốt, nó đã thất bại. Thông báo lỗi "TypeError. Không thể chuyển đổi hoàn toàn đối tượng 'int' thành str" ngụ ý rằng chúng ta đã quên chuyển kiểu dữ liệu của person_id từ số nguyên sang str. Mặc dù Python linh hoạt trong hầu hết mọi thứ, nhưng nó vẫn là ngôn ngữ được đánh máy rất mạnh và các chuỗi bất biến không thể kết hợp với số nguyên

Đối với điều này, trình biên dịch sẽ phải dậy sớm hơn. lần thử tiếp theo

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR];"] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [" + str[person_id] + ", '" + lastname + "'] "]

Chà, nó đang chạy nhưng nó không đẹp chút nào. Đặc biệt là khi rất nhiều từ nối này được sử dụng trong mã, thì câu lệnh sẽ rất rời rạc. Hơn nữa, tôi luôn phải tự mình lo việc chuyển đổi loại

cách tiếp cận cũ. mẫu chuỗi với %s

Khi lướt qua tài liệu về Python, thậm chí cả những nội dung hiện tại hơn và đọc các mục diễn đàn khác nhau trong Stack Overflow hoặc bất kỳ nơi nào khác, có thể thấy các kỹ thuật giống như thế này trong ví dụ của chúng tôi

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [%d, '%s'] " % [person_id, lastname]] 

Hoạt động như một giấc mơ. "%d" là phần giữ chỗ cho một chữ số và "%s" là phần giữ chỗ cho một chuỗi

Tuy nhiên, nếu giá trị này được yêu cầu nhiều lần khi sử dụng ký hiệu này, thì nó lại trở nên khá lộn xộn. Hãy để chúng tôi tưởng tượng một ví dụ trong đó chúng tôi kiểm tra các điều kiện khác nhau trong một truy vấn

 sql = """SELECT lastname , CASE WHEN %d > 10 THEN 'greater' WHEN %d = 10 THEN 'equal' WHEN %d < 10 THEN 'lesser' END vergleich FROM staff WHERE lastname  '%s' and %d > 0 """ % [person_id, person_id, person_id, lastname, person_id] 

Ngay khi chúng tôi nhập một trình giữ chỗ khác tại đây, nguy cơ xảy ra lỗi sẽ tăng lên và chúng tôi phải đếm các vị trí trong mã mỗi lần. Ở đây tốt hơn là chọn các trình giữ chỗ được chỉ định

 sql = """SELECT lastname , CASE WHEN %[person_id]d > 10 THEN 'greater' WHEN %[person_id]d = 10 THEN 'equal' WHEN %[person_id]d < 10 THEN 'lesser' END vergleich FROM staff WHERE lastname  '%[lastname]d' and %[person_id]d > 0 """ % {'person_id': person_id, 'lastname': lastname] 

Tuyệt vời. Mã bây giờ dễ đọc hơn nhiều vì bây giờ chúng ta có thể thấy những gì chúng ta chèn và nơi chúng ta chèn nó ngay lập tức

Ký hiệu này chỉ đặt ra một vấn đề nhỏ, đó là nó bị coi là lỗi thời và, ít nhất là trong Python 3, đã được thay thế bằng một ký hiệu tốt hơn. Khoảng 3 hoặc 4 năm trước, tôi đã đọc nhiều lần trên các diễn đàn rằng ký hiệu này thậm chí còn bị coi là không dùng nữa. Điều này có nghĩa là nó không nên được sử dụng nữa vì tính tiếp tục của nó trong các phiên bản Python sau này không được đảm bảo. Tuy nhiên, ngày nay nó vẫn chưa bị bỏ rơi – có lẽ vì nó vẫn còn rất phổ biến trong các mô-đun

cách tiếp cận mới. mẫu chuỗi với {}

Ký hiệu chính thức mới sử dụng dấu ngoặc nhọn. Nó không chỉ trông khác biệt mà còn chứa đựng nhiều tiềm năng hơn về các tùy chọn định dạng. Khi ký hiệu mới vẫn được thông qua vì nó có thể làm được nhiều việc hơn, tại sao không sử dụng nó một cách nhất quán?

Trước tiên chúng ta hãy xem phiên bản đơn giản

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [{}, '{}'] ".format[person_id, lastname]] 

Điều quan trọng cần lưu ý ở đây là khi giao diện không đóng vai trò gì liên quan đến vị trí dấu phẩy hoặc số 0 đứng đầu, v.v. , thì không cần có sự khác biệt giữa các chuỗi hoặc giá trị số đối với trình giữ chỗ. Có, thậm chí có thể sử dụng bộ dữ liệu, ví dụ

 db.execute["SELECT * FROM staff WHERE person_id in {}".format[[1,3,4]]] 

Hàm định dạng của chuỗi gọi phương thức __str__ của từng đối tượng. Điều này sau đó tương ứng với từng str[object]

Có ký hiệu với nhãn ở đây nhưng không có từ điển nào được truyền đi. Thay vào đó, phân bổ được viết dưới dạng các tham số chức năng

Ai lười viết cũng có thể dùng tuple packing và tuple unpacking cho mình

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for row in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [{}, '{}'] ".format[*row]] 

row là một bộ trong mỗi chu kỳ của vòng lặp vì enumerate[] trả về hai giá trị dưới dạng một bộ. Những thứ này sau đó được buộc vào hàng biến. Với ký hiệu ". format[*row]" bộ dữ liệu có thể được giải nén lại và các giá trị có thể được gọi theo thứ tự thích hợp

Tương tự với từ điển

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [" + person_id + ", '" + lastname + "'] "]

Ví dụ cực đoan nhất cho việc muốn tránh gõ là ví dụ lý thuyết này về chức năng chèn

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [" + person_id + ", '" + lastname + "'] "]

Ở đây, lệnh định dạng chỉ lấy dữ liệu từ các biến được xác định trong không gian tên của lệnh gọi hàm, trong trường hợp này là các tham số chức năng. Tôi đã tự mình sử dụng ví dụ này trong Python 2. 7. Trong Trăn 3. x, tuy nhiên, cách này không hoạt động nữa và tôi tin rằng cách này tốt hơn

Bây giờ, hãy tưởng tượng rằng chúng ta sắp có một nhân viên mới, thứ năm mươi, tên là OʼReilly. Tên được thêm vào nhanh chóng

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [" + person_id + ", '" + lastname + "'] "]

SQLite phàn nàn ở đây "sqlite3. lỗi hoạt động. gần "Reilly". lỗi cú pháp". Đó là những gì tất cả về?

Có thể tìm thấy sự giải thoát giữa cơn điên cuồng mã hóa ở đây bằng cách xóa dấu nháy đơn. Điều này thay đổi tùy theo cơ sở dữ liệu. Trong SQLite, dấu nháy đơn phải được nhân đôi

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [" + person_id + ", '" + lastname + "'] "]

Chà, nó hoạt động ngay bây giờ, nhưng nó chỉ là một cách giải quyết rẻ tiền

Vì vậy, bây giờ chúng tôi đang có một nhân viên mới khác, nhân viên thứ 51, tên là Mr "'];DROP TABLE;". Tên lạ nhưng nếu đó là cách người dùng nhập nó thì nó phải đúng

Nếu chúng tôi không xóa các ký tự khác nhau khỏi sự kiện với Mr O'Reilly và nếu chúng tôi không làm việc với SQLite, một chương trình không cho phép 2 câu lệnh trong một chuỗi thực thi, thì chuỗi truy vấn sẽ xuất hiện như sau

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [" + person_id + ", '" + lastname + "'] "]

Toàn bộ bảng tính đã bị xóa ở đây do đầu vào của người dùng không được vệ sinh. Những lần tiêm SQL này là mối nguy hiểm thực sự đối với tính bảo mật của cơ sở dữ liệu. Chỉ vài năm trước, khi tôi đang thử mọi thứ, tôi tình cờ thấy các nhà bán lẻ trực tuyến lớn thậm chí còn không làm sạch dấu nháy đơn trong tìm kiếm sản phẩm của họ

Thực hành tốt nhất. truy vấn được tham số hóa

Cả ba giải pháp thay thế đã được chứng minh đều hoạt động. Vì lợi ích của tốc độ, bản thân tôi vẫn sử dụng một ký hiệu này hay ký hiệu khác

Tuy nhiên, cách thực hành tốt nhất rất rõ ràng là việc sử dụng "truy vấn được tham số hóa". Bất kể ngôn ngữ lập trình nào, mọi trình kết nối cơ sở dữ liệu đều phải hỗ trợ kiểu truyền truy vấn này. Ít nhất trong Python, tôi biết từ kinh nghiệm thực tế rằng nó hoạt động cho Oracle, MySql, SQLite và PostgreSQL

Ý tưởng đằng sau nó là chuỗi SQL không được biên dịch hoàn toàn bởi một người và sau đó được truyền đến trình kết nối, mà một mẫu và các tham số cho mẫu được truyền đi

Có nhiều lợi thế khác nhau. Một là trình điều khiển cơ sở dữ liệu giả định tất cả các chuyển đổi loại và cách xử lý đặc biệt của các ký hiệu chẳng hạn như dấu nháy đơn. Điều đó có nghĩa là không cần phải lo lắng về các quy ước tương ứng trong cơ sở dữ liệu là gì

Một điều nữa là có những lợi thế về hiệu suất trên các nền tảng cơ sở dữ liệu nhất định nếu cùng một truy vấn được thực hiện với các tham số khác nhau trên cơ sở rất thường xuyên. Sau đó, trình phân tích cú pháp SQL không phải phân tích lại truy vấn để lập kế hoạch thực hiện mỗi lần. Thay vào đó, nó quay trở lại các lần thực thi trước đó và chỉ thay thế các giá trị tại trình giữ chỗ

Thật không may, loại tham số hóa trên các nền tảng cơ sở dữ liệu khác nhau là không nhất quán. Trình điều khiển cơ sở dữ liệu Python có ít nhất một thuộc tính có tên là paramstyle chỉ định kỹ thuật nào sẽ được sử dụng

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [" + person_id + ", '" + lastname + "'] "]

Trong ví dụ về SQLite, trước tiên chúng ta kiểm tra xem paramstyle nào là quan trọng. Đó là 'qmark', đó là dấu chấm hỏi. Câu lệnh chèn thể hiện việc sử dụng. Dấu chấm hỏi được thêm vào cho mọi vị trí. Sau đó, theo đúng thứ tự, các giá trị phải được truyền dưới dạng một bộ dưới dạng tham số thứ hai trong lệnh gọi hàm. Loại chuyển đổi chạy tự động, ngay cả đối với các đối tượng ngày

Ví dụ, trong PostgreSQL, có một định dạng khác cũng cho phép các tham số được chỉ định

 db.execute["CREATE TABLE staff [person_id int, lastname CHAR]; "] 
for person_id, lastname in enumerate[staff_names]: 
    db.execute["INSERT INTO staff [person_id, lastname] VALUES [" + person_id + ", '" + lastname + "'] "]

Thực hành tốt nhất = Chỉ thực hành?

Tiêu đề "Thực tiễn tốt nhất" thực sự gây hiểu lầm. Thay vào đó, nó thực sự nên là "Chỉ thực hành". Thoạt nhìn, các truy vấn được tham số hóa có vẻ rườm rà. Đặc biệt là khi câu lệnh SQL được xây dựng động bằng cách sử dụng nối chuỗi, các tham số rất dễ bị lẫn lộn, đặc biệt là khi các trình giữ chỗ chỉ được đánh dấu bằng dấu chấm hỏi như trường hợp trong SQLite. Tuy nhiên, so sánh, không phải lo lắng về chuyển đổi loại và ký hiệu là một lợi ích lớn

Thông thường, bây giờ tôi buộc phải nói rằng khía cạnh bảo mật - cụ thể là tránh tiêm nhiễm SQL - phải là lý do quan trọng nhất để sử dụng phương pháp hay nhất này và thực sự là đủ.

Trong bối cảnh hàng ngày của khoa học dữ liệu, bối cảnh trong đó phải thực hiện nhiều biện pháp phòng ngừa bảo mật cho đến khi có thể truy cập dữ liệu và khi ứng dụng không nhất thiết phải được vận hành bởi người dùng có khả năng ác tính, thì khía cạnh bảo mật là một hoạt động tự nguyện. Rất hiếm khi trong những năm làm công việc này, tôi gặp phải tình huống mà bất kỳ ai không trực tiếp tham gia vào dự án đều có thể truy cập được mã Python nguy hiểm tiềm tàng. Trong những trường hợp này, mọi người sẽ có quyền truy cập trực tiếp vào cơ sở dữ liệu

Đối với bất kỳ ai muốn tận dụng tối đa Python để tạo SQL và tạo các truy vấn SQL động thực sự mạnh mẽ, thì một lúc nào đó họ sẽ buộc phải từ bỏ phương pháp hay nhất. Chỉ có thể tăng tham số đầu vào giá trị. Chỉ có thể tiếp tục sử dụng tên bảng và tên cột bằng cách sử dụng một trong các kỹ thuật được mô tả ở trên một cách linh hoạt

Trên thực tế, tôi phải thừa nhận rằng bản thân tôi luôn sử dụng kết hợp các truy vấn được tham số hóa và các mẫu chuỗi với {}. Tuy nhiên, điều quan trọng là đảm bảo rằng chỉ những người dùng được ủy quyền mới có quyền truy cập vào tập lệnh và dữ liệu. Bước đầu tiên theo hướng này là đảm bảo rằng mọi người đang chạy tập lệnh cơ sở dữ liệu Python đều có người dùng riêng cho kết nối thay vì có một người dùng chung