如何使用python的insert-into命令将数据从一个SQL服务器传输到另一个SQL服务器？

Question

我想使用python脚本将增量数据从一个SQL服务器传输到另一个SQL服务器。你知道吗

我已经做过了事情：你知道吗

import os

import pyodbc

#os.chdir("D:\TransOrg\Projects\PPAP\Files\Extrusion")
myConn1 = pyodbc.connect( driver="{SQL Server}",server="xxx.xxx.xxx.xxx",database="xxx",user="xxx",password="xxxx" )
myConn2 = pyodbc.connect( driver="{SQL Server}",server="xxx.xxx.xxx.xxx",database="xxx",user="xxx",password="xxx" )


cur1=myConn1.cursor()
cur2=myConn2.cursor()
print('Cursor established')
#cur.execute(''' drop table test_load ;''')
#cur.execute("create table test_load (name  varchar(40), age int) ON [PRIMARY]")
#db_cmd = '''SELECT Fact_Suborder_Journey_Key,SubOrderID,SubOrderDate,SubOrderDate_INT,DIM_Item_Key,DIM_Product_Key,DIM_Category_Key
#,Dim_Customer_Key,DIM_DTH_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_SalesBucket_Key
#,Cancelled_DATE,MarkedShipped_DATE,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,MarkedDelivered_DATE,Margin
#,GSV,ItemPrice,PayablePrice,PaidByGCAmt,PaidByDC,IsValid,IsSuperBooked,IsSameDayCancellation,IsMISvalid,IsCancelled,IsShipped
#,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested,IsProvisionalExecuted
#,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable,IsRenewType
#,IsDeleted,IsCallCenter,OrderType,IS_TV_PRODUCT,MarketPlaceId,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_Suborder_Delivery_Mode_Key,Dim_CustomerMDM_Key
#,Dim_NewVsRepeatCustomerMDM_Key
#FROM transorg_DW.dbo.Fact_Suborder_Journey
#where SubOrderDate between '2016-06-14 00:00:00' and '2016-06-15 23:59:59';'''

#res = cur1.execute(db_cmd)
#print (res)
#for r in res:
 #   print (r)


db_cmd2='''insert into transorg_DW.dbo.Fact_Suborder_Journey_inc (Fact_Suborder_Journey_Key,SubOrderID,SubOrderDate,SubOrderDate_INT,DIM_Item_Key,DIM_Product_Key,DIM_Category_Key
,Dim_Customer_Key,DIM_DTH_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_SalesBucket_Key
,Cancelled_DATE,MarkedShipped_DATE,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,MarkedDelivered_DATE,Margin
,GSV,ItemPrice,PayablePrice,PaidByGCAmt,PaidByDC,IsValid,IsSuperBooked,IsSameDayCancellation,IsMISvalid,IsCancelled,IsShipped
,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested,IsProvisionalExecuted
,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable,IsRenewType
,IsDeleted,IsCallCenter,OrderType,IS_TV_PRODUCT,MarketPlaceId,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_Suborder_Delivery_Mode_Key,Dim_CustomerMDM_Key
,Dim_NewVsRepeatCustomerMDM_Key)
select Fact_Suborder_Journey_Key,SubOrderID,SubOrderDate,SubOrderDate_INT,DIM_Item_Key,DIM_Product_Key,DIM_Category_Key
,Dim_Customer_Key,DIM_DTH_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_SalesBucket_Key
,Cancelled_DATE,MarkedShipped_DATE,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,MarkedDelivered_DATE,Margin
,GSV,ItemPrice,PayablePrice,PaidByGCAmt,PaidByDC,IsValid,IsSuperBooked,IsSameDayCancellation,IsMISvalid,IsCancelled,IsShipped
,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested,IsProvisionalExecuted
,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable,IsRenewType
,IsDeleted,IsCallCenter,OrderType,IS_TV_PRODUCT,MarketPlaceId,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_Suborder_Delivery_Mode_Key,Dim_CustomerMDM_Key
,Dim_NewVsRepeatCustomerMDM_Key from Fact_Suborder_Journey where SubOrderDate between '2016-06-14 00:00:00' and '2016-06-15 23:59:59';'''

res1=cur1.execute(db_cmd2)
cur1.commit()
cur1.close()
cur2.commit()
cur2.close()


#print("table created successfully")

正在获取db\u cmd1的输出，但我想运行db\u cmd2查询，我获取错误。 错误是：- SQL Server]对象名“HS18”无效_DW.dbo.fact\子订单\行程'. （208）（SQLExecDirectW）“）

Answer 1

如果有人想将增量数据从一个sql server传输到另一个sql server 使用此查询并根据您的请求进行修改要求：-你知道吗

我有266列，为什么我使用%s*266，您可以根据表定义使用它们。你知道吗

挑战1我必须插入日期列，因为在日期字段中，它应该用单引号“”括起来，字符串数据也应该用单引号括起来。
挑战2自动插入%s，我不想写%s 266次

import os
from os import listdir
from os.path import isfile, join
import pyodbc


myConn1 = pyodbc.connect( driver="{SQL Server}",server="xxx.xxx.xxx.xxx",database="xxx",user="xxx",password="xxx" )
#myConn2 = pyodbc.connect( driver="{SQL Server}",server="xxx.xxx.xxx.xxx",database="xxx",user="xxx",password="xxx" )


cur1=myConn1.cursor()
#cur2=myConn2.cursor()
print('Cursor established')
db_cmd = '''SELECT
Fact_Suborder_Journey_Key,SubOrderID,OrderID,SubOrderDate,SubOrderDate_INT,OrderDate,OrderDate_INT,DIM_Item_Key
,DIM_Product_Key,DIM_Category_Key,Dim_Customer_Key,DIM_DTH_Key,DIM_Vendor_Key,DIM_Warehouse_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key
,DIM_PaymentMode_Key,DIM_Courier_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_Dnis_Key,Dim_SalesBucket_Key
,PaymentReceived_DATE,Cancelled_DATE,CanceledForRefund_DATE,Verified_DATE,OrderShipped_DATE,MarkedShipped_DATE,OrderReshipped_DATE
,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,OUPWaitingForGatewayResponse_DATE,OrderUnderProcessCOD_DATE,RTO_DATE
,OrderDelivered_DATE,MarkedDelivered_DATE,OrderReturnToVendor_DATE,PaymentDeclined_DATE,LostInTransit_DATE,ORTV_Adjustment_DATE,RTO_Delivered_DATE
,DeliveryAttemptFailed_DATE,ReplacementRequested_DATE,ReturnByCustomerRequested_DATE,Quantity,MRP,MSP
,Margin,GSV,ItemPrice,PriceAfterProductDis,PriceAfterSellerDis,PriceAfterAllDis,PriceAfterMembershipDis,PriceAfterPaymentDis,PayModeNCharge
,AddOnNCharge,ShipNcharge,GiftNcharge,PayablePrice,AdvPaymentAmt,NCAmount,PaidByGCAmt,PaidByDC,RBC_Cost,IsValid,IsSuperBooked,IsSameDayCancellation
,IsMISvalid,IsCancelled,IsShipped,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested
,IsProvisionalExecuted,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable
,IsRenewType,IsDeleted,IsCallCenter,OrderType,SubOrderProductItemID,CallSequenceNumber,AWB_Number_Forward,AWB_Number_Reverse,PaymentModeID,SubOrderStateID
,BranchCode,DTH_ID,OrderReason,DNIS_TYPE,LeadSource_SalesGroup,IS_TV_PRODUCT,MarketPlaceId,Dw_Update_Dt,Dw_Insert_Dt,Reporting_Date,Verified_By,Cancelled_or_CFR_By
,IsSystemVerified,IsSystemCancelled,DIM_SalesBucketProposed_Key,Executed_DATE,ProvisionalExecuted_DATE,OldSuborderId,OldSuborderDate,SubOrderProductPickupID
,SubOrderSellerID,SuborderCourierId,SuborderWarehouseId,OrderReturnToVendor_Actual_DATE,SubOrderReceiverCityID,SubOrderReceiverZip,Dim_CategoryMapping_Key
,CategoryName_L1,CategoryName_L2,PromisedDelivery_Date,Vendor_SLA,Courier_SLA,BookedToShipped_days,ShippedToDelivered_days,OrderCallerNo,HandedToCourier_DATE
,IsHandedToCourier,Dim_OrderDispatchType_Key,CargoReady_DATE,BookedToVerified_Minutes,VerifiedToPickList_Minutes,PickListToCargoReady_Minutes,CargoReadyToGatepass_Minutes
,ShippedToFirstAttempt_Minutes,FirstAttemptToDelivery_Minutes,PicklistGenerated_DATE,GatepassToShipped_Minutes,Canceled_Reason_Key,Canceled_Refund_Reason_Key,Refund_Reason_Key
,RTO_Reason_Key,Sales_Return_Reason_Key,RBC_Reason_Key,RFR_Reason_Key,CancelReason_Instruction_Key,CancelRefundReason_Instruction_Key,Dim_NewVsRepeatCustomer_Key,SameMonthRepeatedAfterDayGap
,Cancelled_DATE_INT,MarkedShipped_DATE_INT,MarkedDelivered_DATE_INT,OrderReturnToVendor_Actual_DATE_INT,MarkedSalesReturn_DATE_INT,RTO_DATE_INT,ReturnByCustomerRequested_DATE_INT
,SubOrder_TIME_ID,BookedToDoor_Hours,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_PriceBand_Key,DisputeRaised_DATE_INT,DisputeRejected_DATE_INT,NEFTFailed_DATE_INT
,NEFTInitiated_DATE_INT,RefundChequeDispatched_DATE_INT,RefundChequePrepared_DATE_INT,Refunded_DATE_INT,ReplacementInitiated_DATE_INT,ReversePickupDelivered_DATE_INT
,ReversePickupDone_DATE_INT,ReversePickupFailed_DATE_INT,ReversePickupRequiredForRefund_DATE_INT,FirstAttempt_DATE,SecondAttempt_DATE,ThirdAttempt_DATE,FourthAttempt_DATE
,FifthAttempt_DATE,SixthAttempt_DATE,FirstAttempt_ToStatusId,SecondAttempt_ToStatusId,ThirdAttempt_ToStatusId,FourthAttempt_ToStatusId,FifthAttempt_ToStatusId
,SixthAttempt_ToStatusId,IsVendorSLAbreached,IsCourierSLAbreached,NumberOfAttemptsToDeliver,NumberOfCourierAttempts,GatePassed_DATE,IsGatePassed,UserLogin,OrderCallRoute
,TSO_Type,IsReplacement,IsMarkedSaleReturn,IsRtoDelivered,Dim_BookedToShippedDaysBucket_Key,Dim_ShippedToDeliveredDaysBucket_Key,BookedToNOW_Days,ShippedToNOW_Days
,VerifiedToNOW_Days,GatePassedToNOW_Days,Dim_BookedToPendingShipmentDaysBucket_Key,Dim_ShippedToPendingDeliveryDaysBucket_Key,Dim_BookedToPendingVerifiedDaysBucket_Key
,Dim_VerifiedToPendingGatepassedDaysBucket_Key,Dim_GatepassedToPendingShipmentDaysBucket_Key,PaidByShippingPassAmount,CouponGroup,Dim_Suborder_Delivery_Mode_Key
,ORV_STATUS_DATE_INT,Dim_NDR_Reason_Key,Dim_Site_key,Dim_BookedToPendingShipmentAgeingBucket_Key,Dim_ShippedToPendingDeliveryAgeingBucket_Key,Dim_Offer_Key
 ,Dim_Deal_Key,Dim_Affiliate_key,First_DeliveryAttemptFailed_Courier_DATE,Marked_RTO_Delivered_DATE,RefundCouponDiscount,IsRefundCouponDiscount,ReverseLITDate
,ReverseLITDate_INT,IsReverseLIT,RefundRemark,Dim_CustomerMDM_Key,Payment_GatewayName,Payment_GatewayMID,Dim_NewVsRepeatCustomerMDM_Key,LITRemark,FWDLITRemark
,ReverseLITRemark,FWDLITRemark_MarkedBy,ReverseLITRemark_MarkedBy,ChequeNumber,ChequeAmount,ChequeBankName,ChequeDate,IsChequeIssued,RefundTransactionId,Refund_Amount
,ParentSuborder_GSV,ParentSuborder_ShippingCharge,ParentSuborder_Margin,ParentCouponCategory_Id,Colllected_ShippingCharge,ParentSuborder_Colllected_ShippingCharge
,Dim_Cancellation_Channel_Key,IsSCRefunded,Dim_RBC_Reason_Key,SubOrderSTID,Is_PrePaid_PayMode,OUP_PrepaidSMSDate,DIM_Actual_PaymentMode_Key,dim_brand_key
FROM table_name
WHERE SubOrderDate between convert(date,'2018-07-25 21:39:58') and convert(date,'2018-07-28 21:39:58');'''

res = cur1.execute(db_cmd)
res1=res.fetchall()
#print (res)
import datetime
for r in res1:
    print(list(r))
    data_list = list(r)
    for ind, val in enumerate(data_list):
        if val is None:
            data_list[ind] = 'NULL'
        elif isinstance(val, datetime.datetime):

            data_list[ind] = "'"+str(val.replace(microsecond=0))+"'"
        elif isinstance(val, str):
            data_list[ind] = "'"+str(val)+"'"
    print(tuple(data_list))
    str_concat = '%s,'*266
    form_str = '('+str_concat.strip(',')+')'
    val = form_str%tuple(data_list)

    db_cmd2='''insert into table_name_ofinsert(Fact_Suborder_Journey_Key,SubOrderID,OrderID,SubOrderDate,SubOrderDate_INT,OrderDate,OrderDate_INT,DIM_Item_Key
,DIM_Product_Key,DIM_Category_Key,Dim_Customer_Key,DIM_DTH_Key,DIM_Vendor_Key,DIM_Warehouse_Key,Dim_Acquisition_Channel_Key,DIM_Geography_Key
,DIM_PaymentMode_Key,DIM_Courier_Key,DIM_LeadSource_Key,Dim_MarketPlace_Key,Dim_Dnis_Key,Dim_SalesBucket_Key
,PaymentReceived_DATE,Cancelled_DATE,CanceledForRefund_DATE,Verified_DATE,OrderShipped_DATE,MarkedShipped_DATE,OrderReshipped_DATE
,MarkedReshipped_DATE,MarkedSalesReturn_DATE,SalesReturn_DATE,OUPWaitingForGatewayResponse_DATE,OrderUnderProcessCOD_DATE,RTO_DATE
,OrderDelivered_DATE,MarkedDelivered_DATE,OrderReturnToVendor_DATE,PaymentDeclined_DATE,LostInTransit_DATE,ORTV_Adjustment_DATE,RTO_Delivered_DATE
,DeliveryAttemptFailed_DATE,ReplacementRequested_DATE,ReturnByCustomerRequested_DATE,Quantity,MRP,MSP
,Margin,GSV,ItemPrice,PriceAfterProductDis,PriceAfterSellerDis,PriceAfterAllDis,PriceAfterMembershipDis,PriceAfterPaymentDis,PayModeNCharge
,AddOnNCharge,ShipNcharge,GiftNcharge,PayablePrice,AdvPaymentAmt,NCAmount,PaidByGCAmt,PaidByDC,RBC_Cost,IsValid,IsSuperBooked,IsSameDayCancellation
,IsMISvalid,IsCancelled,IsShipped,IsMarkedShipped,IsLostInTransit,IsNDR,IsRTO,IsORTV,IsDelivered,IsMarkedDelivered,IsExecuted,IsReturnRequested
,IsProvisionalExecuted,IsSalesReturnDone,IsBuyOrSell,IsSellerShipper,IsNCAwarded,IsMailSentOnStatusChange,IsAdvPayment,IsAdvPaymentPerc,IsPreorderable
,IsRenewType,IsDeleted,IsCallCenter,OrderType,SubOrderProductItemID,CallSequenceNumber,AWB_Number_Forward,AWB_Number_Reverse,PaymentModeID,SubOrderStateID
,BranchCode,DTH_ID,OrderReason,DNIS_TYPE,LeadSource_SalesGroup,IS_TV_PRODUCT,MarketPlaceId,Dw_Update_Dt,Dw_Insert_Dt,Reporting_Date,Verified_By,Cancelled_or_CFR_By
,IsSystemVerified,IsSystemCancelled,DIM_SalesBucketProposed_Key,Executed_DATE,ProvisionalExecuted_DATE,OldSuborderId,OldSuborderDate,SubOrderProductPickupID
,SubOrderSellerID,SuborderCourierId,SuborderWarehouseId,OrderReturnToVendor_Actual_DATE,SubOrderReceiverCityID,SubOrderReceiverZip,Dim_CategoryMapping_Key
,CategoryName_L1,CategoryName_L2,PromisedDelivery_Date,Vendor_SLA,Courier_SLA,BookedToShipped_days,ShippedToDelivered_days,OrderCallerNo,HandedToCourier_DATE
,IsHandedToCourier,Dim_OrderDispatchType_Key,CargoReady_DATE,BookedToVerified_Minutes,VerifiedToPickList_Minutes,PickListToCargoReady_Minutes,CargoReadyToGatepass_Minutes
,ShippedToFirstAttempt_Minutes,FirstAttemptToDelivery_Minutes,PicklistGenerated_DATE,GatepassToShipped_Minutes,Canceled_Reason_Key,Canceled_Refund_Reason_Key,Refund_Reason_Key
,RTO_Reason_Key,Sales_Return_Reason_Key,RBC_Reason_Key,RFR_Reason_Key,CancelReason_Instruction_Key,CancelRefundReason_Instruction_Key,Dim_NewVsRepeatCustomer_Key,SameMonthRepeatedAfterDayGap
,Cancelled_DATE_INT,MarkedShipped_DATE_INT,MarkedDelivered_DATE_INT,OrderReturnToVendor_Actual_DATE_INT,MarkedSalesReturn_DATE_INT,RTO_DATE_INT,ReturnByCustomerRequested_DATE_INT
,SubOrder_TIME_ID,BookedToDoor_Hours,Dim_DSS_Key,Dim_OrderShowTag_Key,Dim_PriceBand_Key,DisputeRaised_DATE_INT,DisputeRejected_DATE_INT,NEFTFailed_DATE_INT
,NEFTInitiated_DATE_INT,RefundChequeDispatched_DATE_INT,RefundChequePrepared_DATE_INT,Refunded_DATE_INT,ReplacementInitiated_DATE_INT,ReversePickupDelivered_DATE_INT
,ReversePickupDone_DATE_INT,ReversePickupFailed_DATE_INT,ReversePickupRequiredForRefund_DATE_INT,FirstAttempt_DATE,SecondAttempt_DATE,ThirdAttempt_DATE,FourthAttempt_DATE
,FifthAttempt_DATE,SixthAttempt_DATE,FirstAttempt_ToStatusId,SecondAttempt_ToStatusId,ThirdAttempt_ToStatusId,FourthAttempt_ToStatusId,FifthAttempt_ToStatusId
,SixthAttempt_ToStatusId,IsVendorSLAbreached,IsCourierSLAbreached,NumberOfAttemptsToDeliver,NumberOfCourierAttempts,GatePassed_DATE,IsGatePassed,UserLogin,OrderCallRoute
,TSO_Type,IsReplacement,IsMarkedSaleReturn,IsRtoDelivered,Dim_BookedToShippedDaysBucket_Key,Dim_ShippedToDeliveredDaysBucket_Key,BookedToNOW_Days,ShippedToNOW_Days
,VerifiedToNOW_Days,GatePassedToNOW_Days,Dim_BookedToPendingShipmentDaysBucket_Key,Dim_ShippedToPendingDeliveryDaysBucket_Key,Dim_BookedToPendingVerifiedDaysBucket_Key
,Dim_VerifiedToPendingGatepassedDaysBucket_Key,Dim_GatepassedToPendingShipmentDaysBucket_Key,PaidByShippingPassAmount,CouponGroup,Dim_Suborder_Delivery_Mode_Key
,ORV_STATUS_DATE_INT,Dim_NDR_Reason_Key,Dim_Site_key,Dim_BookedToPendingShipmentAgeingBucket_Key,Dim_ShippedToPendingDeliveryAgeingBucket_Key,Dim_Offer_Key
 ,Dim_Deal_Key,Dim_Affiliate_key,First_DeliveryAttemptFailed_Courier_DATE,Marked_RTO_Delivered_DATE,RefundCouponDiscount,IsRefundCouponDiscount,ReverseLITDate
,ReverseLITDate_INT,IsReverseLIT,RefundRemark,Dim_CustomerMDM_Key,Payment_GatewayName,Payment_GatewayMID,Dim_NewVsRepeatCustomerMDM_Key,LITRemark,FWDLITRemark
,ReverseLITRemark,FWDLITRemark_MarkedBy,ReverseLITRemark_MarkedBy,ChequeNumber,ChequeAmount,ChequeBankName,ChequeDate,IsChequeIssued,RefundTransactionId,Refund_Amount
,ParentSuborder_GSV,ParentSuborder_ShippingCharge,ParentSuborder_Margin,ParentCouponCategory_Id,Colllected_ShippingCharge,ParentSuborder_Colllected_ShippingCharge
,Dim_Cancellation_Channel_Key,IsSCRefunded,Dim_RBC_Reason_Key,SubOrderSTID,Is_PrePaid_PayMode,OUP_PrepaidSMSDate,DIM_Actual_PaymentMode_Key,dim_brand_key)
 values'''+val
    #print (db_cmd2)


    res2=cur1.execute(db_cmd2)
    cur1.commit()

#print (db_cmd2)


cur1.commit()
cur2.commit()
cur2.close()
cur1.close()


#print("table created successfully")