انتقال دیکشنری Python به SQL Server با JSON

انتقال داده دیکشنری Python به SQL Server: راهنمای کامل با JSON

Python و SQL Server دو ابزار قدرتمند هستند که معمولاً در پروژه‌های مختلف استفاده می‌شوند. پایتون به دلیل کتابخانه‌های گسترده خود برای دستکاری داده‌ها و SQL Server به عنوان یک سیستم مدیریت پایگاه داده رابطه‌ای (RDBMS) شناخته شده است. اغلب نیاز به انتقال داده‌ها بین این دو محیط وجود دارد. این مقاله به بررسی چگونگی انتقال جفت‌های کلید-مقدار (Key-Value Pairs) از دیکشنری‌های پایتون به SQL Server با استفاده از فرمت JSON می‌پردازد.

تصور کنید مجموعه‌ای از داده‌های بدون ساختار یا نیمه‌ساختار یافته دارید که می‌خواهید آن‌ها را در SQL Server ذخیره کنید. استفاده از JSON یک راهکار عالی برای این منظور است، زیرا به شما امکان می‌دهد این نوع داده‌ها را به طور کارآمد مدیریت کنید.

در اینجا یک نمونه دیکشنری پایتون آورده شده است که شامل اطلاعات مشتریان است:

customer_data = {
    'customers': [
        {
            'customer_id': 1,
            'name': 'Alice Smith',
            'email': 'alice@example.com',
            'phone': '123-456-7890',
            'address': {
                'street': '123 Main St',
                'city': 'Anytown',
                'zip': '12345'
            },
            'orders': [
                {'order_id': 101, 'amount': 150.00},
                {'order_id': 102, 'amount': 200.50}
            ]
        },
        {
            'customer_id': 2,
            'name': 'Bob Johnson',
            'email': 'bob@example.com',
            'phone': '987-654-3210',
            'address': {
                'street': '456 Oak Ave',
                'city': 'Otherville',
                'zip': '67890'
            },
            'orders': [
                {'order_id': 201, 'amount': 75.25}
            ]
        }
    ]
}

برای ذخیره این داده‌ها در SQL Server، یک جدول ساده با یک ستون برای ذخیره متن JSON کافی است. این رویکرد به شما انعطاف‌پذیری می‌دهد تا بدون نیاز به تغییر طرح‌واره جدول، داده‌های با ساختارهای متغیر را مدیریت کنید.

CREATE TABLE CustomerJson (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerData NVARCHAR(MAX)
);

اکنون، یک اسکریپت پایتون را مشاهده می‌کنید که دیکشنری را به فرمت JSON تبدیل کرده و سپس آن را در جدول `CustomerJson` در SQL Server درج می‌کند. این اسکریپت از کتابخانه‌های `pyodbc` برای اتصال به SQL Server و `json` برای کار با داده‌های JSON استفاده می‌کند.

import pyodbc
import json

# Connection details for SQL Server
server = 'YOUR_SERVER_NAME' # e.g., 'localhost\SQLEXPRESS'
database = 'YourDatabase'
username = 'YourUsername'
password = 'YourPassword'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

customer_data = {
    'customers': [
        {
            'customer_id': 1,
            'name': 'Alice Smith',
            'email': 'alice@example.com',
            'phone': '123-456-7890',
            'address': {
                'street': '123 Main St',
                'city': 'Anytown',
                'zip': '12345'
            },
            'orders': [
                {'order_id': 101, 'amount': 150.00},
                {'order_id': 102, 'amount': 200.50}
            ]
        },
        {
            'customer_id': 2,
            'name': 'Bob Johnson',
            'email': 'bob@example.com',
            'phone': '987-654-3210',
            'address': {
                'street': '456 Oak Ave',
                'city': 'Otherville',
                'zip': '67890'
            },
            'orders': [
                {'order_id': 201, 'amount': 75.25}
            ]
        }
    ]
}

# Convert dictionary to JSON string
json_data = json.dumps(customer_data, indent=4)

# SQL INSERT statement
sql_insert = "INSERT INTO CustomerJson (CustomerData) VALUES (?)"

# Execute the insert
cursor.execute(sql_insert, json_data)
cnxn.commit()

print("Data successfully inserted into SQL Server.")

cursor.close()
cnxn.close()

برای تأیید اینکه داده‌ها به درستی درج شده‌اند، می‌توانید از یک کوئری ساده SQL استفاده کنید:

SELECT * FROM CustomerJson;

بعد از درج داده‌ها، مهم است که بتوانید اطلاعات مورد نیاز را از ستون JSON در SQL Server استخراج و تجزیه کنید. SQL Server توابع داخلی قدرتمندی برای کار با JSON ارائه می‌دهد.

تابع `OPENJSON` در SQL Server برای تجزیه و تحلیل یک متن JSON و تبدیل آن به یک مجموعه سطر (rowset) استفاده می‌شود. این تابع امکان دسترسی به عناصر مختلف JSON را فراهم می‌کند.

OPENJSON(json_string_expression [, path])

در این تابع، `json_string_expression` متن JSON و `path` یک عبارت مسیر JSON است که عناصر مورد نظر را مشخص می‌کند.

به عنوان مثال، برای مشاهده تمامی داده‌های مشتریان از ستون `CustomerData` در قالب یک جدول، می‌توانید از کوئری زیر استفاده کنید:

SELECT
    A.ID,
    JSON_VALUE(customer.value, '$.customer_id') AS CustomerId,
    JSON_VALUE(customer.value, '$.name') AS CustomerName,
    JSON_VALUE(customer.value, '$.email') AS CustomerEmail,
    JSON_VALUE(customer.value, '$.address.city') AS CustomerCity
FROM
    CustomerJson AS A
CROSS APPLY OPENJSON(A.CustomerData, '$.customers') AS customer;

همچنین، تابع `JSON_VALUE` برای استخراج یک مقدار اسکالر (مانند یک رشته یا عدد) از یک رشته JSON استفاده می‌شود. این تابع زمانی مفید است که نیاز به دریافت یک مقدار خاص از یک مسیر مشخص در JSON دارید.

JSON_VALUE(expression, path)

در اینجا، `expression` رشته JSON و `path` مسیر دقیق به مقدار مورد نظر است.

به عنوان مثال، برای دریافت تنها نام مشتری اول (که در مسیر `$.customers[0].name` قرار دارد)، می‌توانید از کوئری زیر استفاده کنید:

SELECT JSON_VALUE(CustomerData, '$.customers[0].name') AS FirstCustomerName
FROM CustomerJson;

این روش، انتقال داده‌های دیکشنری پایتون به SQL Server را از طریق JSON ساده و کارآمد می‌سازد و انعطاف‌پذیری لازم برای مدیریت داده‌های نیمه‌ساختاریافته را فراهم می‌آورد.