stackql/pystackql

[FEATURE] # Rows inserted in 'Insert into Table Select * from Table' Stmt

vwake7 opened this issue · 1 comments

vwake7 commented

Feature Description
Get # Rows inserted in 'Insert into Table Select * from Table' Stmt in Pystackql

Example(s)
In the below Insert if we can know the number of rows inserted into the table, it will be really helpful. Currently I am seeing this message using pystackql. Link to Discussion [https://github.com/stackql/stackql/discussions/406]

The operation was despatched successfully

  query = """
  Insert into azure.network.interfaces
  (
    resourceGroupName,
    subscriptionId,
    networkInterfaceName,
    data__location,
    data__properties
  )
  select
    '%s',
    '%s',
    'vmss-flex-vnet-nic01-manual-' || '%s',
    location,
    json_replace(json_remove(properties,
                                  '$.allowPort25Out',
                                  '$.auxiliarySku',
                                  '$.provisioningState',
                                  '$.resourceGuid',
                                  '$.macAddress',
                                  '$.vnetEncryptionSupported',
                                  '$.enableIPForwarding',
                                  '$.defaultOutboundAccess',
                                  '$.primary',
                                  '$.virtualMachine',
                                  '$.hostedWorkloads',
                                  '$.tapConfigurations',
                                  '$.nicType',
                                  '$.auxiliaryMode',
                                  '$.ipConfigurations[0].id',
                                  '$.ipConfigurations[0].etag',
                                  '$.ipConfigurations[0].type',
                                  '$.ipConfigurations[0].properties.provisioningState',
                                  '$.ipConfigurations[0].properties.privateIPAddress',
                                  '$.ipConfigurations[0].properties.privateIPAllocationMethod',
                                  '$.ipConfigurations[0].properties.primary',
                                  '$.ipConfigurations[0].properties.privateIPAddressVersion'
                                ),
                      '$.ipConfigurations[0].name',
                      'vmss-flex-vnet-nic01-defaultIpConfiguration'
                      )
  from
    azure.network.interfaces
  where subscriptionId = '%s'
  and resourceGroupName = '%s'
  and networkInterfaceName = '%s'
  ;""" % (rg,subscription_id,counter,subscription_id,rg,vm['nic_name'])

Possible Approaches or Libraries to Consider
@jeffreyaven suggested that there is usually one message inserted per row as per discussion.

The operation was despatched successfully
jeffreyaven commented

fixed in pystackql/3.6.2

now includes a rowsaffected field from the executeStmt method, for example:

from pystackql import StackQL
stackql = StackQL(download_dir=".", custom_auth={ 'google': { 'type': 'interactive' }})
print(stackql.version)
print(stackql.package_version)
query = """
INSERT INTO google.compute.instances(   project,   zone,   data__name,   data__machineType,   data__disks,   data__networkInterfaces ) 
SELECT   
'stackql-demo-2' as project,   
'australia-southeast1-a' as zone, 
instanceId as name, 
CASE instanceType 
WHEN 't2.micro' 
THEN 'https://www.googleapis.com/compute/v1/projects/stackql-demo-2/zones/australia-southeast1-a/machineTypes/e2-micro' 
END as machineType,
'[{"autoDelete":true,"boot":true,"initializeParams":{"diskSizeGb":"10","sourceImage":"https://compute.googleapis.com/compute/v1/projects/ubuntu-os-cloud/global/images/family/ubuntu-2004-lts"},"mode":"READ_WRITE","type":"PERSISTENT"}]'
as disks,   
'[{"subnetwork":"https://www.googleapis.com/compute/v1/projects/stackql-demo-2/regions/australia-southeast1/subnetworks/default"}]' as networkInterfaces 
FROM aws.ec2.instances WHERE region = 'ap-southeast-2' AND instanceType = 't2.micro'
"""
res = stackql.executeStmt(query) 
print(res['rowsaffected'])
  • 👍1