Python thực thi nhiều câu lệnh SQL

Về nhiệm vụ này

Nếu lời nhắc xác nhận được bật, lời nhắc xác nhận sẽ hiển thị sau mỗi câu lệnh CAM KẾT và sau câu lệnh cuối cùng trong truy vấn. Tuy nhiên, một số câu lệnh SQL, chẳng hạn như SET, áp dụng cho phiên hoặc môi trường QMF và không hiển thị lời nhắc xác nhận. Nếu truy vấn không chứa câu lệnh COMMIT, một lời nhắc xác nhận duy nhất sẽ được hiển thị. Phản hồi của bạn đối với lời nhắc áp dụng cho tất cả các thay đổi do truy vấn thực hiện

Nếu một truy vấn chứa nhiều câu lệnh chèn, cập nhật hoặc xóa các hàng trong đối tượng cơ sở dữ liệu, phản hồi của bạn đối với lời nhắc xác nhận sẽ áp dụng cho tất cả các bản cập nhật cơ sở dữ liệu. Thông tin trong lời nhắc xác nhận chứa tổng số hàng được cập nhật bởi tất cả các câu lệnh trong truy vấn. Nếu truy vấn không chứa câu lệnh chèn, cập nhật hoặc xóa hàng và không xóa đối tượng khỏi cơ sở dữ liệu, lời nhắc xác nhận sẽ không hiển thị. Nếu truy vấn không chứa các câu lệnh chèn, cập nhật hoặc xóa hàng nhưng xóa đối tượng khỏi cơ sở dữ liệu, lời nhắc xác nhận DROP sẽ hiển thị. Phản hồi của bạn đối với lời nhắc trong trường hợp này áp dụng cho cả câu lệnh DROP và các câu lệnh khác trong truy vấn

Mọi giá trị biến mà bạn cung cấp cho truy vấn sẽ áp dụng cho tất cả các câu lệnh SQL trong truy vấn bao gồm một biến có tên đó. Ví dụ: nếu bạn cung cấp giá trị "Q. NHÂN VIÊN" cho biến &TABLE trong truy vấn sau, cả câu lệnh UPDATE và INSERT đều thay đổi Q. bàn NHÂN VIÊN.

Nếu truy vấn chứa nhiều câu lệnh và một trong các câu lệnh không thành công, quá trình xử lý sẽ dừng và không có câu lệnh tiếp theo nào được chạy. Nếu các câu lệnh trước câu lệnh bị lỗi đã thay đổi cơ sở dữ liệu, thì những thay đổi này sẽ được khôi phục (không áp dụng cho cơ sở dữ liệu) trừ khi truy vấn chứa câu lệnh CAM KẾT. Nếu truy vấn chứa một hoặc nhiều câu lệnh COMMIT, thì tất cả các thay đổi cơ sở dữ liệu xảy ra trước lỗi SQL và sau câu lệnh COMMIT thành công cuối cùng sẽ được khôi phục. Một số câu lệnh, chẳng hạn như SET, áp dụng cho phiên hoặc môi trường QMF™ và do đó không được khôi phục trong các tình huống lỗi. Biến toàn cục DSQEC_RUN_MQ kiểm soát xem có cho phép các truy vấn có nhiều câu lệnh SQL hay không. Để chạy truy vấn có nhiều câu lệnh, hãy đảm bảo rằng mỗi câu lệnh được phân tách bằng dấu chấm phẩy; . Khi biến được đặt thành 0, tất cả các câu lệnh sau dấu chấm phẩy đầu tiên sẽ bị bỏ qua

Độ dài tối đa cho phép của truy vấn SQL có thể chạy bằng lệnh RUN QUERY được xác định bởi cơ sở dữ liệu mà bạn được kết nối khi bạn đưa ra lệnh

• Trong DB2® dành cho z/OS® , các truy vấn SQL có thể dài tới 2 MB khi biến toàn cục DSQEC_SQLQRYSZ_2M được đặt thành 1. Khi biến được đặt thành 0, tất cả các câu lệnh sau câu lệnh đầu tiên trong truy vấn sẽ bị bỏ qua khi bạn đưa ra lệnh RUN QUERY.

  Giới thiệu về cơ sở dữ liệu trong Python

  Người bắt đầu

  4 giờ

  91. 2k

  In this course, you'll learn the basics of relational databases and how to interact with them.

  Xem chi tiết Mũi tên phải

  bắt đầu khóa học

  Giới thiệu về SQL

  Người bắt đầu

  2 giờ

  146. 5K

  Learn how to create and query relational databases using SQL in just two hours.

  Xem chi tiết Mũi tên phải

  bắt đầu khóa học

  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_id5 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_id6, 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_id7 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_id8, 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_id9, 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ư

  ________số 8_______

  Ở đây, chúng tôi giả định 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_id9 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 của các 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_id0

  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_id1

  Đặ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_id2Tí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 ta 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_id9 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_id7 ở 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ược điểm của chúng 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_id3

  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_id4

  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 sẽ 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

  Làm thế nào tôi có thể thực hiện một. Tệp SQL trong Python?

  Phương pháp tiếp cận .

  Bước 1. Trước tiên, chúng ta cần nhập mô-đun sqlite3 bằng Python

  Bước 2. Kết nối với cơ sở dữ liệu bằng cách tạo cơ sở dữ liệu. .

  Bước 3. Tạo đối tượng con trỏ sau khi thực hiện kết nối cơ sở dữ liệu

  Bước 4. Viết truy vấn SQL có thể thực thi được

  Bước 5. Thực thi đối tượng con trỏ

  Python có thể thực thi truy vấn SQL không?

  Một cách nhanh chóng và dễ dàng để có thể chạy các truy vấn SQL bằng Python là sử dụng SQLite . SQLite là một thư viện sử dụng công cụ cơ sở dữ liệu SQL. Nó hoạt động tương đối nhanh và đã được chứng minh là có độ tin cậy cao. SQLite là công cụ cơ sở dữ liệu được sử dụng phổ biến nhất trong môi trường thử nghiệm.