问:
Convert a Pandas DataFrame to a multidimensional ndarray
I have a DataFrame with columns for the x, y, z coordinates and the value at this position and I want to convert this to a 3-dimensional ndarray.

To make things more complicated, not all values exist in the DataFrame (these can just be replaced by NaN in the ndarray).

Just a simple example:

```<code>df = pd.DataFrame({'x': [1, 2, 1, 3, 1, 2, 3, 1, 2],
                   'y': [1, 1, 2, 2, 1, 1, 1, 2, 2],
                   'z': [1, 1, 1, 1, 2, 2, 2, 2, 2],
                   'value': [1, 2, 3, 4, 5, 6, 7, 8, 9]})
</code>```

Should result in the ndarray:

```<code>array([[[  1.,   2.,  nan],
        [  3.,  nan,   4.]],

       [[  5.,   6.,   7.],
        [  8.,   9.,  nan]]])
</code>```

For two dimensions, this is easy:

```<code>array = df.pivot_table(index="y", columns="x", values="value").as_matrix()
</code>```

However, this method can not be applied to three or more dimensions.

Could you give me some suggestions?

Bonus points if this also works for more than three dimensions, handles multiple defined values (by taking the average) and ensures that all x, y, z coordinates are consecutive (by inserting row/columns of NaN when a coordinate is missing).

EDIT: Some more explanations:

I read data from a CSV file which has the columns for x, y, z coordinates, optionally the frequency and the measurement value at this point and frequency. Then I round the coordinates to a specified precision (e.g. 0.1m) and want to get an ndarray which contains the averaged measurement values at each (rounded) coordinates. The indizes of the values do not need to coincide with the location. However they need to be in the correct order.

EDIT: I just ran a quick performance test:

The solution of jakevdp takes 1.598s, Divikars solution takes 7.405s, JohnE's solution takes 7.867s and Wens solution takes 6.286s to complete.

Body:
I have a DataFrame with columns for the x, y, z coordinates and the value at this position and I want to convert this to a 3-dimensional ndarray.

To make things more complicated, not all values exist in the DataFrame (these can just be replaced by NaN in the ndarray).

Just a simple example:

df = pd.DataFrame({'x': [1, 2, 1, 3, 1, 2, 3, 1, 2],
                   'y': [1, 1, 2, 2, 1, 1, 1, 2, 2],
                   'z': [1, 1, 1, 1, 2, 2, 2, 2, 2],
                   'value': [1, 2, 3, 4, 5, 6, 7, 8, 9]})

Should result in the ndarray:

array([[[  1.,   2.,  nan],
        [  3.,  nan,   4.]],

       [[  5.,   6.,   7.],
        [  8.,   9.,  nan]]])

For two dimensions, this is easy:

array = df.pivot_table(index="y", columns="x", values="value").as_matrix()

However, this method can not be applied to three or more dimensions.

Could you give me some suggestions?

Bonus points if this also works for more than three dimensions, handles multiple defined values (by taking the average) and ensures that all x, y, z coordinates are consecutive (by inserting row/columns of NaN when a coordinate is missing).

EDIT: Some more explanations:

I read data from a CSV file which has the columns for x, y, z coordinates, optionally the frequency and the measurement value at this point and frequency. Then I round the coordinates to a specified precision (e.g. 0.1m) and want to get an ndarray which contains the averaged measurement values at each (rounded) coordinates. The indizes of the values do not need to coincide with the location. However they need to be in the correct order.

EDIT: I just ran a quick performance test:

The solution of jakevdp takes 1.598s, Divikars solution takes 7.405s, JohnE's solution takes 7.867s and Wens solution takes 6.286s to complete.

Body:
We can using stack

np.reshape(df.groupby(['z', 'y', 'x'])['value'].mean().unstack([1,2]).stack([0,1],dropna=False).values,(2,2,3))

Out[451]:
array([[[  1.,   2.,  nan],
        [  3.,  nan,   4.]],
       [[  5.,   6.,   7.],
        [  8.,   9.,  nan]]])


来源: stackexchange

问:
Is there a way to automatically schedule the bootup of a Lightsail instance every 3 months?
I'm looking to automatically renew my SSL certificates for a website I'm hosting on GitLab pages using certbot auto. I already have this working, but I have to keep my Lightsail instance running continuously.

I'm just looking for an automatic way to boot up my Lightsail instance every 3 months, and once booted run a little script, and then power down again. At the moment it's costing me $5 month, and I'm only using it for a few minutes each time.

Is there a way to automatically schedule the bootup of a Lightsail instance every 3 months?

答:
Suggested approach: A scheduled Lambda function that fires every 3 months and performs 4 steps.

Start up your instance

Use the EC2 RunCommand API to remotely execute a command on your lightsail instance

Monitor the command until complete

Shut down the instance

Some prerequisites:

Create a Lambda function and grant it permissions with an IAM role to use ssm:*, ec2:startinstances and ec2:stopinstances (This will allow your lambda function to communicate to your lightsail instance and also  monitor and send commands.

Make the lambda function a scheduled function, so you can trigger it every 3 months automaticalle

Have SSMAgent installed on your instance like this

Give your instance the appropriate IAM permissions for SSM communications through an instance policy (This will allow the instance to communicate with AWS SSM)

"ec2messages:*","ssm:updateinstanceinformation","ssm:listassociations"

Now write your lambda function using the AWS SDK and it'll work like a charm.
If you're worried about costs, unless you run one mother of a script, you should fall within the free tier as you get 400 GB-seconds of compute time per month.

This means you can run a lambda function with 1GB of memory for 400 seconds every month for free.

PS: I mentioned EC2 alot, I'm aware you're using lightsail but as it's just a wrapper for EC2 I imagine the same functionality is available, correct me if I'm wrong.

ContentLicense: CC BY-SA 3.0


问:
Laravel deployment HTTP ERROR 500
I have my project in /var/www/html/project on the server,

When ever i hit example.com shows default server page, Then i tried
example.com/project i got list of my project files/folders, Then i tried example.com/project/public it gave me HTTP ERROR 500 error.

Here is my public/index.php file :

```<code><?php
  require __DIR__.'/../bootstrap/autoload.php';
  $app = require_once __DIR__.'/../bootstrap/app.php';
  $kernel = $app->make(Illuminate\Contracts\Http\Kernel::class);
  $response = $kernel->handle(
      $request = Illuminate\Http\Request::capture()
  );
  $response->send();
  $kernel->terminate($request, $response);
</code>```

My project/storage permissions are drwxr-xrwx.  5 root root   4096 Apr 20 12:56 storage

What am i missing ?

答:
Had the same exact issue and in my case the problem was the default php version (which was 5.5+) of the shared hosting service that I use.
After I changed the php version to 7+ (laravel 5.5 needs php version >= 7) everything started to work. Hope that helps someone!</p>

Title: Laravel deployment HTTP ERROR 500

Body:
I have my project in /var/www/html/project on the server,

When ever i hit example.com shows default server page, Then i tried
example.com/project i got list of my project files/folders, Then i tried example.com/project/public it gave me HTTP ERROR 500 error.

Here is my public/index.php file :

<?php
  require __DIR__.'/../bootstrap/autoload.php';
  $app = require_once __DIR__.'/../bootstrap/app.php';
  $kernel = $app->make(Illuminate\Contracts\Http\Kernel::class);
  $response = $kernel->handle(
      $request = Illuminate\Http\Request::capture()
  );
  $response->send();
  $kernel->terminate($request, $response);

My project/storage permissions are drwxr-xrwx.  5 root root   4096 Apr 20 12:56 storage

What am i missing ?


问:
JQuery Default text on empty text box
I have a email form text box that while it is empty I would like it to have the value "E-Mail" And when you click on it the text goes away. If someone clicks on it and doesn't enter text. on Blur I would like for it to return to having the default text.

I have been trying a few things but nothing is working. Could someone please point me in the right direction?

答:
it goes something like this</p>

```<code>$('#yourElement').focus(function(){
    //Check val for email
    if($(this).val() == 'E-Mail'){
        $(this).val('');
    }
}).blur(function(){
    //check for empty input
    if($(this).val() == ''){
        $(this).val('E-Mail');
    }
});
</code>```

Title: JQuery Default text on empty text box

Body:
it goes something like this

$('#yourElement').focus(function(){
    //Check val for email
    if($(this).val() == 'E-Mail'){
        $(this).val('');
    }
}).blur(function(){
    //check for empty input
    if($(this).val() == ''){
        $(this).val('E-Mail');
    }
});

ContentLicense: CC BY-SA 2.5


问:
Using ISSET with checkboxes
I am working on a wordpress search form to refine the current search and what Im trying to do is have the search results page with the search from and it's values set based on the query.

So far I've been successful in doing so with single select drop downs and single checkboxes like so --

```<code><!-- SINGLE SELECT -->
    <select name="baths" class="form-control">
    <?php if (isset($_GET['baths'])) {
        $bths = $_GET['baths']; ?>
    <option value="<?php echo $bths; ?>"><?php echo $bths; ?></option>
    <?php } else { ?>
    <option value="Any">Any</option>
    <?php } ?>
    <option value="Any">Any</option>
    <option value="1">1+</option>
    <option value="2">2+</option>
    <option value="3">3+</option>
    <option value="4">4+</option>
    <option value="5">5+</option>
    <option value="6">6+</option>
    <option value="7">7+</option>
    <option value="8">8+</option>
    <option value="9">9+</option>
    <option value="10">10+</option>
    </select>

<!-- SINGLE CHECKBOX -->
<input type="checkbox" name="dogs" class="styled" value ="yes" <?php if (isset($_GET['dogs'])) { ?>checked<?php } ?>>
</code>```

That works, but for the multiple values it doesn't. This is my function to generate a set of checkboxes to select amenities -

```<code><?php
$amenity_array = array();
$id            = get_query_var('site');
if (!empty($id)) {
  $property_amenities = get_post_meta($id, 'imic_property_amenities', true);
  global $imic_options;
  foreach ($property_amenities as $properties_amenities_temp) {
    if ($properties_amenities_temp != 'Not Selected') {
      array_push($amenity_array, $properties_amenities_temp);
    }
  }
}
global $imic_options;
if (isset($imic_options['properties_amenities']) && count($imic_options['properties_amenities']) > 1) {
  foreach ($imic_options['properties_amenities'] as $properties_amenities) {
    $am_name = strtolower(str_replace(' ', '', $properties_amenities));
    $check   = '';
    if (in_array($properties_amenities, $amenity_array)) {
      $check = 'checked="checked"';
    }

<!-- HERE I TRY TO FIND THE SELECTED CHECKBOXES AND CHECK THEM OFF -->
    if (isset($_GET['p_am'])) {
      $ams = $_GET['p_am'];

      echo '<div class="checkbox"><input type="checkbox" name="p_am" ' . $check . ' class="styled" value ="' . $properties_amenities . '"><label for="' . $am_name . '">' . $properties_amenities . '</label></div>';
    } else {
      echo '<div class="checkbox"><input type="checkbox" name="p_am" ' . $check . ' class="styled" value ="' . $properties_amenities . '"><label for="' . $am_name . '">' . $properties_amenities . '</label></div>';
    }
<!-- END ISSET -->

  }
} else {
  _e('There is no Properties Amenities', 'framework');
}
?>
</code>```

For the multi select drop down I am using bootstrap multiselect, so on my template the  code looks like this --

```<code><select name="property_type[]" id="pt-multi" class="form-control multi-select2" multiple="multiple">
<?php
 $terms = get_terms( "property-type", array( 'hide_empty' => 0 ) );
 $count = count($terms);
 if ( $count > 0  ){
   echo "<option value='Any'>All</option>";
   foreach ( $terms as $term ) {
     echo "<option value='" . $term->slug . "'>" . $term->name . "</option>";
   }
 }
?>
</select>
</code>```

On the page it renders out as ---

```<code><select name="property_type[]" id="pt-multi" class="form-control multi-select2 iOSselect" multiple="multiple" style="display: none;">
  <option value="Any">All</option>
  <option value="co-op">Co-Op</option>
  <option value="condo">Condo</option>
</select>

<div class="btn-group" style="width: 100%;">
  <button type="button" class="multiselect dropdown-toggle btn btn-default form-control multi-select2" data-toggle="dropdown" title="Property Type" style="width: 100%; overflow: hidden; text-overflow: ellipsis;">
  <span class="multiselect-selected-text">Property Type</span>
  <b class="caret"></b></button>
  <ul class="multiselect-container dropdown-menu pull-right">
    <li class="multiselect-item multiselect-all">
      <a tabindex="0" class="multiselect-all">
        <label class="checkbox"><input type="checkbox" value="multiselect-all">  Select all</label>
      </a>
    </li>
    <li>
      <a tabindex="0"><label class="checkbox">
        <input type="checkbox" value="Any"> All</label>
      </a>
    </li>
    <li>
      <a tabindex="0"><label class="checkbox"><input type="checkbox" value="co-op"> Co-Op</label>
      </a>
    </li>
    <li>
      <a tabindex="0"><label class="checkbox"><input type="checkbox" value="condo"> Condo</label>
      </a>
    </li>
  </ul>
</div>
</code>```

Any ideas?

UPDATE

Using my code from above my amenities checkbox renders out on the page like so ---

```<code><div>
  <label>amenities</label>
  <div class="checkbox">
    <input type="checkbox" name="p_am" class="styled" value="Doorman (Full Time)">
    <label for="doorman(fulltime)">Doorman (Full Time)</label>
  </div>

  <div class="checkbox">
    <input type="checkbox" name="p_am" class="styled" value="Doorman (Part Time)"><label for="doorman(parttime)">Doorman (Part Time)</label>
  </div>

  <div class="checkbox">
    <input type="checkbox" name="p_am" class="styled" value="Laundry (In-Unit)"><label for="laundry(in-unit)">Laundry (In-Unit)</label>
  </div>

  <div class="checkbox">
    <input type="checkbox" name="p_am" class="styled" value="Fitness Center"><label for="fitnesscenter">Fitness Center</label>
  </div>
</div>
</code>```

This works with my search function allowing users to check off which amenities to include in the query string --

```<code>p_am=Doorman+%28Full+Time%29&p_am=Indoor+Pool
</code>```

My search function is extensive but as far the relevant parts to the amenities -

```<code>$amenities = isset($_GET['p_am'])?$_GET['p_am']:'';
$amenities = ($amenities == __('Any', 'framework')) ? '' : $amenities;

// .....

if (!empty($amenities)) {
  array_push($meta_query,array(
    'key' => 'imic_property_amenities',
    'value' => $amenities,
    'compare'=>'LIKE'
  ));
}
</code>```

UPDATE

In regards to setting the dropdown options for the bootstrap multiselect, I'm trying to set the options using the following code -

```<code><?php

$taxonomyName = "city-type";

$parent_terms = get_terms( $taxonomyName, array( 'parent' => 0, 'orderby' => 'slug', 'hide_empty' => false ) );

echo "<select name='property_nhb[]' class='form-control multi-select' id='p-nhb' multiple>";

foreach ( $parent_terms as $pterm ) {
 // echo "<option data-val='" . $pterm->slug . "' value='Any' " . $selected . ">Any</option>";
    //Get the Child terms
    $terms = get_terms( $taxonomyName, array( 'parent' => $pterm->term_id, 'orderby' => 'slug', 'hide_empty' => false ) );
    foreach ( $terms as $term ) {

if(isset($_GET['property_nhb[]'])) {
$pnhb = $_GET['property_nhb'];

$selected = 'selected';

}

        echo "<option data-val='" . $pterm->slug . "' value='" . $term->slug . "' " . $selected . " >" . $term->name . "</option>";
    }
}
echo "</select>";
?>
</code>```

My current search function code

答: Your checkboxes has the same name. Try to add different name or use "p_am[]" to treat them as array.</p>

Title: Using ISSET with checkboxes

Body:
I am working on a wordpress search form to refine the current search and what Im trying to do is have the search results page with the search from and it's values set based on the query.

So far I've been successful in doing so with single select drop downs and single checkboxes like so --

    <select name="baths" class="form-control">
    <?php if (isset($_GET['baths'])) {
        $bths = $_GET['baths']; ?>
    <option value="<?php echo $bths; ?>"><?php echo $bths; ?></option>
    <?php } else { ?>
    <option value="Any">Any</option>
    <?php } ?>
    <option value="Any">Any</option>
    <option value="1">1+</option>
    <option value="2">2+</option>
    <option value="3">3+</option>
    <option value="4">4+</option>
    <option value="5">5+</option>
    <option value="6">6+</option>
    <option value="7">7+</option>
    <option value="8">8+</option>
    <option value="9">9+</option>
    <option value="10">10+</option>
    </select>

<input type="checkbox" name="dogs" class="styled" value ="yes" <?php if (isset($_GET['dogs'])) { ?>checked<?php } ?>>

That works, but for the multiple values it doesn't. This is my function to generate a set of checkboxes to select amenities -

<?php
$amenity_array = array();
$id            = get_query_var('site');
if (!empty($id)) {
  $property_amenities = get_post_meta($id, 'imic_property_amenities', true);
  global $imic_options;
  foreach ($property_amenities as $properties_amenities_temp) {
    if ($properties_amenities_temp != 'Not Selected') {
      array_push($amenity_array, $properties_amenities_temp);
    }
  }
}
global $imic_options;
if (isset($imic_options['properties_amenities']) && count($imic_options['properties_amenities']) > 1) {
  foreach ($imic_options['properties_amenities'] as $properties_amenities) {
    $am_name = strtolower(str_replace(' ', '', $properties_amenities));
    $check   = '';
    if (in_array($properties_amenities, $amenity_array)) {
      $check = 'checked="checked"';
    }

    if (isset($_GET['p_am'])) {
      $ams = $_GET['p_am'];

      echo '
<input type="checkbox" name="p_am" ' . $check . ' class="styled" value ="' . $properties_amenities . '">' . $properties_amenities . '
';
    } else {
      echo '
<input type="checkbox" name="p_am" ' . $check . ' class="styled" value ="' . $properties_amenities . '">' . $properties_amenities . '
';
    }

  }
} else {
  _e('There is no Properties Amenities', 'framework');
}
?>

For the multi select drop down I am using bootstrap multiselect, so on my template the  code looks like this --

<select name="property_type[]" id="pt-multi" class="form-control multi-select2" multiple="multiple">
<?php
 $terms = get_terms( "property-type", array( 'hide_empty' => 0 ) );
 $count = count($terms);
 if ( $count > 0  ){
   echo "<option value='Any'>All</option>";
   foreach ( $terms as $term ) {
     echo "<option value='" . $term->slug . "'>" . $term->name . "</option>";
   }
 }
?>
</select>

On the page it renders out as ---

<select name="property_type[]" id="pt-multi" class="form-control multi-select2 iOSselect" multiple="multiple" style="display: none;">
  <option value="Any">All</option>
  <option value="co-op">Co-Op</option>
  <option value="condo">Condo</option>
</select>

  <button type="button" class="multiselect dropdown-toggle btn btn-default form-control multi-select2" data-toggle="dropdown" title="Property Type" style="width: 100%; overflow: hidden; text-overflow: ellipsis;">
  Property Type
  </button>

        <input type="checkbox" value="multiselect-all">  Select all

        <input type="checkbox" value="Any"> All

      <input type="checkbox" value="co-op"> Co-Op

      <input type="checkbox" value="condo"> Condo

Any ideas?

UPDATE

Using my code from above my amenities checkbox renders out on the page like so ---

  amenities

    <input type="checkbox" name="p_am" class="styled" value="Doorman (Full Time)">
    Doorman (Full Time)

    <input type="checkbox" name="p_am" class="styled" value="Doorman (Part Time)">Doorman (Part Time)

    <input type="checkbox" name="p_am" class="styled" value="Laundry (In-Unit)">Laundry (In-Unit)

    <input type="checkbox" name="p_am" class="styled" value="Fitness Center">Fitness Center

This works with my search function allowing users to check off which amenities to include in the query string --

p_am=Doorman+%28Full+Time%29&p_am=Indoor+Pool

My search function is extensive but as far the relevant parts to the amenities -

$amenities = isset($_GET['p_am'])?$_GET['p_am']:'';
$amenities = ($amenities == __('Any', 'framework')) ? '' : $amenities;

// .....

if (!empty($amenities)) {
  array_push($meta_query,array(
    'key' => 'imic_property_amenities',
    'value' => $amenities,
    'compare'=>'LIKE'
  ));
}

UPDATE

In regards to setting the dropdown options for the bootstrap multiselect, I'm trying to set the options using the following code -

<?php

$taxonomyName = "city-type";

$parent_terms = get_terms( $taxonomyName, array( 'parent' => 0, 'orderby' => 'slug', 'hide_empty' => false ) );

echo "<select name='property_nhb[]' class='form-control multi-select' id='p-nhb' multiple>";

foreach ( $parent_terms as $pterm ) {
 // echo "<option data-val='" . $pterm->slug . "' value='Any' " . $selected . ">Any</option>";
    //Get the Child terms
    $terms = get_terms( $taxonomyName, array( 'parent' => $pterm->term_id, 'orderby' => 'slug', 'hide_empty' => false ) );
    foreach ( $terms as $term ) {

if(isset($_GET['property_nhb[]'])) {
$pnhb = $_GET['property_nhb'];

$selected = 'selected';

}

        echo "<option data-val='" . $pterm->slug . "' value='" . $term->slug . "' " . $selected . " >" . $term->name . "</option>";
    }
}
echo "</select>";
?>

My current search function code


问:
SQL Server: convert an int value into a time value
In a SQL Server query, I want to convert an int into a time.

The int is representing hours and minutes (hhmm).

For example:

```<code>1945 --> 19:45
30 --> 00:30
1 --> 00:01
</code>```

This query, after modification with your solution, should return true, true, true.

```<code>declare @int1 int = 0
declare @int2 int = 35
declare @int3 int = 1810

select
    @int1 = '00:00', @int2 = '00:35', @int3 = '18:10'
</code>```

答:
Try:</p>

```<code>WITH Times AS(
    SELECT *
    FROM (VALUES (1945),(30),(1)) AS V(IntTime))
SELECT IntTime,
       CONVERT(time,STUFF(RIGHT('0000' + CONVERT(varchar(5),IntTime),4),3,0,':')) As TimeTime
FROM Times;
</code>```

Body:
In a SQL Server query, I want to convert an int into a time.

The int is representing hours and minutes (hhmm).

For example:

1945 --> 19:45
30 --> 00:30
1 --> 00:01

This query, after modification with your solution, should return true, true, true.

declare @int1 int = 0
declare @int2 int = 35
declare @int3 int = 1810

select
    @int1 = '00:00', @int2 = '00:35', @int3 = '18:10'

Body:
Try:

WITH Times AS(
    SELECT *
    FROM (VALUES (1945),(30),(1)) AS V(IntTime))
SELECT IntTime,
       CONVERT(time,STUFF(RIGHT('0000' + CONVERT(varchar(5),IntTime),4),3,0,':')) As TimeTime
FROM Times;


问:
Trouble trying to sort list of lists/tuples in Python 3
I have a list of lists containg 2 items, taken from a text file, and I'm trying to sort the outer list by the second item in each inner list (the numbers without num). I've looked at other posts about sorting but I can't manage to get my list to sort, help would be appreciated as I've been trying to find out the issue for hours. The problem is that the sort method is not sorting the varibale data.

Here is my code:

```<code>def sortItems(data):
    data.sort(key=lambda x: x[1])
    print(data)

get_file = input(str("Please enter a filename: "))
path = get_file + ".txt"

try:
   file = open(path)
except IOError:
   print('The file could not be opened.')
   exit()

for line in iter(file):
    record = line.strip()
    a_list = []
    a_list.append(record.split())
    sortItems(a_list)
</code>```

The text file I'm reading from:

```<code>num1 3453
num2 234
num3 35676776
num4 45
num5 23354
</code>```

I'd like the output to look like:

```<code>[['num3', '35676776']]
[['num5', '23354']]
[['num1', '3453']]
[['num2', '234']]
[['num4', '45']]
</code>```

However my out put is:

```<code>[['num1', '3453']]
[['num2', '234']]
[['num3', '35676776']]
[['num4', '45']]
[['num5', '23354']]
</code>```

Any help is very appreciated as I'm somewhat new to using Python.

答:
You are overwriting a_list every iteration of the loop. You want to put everything in the same list, then sort numerically, rather than lexicographically.

```<code>get_file = input(str("Please enter a filename: "))
path = get_file + ".txt"

try:
    with open(path) as f
        a_list = [] # define the list outside the loop, so it doesn't get overwritten
        for line in f:
            record = line.strip()
            a_list.append(record.split())
        a_list.sort(key=lambda x: int(x[1]), reverse=True)
except IOError:
    print('The file could not be opened.')
    exit()
</code>```

Body:
I have a list of lists containg 2 items, taken from a text file, and I'm trying to sort the outer list by the second item in each inner list (the numbers without num). I've looked at other posts about sorting but I can't manage to get my list to sort, help would be appreciated as I've been trying to find out the issue for hours. The problem is that the sort method is not sorting the varibale data.

Here is my code:

def sortItems(data):
    data.sort(key=lambda x: x[1])
    print(data)

get_file = input(str("Please enter a filename: "))
path = get_file + ".txt"

try:
   file = open(path)
except IOError:
   print('The file could not be opened.')
   exit()

for line in iter(file):
    record = line.strip()
    a_list = []
    a_list.append(record.split())
    sortItems(a_list)

The text file I'm reading from:

num1 3453
num2 234
num3 35676776
num4 45
num5 23354

I'd like the output to look like:

[['num3', '35676776']]
[['num5', '23354']]
[['num1', '3453']]
[['num2', '234']]
[['num4', '45']]

However my out put is:

[['num1', '3453']]
[['num2', '234']]
[['num3', '35676776']]
[['num4', '45']]
[['num5', '23354']]

Any help is very appreciated as I'm somewhat new to using Python.

Body:
You are overwriting a_list every iteration of the loop. You want to put everything in the same list, then sort numerically, rather than lexicographically.

get_file = input(str("Please enter a filename: "))
path = get_file + ".txt"

try:
    with open(path) as f
        a_list = [] # define the list outside the loop, so it doesn't get overwritten
        for line in f:
            record = line.strip()
            a_list.append(record.split())
        a_list.sort(key=lambda x: int(x[1]), reverse=True)
except IOError:
    print('The file could not be opened.')
    exit()


问:
How do I speed up an insert that deals with huge amount of data and non indexed joins?
I have the following query that will be used to fetch data from legacy tables. It's no surprise but the amount of data is huge and thus it takes a long time. The first select takes 40 minutes to run using an empty dbo.commodities_copy table as a starting point and yields around 26,000 rows. Keep in mind that there are separate databases: STAGING and PRESTAGING and that some joins are made using non-PK fields, which is most definately making an impact in its performance. This is something that I cannot fix, due to the way data was organized from the start. Also the transaction table has around 1 million rows, which also impacts heavily on performance. The entire script takes a total of 3.5 hours to execute when using an EMPTY dbo.commodities_copy table. I have not tested on insertion to a table with data.

The goal of the query is to get commodity information from the transaction table (if you guessed this was supposed to be noSQL data, you guessed right) and if the commodity code exists in the commodity table, do not insert a commodity in it.

The group bys are absolutely needed to get around duplicates, since a transactions may share the same commodity. The commodity code should be unique in the commodities table, but currently it is not - though if it helps, it's possible we could alter it.

What can I do to speed it up?

```<code>INSERT INTO STAGING.dbo.commodities_copy
(commodity_code,
short_description_sched_b,
short_description_sched_tsusa,
long_description_sched_b,
long_description_sched_tsusa,
measurement_unit_1_sched_b,
measurement_unit_1_sched_tsusa,
measurement_unit_2_sched_b,
measurement_unit_2_sched_tsusa,
end_use_sched_b,
end_use_sched_tsusa,
year,
created_by,
created_on,
taxable_sched_b,
taxable_sched_tsusa,
non_taxable_sched_b,
non_taxable_sched_tsusa,
fk_sic_sched_b,
fk_sic_sched_tsusa,
chapter,
header,
sub_header,
needs_validation)
SELECT
    --Distinct
    Commodity_Code,
    iif(miob2.DESC_COMM is null, UPPER(socrata.Commodity_Short_Name), miob2.DESC_COMM) as short_commmodity_description_b,
    iif(mio2tsusa.DESC_COMM is null, UPPER(socrata.Commodity_Short_Name), mio2tsusa.DESC_COMM) as short_commmodity_description_tsusa,
    socrata.Commodity_description as long_commodity_description_b,
    socrata.Commodity_description as long_commodity_description_tsusa,
    iif(miob2.UNIDAD is null, socrata.unit_1, miob2.UNIDAD) as unit_1_b,
    iif(mio2tsusa.UNIDAD is null, socrata.unit_1, mio2tsusa.UNIDAD) as unit_1_tsusa,
    MAX(socrata.unit_2) as unit_2_b,
    MAX(socrata.unit_2) as unit_2_tsusa,
    socrata.end_use_e as end_use_b,
    socrata.end_use_i as end_use_tsusa,
    MAX(socrata.[year]),
    'system' as created_by,
    getdate() as created_on,
    miob.TRIBUTA as taxable_b,
    miotsusa.TRIBUTA as taxable_tsusa,
    miob.NTRIBUTA as non_taxable_b,
    miotsusa.NTRIBUTA as non_taxable_tsusa,
    sicb.id as sic_id_b,
    sictsusa.id as sic_id_tsusa,
    SUBSTRING(Commodity_Code, 1, 2) as chapter,
    SUBSTRING(Commodity_Code, 1, 4) as header,
    SUBSTRING(Commodity_Code, 1, 6) as sub_header,
    0 as needs_validation

FROM PRE_STAGING.dbo.TRANSACTIONS_FROM_SOCRATA socrata
Left join PRE_STAGING.DBO.MIOB_TBL miob ON miob.COMM=socrata.Commodity_Code
Left join PRE_STAGING.dbo.MSCHB_TBL miob2 ON miob2.COMM=socrata.Commodity_Code
Left join PRE_STAGING.dbo.MIOTSUSA_TBL miotsusa ON miotsusa.COMM=socrata.Commodity_Code
Left join PRE_STAGING.dbo.MTSUSA_TBL mio2tsusa ON mio2tsusa.COMM=socrata.Commodity_Code
Left join STAGING.dbo.sics_altered sicb ON sicb.sic_code = miob.SIC
Left join STAGING.dbo.sics_altered sictsusa ON sictsusa.sic_code = miotsusa.SIC
WHERE NOT EXISTS
(Select Distinct commodity_code from STAGING.dbo.commodities_copy)
group by
Commodity_Code,
iif(miob2.DESC_COMM is null, UPPER(socrata.Commodity_Short_Name), miob2.DESC_COMM),
iif(mio2tsusa.DESC_COMM is null, UPPER(socrata.Commodity_Short_Name), mio2tsusa.DESC_COMM),
socrata.Commodity_description,
socrata.Commodity_description,
iif(miob2.UNIDAD is null, socrata.unit_1, miob2.UNIDAD),
iif(mio2tsusa.UNIDAD is null, socrata.unit_1, mio2tsusa.UNIDAD),
socrata.end_use_e,
socrata.end_use_i,
miob.TRIBUTA,
miotsusa.TRIBUTA,
miob.NTRIBUTA,
miotsusa.NTRIBUTA,
sicb.id,
sictsusa.id,
SUBSTRING(Commodity_Code, 1, 2),
SUBSTRING(Commodity_Code, 1, 4),
SUBSTRING(Commodity_Code, 1, 6)
</code>```

The tables used are the following:

STAGING.dbo.commodities_copy:

```<code>CREATE TABLE [dbo].[commodities_copy](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [chapter] [varchar](5) NULL,
    [header] [varchar](5) NULL,
    [sub_header] [varchar](10) NULL,
    [commodity_code] [varchar](20) NULL,
    [short_description_sched_b] [varchar](100) NULL,
    [long_description_sched_b] [varchar](200) NULL,
    [measurement_unit_1_sched_b] [varchar](5) NULL,
    [measurement_unit_2_sched_b] [varchar](5) NULL,
    [end_use_sched_b] [int] NULL,
    [sitc_sched_b] [varchar](20) NULL,
    [usda_sched_b] [int] NULL,
    [hitech_sched_b] [int] NULL,
    [naics_fk_id_sched_b] [bigint] NULL,
    [short_description_sched_tsusa] [varchar](100) NULL,
    [long_description_sched_tsusa] [varchar](200) NULL,
    [measurement_unit_1_sched_tsusa] [varchar](5) NULL,
    [measurement_unit_2_sched_tsusa] [varchar](5) NULL,
    [end_use_sched_tsusa] [int] NULL,
    [sitc_sched_tsusa] [varchar](20) NULL,
    [usda_sched_tsusa] [int] NULL,
    [hitech_sched_tsusa] [int] NULL,
    [naics_fk_id_sched_tsusa] [bigint] NULL,
    [year] [int] NOT NULL,
    [created_on] [datetime] NOT NULL,
    [created_by] [varchar](50) NULL,
    [updated_on] [datetime] NULL,
    [updated_by] [varchar](50) NULL,
    [needs_validation] [bit] NOT NULL,
    [taxable_sched_b] [nchar](3) NULL,
    [non_taxable_sched_b] [nchar](3) NULL,
    [taxable_sched_tsusa] [nchar](3) NULL,
    [non_taxable_sched_tsusa] [nchar](3) NULL,
    [fk_sic_sched_b] [bigint] NULL,
    [fk_sic_sched_tsusa] [bigint] NULL
) ON [PRIMARY]
</code>```

STAGING.dbo.sics_altered:

```<code>CREATE TABLE [dbo].[sics_altered](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [sic_code] [varchar](4) NULL,
    [sic_description] [varchar](max) NULL,
    [created_on] [datetime] NOT NULL,
    [created_by] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
</code>```

The rest are in PRESTAGING:

PRESTAGING.dbo.TRANSACTIONS_FROM_SOCRATA:
This is the table with 1.3 million rows

```<code>CREATE TABLE [dbo].[TRANSACTIONS_FROM_SOCRATA](
    [Trade] [varchar](255) NULL,
    [Year] [varchar](255) NULL,
    [Month] [varchar](50) NULL,
    [Commodity_Code] [varchar](50) NULL,
    [Commodity_Short_Name] [varchar](255) NULL,
    [Commodity_description] [varchar](255) NULL,
    [cty_code] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [Subcountry_code] [varchar](50) NULL,
    [district] [varchar](50) NULL,
    [dist_name] [varchar](255) NULL,
    [data] [varchar](50) NULL,
    [sitc] [varchar](50) NULL,
    [SITC_Short_Desc] [varchar](255) NULL,
    [SITC_Long_Desc] [varchar](255) NULL,
    [naics] [varchar](50) NULL,
    [NAICS_description] [varchar](255) NULL,
    [end_use_i] [varchar](50) NULL,
    [end_use_e] [varchar](50) NULL,
    [hts_desc] [varchar](255) NULL,
    [unit_1] [varchar](50) NULL,
    [qty_1] [varchar](50) NULL,
    [unit_2] [varchar](50) NULL,
    [qty_2] [varchar](50) NULL,
    [ves_val_mo] [varchar](50) NULL,
    [ves_wgt_mo] [varchar](50) NULL,
    [cards_mo] [varchar](50) NULL,
    [air_val_mo] [varchar](50) NULL,
    [air_wgt_mo] [varchar](50) NULL,
    [dut_val_mo] [varchar](50) NULL,
    [cal_dut_mo] [varchar](50) NULL,
    [con_cha_mo] [varchar](50) NULL,
    [con_cif_mo] [varchar](50) NULL,
    [gen_val_mo] [varchar](50) NULL,
    [gen_cha_mo] [varchar](50) NULL,
    [gen_cif_mo] [varchar](50) NULL,
    [air_cha_mo] [varchar](50) NULL,
    [ves_cha_mo] [varchar](50) NULL,
    [cnt_cha_mo] [varchar](50) NULL,
    [rev_data] [varchar](50) NULL
) ON [PRIMARY]
</code>```

PRESTAGING.dbo.MIOB_TBL:

```<code>CREATE TABLE [dbo].[MIOB_TBL](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [COMM] [nchar](10) NOT NULL,
    [INSUMO] [nchar](3) NULL,
    [PBTO] [nchar](4) NULL,
    [SIC] [nchar](4) NULL,
    [NAICS] [nchar](6) NULL,
    [TRIBUTA] [nchar](3) NULL,
    [NTRIBUTA] [nchar](3) NULL,
    [LAST_UPDATE] [date] NULL,
    [LAST_UPDATED_BY] [nchar](20) NULL,
    [CREATION_DATE] [date] NULL,
    [CREATED_BY] [nchar](15) NULL,
    [migrated_on] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
</code>```

PRESTAGING.dbo.MIOTSUSA_TBL:

```<code>CREATE TABLE [dbo].[MIOTSUSA_TBL](
    [COMM] [nchar](10) NOT NULL,
    [INSUMO] [nchar](3) NULL,
    [PBTO] [nchar](4) NULL,
    [SIC] [nchar](4) NULL,
    [NAICS] [nchar](6) NULL,
    [TRIBUTA] [nchar](3) NULL,
    [NTRIBUTA] [nchar](3) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
    [migrated_on] [datetime] NOT NULL
) ON [PRIMARY]
</code>```

PRESTAGING.dbo.MSCHB_TBL:

```<code>CREATE TABLE [dbo].[MSCHB_TBL](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [COMM] [nchar](10) NOT NULL,
    [DESC_COMM] [nchar](50) NULL,
    [UNIDAD] [nchar](3) NULL,
    [LAST_UPDATE] [date] NULL,
    [LAST_UPDATED_BY] [nchar](20) NULL,
    [CREATION_DATE] [date] NULL,
    [CREATED_BY] [nchar](15) NULL,
    [migrated_on] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
</code>```

PRESTAGING.dbo.MTSUSA_TBL

```<code>CREATE TABLE [dbo].[MTSUSA_TBL](
    [COMM] [nchar](10) NOT NULL,
    [DESC_COMM] [nchar](50) NULL,
    [UNIDAD] [nchar](3) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
    [migrated_on] [datetime] NOT NULL
) ON [PRIMARY]
</code>```

Let me know if there's anything else I need to provide.

答:
With all those left outer joins, the query optimizer has to start with TRANSACTION_FROM_SOCRATA, so I would start with that. The only filtering is the NOT IN clause--would that cut down the 1MM rows to something more reasonable? If not, you're pretty much doomed to running at least one table scan (and possibly several) on the entire table.

If filtering on Commodity_Code would significantly cut things down, that can only be done if the column is indexed, so that SQL can find and read only those rows. It can only do that if there is an index on column--otherwise you're back to a table scan. Similarly, having an index on commodity_code in table commodities_copy` would help as well, if that table is large.

As discussed in the comments, a NOT EXISTS check would be most efficient, written as a correlated subquery:

```<code>WHERE NOT EXISTS (select commodity_code
                   from STAGING.dbo.commodities_copy
                   where commodity_code = socrtata.Commodity_Code)
</code>```

(I'd want to do a lot of testing on this, checking and double-checking everything. Improving performance is tricky, doubly so when done through SO.)

Body:
I have the following query that will be used to fetch data from legacy tables. It's no surprise but the amount of data is huge and thus it takes a long time. The first select takes 40 minutes to run using an empty dbo.commodities_copy table as a starting point and yields around 26,000 rows. Keep in mind that there are separate databases: STAGING and PRESTAGING and that some joins are made using non-PK fields, which is most definately making an impact in its performance. This is something that I cannot fix, due to the way data was organized from the start. Also the transaction table has around 1 million rows, which also impacts heavily on performance. The entire script takes a total of 3.5 hours to execute when using an EMPTY dbo.commodities_copy table. I have not tested on insertion to a table with data.

The goal of the query is to get commodity information from the transaction table (if you guessed this was supposed to be noSQL data, you guessed right) and if the commodity code exists in the commodity table, do not insert a commodity in it.

The group bys are absolutely needed to get around duplicates, since a transactions may share the same commodity. The commodity code should be unique in the commodities table, but currently it is not - though if it helps, it's possible we could alter it.

What can I do to speed it up?

INSERT INTO STAGING.dbo.commodities_copy
(commodity_code,
short_description_sched_b,
short_description_sched_tsusa,
long_description_sched_b,
long_description_sched_tsusa,
measurement_unit_1_sched_b,
measurement_unit_1_sched_tsusa,
measurement_unit_2_sched_b,
measurement_unit_2_sched_tsusa,
end_use_sched_b,
end_use_sched_tsusa,
year,
created_by,
created_on,
taxable_sched_b,
taxable_sched_tsusa,
non_taxable_sched_b,
non_taxable_sched_tsusa,
fk_sic_sched_b,
fk_sic_sched_tsusa,
chapter,
header,
sub_header,
needs_validation)
SELECT
    --Distinct
    Commodity_Code,
    iif(miob2.DESC_COMM is null, UPPER(socrata.Commodity_Short_Name), miob2.DESC_COMM) as short_commmodity_description_b,
    iif(mio2tsusa.DESC_COMM is null, UPPER(socrata.Commodity_Short_Name), mio2tsusa.DESC_COMM) as short_commmodity_description_tsusa,
    socrata.Commodity_description as long_commodity_description_b,
    socrata.Commodity_description as long_commodity_description_tsusa,
    iif(miob2.UNIDAD is null, socrata.unit_1, miob2.UNIDAD) as unit_1_b,
    iif(mio2tsusa.UNIDAD is null, socrata.unit_1, mio2tsusa.UNIDAD) as unit_1_tsusa,
    MAX(socrata.unit_2) as unit_2_b,
    MAX(socrata.unit_2) as unit_2_tsusa,
    socrata.end_use_e as end_use_b,
    socrata.end_use_i as end_use_tsusa,
    MAX(socrata.[year]),
    'system' as created_by,
    getdate() as created_on,
    miob.TRIBUTA as taxable_b,
    miotsusa.TRIBUTA as taxable_tsusa,
    miob.NTRIBUTA as non_taxable_b,
    miotsusa.NTRIBUTA as non_taxable_tsusa,
    sicb.id as sic_id_b,
    sictsusa.id as sic_id_tsusa,
    SUBSTRING(Commodity_Code, 1, 2) as chapter,
    SUBSTRING(Commodity_Code, 1, 4) as header,
    SUBSTRING(Commodity_Code, 1, 6) as sub_header,
    0 as needs_validation

FROM PRE_STAGING.dbo.TRANSACTIONS_FROM_SOCRATA socrata
Left join PRE_STAGING.DBO.MIOB_TBL miob ON miob.COMM=socrata.Commodity_Code
Left join PRE_STAGING.dbo.MSCHB_TBL miob2 ON miob2.COMM=socrata.Commodity_Code
Left join PRE_STAGING.dbo.MIOTSUSA_TBL miotsusa ON miotsusa.COMM=socrata.Commodity_Code
Left join PRE_STAGING.dbo.MTSUSA_TBL mio2tsusa ON mio2tsusa.COMM=socrata.Commodity_Code
Left join STAGING.dbo.sics_altered sicb ON sicb.sic_code = miob.SIC
Left join STAGING.dbo.sics_altered sictsusa ON sictsusa.sic_code = miotsusa.SIC
WHERE NOT EXISTS
(Select Distinct commodity_code from STAGING.dbo.commodities_copy)
group by
Commodity_Code,
iif(miob2.DESC_COMM is null, UPPER(socrata.Commodity_Short_Name), miob2.DESC_COMM),
iif(mio2tsusa.DESC_COMM is null, UPPER(socrata.Commodity_Short_Name), mio2tsusa.DESC_COMM),
socrata.Commodity_description,
socrata.Commodity_description,
iif(miob2.UNIDAD is null, socrata.unit_1, miob2.UNIDAD),
iif(mio2tsusa.UNIDAD is null, socrata.unit_1, mio2tsusa.UNIDAD),
socrata.end_use_e,
socrata.end_use_i,
miob.TRIBUTA,
miotsusa.TRIBUTA,
miob.NTRIBUTA,
miotsusa.NTRIBUTA,
sicb.id,
sictsusa.id,
SUBSTRING(Commodity_Code, 1, 2),
SUBSTRING(Commodity_Code, 1, 4),
SUBSTRING(Commodity_Code, 1, 6)

The tables used are the following:

STAGING.dbo.commodities_copy:

CREATE TABLE [dbo].[commodities_copy](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [chapter] [varchar](5) NULL,
    [header] [varchar](5) NULL,
    [sub_header] [varchar](10) NULL,
    [commodity_code] [varchar](20) NULL,
    [short_description_sched_b] [varchar](100) NULL,
    [long_description_sched_b] [varchar](200) NULL,
    [measurement_unit_1_sched_b] [varchar](5) NULL,
    [measurement_unit_2_sched_b] [varchar](5) NULL,
    [end_use_sched_b] [int] NULL,
    [sitc_sched_b] [varchar](20) NULL,
    [usda_sched_b] [int] NULL,
    [hitech_sched_b] [int] NULL,
    [naics_fk_id_sched_b] [bigint] NULL,
    [short_description_sched_tsusa] [varchar](100) NULL,
    [long_description_sched_tsusa] [varchar](200) NULL,
    [measurement_unit_1_sched_tsusa] [varchar](5) NULL,
    [measurement_unit_2_sched_tsusa] [varchar](5) NULL,
    [end_use_sched_tsusa] [int] NULL,
    [sitc_sched_tsusa] [varchar](20) NULL,
    [usda_sched_tsusa] [int] NULL,
    [hitech_sched_tsusa] [int] NULL,
    [naics_fk_id_sched_tsusa] [bigint] NULL,
    [year] [int] NOT NULL,
    [created_on] [datetime] NOT NULL,
    [created_by] [varchar](50) NULL,
    [updated_on] [datetime] NULL,
    [updated_by] [varchar](50) NULL,
    [needs_validation] [bit] NOT NULL,
    [taxable_sched_b] [nchar](3) NULL,
    [non_taxable_sched_b] [nchar](3) NULL,
    [taxable_sched_tsusa] [nchar](3) NULL,
    [non_taxable_sched_tsusa] [nchar](3) NULL,
    [fk_sic_sched_b] [bigint] NULL,
    [fk_sic_sched_tsusa] [bigint] NULL
) ON [PRIMARY]

STAGING.dbo.sics_altered:

CREATE TABLE [dbo].[sics_altered](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [sic_code] [varchar](4) NULL,
    [sic_description] [varchar](max) NULL,
    [created_on] [datetime] NOT NULL,
    [created_by] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The rest are in PRESTAGING:

PRESTAGING.dbo.TRANSACTIONS_FROM_SOCRATA:
This is the table with 1.3 million rows

CREATE TABLE [dbo].[TRANSACTIONS_FROM_SOCRATA](
    [Trade] [varchar](255) NULL,
    [Year] [varchar](255) NULL,
    [Month] [varchar](50) NULL,
    [Commodity_Code] [varchar](50) NULL,
    [Commodity_Short_Name] [varchar](255) NULL,
    [Commodity_description] [varchar](255) NULL,
    [cty_code] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [Subcountry_code] [varchar](50) NULL,
    [district] [varchar](50) NULL,
    [dist_name] [varchar](255) NULL,
    [data] [varchar](50) NULL,
    [sitc] [varchar](50) NULL,
    [SITC_Short_Desc] [varchar](255) NULL,
    [SITC_Long_Desc] [varchar](255) NULL,
    [naics] [varchar](50) NULL,
    [NAICS_description] [varchar](255) NULL,
    [end_use_i] [varchar](50) NULL,
    [end_use_e] [varchar](50) NULL,
    [hts_desc] [varchar](255) NULL,
    [unit_1] [varchar](50) NULL,
    [qty_1] [varchar](50) NULL,
    [unit_2] [varchar](50) NULL,
    [qty_2] [varchar](50) NULL,
    [ves_val_mo] [varchar](50) NULL,
    [ves_wgt_mo] [varchar](50) NULL,
    [cards_mo] [varchar](50) NULL,
    [air_val_mo] [varchar](50) NULL,
    [air_wgt_mo] [varchar](50) NULL,
    [dut_val_mo] [varchar](50) NULL,
    [cal_dut_mo] [varchar](50) NULL,
    [con_cha_mo] [varchar](50) NULL,
    [con_cif_mo] [varchar](50) NULL,
    [gen_val_mo] [varchar](50) NULL,
    [gen_cha_mo] [varchar](50) NULL,
    [gen_cif_mo] [varchar](50) NULL,
    [air_cha_mo] [varchar](50) NULL,
    [ves_cha_mo] [varchar](50) NULL,
    [cnt_cha_mo] [varchar](50) NULL,
    [rev_data] [varchar](50) NULL
) ON [PRIMARY]

PRESTAGING.dbo.MIOB_TBL:

CREATE TABLE [dbo].[MIOB_TBL](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [COMM] [nchar](10) NOT NULL,
    [INSUMO] [nchar](3) NULL,
    [PBTO] [nchar](4) NULL,
    [SIC] [nchar](4) NULL,
    [NAICS] [nchar](6) NULL,
    [TRIBUTA] [nchar](3) NULL,
    [NTRIBUTA] [nchar](3) NULL,
    [LAST_UPDATE] [date] NULL,
    [LAST_UPDATED_BY] [nchar](20) NULL,
    [CREATION_DATE] [date] NULL,
    [CREATED_BY] [nchar](15) NULL,
    [migrated_on] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

PRESTAGING.dbo.MIOTSUSA_TBL:

CREATE TABLE [dbo].[MIOTSUSA_TBL](
    [COMM] [nchar](10) NOT NULL,
    [INSUMO] [nchar](3) NULL,
    [PBTO] [nchar](4) NULL,
    [SIC] [nchar](4) NULL,
    [NAICS] [nchar](6) NULL,
    [TRIBUTA] [nchar](3) NULL,
    [NTRIBUTA] [nchar](3) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
    [migrated_on] [datetime] NOT NULL
) ON [PRIMARY]

PRESTAGING.dbo.MSCHB_TBL:

CREATE TABLE [dbo].[MSCHB_TBL](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [COMM] [nchar](10) NOT NULL,
    [DESC_COMM] [nchar](50) NULL,
    [UNIDAD] [nchar](3) NULL,
    [LAST_UPDATE] [date] NULL,
    [LAST_UPDATED_BY] [nchar](20) NULL,
    [CREATION_DATE] [date] NULL,
    [CREATED_BY] [nchar](15) NULL,
    [migrated_on] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

PRESTAGING.dbo.MTSUSA_TBL

CREATE TABLE [dbo].[MTSUSA_TBL](
    [COMM] [nchar](10) NOT NULL,
    [DESC_COMM] [nchar](50) NULL,
    [UNIDAD] [nchar](3) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
    [migrated_on] [datetime] NOT NULL
) ON [PRIMARY]

Let me know if there's anything else I need to provide.

Body:
With all those left outer joins, the query optimizer has to start with TRANSACTION_FROM_SOCRATA, so I would start with that. The only filtering is the NOT IN clause--would that cut down the 1MM rows to something more reasonable? If not, you're pretty much doomed to running at least one table scan (and possibly several) on the entire table.

If filtering on Commodity_Code would significantly cut things down, that can only be done if the column is indexed, so that SQL can find and read only those rows. It can only do that if there is an index on column--otherwise you're back to a table scan. Similarly, having an index on commodity_code in table commodities_copy` would help as well, if that table is large.

As discussed in the comments, a NOT EXISTS check would be most efficient, written as a correlated subquery:

WHERE NOT EXISTS (select commodity_code
                   from STAGING.dbo.commodities_copy
                   where commodity_code = socrtata.Commodity_Code)

(I'd want to do a lot of testing on this, checking and double-checking everything. Improving performance is tricky, doubly so when done through SO.)


问:
Twitter datepicker in dropdown menu
I want to add a datepicker in dropdown menu. I use this datepicker link

Next, I added code to not close the menu when I click on inputs select date, the input disappears. How can i fix it?

```<code>$('.datepicker').datepicker({
    autoclose: true
});

$('.dropdown-toggle').dropdown();
$('.jsolr-search-result-form .dropdown input, .jsolr-search-result-form .dropdown label, .jsolr-search-result-form ul span').click(function(e) {
  e.stopPropagation();
});
</code>```

答:
This one was very troublesome. I also have a couple of datepickers located within some Bootstrap drop-down menus. I solved this using two ways.

The first uses jQuery to stop the event from bubbling up one time when the DatePicker is clicked

```<code>$('.yourDatePicker').on('hide', function(event) {
    event.stopPropagation();
    $(this).closest('.yourDropDown').one('hide.bs.dropdown', function(ev) {
        return false;
    });
});
</code>```

The other, more permant solution was to change the datepicker.js file itself, but it sounds like you are using the datepicker cdn. If you can download it and use it locally, you can crack it open you will find code that looks like this

```<code>click: function(e){
    e.preventDefault();
    var target = $(e.target).closest('span, td, th'),
        year, month, day;
    if (target.length === 1){
        switch (target[0].nodeName.toLowerCase()){
...ect...
</code>```

If you add a e.stopPropagation() function to this area, like this

```<code>click: function(e){
    e.preventDefault();
    e.stopPropagation(); // <-- add this
    var target = $(e.target).closest('span, td, th'),
        year, month, day;
    if (target.length === 1){
        switch (target[0].nodeName.toLowerCase()){
</code>```

your datepickers will stop interfering with your other DOM elements, though this solution is more widespread, and I would test to make sure it doesn't impact other areas in your code.

Title: Twitter datepicker in dropdown menu

Body:
I want to add a datepicker in dropdown menu. I use this datepicker link

Next, I added code to not close the menu when I click on inputs select date, the input disappears. How can i fix it?

$('.datepicker').datepicker({
    autoclose: true
});

$('.dropdown-toggle').dropdown();
$('.jsolr-search-result-form .dropdown input, .jsolr-search-result-form .dropdown label, .jsolr-search-result-form ul span').click(function(e) {
  e.stopPropagation();
});

Body:
This one was very troublesome. I also have a couple of datepickers located within some Bootstrap drop-down menus. I solved this using two ways.

The first uses jQuery to stop the event from bubbling up one time when the DatePicker is clicked

$('.yourDatePicker').on('hide', function(event) {
    event.stopPropagation();
    $(this).closest('.yourDropDown').one('hide.bs.dropdown', function(ev) {
        return false;
    });
});

The other, more permant solution was to change the datepicker.js file itself, but it sounds like you are using the datepicker cdn. If you can download it and use it locally, you can crack it open you will find code that looks like this

click: function(e){
    e.preventDefault();
    var target = $(e.target).closest('span, td, th'),
        year, month, day;
    if (target.length === 1){
        switch (target[0].nodeName.toLowerCase()){
...ect...

If you add a e.stopPropagation() function to this area, like this

click: function(e){
    e.preventDefault();
    e.stopPropagation(); // <-- add this
    var target = $(e.target).closest('span, td, th'),
        year, month, day;
    if (target.length === 1){
        switch (target[0].nodeName.toLowerCase()){

your datepickers will stop interfering with your other DOM elements, though this solution is more widespread, and I would test to make sure it doesn't impact other areas in your code.


问:
Update/Modify existing file on server
I want to add content to file on every page load, but it overwrite file, not updating content. my original code:

```<code>$file_handle = fopen('log.txt', 'w');
fwrite($file_handle, $message);
fclose($file_handle);
</code>```

After i searching, nothing found to solve my prolem:

How to edit/update a txt file with php

PHP Modify a single line in a text file

I used w+ and r+ but it didn't work.

```<code>$file_handle = fopen('log.txt', 'w+');
fwrite($file_handle, $message);
fclose($file_handle);
</code>```

Both write file with new content, not keep old contents. I want to keep old contents, just append new content to existing file.

答:
You are trying to append to a file so you need the mode 'a':

```<code>$file_handle = fopen('log.txt', 'a');
fwrite($file_handle, $message);
fclose($file_handle);
</code>```

From the docs:

'a': Open for writing only; place the file pointer at the end of the file. If the file does not exist, attempt to create it. In this mode, fseek() has no effect, writes are always appended.

Title: Update/Modify existing file on server

Body:
I want to add content to file on every page load, but it overwrite file, not updating content. my original code:

$file_handle = fopen('log.txt', 'w');
fwrite($file_handle, $message);
fclose($file_handle);

After i searching, nothing found to solve my prolem:

How to edit/update a txt file with php

PHP Modify a single line in a text file

I used w+ and r+ but it didn't work.

$file_handle = fopen('log.txt', 'w+');
fwrite($file_handle, $message);
fclose($file_handle);

Both write file with new content, not keep old contents. I want to keep old contents, just append new content to existing file.

Body:
You are trying to append to a file so you need the mode 'a':

$file_handle = fopen('log.txt', 'a');
fwrite($file_handle, $message);
fclose($file_handle);

From the docs:

'a': Open for writing only; place the file pointer at the end of the file. If the file does not exist, attempt to create it. In this mode, fseek() has no effect, writes are always appended.


问:
App Icons not included in build from Xcode
I can't see iOS AppIcon when installing the build from the latest Xcode Beta. What could be the possible error?

I have included the required icon sizes including 1024x1024.

答:
In my case the app icon started to show up correctly in Finder after I changed

```<code><key>CFBundleIconFile</key>
<string>AppIcon</string>
</code>```

into

```<code><key>CFBundleIconFile</key>
<string>AppIcon.icns</string>
</code>```

in the Info.plist. My XCode version is 8.3.3

ContentLicense: CC BY-SA 4.0

Body:
In my case the app icon started to show up correctly in Finder after I changed

<key>CFBundleIconFile</key>
<string>AppIcon</string>

into

<key>CFBundleIconFile</key>
<string>AppIcon.icns</string>

in the Info.plist. My XCode version is 8.3.3


问:
Static attributes in Cheetah
I'm working on a project using Cheetah. I'd like to create a class like the following one:

```<code>from Cheetah.Template import Template
class TemplateObject(Template):
    className = "Default ClassName"
    def header(self):
        return "My Class name is {}".format(type(self).className)
</code>```

and then i'd like to be able in one of my template to do something like:

```<code>#extends TemplateObject
#staticarg className = "CustomClassName" ## Something to declare a static member ?
$self.header()
</code>```

is it possible to do this ?

Thank you

答:
You're looking for #attr.

Example:

```<code>#attr className = "CustomClassName"
</code>```

Title: Static attributes in Cheetah

Body:
I'm working on a project using Cheetah. I'd like to create a class like the following one:

from Cheetah.Template import Template
class TemplateObject(Template):
    className = "Default ClassName"
    def header(self):
        return "My Class name is {}".format(type(self).className)

and then i'd like to be able in one of my template to do something like:

#extends TemplateObject
#staticarg className = "CustomClassName" ## Something to declare a static member ?
$self.header()

is it possible to do this ?

Thank you

Body:
You're looking for #attr.

Example:

#attr className = "CustomClassName"


问:
Adjust Elements after Manipulating String into AttributedString - Swift 4
I have the following which allows me to create a bullet list which works really well, however, after the bullet list is created I need to manipulate the outputted Attributed string to have certain elements either in bold or in italics or both.

The function I have is:

```<code>@IBOutlet var label: UILabel!
let bulletString = ["String 1","String 2","String 3"]

label.attributedText = label.bulletPoints(stringList: bulletString, font: UIFont.stdFontMediumSeventeen, bullet: "•", lineSpacing: 4, paragraphSpacing: 4, textColor: UIColor.darkGreyColor, bulletColor: UIColor.darkGreyColor)

func bulletPoints(stringList: [String],font: UIFont,bullet: String = "\u{2022}",indentation: CGFloat = 20,lineSpacing: CGFloat = 2,paragraphSpacing: CGFloat = 12,textColor: UIColor = .gray,bulletColor: UIColor = .red) -> NSAttributedString{
    let textAttributes: [NSAttributedStringKey: Any] = [NSAttributedStringKey.font: font, NSAttributedStringKey.foregroundColor: textColor]
    let bulletAttributes: [NSAttributedStringKey: Any] = [NSAttributedStringKey.font: font, NSAttributedStringKey.foregroundColor: bulletColor]

    let paragraphStyle = NSMutableParagraphStyle()
    let nonOptions = [NSTextTab.OptionKey: Any]()
    paragraphStyle.tabStops = [NSTextTab(textAlignment: .left, location: indentation, options: nonOptions)]
    paragraphStyle.defaultTabInterval = indentation
    paragraphStyle.lineSpacing = lineSpacing
    paragraphStyle.paragraphSpacing = paragraphSpacing
    paragraphStyle.headIndent = indentation

    let bulletList = NSMutableAttributedString()
    for string in stringList {
        let formattedString = "\(bullet)\t\(string)\n"
        let attributedString = NSMutableAttributedString(string: formattedString)

        attributedString.addAttributes(
            [NSAttributedStringKey.paragraphStyle : paragraphStyle],
            range: NSMakeRange(0, attributedString.length))

        attributedString.addAttributes(
            textAttributes,
            range: NSMakeRange(0, attributedString.length))

        let string:NSString = NSString(string: formattedString)
        let rangeForBullet:NSRange = string.range(of: bullet)
        attributedString.addAttributes(bulletAttributes, range: rangeForBullet)
        bulletList.append(attributedString)
    }
    return bulletList
}
</code>```

What I am looking for is a way to pass in a boolean to state if the bullet string requires either bold or italic text and if so what the elements of the intital string are that need this treatment.

The bulletPoints function sits in an extension file and works as expected.

答:
One of the possible ways to make it work with the function mentioned in the question - to modify stringList parameter.

First of all let's define model class BulletString:

```<code>class BulletString {
   var text: String
   var attributes: [NSAttributedStringKey : Any]?

   init(string: String) {
      text = string
   }
}
</code>```

Now your bullet stringList in you function should be [BulletString] type. Define two bulletStrings and pass them to your function. Here is a working solution with your function:

```<code>    let bulletString1 = BulletString.init(string: "string1")
    bulletString1.attributes = [NSAttributedStringKey.font: UIFont.boldSystemFont(ofSize: 18.0)]

    let bulletString2 = BulletString.init(string: "string2")

    let bullets = [bulletString1, bulletString2]

    label.attributedText = bulletPoints(stringList: bullets, font: UIFont.systemFont(ofSize: 17), bullet: "•", lineSpacing: 4, paragraphSpacing: 4, textColor: UIColor.darkGray, bulletColor: UIColor.darkGray)
    label.textColor = .black

func bulletPoints(stringList: [BulletString], font: UIFont,bullet: String = "\u{2022}",indentation: CGFloat = 20,lineSpacing: CGFloat = 2,paragraphSpacing: CGFloat = 12,textColor: UIColor = .gray,bulletColor: UIColor = .red) -> NSAttributedString{
    let textAttributes: [NSAttributedStringKey: Any] = [NSAttributedStringKey.font: font, NSAttributedStringKey.foregroundColor: textColor]
    let bulletAttributes: [NSAttributedStringKey: Any] = [NSAttributedStringKey.font: font, NSAttributedStringKey.foregroundColor: bulletColor]

    let paragraphStyle = NSMutableParagraphStyle()
    let nonOptions = [NSTextTab.OptionKey: Any]()
    paragraphStyle.tabStops = [NSTextTab(textAlignment: .left, location: indentation, options: nonOptions)]
    paragraphStyle.defaultTabInterval = indentation
    paragraphStyle.lineSpacing = lineSpacing
    paragraphStyle.paragraphSpacing = paragraphSpacing
    paragraphStyle.headIndent = indentation

    let bulletList = NSMutableAttributedString()
    for bulletString in stringList {
      let formattedString = "\(bullet)\t\(bulletString.text)\n"
      let attributedString = NSMutableAttributedString(string: formattedString)

      attributedString.addAttributes(
        [NSAttributedStringKey.paragraphStyle : paragraphStyle],
        range: NSMakeRange(0, attributedString.length))

      attributedString.addAttributes(
        textAttributes,
        range: NSMakeRange(0, attributedString.length))

      // Here your custom attributes you provided in BulletString
      if let attr = bulletString.attributes {
        attributedString.addAttributes(attr, range: NSMakeRange(0, attributedString.length))
      }

      let string:NSString = NSString(string: formattedString)
      let rangeForBullet:NSRange = string.range(of: bullet)
      attributedString.addAttributes(bulletAttributes, range: rangeForBullet)

      bulletList.append(attributedString)
    }
    return bulletList
}
</code>```

Results

Body:
I have the following which allows me to create a bullet list which works really well, however, after the bullet list is created I need to manipulate the outputted Attributed string to have certain elements either in bold or in italics or both.

The function I have is:

@IBOutlet var label: UILabel!
let bulletString = ["String 1","String 2","String 3"]

label.attributedText = label.bulletPoints(stringList: bulletString, font: UIFont.stdFontMediumSeventeen, bullet: "•", lineSpacing: 4, paragraphSpacing: 4, textColor: UIColor.darkGreyColor, bulletColor: UIColor.darkGreyColor)

func bulletPoints(stringList: [String],font: UIFont,bullet: String = "\u{2022}",indentation: CGFloat = 20,lineSpacing: CGFloat = 2,paragraphSpacing: CGFloat = 12,textColor: UIColor = .gray,bulletColor: UIColor = .red) -> NSAttributedString{
    let textAttributes: [NSAttributedStringKey: Any] = [NSAttributedStringKey.font: font, NSAttributedStringKey.foregroundColor: textColor]
    let bulletAttributes: [NSAttributedStringKey: Any] = [NSAttributedStringKey.font: font, NSAttributedStringKey.foregroundColor: bulletColor]

    let paragraphStyle = NSMutableParagraphStyle()
    let nonOptions = [NSTextTab.OptionKey: Any]()
    paragraphStyle.tabStops = [NSTextTab(textAlignment: .left, location: indentation, options: nonOptions)]
    paragraphStyle.defaultTabInterval = indentation
    paragraphStyle.lineSpacing = lineSpacing
    paragraphStyle.paragraphSpacing = paragraphSpacing
    paragraphStyle.headIndent = indentation

    let bulletList = NSMutableAttributedString()
    for string in stringList {
        let formattedString = "\(bullet)\t\(string)\n"
        let attributedString = NSMutableAttributedString(string: formattedString)

        attributedString.addAttributes(
            [NSAttributedStringKey.paragraphStyle : paragraphStyle],
            range: NSMakeRange(0, attributedString.length))

        attributedString.addAttributes(
            textAttributes,
            range: NSMakeRange(0, attributedString.length))

        let string:NSString = NSString(string: formattedString)
        let rangeForBullet:NSRange = string.range(of: bullet)
        attributedString.addAttributes(bulletAttributes, range: rangeForBullet)
        bulletList.append(attributedString)
    }
    return bulletList
}

What I am looking for is a way to pass in a boolean to state if the bullet string requires either bold or italic text and if so what the elements of the intital string are that need this treatment.

The bulletPoints function sits in an extension file and works as expected.

Body:
One of the possible ways to make it work with the function mentioned in the question - to modify stringList parameter.

First of all let's define model class BulletString:

class BulletString {
   var text: String
   var attributes: [NSAttributedStringKey : Any]?

   init(string: String) {
      text = string
   }
}

Now your bullet stringList in you function should be [BulletString] type. Define two bulletStrings and pass them to your function. Here is a working solution with your function:

    let bulletString1 = BulletString.init(string: "string1")
    bulletString1.attributes = [NSAttributedStringKey.font: UIFont.boldSystemFont(ofSize: 18.0)]

    let bulletString2 = BulletString.init(string: "string2")

    let bullets = [bulletString1, bulletString2]

    label.attributedText = bulletPoints(stringList: bullets, font: UIFont.systemFont(ofSize: 17), bullet: "•", lineSpacing: 4, paragraphSpacing: 4, textColor: UIColor.darkGray, bulletColor: UIColor.darkGray)
    label.textColor = .black

func bulletPoints(stringList: [BulletString], font: UIFont,bullet: String = "\u{2022}",indentation: CGFloat = 20,lineSpacing: CGFloat = 2,paragraphSpacing: CGFloat = 12,textColor: UIColor = .gray,bulletColor: UIColor = .red) -> NSAttributedString{
    let textAttributes: [NSAttributedStringKey: Any] = [NSAttributedStringKey.font: font, NSAttributedStringKey.foregroundColor: textColor]
    let bulletAttributes: [NSAttributedStringKey: Any] = [NSAttributedStringKey.font: font, NSAttributedStringKey.foregroundColor: bulletColor]

    let paragraphStyle = NSMutableParagraphStyle()
    let nonOptions = [NSTextTab.OptionKey: Any]()
    paragraphStyle.tabStops = [NSTextTab(textAlignment: .left, location: indentation, options: nonOptions)]
    paragraphStyle.defaultTabInterval = indentation
    paragraphStyle.lineSpacing = lineSpacing
    paragraphStyle.paragraphSpacing = paragraphSpacing
    paragraphStyle.headIndent = indentation

    let bulletList = NSMutableAttributedString()
    for bulletString in stringList {
      let formattedString = "\(bullet)\t\(bulletString.text)\n"
      let attributedString = NSMutableAttributedString(string: formattedString)

      attributedString.addAttributes(
        [NSAttributedStringKey.paragraphStyle : paragraphStyle],
        range: NSMakeRange(0, attributedString.length))

      attributedString.addAttributes(
        textAttributes,
        range: NSMakeRange(0, attributedString.length))

      // Here your custom attributes you provided in BulletString
      if let attr = bulletString.attributes {
        attributedString.addAttributes(attr, range: NSMakeRange(0, attributedString.length))
      }

      let string:NSString = NSString(string: formattedString)
      let rangeForBullet:NSRange = string.range(of: bullet)
      attributedString.addAttributes(bulletAttributes, range: rangeForBullet)

      bulletList.append(attributedString)
    }
    return bulletList
}

Results


问:
filtering in an ng-repeat from JSON response
I've got a question about filtering within a ng-repeat. But this isn't a simple question since I'm not really having a problem that needs a solution. In this case, I'm looking for the best possible answer that doesn't require a lot of code but still does the trick as to how I want it to. It will become more clear in my description.

a short introduction:

So I'm currently working on a shopping application. The shopping application has a connection with an existing PrestaShop webshop through the web service API. A rest API for PrestaShop. So after creating a customer section, products section, and a cart system, I would like to be able to update the "stock amount" of each product within the application. Many people would tell me: Well that's easy, just create a form with ng-repeat response data from the database and the possibility of updating the stock on let's say a button ng-click event.

Well I've done that but now the tricky part, and to explain I will provide some less important examples to make it more clear of what I'm asking:

The prestashop database

The prestashop database is a database that exists of over 250 tables. Among those tables are various connections through id's and attribute id's. Some simple examples are:

The customer table has an id_address row which connects to the address table. Within this table, all the 'customersaddress info is placed. So far, let's say updating a customer I've created a combination of update thecustomertable with thecustomer_idbut also update theaddresstable where theaddress_idis equal to theid_addressfrom the selectedcustomerwithin thecustomer` table.

Products also have some linked tables. Think about prices, taxes, sizes, and colors. The id_product_attribute is an attribute_id that could correspond to a color or size.

As explained above the prestashop database structure is very detailed which confines to using various filters for showing the wanted data.

Using the webservice

So for using the webservice I'm using a combination of angularJS, javascript, php, and JSON. The JSON is the actual data. by using $http.get(url, options) I'm able to get the data from the database. Then for showing the data I'm using response. A example is shown below:

```<code>$http.get('config/get/getCustomers.php', { cache: true }).then(function (response) {
    $scope.customers = response.data.customers.customer
});
</code>```

using $scope.customers = response.data.customers.customer gives me the option to show the customer data by using, for example, a ng-repeat as in the example below

    $http.get('config/get/getCustomers.php', { cache: true }).then(function (response) {
        $scope.customers = response.data.customers.customer
    });```
<pre class="snippet-code-html lang-html prettyprint-override"><code><script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.2.23/angular.min.js"></script>
<div class="row listrow" ng-repeat="customer in customers | orderBy: customer.id">
        <div class="col-lg-8">
            <p class="customer" ng-bind="customer.id + ' ' + customer.firstname + ' ' + customer.lastname">
      </p>
        </div>
</div></code>```

In this case, the response would be empty since there is no database connection. To create a full example for testing here is some JSON data you could use from the customers table.

```<code>{"customers":{"customer":[{"id":"1","id_default_group":"0","id_lang":"0","newsletter_date_add":"0000-00-00 00:00:00","ip_registration_newsletter":{},"last_passwd_gen":"2017-10-30 09:27:03","secure_key":{},"deleted":"0","passwd":"$2y$10$eZvPTD9bkxwSfWN7qovrmee\/Den2TqZ5a6YjcGC\/lha3oU59MCjOO","lastname":"TestStefans","firstname":"John","email":"pub@prestashop.com","id_gender":"0","birthday":"0000-00-00","newsletter":"0","optin":"0","website":{},"company":{},"siret":{},"ape":{},"outstanding_allow_amount":"0.000000","show_public_prices":"0","id_risk":"0","max_payment_days":"0","active":"1","note":{},"is_guest":"0","id_shop":"1","id_shop_group":"1","date_add":"2017-04-26 14:01:31","date_upd":"2017-10-27 15:56:54","reset_password_token":{},"reset_password_validity":"0000-00-00 00:00:00","associations":{"groups":{"@attributes":{"nodeType":"group","api":"groups"},"group":{"id":"3"}}}},{"id":"2","id_default_group":"0","id_lang":"1","newsletter_date_add":"0000-00-00 00:00:00","ip_registration_newsletter":{},"last_passwd_gen":"2017-10-27 15:56:55","secure_key":{},"deleted":"0","passwd":"$2y$10$C8M6TIuzmZjP9kh06Hai8.XyuNG9WcSi9L34oXqAuidsJkoYog4WW","lastname":"Demoers","firstname":"Demoers","email":"demo@sdwebdesign.nl","id_gender":"0","birthday":"0000-00-00","newsletter":"0","optin":"0","website":{},"company":{},"siret":{},"ape":{},"outstanding_allow_amount":"0.000000","show_public_prices":"0","id_risk":"0","max_payment_days":"0","active":"1","note":{},"is_guest":"0","id_shop":"1","id_shop_group":"1","date_add":"2017-04-26 14:01:33","date_upd":"2017-10-27 15:56:55","reset_password_token":{},"reset_password_validity":"0000-00-00 00:00:00","associations":{"groups":{"@attributes":{"nodeType":"group","api":"groups"},"group":{"id":"3"}}}}]}}
</code>```

So with this information here is my question:

While working on the application the following data is given within the stock_availables table.

So while using the $http.get() and the response functions I was able to create a form for updating the stock. But now take a look at the id_product_attribute. This row corresponds to a product_attribute. In this case a size or color. Now I would like to filter these results. In this case by using for example ng-if="id_product_attribute == 0" I'm able to filter in a way that only all with this id_attribute are shown. But I would like to show all of them. So also with id="1", id="2", etc. But there is where I'm wondering, is there a way to do this in a sufficient way that wouldn't require a ng-if for each id? Since there are around 50 different id_attribute's which each corresponds to a color or size. So is there a function that could get each different id and filter that in a sufficient way than using many ng-if clauses. Look at the following full code as an example.

myApp.controller('ProductsController', function($scope, $http){

    $http.get('config/get/getProducts.php', {cache: true}).then(function(response){
        $scope.products = response.data.products.product
    });
    // Stock check + view
    $http.get('config/get/getStock.php', {cache: true}).then(function (response) {
        $scope.stock_availables = response.data.stock_availables.stock_available
    });

// Update Stock
    $scope.updateStock = function(stock_available) {

        var stock_id = stock_available.id;
        var product_id = stock_available.id_product;
        var stock_quantity = stock_available.quantity;

        console.log(stock_id + product_id + stock_quantity);

        // Post to the database //

    };
});```
<pre class="snippet-code-html lang-html prettyprint-override"><code><script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.2.23/angular.min.js"></script>
<div class="row listrow">
        <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
            <ng-form name="stock">
                <div ng-repeat="stock_available in stock_availables">
                    <div class="row full-width padding-tb-15" ng-if="stock_available.id_product == product.id  && stock_available.id_product_attribute == 0" ng-repeat="product in products">
                        <div class="col-lg-12 form-stock-group hidden">
                            <input title="id" type="text" name="stockId" class="form-control" aria-describedby="" ng-value="stock_available.id">
                        </div>
                        <div class="col-lg-4">
                            <h5 ng-bind="product.name.language"></h5>
                        </div>
                        <div class="col-lg-3">
                            <h5 ng-bind="product.ean13"></h5>
                        </div>
                        <div class="col-lg-2">
                            <h5 ng-bind="product.reference"></h5>
                        </div>
                        <div ng-if="stock_available.id_product == product.id && stock_available.id_product_attribute == 0" ng-repeat="stock_available in stock_availables" class="col-lg-1 text-center">
                            <input title="stock_id" type="text" name="stock_id" class="form-control hidden" aria-describedby=""  ng-model="stock_available.id" ng-value="stock_available.id">
                            <input title="stock_id_product" type="text" name="stock_id_product" class="form-control hidden" aria-describedby="" ng-model="stock_available.id_product" ng-value="stock_available.id_product">
                            <h5 title="quantity" name="stockQuantity" aria-describedby="" ng-model="stock_available.quantity" ng-bind="stock_available.quantity"></h5>
                        </div>
                        <div ng-if="stock_available.id_product == product.id && stock_available.id_product_attribute == 0" ng-repeat="stock_available in stock_availables" class="col-lg-1 text-center">
                            <input title="updateStockAmount" class="form-control form-control-stock" type="number">
                        </div>
                        <div class="col-lg-1 text-center">

                                <i class="fa fa-print" aria-hidden="true"></i>

                        </div>
                    </div>
                </div>
            </ng-form>
        </div>
    </div></code>```

I trying to create the following view:

Product-id 1: then all sizes, colors, etc.

Product-id 2: then all sizes, colors, etc.

Yes it is possible with writing 50 different ng-if statements but I'm asking if there is a better, easier and more sufficient way to do this?

A simple build of the dummy code can be found here

As always,

Thanks in advance!

答:
I also would suggest using the db query to provide the ordering of your elements. However, we don't always have access to change a backend or how it functions.

SO ..

I would suggest using a sort function to convert your response data before setting $scope.customers

```<code>$http.get('config/get/getCustomers.php', { cache: true }).then(function (response) {
        var temp = response.data.customers.customer;
        temp.sort(fucntion (item1, item2) {
            if (item1.product === item2.product) {
                if (item1.size === item2.size) {
                    return item1.color > item2.color;
                }
                return item1.size > item2.size;
            }
            return item1.product > item2.product;
        }
        $scope.customers = temp
 });
</code>```

Body:
I've got a question about filtering within a ng-repeat. But this isn't a simple question since I'm not really having a problem that needs a solution. In this case, I'm looking for the best possible answer that doesn't require a lot of code but still does the trick as to how I want it to. It will become more clear in my description.

a short introduction:

So I'm currently working on a shopping application. The shopping application has a connection with an existing PrestaShop webshop through the web service API. A rest API for PrestaShop. So after creating a customer section, products section, and a cart system, I would like to be able to update the "stock amount" of each product within the application. Many people would tell me: Well that's easy, just create a form with ng-repeat response data from the database and the possibility of updating the stock on let's say a button ng-click event.

Well I've done that but now the tricky part, and to explain I will provide some less important examples to make it more clear of what I'm asking:

The prestashop database

The prestashop database is a database that exists of over 250 tables. Among those tables are various connections through id's and attribute id's. Some simple examples are:

The customer table has an id_address row which connects to the address table. Within this table, all the 'customersaddress info is placed. So far, let's say updating a customer I've created a combination of update thecustomertable with thecustomer_idbut also update theaddresstable where theaddress_idis equal to theid_addressfrom the selectedcustomerwithin thecustomer` table.

Products also have some linked tables. Think about prices, taxes, sizes, and colors. The id_product_attribute is an attribute_id that could correspond to a color or size.

As explained above the prestashop database structure is very detailed which confines to using various filters for showing the wanted data.

Using the webservice

So for using the webservice I'm using a combination of angularJS, javascript, php, and JSON. The JSON is the actual data. by using $http.get(url, options) I'm able to get the data from the database. Then for showing the data I'm using response. A example is shown below:

$http.get('config/get/getCustomers.php', { cache: true }).then(function (response) {
    $scope.customers = response.data.customers.customer
});

using $scope.customers = response.data.customers.customer gives me the option to show the customer data by using, for example, a ng-repeat as in the example below

    $http.get('config/get/getCustomers.php', { cache: true }).then(function (response) {
        $scope.customers = response.data.customers.customer
    });

In this case, the response would be empty since there is no database connection. To create a full example for testing here is some JSON data you could use from the customers table.

{"customers":{"customer":[{"id":"1","id_default_group":"0","id_lang":"0","newsletter_date_add":"0000-00-00 00:00:00","ip_registration_newsletter":{},"last_passwd_gen":"2017-10-30 09:27:03","secure_key":{},"deleted":"0","passwd":"$2y$10$eZvPTD9bkxwSfWN7qovrmee\/Den2TqZ5a6YjcGC\/lha3oU59MCjOO","lastname":"TestStefans","firstname":"John","email":"pub@prestashop.com","id_gender":"0","birthday":"0000-00-00","newsletter":"0","optin":"0","website":{},"company":{},"siret":{},"ape":{},"outstanding_allow_amount":"0.000000","show_public_prices":"0","id_risk":"0","max_payment_days":"0","active":"1","note":{},"is_guest":"0","id_shop":"1","id_shop_group":"1","date_add":"2017-04-26 14:01:31","date_upd":"2017-10-27 15:56:54","reset_password_token":{},"reset_password_validity":"0000-00-00 00:00:00","associations":{"groups":{"@attributes":{"nodeType":"group","api":"groups"},"group":{"id":"3"}}}},{"id":"2","id_default_group":"0","id_lang":"1","newsletter_date_add":"0000-00-00 00:00:00","ip_registration_newsletter":{},"last_passwd_gen":"2017-10-27 15:56:55","secure_key":{},"deleted":"0","passwd":"$2y$10$C8M6TIuzmZjP9kh06Hai8.XyuNG9WcSi9L34oXqAuidsJkoYog4WW","lastname":"Demoers","firstname":"Demoers","email":"demo@sdwebdesign.nl","id_gender":"0","birthday":"0000-00-00","newsletter":"0","optin":"0","website":{},"company":{},"siret":{},"ape":{},"outstanding_allow_amount":"0.000000","show_public_prices":"0","id_risk":"0","max_payment_days":"0","active":"1","note":{},"is_guest":"0","id_shop":"1","id_shop_group":"1","date_add":"2017-04-26 14:01:33","date_upd":"2017-10-27 15:56:55","reset_password_token":{},"reset_password_validity":"0000-00-00 00:00:00","associations":{"groups":{"@attributes":{"nodeType":"group","api":"groups"},"group":{"id":"3"}}}}]}}

So with this information here is my question:

While working on the application the following data is given within the stock_availables table.

So while using the $http.get() and the response functions I was able to create a form for updating the stock. But now take a look at the id_product_attribute. This row corresponds to a product_attribute. In this case a size or color. Now I would like to filter these results. In this case by using for example ng-if="id_product_attribute == 0" I'm able to filter in a way that only all with this id_attribute are shown. But I would like to show all of them. So also with id="1", id="2", etc. But there is where I'm wondering, is there a way to do this in a sufficient way that wouldn't require a ng-if for each id? Since there are around 50 different id_attribute's which each corresponds to a color or size. So is there a function that could get each different id and filter that in a sufficient way than using many ng-if clauses. Look at the following full code as an example.

myApp.controller('ProductsController', function($scope, $http){

    $http.get('config/get/getProducts.php', {cache: true}).then(function(response){
        $scope.products = response.data.products.product
    });
    // Stock check + view
    $http.get('config/get/getStock.php', {cache: true}).then(function (response) {
        $scope.stock_availables = response.data.stock_availables.stock_available
    });

// Update Stock
    $scope.updateStock = function(stock_available) {

        var stock_id = stock_available.id;
        var product_id = stock_available.id_product;
        var stock_quantity = stock_available.quantity;

        console.log(stock_id + product_id + stock_quantity);

        // Post to the database //

    };
});

            <ng-form name="stock">

                            <input title="id" type="text" name="stockId" class="form-control" aria-describedby="" ng-value="stock_available.id">

                            <input title="stock_id" type="text" name="stock_id" class="form-control hidden" aria-describedby=""  ng-model="stock_available.id" ng-value="stock_available.id">
                            <input title="stock_id_product" type="text" name="stock_id_product" class="form-control hidden" aria-describedby="" ng-model="stock_available.id_product" ng-value="stock_available.id_product">

                            <input title="updateStockAmount" class="form-control form-control-stock" type="number">

            </ng-form>

I trying to create the following view:

Product-id 1: then all sizes, colors, etc.

Product-id 2: then all sizes, colors, etc.

Yes it is possible with writing 50 different ng-if statements but I'm asking if there is a better, easier and more sufficient way to do this?

A simple build of the dummy code can be found here

As always,

Thanks in advance!

Body:
I also would suggest using the db query to provide the ordering of your elements. However, we don't always have access to change a backend or how it functions.

SO ..

I would suggest using a sort function to convert your response data before setting $scope.customers

$http.get('config/get/getCustomers.php', { cache: true }).then(function (response) {
        var temp = response.data.customers.customer;
        temp.sort(fucntion (item1, item2) {
            if (item1.product === item2.product) {
                if (item1.size === item2.size) {
                    return item1.color > item2.color;
                }
                return item1.size > item2.size;
            }
            return item1.product > item2.product;
        }
        $scope.customers = temp
 });


问:
Finding two matches with the same string in regex
Is there a way in regex to find a string if it occurs twice in given structures (i.e. like in XML parsing)?
This code obviously does not work as it finds the first tag and then the last closing tag:

```<code>re.findall(r'<(.+)>([\s\S]*)</(.+)>', s)
</code>```

So is there a way to tell regex that the third match should be the same as the first?

Full code:

```<code>import re

s = '''<a1>
    <a2>
        1
    </a2>
    <b2>
        52
    </b2>
    <c2>
        <a3>
            Abc
        </a3>
    </c2>
</a1>
<b1>
    21
</b1>'''

matches = re.findall(r'<(.+)>([\s\S]*)</(.+)>', s)
for match in matches:
    print(match)
</code>```

Result should be all the tags with the contents:

```<code>    [('a1', '\n    <a2>\n        1\n    </a2>\n    <b2>\n        52\n    </b2>\n    <c2>\n        <a3>\n            Abc\n        </a3>\n    </c2>\n'),
     ('a2', '\n        1\n    '),
      ...]
</code>```

Note: I am not looking for a complete xml parsing package. The question is specificly about solving the given problem with regex.

答:
You can use backreferences and simple recursion:

```<code>>>> def m(s):
...    matches = re.findall(r'<(.+)>([\s\S]*)</(\1)>', s)
...    for k,s2,_ in matches:
...        print (k,s2)
...        m(s2)
...
>>> m(s)
('a1', '\n    <a2>\n  ...[dropped]...      </a3>\n    </c2>\n')
('a2', '\n        1\n    ')
('b2', '\n        52\n    ')
('c2', '\n        <a3>\n            Abc\n        </a3>\n    ')
('a3', '\n            Abc\n        ')
('b1', '\n    21\n')
</code>```

More about backreferences from Microsoft Docs.

Edited

For extra fun, with generator. Thanks @mrCarnivore about your suggestion to remove if s:

```<code>>>> def m(s):
...    matches = re.findall(r'<(.+)>([\s\S]*)</(\1)>', s)
...    for k,s2,_ in matches:
...        yield (k,s2)
...        yield from m(s2)
...
>>> for x in m(s):
...    x
...
('a1', '\n    <a2>\ [....]     Abc\n        </a3>\n    </c2>\n')
('a2', '\n        1\n    ')
('b2', '\n        52\n    ')
('c2', '\n        <a3>\n            Abc\n        </a3>\n    ')
('a3', '\n            Abc\n        ')
('b1', '\n    21\n')
</code>```

Body:
Is there a way in regex to find a string if it occurs twice in given structures (i.e. like in XML parsing)?
This code obviously does not work as it finds the first tag and then the last closing tag:

re.findall(r'<(.+)>([\s\S]*)</(.+)>', s)

So is there a way to tell regex that the third match should be the same as the first?

Full code:

import re

s = '''<a1>
    <a2>
        1
    </a2>
    <b2>
        52
    </b2>
    <c2>
        <a3>
            Abc
        </a3>
    </c2>
</a1>
<b1>
    21
</b1>'''

matches = re.findall(r'<(.+)>([\s\S]*)</(.+)>', s)
for match in matches:
    print(match)

Result should be all the tags with the contents:

    [('a1', '\n    <a2>\n        1\n    </a2>\n    <b2>\n        52\n    </b2>\n    <c2>\n        <a3>\n            Abc\n        </a3>\n    </c2>\n'),
     ('a2', '\n        1\n    '),
      ...]

Note: I am not looking for a complete xml parsing package. The question is specificly about solving the given problem with regex.

Body:
You can use backreferences and simple recursion:

>>> def m(s):
...    matches = re.findall(r'<(.+)>([\s\S]*)</(\1)>', s)
...    for k,s2,_ in matches:
...        print (k,s2)
...        m(s2)
...
>>> m(s)
('a1', '\n    <a2>\n  ...[dropped]...      </a3>\n    </c2>\n')
('a2', '\n        1\n    ')
('b2', '\n        52\n    ')
('c2', '\n        <a3>\n            Abc\n        </a3>\n    ')
('a3', '\n            Abc\n        ')
('b1', '\n    21\n')

More about backreferences from Microsoft Docs.

Edited

For extra fun, with generator. Thanks @mrCarnivore about your suggestion to remove if s:

>>> def m(s):
...    matches = re.findall(r'<(.+)>([\s\S]*)</(\1)>', s)
...    for k,s2,_ in matches:
...        yield (k,s2)
...        yield from m(s2)
...
>>> for x in m(s):
...    x
...
('a1', '\n    <a2>\ [....]     Abc\n        </a3>\n    </c2>\n')
('a2', '\n        1\n    ')
('b2', '\n        52\n    ')
('c2', '\n        <a3>\n            Abc\n        </a3>\n    ')
('a3', '\n            Abc\n        ')
('b1', '\n    21\n')


问:
On button click change a value outside a function?
I want to change or make the app variable in the config parameter dynamic. For that I created some radio buttons with value. So wondering how can I pass the value based on user preference.
2) Second, if the selection is changed from selection1 to selection2, then it should auto refresh.</p>

```<code><html>
    <head>
        <meta charset="utf-8">
        <title></title>
        <link rel="stylesheet" href="../../resources/assets/client/client.css">
        <link rel="stylesheet" href="sense-search.min.css"/>
    </head>
    <input type="radio" name = "Product" value = "987-34587a8a5e13" /> abc
    <input type="radio" name = "Product" value = "786-2fad2cbd8249" /> def
    <body style="width: 100%; margin: 20px auto; font-family: arial;">

        <h2></h2>
        <br>
        <sense-search-input id="myInput" mode="visualizations"></sense-search-input>
        <sense-search-results id="myResults"></sense-search-results>
        <script type="text/javascript"
src="../../resources/assets/external/requirejs/require.js"></script>
        <script type="text/javascript" src="sense-search.js"></script>
        <script type="text/javascript">

            //var str ;

            //$("input[name=Product]").click(function(){
                //alert("selected");
            //});

            var config = {
                host: "xyz",
                prefix: "/pnb/",
                isSecure: true,
                app: '987-34587a8a5e13',
                port: 443
            }
</code>```

答:
Free code.
https://api.jquery.com/change/

Just listen to change event of that input... when it changes...

```<code>$(document).ready(function() {
    $('input[type=radio][name=Product]').change(function() {
         app.yourstuff = this.value;
         alert("selected "+this.value);
    });
});
</code>```

Body:
I want to change or make the app variable in the config parameter dynamic. For that I created some radio buttons with value. So wondering how can I pass the value based on user preference.
2) Second, if the selection is changed from selection1 to selection2, then it should auto refresh.

        <meta charset="utf-8">
        <title></title>
        <link rel="stylesheet" href="../../resources/assets/client/client.css">
        <link rel="stylesheet" href="sense-search.min.css"/>

    <input type="radio" name = "Product" value = "987-34587a8a5e13" /> abc
    <input type="radio" name = "Product" value = "786-2fad2cbd8249" /> def

        <sense-search-input id="myInput" mode="visualizations"></sense-search-input>
        <sense-search-results id="myResults"></sense-search-results>

Body:
Free code.
https://api.jquery.com/change/

Just listen to change event of that input... when it changes...

$(document).ready(function() {
    $('input[type=radio][name=Product]').change(function() {
         app.yourstuff = this.value;
         alert("selected "+this.value);
    });
});