programming python

S trong SQL Python là gì?

Có rất nhiều tình huống mà người ta muốn chèn tham số vào truy vấn SQL và có nhiều cách để triển khai truy vấn SQL theo khuôn mẫu trong python. Không đi sâu vào so sánh các cách tiếp cận khác nhau, bài đăng này giải thích một phương pháp đơn giản và hiệu quả để tham số hóa SQL bằng JinjaSql. Bên cạnh nhiều tính năng mạnh mẽ của Jinja2, chẳng hạn như các câu lệnh và vòng lặp có điều kiện, JinjaSql cung cấp một cách rõ ràng và đơn giản để tham số hóa không chỉ các giá trị được thay thế trong các mệnh đề 

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
5 và 
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
6, mà còn cả chính các câu lệnh SQL, bao gồm tham số hóa tên bảng và tên cột cũng như soạn thảo các truy vấn

Thay thế tham số cơ bản

Giả sử chúng ta có một bảng 

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
7 chứa hồ sơ về các giao dịch tài chính. Các cột trong bảng này có thể là 
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
8, 
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
9, 
user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
0 và 
user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
1. Để tính toán số lượng giao dịch và tổng số tiền cho một người dùng nhất định vào một ngày nhất định, một truy vấn trực tiếp tới cơ sở dữ liệu có thể trông giống như

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = 1234
    and transaction_date = '2019-03-02'
group by
    user_id

Ở đây, chúng tôi giả sử rằng cơ sở dữ liệu sẽ tự động chuyển đổi định dạng 

user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
2 của chuỗi biểu diễn ngày thành một loại ngày thích hợp

Nếu chúng tôi muốn chạy truy vấn ở trên cho một người dùng và ngày tùy ý, chúng tôi cần tham số hóa các giá trị 

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
9 và 
user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
0. Trong JinjaSql, mẫu tương ứng sẽ đơn giản trở thành

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id

Ở đây, các giá trị đã được thay thế bằng các trình giữ chỗ có tên biến python được đặt trong dấu ngoặc nhọn kép 

user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
5. Lưu ý rằng tên biến 
user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
6 và 
user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
7 được chọn chỉ để chứng minh rằng chúng là tên biến và không liên quan gì đến chính tên cột. Phiên bản dễ đọc hơn của cùng một mẫu được lưu trữ trong biến python là

user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''

Tiếp theo, chúng ta cần thiết lập các tham số cho truy vấn

params = {
    'user_id': 1234,
    'transaction_date': '2019-03-02',
}

Giờ đây, việc tạo truy vấn SQL từ mẫu này thật đơn giản

from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]

Nếu chúng tôi in 

user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
8 và 
user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
9, chúng tôi thấy rằng cái trước là một chuỗi được tham số hóa và cái sau là một 
params = {
    'user_id': 1234,
    'transaction_date': '2019-03-02',
}
0 tham số

>>> print[query]
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = %[user_id]s
    and transaction_date = %[transaction_date]s
group by
    user_id>>> print[bind_params]
OrderedDict[[['user_id', 1234], ['transaction_date', '2018-03-01']]]
Chạy truy vấn được tham số hóa

Nhiều kết nối cơ sở dữ liệu có tùy chọn chuyển 

user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
9 làm đối số cho phương thức thực thi truy vấn SQL trên kết nối. Đối với một nhà khoa học dữ liệu, có thể tự nhiên nhận được kết quả của truy vấn trong khung dữ liệu Pandas. Khi chúng tôi có kết nối 
params = {
    'user_id': 1234,
    'transaction_date': '2019-03-02',
}
2, việc này dễ dàng như chạy 
params = {
    'user_id': 1234,
    'transaction_date': '2019-03-02',
}
3

import pandas as pd
frm = pd.read_sql[query, conn, params=bind_params]

Xem tài liệu JinjaSql để biết các ví dụ khác

Từ một mẫu đến truy vấn SQL cuối cùng

Người ta thường mong muốn mở rộng hoàn toàn truy vấn với tất cả các tham số trước khi chạy nó. Ví dụ: ghi nhật ký truy vấn đầy đủ là vô giá để gỡ lỗi các quy trình hàng loạt vì người ta có thể sao chép-dán truy vấn từ nhật ký trực tiếp vào giao diện SQL tương tác. Thật hấp dẫn khi thay thế 

user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
9 thành 
user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
8 bằng cách thay thế chuỗi tích hợp trong python. Tuy nhiên, chúng tôi nhanh chóng nhận thấy rằng các tham số chuỗi cần được trích dẫn để tạo ra SQL thích hợp. Ví dụ: trong mẫu ở trên, giá trị ngày tháng phải được đặt trong dấu nháy đơn

>>> print[query % bind_params]
​
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = 1234
    and transaction_date = 2018-03-01
group by
    user_id

Để giải quyết vấn đề này, chúng ta cần một hàm trợ giúp để trích dẫn chính xác các tham số là chuỗi. Chúng tôi phát hiện xem một tham số có phải là một chuỗi hay không, bằng cách gọi

from six import string_types
isinstance[value, string_types]

Điều này hoạt động cho cả python 3 và 2. 7. Các tham số chuỗi được chuyển đổi thành loại 

params = {
    'user_id': 1234,
    'transaction_date': '2019-03-02',
}
6, dấu nháy đơn trong tên được thoát bằng dấu nháy đơn khác và cuối cùng, toàn bộ giá trị được đặt trong dấu nháy đơn

from six import string_typesdef quote_sql_string[value]:
    '''
    If `value` is a string type, escapes single quotes in the string
    and returns the string enclosed in single quotes.
    '''
    if isinstance[value, string_types]:
        new_value = str[value]
        new_value = new_value.replace["'", "''"]
        return "'{}'".format[new_value]
    return value

Cuối cùng, để chuyển đổi mẫu thành SQL thích hợp, chúng tôi lặp lại 

user_transaction_template = '''
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ user_id }}
    and transaction_date = {{ transaction_date }}
group by
    user_id
'''
9, trích dẫn các chuỗi và sau đó thực hiện thay thế chuỗi

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
0

Bây giờ chúng tôi có thể dễ dàng nhận được truy vấn cuối cùng mà chúng tôi có thể đăng nhập hoặc chạy một cách tương tác

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
1

Đặt tất cả lại với nhau, một hàm trợ giúp khác kết thúc các lệnh gọi JinjaSql và chỉ cần lấy mẫu và 

params = {
    'user_id': 1234,
    'transaction_date': '2019-03-02',
}
8 tham số, rồi trả về SQL đầy đủ

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
2Tính toán số liệu thống kê trên một cột

Tính toán số liệu thống kê về các giá trị được lưu trữ trong một cột cơ sở dữ liệu cụ thể rất hữu ích cả khi khám phá dữ liệu lần đầu tiên và để xác thực dữ liệu trong sản xuất. Vì chúng tôi chỉ muốn minh họa một số tính năng của các mẫu, nên để đơn giản, chúng ta hãy chỉ làm việc với các cột 

params = {
    'user_id': 1234,
    'transaction_date': '2019-03-02',
}
9, chẳng hạn như cột 
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
9 trong bảng 
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
7 ở trên. Đối với các cột số nguyên, chúng tôi quan tâm đến số lượng giá trị duy nhất, giá trị tối thiểu và tối đa và số lượng null. Một số cột có thể có giá trị mặc định là, chẳng hạn như, 
from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]
2, những hạn chế của cột nằm ngoài phạm vi của bài đăng này, tuy nhiên, chúng tôi muốn nắm bắt điều đó bằng cách báo cáo số lượng giá trị mặc định

Xem xét mẫu và chức năng sau. Hàm lấy tên bảng, tên cột và giá trị mặc định làm đối số và trả về SQL để tính toán số liệu thống kê

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
3

Hàm này đơn giản và rất mạnh vì nó áp dụng cho bất kỳ cột nào trong bất kỳ bảng nào. Lưu ý cú pháp 

from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]
3 trong mẫu. Nếu giá trị mặc định được truyền cho hàm là 
from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]
4, thì SQL trả về 0 trong trường 
from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]
5

Hàm và mẫu ở trên cũng sẽ hoạt động với chuỗi, ngày tháng và các loại dữ liệu khác nếu 

from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]
6 được đặt thành Không có. Tuy nhiên, để xử lý các kiểu dữ liệu khác nhau một cách thông minh hơn, cần mở rộng hàm để lấy kiểu dữ liệu làm đối số và xây dựng logic cụ thể cho các kiểu dữ liệu khác nhau. Ví dụ: người ta có thể muốn biết giá trị tối thiểu và tối đa của độ dài chuỗi thay vì giá trị tối thiểu và tối đa của chính giá trị đó

Hãy xem đầu ra cho cột 

from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]
7

select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = {{ uid }}
    and transaction_date = {{ tdate }}
group by
    user_id
4

Lưu ý rằng các dòng trống xuất hiện ở vị trí của mệnh đề 

from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]
8 và có thể bị xóa

Tóm lược

Với các hàm trợ giúp ở trên, việc tạo và chạy các truy vấn SQL theo khuôn mẫu trong python rất dễ dàng. Bởi vì các chi tiết thay thế tham số bị ẩn, người ta có thể tập trung vào việc xây dựng mẫu và tập hợp các tham số, sau đó gọi một hàm duy nhất để lấy SQL cuối cùng

Một cảnh báo quan trọng là nguy cơ tiêm mã. Đối với các quy trình hàng loạt, đây không phải là vấn đề, nhưng việc sử dụng cấu trúc 

from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]
9 trong các ứng dụng web có thể nguy hiểm. Từ khóa 
from jinjasql import JinjaSql
j = JinjaSql[param_style='pyformat']
query, bind_params = j.prepare_query[user_transaction_template, params]
9 chỉ ra rằng người dùng [bạn] tự tin rằng không thể tiêm mã và chịu trách nhiệm chỉ cần đặt bất kỳ chuỗi nào được chuyển trực tiếp vào các tham số vào truy vấn

Mặt khác, khả năng đặt một chuỗi tùy ý trong truy vấn cho phép một người chuyển toàn bộ khối mã vào một mẫu. Ví dụ: thay vì chuyển 

>>> print[query]
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = %[user_id]s
    and transaction_date = %[transaction_date]s
group by
    user_id>>> print[bind_params]
OrderedDict[[['user_id', 1234], ['transaction_date', '2018-03-01']]]
1 ở trên, người ta có thể chuyển 
>>> print[query]
select
    user_id
    , count[*] as num_transactions
    , sum[amount] as total_amount
from
    transactions
where
    user_id = %[user_id]s
    and transaction_date = %[transaction_date]s
group by
    user_id>>> print[bind_params]
OrderedDict[[['user_id', 1234], ['transaction_date', '2018-03-01']]]
2 và truy vấn vẫn hoạt động

Để khám phá các tính năng mạnh mẽ hơn nữa của các mẫu SQL, hãy xem thêm hướng dẫn về Các mẫu SQL nâng cao trong Python với JinjaSql

Mã trong bài đăng này được cấp phép theo Giấy phép MIT. Bài đăng này lần đầu tiên xuất hiện trên blog Cuộc sống xung quanh dữ liệu

S% trong SQL là gì?

%s là trình giữ chỗ được sử dụng trong các chức năng như sprintf . Kiểm tra hướng dẫn để biết các trình giữ chỗ có thể khác. $sql = sprintf[$sql, "Kiểm tra"]; . Nó cũng được sử dụng để đảm bảo rằng tham số được truyền thực sự phù hợp với trình giữ chỗ.

S trong Python mysql là gì?

Sử dụng Biến Python trong Truy vấn Chèn MySQL . Khi sử dụng truy vấn được tham số hóa, chúng ta có thể chuyển các biến Python dưới dạng tham số truy vấn trong đó phần giữ chỗ [%s] được sử dụng cho các tham số .

SQL trong Python là gì?

SQL, viết tắt của ngôn ngữ truy vấn có cấu trúc , là ngôn ngữ lập trình trong đó người dùng truy vấn cơ sở dữ liệu quan hệ. Các nhà khoa học dữ liệu sử dụng SQL trong Python trong nhiều trường hợp khác nhau, được quyết định bởi trường hợp sử dụng hiện tại hoặc theo sở thích cá nhân.

Các loại câu lệnh SQL khác nhau là gì?

Các loại câu lệnh SQL .
Tuyên bố Ngôn ngữ Định nghĩa Dữ liệu [DDL]
Ngôn ngữ thao tác dữ liệu [DML]
Báo cáo kiểm soát giao dịch
Báo cáo kiểm soát phiên
Tuyên bố kiểm soát hệ thống
Câu lệnh SQL nhúng

Bài Viết Liên Quan

Mã hóa JavaScript trực tuyến
Php có cho phép bạn gửi email trực tiếp từ tập lệnh không?
Bảng xếp hạng cầu thủ NBA của ESPN 2023
Làm nổi bật excel
Chuyên gia lập trình python pdf github
Bạn có thể nhập Python vào Excel không?
Google Trang tính hôm nay trong tháng
Xóa thẻ bắt đầu bằng # khỏi văn bản python
10 cm vuông 6mm vuông bằng bao nhiêu mm vuông
Tải xuống tệp Python
Cách nhân đôi từng phần tử trong danh sách python
Cách lấy dữ liệu từ API và hiển thị trong bảng HTML
Làm cách nào để sử dụng mệnh đề WHERE trong Python SQL?
Làm cách nào để kiểm tra kết nối máy chủ trong PHP?
Một dòng mã python được gọi là gì?
Có bao nhiêu số có ba chữ số có hàng trăm bằng 3
Làm cách nào để thêm MATLAB trong Python?
Python đọc đầu ra PowerShell
Bảng Python-pptx
Colorado miễn ngưỡng tiền lương 2023

Toplist mới

#1
Top 9 review kem chống nắng cho bà bầu 2023
6 tháng trước
#2
Top 5 tiếng anh lớp 2 unit 7 trang 46 2023
6 tháng trước
#3
Top 10 tải: mẫu the nhân viên trên excel 2023
6 tháng trước
#4
Top 7 tuyển dụng nhân viên chốt đơn tại nhà 2023
6 tháng trước
#5
Top 7 mẫu nhà 2 tầng chữ l 100m2 mái bằng 2023
6 tháng trước
#6
Top 4 truyện ngắn về quê hương lớp 2 2023
6 tháng trước
#7
Top 6 sơ đồ bộ máy nhà nước thời hồ 2023
6 tháng trước
#8
Top 8 trước việc nhật đảo chính pháp, đảng ta có chủ trương gì mới 2023
6 tháng trước
#9
Top 7 dân số đông đã đem đến cho nước ta 2023
6 tháng trước

Bài mới nhất

Căn hộ city land nguyễn văn lượng bàn giao chưa năm 2024
Kế toán trưởng và trưởng phòng kế toán năm 2024
Lỗi app cant your a pc dragon ball xenoverse 2 năm 2024
Sửa lỗi ảnh không mở được sau khi khôi phục năm 2024
Hội thánh phúc âm ngũ tuần là gì năm 2024
Bộ nhận dạng thương hiệu tiếng anh là gì năm 2024
Kê khai thuế tncn của văn phòng đại diện năm 2024
Bài8 sgk toán 6 tập 2 trang 75 năm 2024
Download top 100 bài hát hay nhất 2023 năm 2024
Quá trình nào tạo ra nhiều năng lượng nhất năm 2024

Chủ Đề

Toplist programming Địa Điểm Hay Hỏi Đáp Là gì Mẹo Hay Nghĩa của từ Học Tốt Công Nghệ Khỏe Đẹp bao nhiêu mẹo hay Top List Bao nhiêu Sản phẩm tốt Bài Tập Xây Đựng Ngôn ngữ đánh giá Tiếng anh Bài tập So Sánh Ở đâu So sánh Hướng dẫn Dịch bao nhieu Tại sao Đại học hướng dẫn Máy tính Thế nào Vì sao Bao lâu Khoa Học Hà Nội