Hướng dẫn insert json to sql server python - chèn json vào Python server sql

Đối số thứ hai cho

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

9 phải là một chuỗi các giá trị để nội suy, một đối số cho mỗi tham số SQL trong câu lệnh của bạn.

Thay vào đó, bạn đã cho ODBC một chuỗi, đó cũng là một chuỗi, nhưng một chuỗi chứa [nhiều] phần tử [ký tự] nhiều hơn so với truy vấn của bạn yêu cầu.

Sử dụng một bộ tuple một phần tử ở đây:

cursor.execute["Insert Into Ticket_Info values [?]", [json.dumps[record],]]

Tôi cũng đặt dấu ngoặc đơn xung quanh phần Giá trị, theo cú pháp SQL Server

CREATE TABLE [dbo].[TableJSON][
    [Dates] [datetime] NULL,
    [Type] [varchar][1] NULL,
    [Code] [varchar][20] NULL,
    [Price] [money] NULL,
    [Unit] [int] NULL,
    [SubTotal] [money] NULL,
    [Brokeage Rate] [money] NULL,
    [Tax] [money] NULL,
    [Stamp Duty] [money] NULL,
    [Clearing Fee] [money] NULL,
    [Amount] [money] NULL,
    [Svc Cost] [money] NULL
] ON [PRIMARY]
GO

0:

CREATE TABLE [dbo].[TableJSON][
    [Dates] [datetime] NULL,
    [Type] [varchar][1] NULL,
    [Code] [varchar][20] NULL,
    [Price] [money] NULL,
    [Unit] [int] NULL,
    [SubTotal] [money] NULL,
    [Brokeage Rate] [money] NULL,
    [Tax] [money] NULL,
    [Stamp Duty] [money] NULL,
    [Clearing Fee] [money] NULL,
    [Amount] [money] NULL,
    [Svc Cost] [money] NULL
] ON [PRIMARY]
GO

1 giới thiệu danh sách hoặc danh sách các giá trị dữ liệu sẽ được chèn. Phải có một giá trị dữ liệu cho mỗi cột trong cột_list, nếu được chỉ định hoặc trong bảng. Danh sách giá trị phải được đính kèm trong ngoặc đơn.
Introduces the list or lists of data values to be inserted. There must be one data value for each column in column_list, if specified, or in the table. The value list must be enclosed in parentheses.

Trừ khi

CREATE TABLE [dbo].[TableJSON][
    [Dates] [datetime] NULL,
    [Type] [varchar][1] NULL,
    [Code] [varchar][20] NULL,
    [Price] [money] NULL,
    [Unit] [int] NULL,
    [SubTotal] [money] NULL,
    [Brokeage Rate] [money] NULL,
    [Tax] [money] NULL,
    [Stamp Duty] [money] NULL,
    [Clearing Fee] [money] NULL,
    [Amount] [money] NULL,
    [Svc Cost] [money] NULL
] ON [PRIMARY]
GO

2 chỉ có một cột mỗi hàng [không thể xảy ra, bạn sẽ có một cột chính chính ít nhất], bạn có thể cần chỉ định cột nào bạn đang chèn giá trị của mình vào:

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

nơi bạn cần thay thế

CREATE TABLE [dbo].[TableJSON][
    [Dates] [datetime] NULL,
    [Type] [varchar][1] NULL,
    [Code] [varchar][20] NULL,
    [Price] [money] NULL,
    [Unit] [int] NULL,
    [SubTotal] [money] NULL,
    [Brokeage Rate] [money] NULL,
    [Tax] [money] NULL,
    [Stamp Duty] [money] NULL,
    [Clearing Fee] [money] NULL,
    [Amount] [money] NULL,
    [Svc Cost] [money] NULL
] ON [PRIMARY]
GO

3 bằng tên cột thực tế trong bảng của bạn.

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

6] Sử dụng SQL Server Management Studio - sp_execute_external_script

Do sự phổ biến của các chương trình học máy, từ SQL Server 2016 trở đi, chúng tôi có thể thực thi các tập lệnh Python và R trong môi trường SQL Server, đặc biệt là & NBSP; SP_execute_external_script đang được sử dụng.

Trước khi chúng tôi chạy qua các tập lệnh, chúng tôi cần thực hiện một số cấu hình:

a] Thực hiện tập lệnh SQL bên dưới để bật cài đặt:
File "C:\Users\trees\RESTAPI_Python_Code\Test2.py", line 70, in
cursor.execute[['INSERT INTO dbo.bms[[Value],[Unit],[WatchName]]'],[json.dumps[data]]]
File "C:\Python310\lib\json_init.py", line 231, in dumps
return default_encoder.encode[obj]
File "C:\Python310\lib\json\encoder.py", line 199, in encode
chunks = self.iterencode[o, one_shot=True]
File "C:\Python310\lib\json\encoder.py", line 257, in iterencode
return iterencode[o, 0]
File "C:\Python310\lib\json\encoder.py", line 179, in default
raise TypeError[f'Object of type {o.class.name} '
TypeError: Object of type bytes is not JSON serializable

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

6] Sử dụng SQL Server Management Studio - sp_execute_external_script

Do sự phổ biến của các chương trình học máy, từ SQL Server 2016 trở đi, chúng tôi có thể thực thi các tập lệnh Python và R trong môi trường SQL Server, đặc biệt là & NBSP; SP_execute_external_script đang được sử dụng.

Trước khi chúng tôi chạy qua các tập lệnh, chúng tôi cần thực hiện một số cấu hình:Step 1. Create the table structure for Table TableJSON by executing the SQL command below:

CREATE TABLE [dbo].[TableJSON][
    [Dates] [datetime] NULL,
    [Type] [varchar][1] NULL,
    [Code] [varchar][20] NULL,
    [Price] [money] NULL,
    [Unit] [int] NULL,
    [SubTotal] [money] NULL,
    [Brokeage Rate] [money] NULL,
    [Tax] [money] NULL,
    [Stamp Duty] [money] NULL,
    [Clearing Fee] [money] NULL,
    [Amount] [money] NULL,
    [Svc Cost] [money] NULL
] ON [PRIMARY]
GO

a] Thực hiện tập lệnh SQL bên dưới để bật cài đặt: Copy and paste the following JSON Data to a physical file and named it as source.json or you can download a copy of source.json file here.

b] Khởi động lại các dịch vụ máy chủ SQL.

How to load JSON's data into SQL Server

Sau khi thực hiện những điều này, chúng tôi có thể thực thi tập lệnh SQL bên dưới:
2 ]   Using SSIS in Visual Studio
3 ]   Using PowerShell
4 ]   Using Node.js
5 ]   Using Python
6 ]   Using SQL Server Management Studio - sp_execute_external_script command
7 ]   Using .NET [C#]
8 ]   Using PHP
9 ]   Using Java
10 ] Using Golang

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

7] Sử dụng .NET [C#]1 ] Using SQL Server Management Studio - OPENROWSET + OPENJSON Commands

Trong môi trường .NET, chúng ta có thể sử dụng đối tượng SQLBulKCopy để nhập dữ liệu hiệu quả vào cơ sở dữ liệu.

--This is for testing purposes, so clean up the table
TRUNCATE TABLE TableJSON;


--Import the data to target table
INSERT INTO TableJSON
SELECT tbl.*
FROM OPENROWSET [BULK 'C:\yourpath\source.json', SINGLE_CLOB] js
CROSS APPLY OPENJSON[BulkColumn]
WITH
[
    [Dates] [datetime],
    [Type] [varchar][1],
    [Code] [varchar][20],
    [Price] [money],
    [Unit] [int],
    [SubTotal] [money],
    [Brokeage Rate] [money],
    [Tax] [money],
    [Stamp Duty] [money],
    [Clearing Fee] [money],
    [Amount] [money],
    [Svc Cost] [money]
] tbl;

Lưu ý: Bạn có thể gặp phải lỗi sau khi bạn thực thi lệnh OpenRowset.

& nbsp; SQL Server đã chặn truy cập vào câu lệnh 'OpenRowset/OpenDataSource' của thành phần 'Truy vấn phân phối ad hoc' vì thành phần này bị tắt như một phần của cấu hình bảo mật cho máy chủ này. Quản trị viên hệ thống có thể cho phép sử dụng 'các truy vấn phân tán ad hoc' bằng cách sử dụng sp_configure. Để biết thêm thông tin về việc cho phép 'các truy vấn phân tán ad hoc', hãy tìm kiếm 'các truy vấn phân tán ad hoc' trong sách SQL Server trực tuyến. & NBSP;

Để giải quyết vấn đề, bạn sẽ cần thực hiện các lệnh SQL sau:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0' , N'AllowInProcess' , 1
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0' , N'DynamicParameters' , 1
GO

 

Lệnh OpenJson SQL tương đối mới trong SQL Server nhưng chúng tôi nhận ra rằng nó đã bắt đầu trở nên phổ biến trong số những người dùng SQL Server vì nó có thể được sử dụng để đọc dữ liệu ở định dạng JSON một cách dễ dàng.

Nếu tất cả được thực hiện đúng, bạn sẽ có thể thấy dữ liệu được chèn thành công.

Sau đó, bạn có thể kiểm tra dữ liệu được nhập vào & nbsp; Bảng: TableJson trong SQL Server.table: TableJSON in SQL Server.

2] Sử dụng SSIS trong Visual Studio

& nbsp; Để tạo dự án SSIS trong Visual Studio, trước tiên bạn sẽ cần tải xuống và cài đặt riêng các công cụ dữ liệu SQL Server [SSDT].SQL Server Data Tools [SSDT] separately.

& nbsp; & nbsp; Tải xuống Công cụ dữ liệu SQL Server [SSDT] ​​cho Visual Studio

& nbsp; Sau khi được cài đặt và có sẵn, bạn sẽ có thể thấy một ghi chú cây mới có tên là Business Intelligence, sau đó chọn Dự án Dịch vụ Tích hợp để tiếp tục.Business Intelligence, and then select Integrated Services Project to continue.

Để bắt đầu, chúng ta có thể kéo và thả tác vụ SQL thực thi vào luồng điều khiển.Execute SQL Task into the Control Flow.

Sau đó, chúng ta cần định cấu hình kết nối cũng như SQLStatement sẽ được thực thi, đó là để làm sạch bảng đích với SQL bên dưới:Connection as well as the SQLStatement to be executed, which is to clean up the target table with SQL below:

Truncate Table TableJSON;

Sau đó, kéo và thả một tác vụ luồng dữ liệu bên dưới tác vụ SQL thực thi mà chúng tôi đã tạo.Data Flow Task underneath the Execute SQL Task that we have created.

Khi điều này được thực hiện, chọn tác vụ SQL thực thi và mũi tên màu xanh lá cây sẽ xuất hiện.

Kéo mũi tên màu xanh lá cây lên đỉnh của tác vụ luồng dữ liệu và liên kết chúng với nhau.

Bây giờ, hãy nhấp đúp vào tác vụ luồng dữ liệu và nó sẽ đưa chúng ta đến tab luồng dữ liệu.Data Flow tab.

Luồng dữ liệu là nơi chúng tôi chọn, chuyển đổi nguồn dữ liệu và sau đó tải nó vào đích dữ liệu.

Đầu tiên, chúng ta cần chọn và kéo nguồn OLE DB từ hộp công cụ SSIS> Các nguồn khác vào không gian làm việc luồng dữ liệu.OLE DB Source from SSIS Toolbox > Other Sources into the Data Flow workspace.

Nhấp đúp chuột vào nguồn OLE DB để chỉnh sửa trình quản lý kết nối, ánh xạ cột, xử lý lỗi.

Đối với chế độ truy cập dữ liệu, chúng tôi cần chọn lệnh SQL và nhập SQL sau, chính xác là các lệnh OpenRowset + OpenJSON trong phương thức trước vào văn bản lệnh SQL.Data access mode, we need to select SQL command and enter the following SQL, which is exactly the OPENROWSET + OPENJSON commands in the previous method to SQL command text.

SELECT tbl.*
FROM OPENROWSET [BULK 'C:\yourpath\source.json', SINGLE_CLOB] js
CROSS APPLY OPENJSON[BulkColumn]
WITH
[
    [Dates] [datetime],
    [Type] [varchar][1],
    [Code] [varchar][20],
    [Price] [money],
    [Unit] [int],
    [SubTotal] [money],
    [Brokeage Rate] [money],
    [Tax] [money],
    [Stamp Duty] [money],
    [Clearing Fee] [money],
    [Amount] [money],
    [Svc Cost] [money]
] tbl;

Khi điều này được thực hiện, sau đó chúng ta cần chọn và kéo điểm đến OLE DB từ hộp công cụ SSIS> Các điểm đến khác vào không gian làm việc luồng dữ liệu.OLE DB Destination from SSIS Toolbox > Other Destinations into the Data Flow workspace.

Sau này, liên kết các luồng với nhau.

Hãy thử thực thi gói bằng cách nhấp vào nút Bắt đầu [hoặc nhấn F5].Start [or press F5] button.

Bạn có thể thấy rằng gói đang được thực hiện thành công.

Sau đó, bạn có thể kiểm tra dữ liệu được nhập vào & nbsp; Bảng: TableJson trong SQL Server.table: TableJSON in SQL Server.

2] Sử dụng SSIS trong Visual Studio

& nbsp; Để tạo dự án SSIS trong Visual Studio, trước tiên bạn sẽ cần tải xuống và cài đặt riêng các công cụ dữ liệu SQL Server [SSDT].

& nbsp; & nbsp; Tải xuống Công cụ dữ liệu SQL Server [SSDT] ​​cho Visual Studio

• & nbsp; Sau khi được cài đặt và có sẵn, bạn sẽ có thể thấy một ghi chú cây mới có tên là Business Intelligence, sau đó chọn Dự án Dịch vụ Tích hợp để tiếp tục.

Để bắt đầu, chúng ta có thể kéo và thả tác vụ SQL thực thi vào luồng điều khiển.

Install-Module dbatools

Sau đó, chúng ta cần định cấu hình kết nối cũng như SQLStatement sẽ được thực thi, đó là để làm sạch bảng đích với SQL bên dưới:

$TargetServer = 'ServerName'
$TargetDb = 'DatabaseName'
$TableName = 'TableJSON'

$SourcePath = 'C:\yourpath\source.json'

try{

    $data = Get-Content $SourcePath | ConvertFrom-Json
   
    $data | ConvertTo-DbaDataTable | Write-DbaDbTableData -SqlInstance $TargetServer -Database $TargetDb -Table $TableName -Truncate -Confirm:$false;

    [[Get-Date].ToString[] + " | File Imported: " + $SourcePath];
}
catch
{
    $ErrorMessage = $_.Exception.Message
    $FailedItem = $_.Exception.ItemName
    [[Get-Date].ToString[] + " | Error: " + $ErrorMessage + " - " + $FailedItem];
}

Sau đó, kéo và thả một tác vụ luồng dữ liệu bên dưới tác vụ SQL thực thi mà chúng tôi đã tạo.

Khi điều này được thực hiện, chọn tác vụ SQL thực thi và mũi tên màu xanh lá cây sẽ xuất hiện.

Kéo mũi tên màu xanh lá cây lên đỉnh của tác vụ luồng dữ liệu và liên kết chúng với nhau.

Sau đó, bạn có thể kiểm tra dữ liệu được nhập vào & nbsp; Bảng: TableJson trong SQL Server.table: TableJSON in SQL Server.

2] Sử dụng SSIS trong Visual Studio

& nbsp; Để tạo dự án SSIS trong Visual Studio, trước tiên bạn sẽ cần tải xuống và cài đặt riêng các công cụ dữ liệu SQL Server [SSDT].

• & nbsp; & nbsp; Tải xuống Công cụ dữ liệu SQL Server [SSDT] ​​cho Visual Studio

& nbsp; Sau khi được cài đặt và có sẵn, bạn sẽ có thể thấy một ghi chú cây mới có tên là Business Intelligence, sau đó chọn Dự án Dịch vụ Tích hợp để tiếp tục.

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

0

Để bắt đầu, chúng ta có thể kéo và thả tác vụ SQL thực thi vào luồng điều khiển.

Sau đó, chúng ta cần định cấu hình kết nối cũng như SQLStatement sẽ được thực thi, đó là để làm sạch bảng đích với SQL bên dưới:

Sau đó, kéo và thả một tác vụ luồng dữ liệu bên dưới tác vụ SQL thực thi mà chúng tôi đã tạo.TCP/IP setting is enabled under Protocols for MSSQLServer. We can find this setting by launching Computer Management and then look for Services and Applications > SQL Server Configuration Manager > SQL Server Network Configuration.

Khi điều này được thực hiện, chọn tác vụ SQL thực thi và mũi tên màu xanh lá cây sẽ xuất hiện.

Sau đó, bạn có thể kiểm tra dữ liệu được nhập vào & nbsp; Bảng: TableJson trong SQL Server.table: TableJSON in SQL Server.

5] Sử dụng Python

Việc thực hiện trong Python cũng dễ dàng.

Trước khi thực hiện, bạn cần cài đặt các mô -đun sau:

• gấu trúc
• Flask-SQLAlchemy

Chúng tôi có thể cài đặt hai thư viện này từ cài đặt PIP lệnh, chẳng hạn như:

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

1

Kịch bản được hiển thị như sau:

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

2

Chúng tôi đang sử dụng Pandas 'read_jsonmethod để tải JSON vào khung dữ liệu.

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

Sau đó, bạn có thể kiểm tra dữ liệu được nhập vào & nbsp; Bảng: TableJson trong SQL Server.table: TableJSON in SQL Server.

6] Sử dụng SQL Server Management Studio - sp_execute_external_script

Do sự phổ biến của các chương trình học máy, từ SQL Server 2016 trở đi, chúng tôi có thể thực thi các tập lệnh Python và R trong môi trường SQL Server, đặc biệt là & NBSP; SP_execute_external_script đang được sử dụng.sp_execute_external_script command is being used.

Trước khi chúng tôi chạy qua các tập lệnh, chúng tôi cần thực hiện một số cấu hình:

a] Thực hiện tập lệnh SQL bên dưới để bật cài đặt:

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

3

b] Khởi động lại các dịch vụ máy chủ SQL.

Sau khi thực hiện những điều này, chúng tôi có thể thực thi tập lệnh SQL bên dưới:

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

4

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

Sau đó, bạn có thể kiểm tra dữ liệu được nhập vào & nbsp; Bảng: TableJson trong SQL Server.table: TableJSON in SQL Server.

6] Sử dụng SQL Server Management Studio - sp_execute_external_script

Do sự phổ biến của các chương trình học máy, từ SQL Server 2016 trở đi, chúng tôi có thể thực thi các tập lệnh Python và R trong môi trường SQL Server, đặc biệt là & NBSP; SP_execute_external_script đang được sử dụng.

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

5

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

Sau đó, bạn có thể kiểm tra dữ liệu được nhập vào & nbsp; Bảng: TableJson trong SQL Server.table: TableJSON in SQL Server.

6] Sử dụng SQL Server Management Studio - sp_execute_external_script

Do sự phổ biến của các chương trình học máy, từ SQL Server 2016 trở đi, chúng tôi có thể thực thi các tập lệnh Python và R trong môi trường SQL Server, đặc biệt là & NBSP; SP_execute_external_script đang được sử dụng.

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

6

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

Sau đó, bạn có thể kiểm tra dữ liệu được nhập vào & nbsp; Bảng: TableJson trong SQL Server.table: TableJSON in SQL Server.

6] Sử dụng SQL Server Management Studio - sp_execute_external_script

Do sự phổ biến của các chương trình học máy, từ SQL Server 2016 trở đi, chúng tôi có thể thực thi các tập lệnh Python và R trong môi trường SQL Server, đặc biệt là & NBSP; SP_execute_external_script đang được sử dụng.

Trước khi chúng tôi chạy qua các tập lệnh, chúng tôi cần thực hiện một số cấu hình:

• a] Thực hiện tập lệnh SQL bên dưới để bật cài đặt:
• b] Khởi động lại các dịch vụ máy chủ SQL.
• JSON-java
• json-simple

Sau khi thực hiện những điều này, chúng tôi có thể thực thi tập lệnh SQL bên dưới:

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

• 7] Sử dụng .NET [C#]
• Trong môi trường .NET, chúng ta có thể sử dụng đối tượng SQLBulKCopy để nhập dữ liệu hiệu quả vào cơ sở dữ liệu.

8] Sử dụng PHP

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

7

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

7] Sử dụng .NET [C#]table: Sheet1$ in SQL Server.

10 ] Using Golang

Trong môi trường .NET, chúng ta có thể sử dụng đối tượng SQLBulKCopy để nhập dữ liệu hiệu quả vào cơ sở dữ liệu.

8] Sử dụng PHP

cursor.execute["Insert Into Ticket_Info [] values [?]", [json.dumps[record],]]

8

Bạn sẽ có thể thấy rằng tập lệnh đang được thực thi thành công.

Sau đó, bạn có thể kiểm tra dữ liệu được nhập vào & nbsp; Bảng: TableJson trong SQL Server.table: TableJSON in SQL Server.