问:
Problem with authenticating private channels in laravel with java client
I want to send broadcast messages from server (using laravel) to clients (using java).

What I'm using

Pusher as boradcast driver.

laravel passport for api authentication.

What I've done in server side

I've configured my Pusher credentials in .env file.

Uncommented App\Providers\BroadcastServiceProvider::class line in config/app.php file.

In config/auth.php file I've added the following:

'guards' => [
     'web' => [
         'driver' => 'session',
         'provider' => 'users',
     ],

     'devices' => [
         'driver' => 'session',
         'provider' => 'devices',
     ],

     'api' => [
         'driver' => 'passport',
         'provider' => 'devices',
     ],
 ],

'providers' => [
    'users' => [
        'driver' => 'eloquent',
        'model' => App\User::class,
    ],

     // using devices table to authenticate over api guard
    'devices' => [
        'driver' => 'eloquent',
        'model' => App\Device::class,
    ],
],
```

<ol start="4">
<li>In <code>App\Providers\BroadcastServiceProvider</code> class I added the following to <code>boot()</code> function:</li>
</ol>

<pre class="lang-php prettyprint-override"><code>Broadcast::routes(['prefix' => 'api', 'middleware' => 'auth:api']);
</code>```

In routes/channels.php I added the following:

Broadcast::channel('device.{device_id}', function ($device, $device_id) {
    return $device->id === $device_id;
});
```

<ol start="6">
<li>Created an event <code>AdvertisementAdded</code> by running <code>php artisan make:event AdvertisementAdded</code>, added <code>implements ShouldBroadcast</code> then added the following to its <code>broadcastOn()</code> method:</li>
</ol>

<pre class="lang-php prettyprint-override"><code>return new PrivateChannel('device.'.$this->device_id);
</code>```

What I've done in client side

Because I'm just testing now, I got my access_token and device_id by sending a login request from postman

I copied that accessToken to my java client and stored it in accessToken variable as String, here's the code:

String accessToken = "Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImp0aSI6ImY3ZTVlMTAzZWE3MzJjMTI5NzY1YTliMmMzOTM0N2ZhOGE4OTU5MjRjNDA5ZjgyOTA4ZDg5NTFjZTBkOGZlNTA2M2M1YTI1MDBlOTdhZDdiIn0.eyJhdWQiOiIxIiwianRpIjoiZjdlNWUxMDNlYTczMmMxMjk3NjVhOWIyYzM5MzQ3ZmE4YTg5NTkyNGM0MDlmODI5MDhkODk1MWNlMGQ4ZmU1MDYzYzVhMjUwMGU5N2FkN2IiLCJpYXQiOjE1NTkwOTYyNDgsIm5iZiI6MTU1OTA5NjI0OCwiZXhwIjoxNTkwNzE4NjQ3LCJzdWIiOiI3Iiwic2NvcGVzIjpbXX0.FKeE9Z-wv2yUNQPl-qsbu9baYGTdbQ6DuzaI1R8azR6l1CIP9uRI4hCaoWvgx0GXWWLPRNhfQl-YD3KP2YOraW16-h4ie_95B9VQrpFxXnlqKojsfh1xSrSNSl5HncslMWQPVjoesBpM5y_cpG19PGgu-SWo0W6s9Fiz_Nm70oyyZB9mSqU8PVQvAOSNr6TMR0aC3iMLFfkyZkTSwj8EoRyD2LGW6v4PFriqx8JLbZASCOiUYBlYnunWrTFDOAenZcoa5Sw7u7kbSvYehjDKRwKjQM6zmPfi0A3Mp0CHjHE599OXb-NG2IMH-wmlT0vEZjP2U97hxmsNW1RtHNXWaRKFL9T-WVmZbJf3fH5hXqTv495L3MQfq_m5YFHyc5NuIqK4K4xMJB956a33ICnH8DmvPmJgderNAhqEX1JHUAsR63K7xbZxRBDS8OlQYcEf-_v75X0kT1s067enSvI8Vs212AVnI6k0FmgQNM8DfJUq6YduD0m2F2ZWpKPrwdd6PdW5ZlZTEv-D8dYIEQ_CwOWohNoENATmTqxDpPBxK5c723MEt8S7Sa9MEGAo56HW3-9pbazbEdY1GqPWKVkov7K_6eBFcWsV67AgJpoKFt6RiBfRvokgiH96WG89qBB_Ucpm8uBahX93FaOXhVLW0VjJH2LQKrGw0bb5LS8Ql5o";
String deviceId = "7";

Map<String, String> authHeaders = new HashMap();

authHeaders.put("Authorization", accessToken);

HttpAuthorizer authorizer = new HttpAuthorizer("http://localhost:8000/api/broadcasting/auth");
authorizer.setHeaders(authHeaders);
PusherOptions options = new PusherOptions();
options.setAuthorizer(authorizer).setCluster(PUSHER_CLUSTER);
Pusher pusher = new Pusher(PUSHER_APP_KEY, options);

pusher.subscribePrivate("private-device." + deviceId, new PrivateChannelEventListener() {
    @Override
    public void onEvent(String channelName, String eventName, final String data) {
        System.out.println(String.format("Received event on channel [%s]", channelName));
    }

    @Override
    public void onSubscriptionSucceeded(String string) {
        System.out.println(String.format("Subscribed to channel [%s]", string));
    }

    @Override
    public void onAuthenticationFailure(String string, Exception excptn) {
        System.out.println(string);
    }
});

pusher.connect(new ConnectionEventListener() {
    @Override
    public void onConnectionStateChange(ConnectionStateChange change) {
        System.out.println("State changed to " + change.getCurrentState() +
                           " from " + change.getPreviousState());
    }

    @Override
    public void onError(String message, String code, Exception e) {
        System.out.println("There was a problem connecting!");
    }
});

// Keeping main thread alive
while (true) {
    try {
        Thread.sleep(1000);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
}
```

<p>When running the code above, it outputs the following on console:</p>

```State changed to CONNECTING from DISCONNECTED
State changed to CONNECTED from CONNECTING
java.io.IOException: Server returned HTTP response code: 403 for URL: http://localhost:8000/api/broadcasting/auth
```

<p>I'm sure that <code>auth:api</code> middleware is working as I expect on other requests.</p>

<p>Here's a snippet from my <code>routes/api.php</code>:</p>

<pre class="lang-php prettyprint-override"><code>Route::middleware('auth:api')->group(function () {
    Route::prefix('advertisements')->group(function () {
        Route::get('/request', 'AdvertisementsController@getDeviceAdvertisements')
            ->name('advertisements.getDeviceAdvertisements');
    });
});
</code>```

And here's a test to that route from postman (with the same access token as above):

And here's a test to api/broadcasting/auth route from postman (with the same access token as above):

What's the problem? Why all api routes under auth:api middleware working properly but not api/broadcasting/auth route??

Note

I tried working with public channels with no problems.

答:
After a whole day of searching, finally It's solved.

The error happens when authorizing the channel, not when authenticating the request using auth:api middleware.

My private channel authorizing function in routes/channels.php always returns false meaning it will reject all subscribing requests to private-device.{device_id} channel:

Broadcast::channel('device.{device_id}', function ($device, $device_id) {
    // this always return false, because of inequality of types
    return $device->id === $device_id;
});
```

<p>Authorizing function above always return <code>false</code>, because of inequality of types between <code>$device->id</code> (which is of type <code>int</code>) and <code>$device_id</code> (which is of type <code>string</code>).</p>

<p>So, in order to solve the problem, I cast both of them to <code>int</code> and then checked for equality.</p>

<p>Here's the code I used to solve the problem:</p>

<pre class="lang-php prettyprint-override"><code>Broadcast::channel('device.{device_id}', function ($device, $device_id) {
    return (int) $device->id === (int) $device_id;
});
</code>```

Body:
I want to send broadcast messages from server (using laravel) to clients (using java).

What I'm using

Pusher as boradcast driver.

laravel passport for api authentication.

What I've done in server side

I've configured my Pusher credentials in .env file.

Uncommented App\Providers\BroadcastServiceProvider::class line in config/app.php file.

In config/auth.php file I've added the following:

'guards' => [
     'web' => [
         'driver' => 'session',
         'provider' => 'users',
     ],

     'devices' => [
         'driver' => 'session',
         'provider' => 'devices',
     ],

     'api' => [
         'driver' => 'passport',
         'provider' => 'devices',
     ],
 ],

'providers' => [
    'users' => [
        'driver' => 'eloquent',
        'model' => App\User::class,
    ],

     // using devices table to authenticate over api guard
    'devices' => [
        'driver' => 'eloquent',
        'model' => App\Device::class,
    ],
],

In App\Providers\BroadcastServiceProvider class I added the following to boot() function:

Broadcast::routes(['prefix' => 'api', 'middleware' => 'auth:api']);

In routes/channels.php I added the following:

Broadcast::channel('device.{device_id}', function ($device, $device_id) {
    return $device->id === $device_id;
});

Created an event AdvertisementAdded by running php artisan make:event AdvertisementAdded, added implements ShouldBroadcast then added the following to its broadcastOn() method:

return new PrivateChannel('device.'.$this->device_id);

What I've done in client side

Because I'm just testing now, I got my access_token and device_id by sending a login request from postman

I copied that accessToken to my java client and stored it in accessToken variable as String, here's the code:

String accessToken = "Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsImp0aSI6ImY3ZTVlMTAzZWE3MzJjMTI5NzY1YTliMmMzOTM0N2ZhOGE4OTU5MjRjNDA5ZjgyOTA4ZDg5NTFjZTBkOGZlNTA2M2M1YTI1MDBlOTdhZDdiIn0.eyJhdWQiOiIxIiwianRpIjoiZjdlNWUxMDNlYTczMmMxMjk3NjVhOWIyYzM5MzQ3ZmE4YTg5NTkyNGM0MDlmODI5MDhkODk1MWNlMGQ4ZmU1MDYzYzVhMjUwMGU5N2FkN2IiLCJpYXQiOjE1NTkwOTYyNDgsIm5iZiI6MTU1OTA5NjI0OCwiZXhwIjoxNTkwNzE4NjQ3LCJzdWIiOiI3Iiwic2NvcGVzIjpbXX0.FKeE9Z-wv2yUNQPl-qsbu9baYGTdbQ6DuzaI1R8azR6l1CIP9uRI4hCaoWvgx0GXWWLPRNhfQl-YD3KP2YOraW16-h4ie_95B9VQrpFxXnlqKojsfh1xSrSNSl5HncslMWQPVjoesBpM5y_cpG19PGgu-SWo0W6s9Fiz_Nm70oyyZB9mSqU8PVQvAOSNr6TMR0aC3iMLFfkyZkTSwj8EoRyD2LGW6v4PFriqx8JLbZASCOiUYBlYnunWrTFDOAenZcoa5Sw7u7kbSvYehjDKRwKjQM6zmPfi0A3Mp0CHjHE599OXb-NG2IMH-wmlT0vEZjP2U97hxmsNW1RtHNXWaRKFL9T-WVmZbJf3fH5hXqTv495L3MQfq_m5YFHyc5NuIqK4K4xMJB956a33ICnH8DmvPmJgderNAhqEX1JHUAsR63K7xbZxRBDS8OlQYcEf-_v75X0kT1s067enSvI8Vs212AVnI6k0FmgQNM8DfJUq6YduD0m2F2ZWpKPrwdd6PdW5ZlZTEv-D8dYIEQ_CwOWohNoENATmTqxDpPBxK5c723MEt8S7Sa9MEGAo56HW3-9pbazbEdY1GqPWKVkov7K_6eBFcWsV67AgJpoKFt6RiBfRvokgiH96WG89qBB_Ucpm8uBahX93FaOXhVLW0VjJH2LQKrGw0bb5LS8Ql5o";
String deviceId = "7";

Map<String, String> authHeaders = new HashMap();

authHeaders.put("Authorization", accessToken);

HttpAuthorizer authorizer = new HttpAuthorizer("http://localhost:8000/api/broadcasting/auth");
authorizer.setHeaders(authHeaders);
PusherOptions options = new PusherOptions();
options.setAuthorizer(authorizer).setCluster(PUSHER_CLUSTER);
Pusher pusher = new Pusher(PUSHER_APP_KEY, options);

pusher.subscribePrivate("private-device." + deviceId, new PrivateChannelEventListener() {
    @Override
    public void onEvent(String channelName, String eventName, final String data) {
        System.out.println(String.format("Received event on channel [%s]", channelName));
    }

    @Override
    public void onSubscriptionSucceeded(String string) {
        System.out.println(String.format("Subscribed to channel [%s]", string));
    }

    @Override
    public void onAuthenticationFailure(String string, Exception excptn) {
        System.out.println(string);
    }
});

pusher.connect(new ConnectionEventListener() {
    @Override
    public void onConnectionStateChange(ConnectionStateChange change) {
        System.out.println("State changed to " + change.getCurrentState() +
                           " from " + change.getPreviousState());
    }

    @Override
    public void onError(String message, String code, Exception e) {
        System.out.println("There was a problem connecting!");
    }
});

// Keeping main thread alive
while (true) {
    try {
        Thread.sleep(1000);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
}

When running the code above, it outputs the following on console:

State changed to CONNECTING from DISCONNECTED
State changed to CONNECTED from CONNECTING
java.io.IOException: Server returned HTTP response code: 403 for URL: http://localhost:8000/api/broadcasting/auth

I'm sure that auth:api middleware is working as I expect on other requests.

Here's a snippet from my routes/api.php:

Route::middleware('auth:api')->group(function () {
    Route::prefix('advertisements')->group(function () {
        Route::get('/request', 'AdvertisementsController@getDeviceAdvertisements')
            ->name('advertisements.getDeviceAdvertisements');
    });
});

And here's a test to that route from postman (with the same access token as above):

And here's a test to api/broadcasting/auth route from postman (with the same access token as above):

What's the problem? Why all api routes under auth:api middleware working properly but not api/broadcasting/auth route??

Note

I tried working with public channels with no problems.

Body:
After a whole day of searching, finally It's solved.

The error happens when authorizing the channel, not when authenticating the request using auth:api middleware.

My private channel authorizing function in routes/channels.php always returns false meaning it will reject all subscribing requests to private-device.{device_id} channel:

Broadcast::channel('device.{device_id}', function ($device, $device_id) {
    // this always return false, because of inequality of types
    return $device->id === $device_id;
});

Authorizing function above always return false, because of inequality of types between $device->id (which is of type int) and $device_id (which is of type string).

So, in order to solve the problem, I cast both of them to int and then checked for equality.

Here's the code I used to solve the problem:

Broadcast::channel('device.{device_id}', function ($device, $device_id) {
    return (int) $device->id === (int) $device_id;
});


来源: stackexchange

问:
Woocommerce rest api integrate with laravel error
I am using woocommerce rest api 2.0 and php 7.1 and laravel 5.6+. I try to install Laravel 5 wrapper for the Woocommerce REST API but it throws an error.

This is the error:

```<code>Problem 1
    - Installation request for pixelpeter/laravel5-woocommerce-api-client 3.0 -> satisfiable by pixelpeter/laravel5-woocommerce-api-client[v3.0.0].
    - pixelpeter/laravel5-woocommerce-api-client v3.0.0 requires automattic/woocommerce 1.3.* -> satisfiable by automattic/woocommerce[1.3.0] but these conflict with your requirements or minimum-stability.
</code>```

Is there any possible solution to this issue?

答:
This is require automattic/woocommerce lib, so you should install this library first.</p>

```<code>composer require automattic/woocommerce
</code>```

ContentLicense: CC BY-SA 4.0

Body:
I am using woocommerce rest api 2.0 and php 7.1 and laravel 5.6+. I try to install Laravel 5 wrapper for the Woocommerce REST API but it throws an error.

This is the error:

Problem 1
    - Installation request for pixelpeter/laravel5-woocommerce-api-client 3.0 -> satisfiable by pixelpeter/laravel5-woocommerce-api-client[v3.0.0].
    - pixelpeter/laravel5-woocommerce-api-client v3.0.0 requires automattic/woocommerce 1.3.* -> satisfiable by automattic/woocommerce[1.3.0] but these conflict with your requirements or minimum-stability.

Is there any possible solution to this issue?

Body:
This is require automattic/woocommerce lib, so you should install this library first.

composer require automattic/woocommerce


问:
Google drive api rest upload file with service account not works
After creating a service account from the google iam console, and having obtained the json with the private_key,client_email and so on... I have problems integrating them into my javascript code to upload a file to google drive with an authentication server to server. With the use of nodejs I didn't have any problem (I used JWTtoken), I would be grateful if you could help me to insert the authentication parameters correctly in the javascript post request code of the example.   </p>

```<code>       $.ajax({
        type: "POST",
        url: "https://www.googleapis.com/upload/drive/v3/files",
        data:{
            uploadType:"multipart",
            parents: ["xxxxxxxxxxxxxxxxxxxxxxxx"]
        },
        success: function (data) {
            console.log(data);
        },
        error: function (error) {
            console.log(error);
        },
        async: true,
        data: formData,
        cache: false,
        contentType: false,
        Authorization: ????????????????? <---
        processData: false,
        timeout: 60000
    });
};
</code>```

<p>When I try to do to update this file I see no result. Can someone help me? </p>

答:
Add contentType: false inside $.ajax function.

Add your file in the data object inside ajax call. You should use FormData object like this:

```<code>var formData = new FormData();

formData.append('file', $('.file-input')[0].files[0]);
</code>```

and then set data: formData inside $.ajax function

Body:
After creating a service account from the google iam console, and having obtained the json with the private_key,client_email and so on... I have problems integrating them into my javascript code to upload a file to google drive with an authentication server to server. With the use of nodejs I didn't have any problem (I used JWTtoken), I would be grateful if you could help me to insert the authentication parameters correctly in the javascript post request code of the example.

       $.ajax({
        type: "POST",
        url: "https://www.googleapis.com/upload/drive/v3/files",
        data:{
            uploadType:"multipart",
            parents: ["xxxxxxxxxxxxxxxxxxxxxxxx"]
        },
        success: function (data) {
            console.log(data);
        },
        error: function (error) {
            console.log(error);
        },
        async: true,
        data: formData,
        cache: false,
        contentType: false,
        Authorization: ????????????????? <---
        processData: false,
        timeout: 60000
    });
};

When I try to do to update this file I see no result. Can someone help me?

Body:
Add contentType: false inside $.ajax function.

Add your file in the data object inside ajax call. You should use FormData object like this:

var formData = new FormData();

formData.append('file', $('.file-input')[0].files[0]);

and then set data: formData inside $.ajax function


问:
How do I assign multiple different type of infomation to one name?
Sorry If the question was asked wrong, but I am doing a final project in CSP where I want to make a sorting system in Java 1.8.

I have used Hashmaps to store elements in the past, but apparently, they can only store up to 2 elements,
I want to do something along the line of:

```<code><String,Double,Int,Int,String>
sortPls.put("Jackson",8.2,12,6000,"A");
</code>```

how do I do it and is there an easier way?

I am using this website called Repl.it, this is my first year coding.

```<code>public static HashMap<String,Double,Int,Int,String> sortPls= new HashMap<String,Double,Int,Int,String>();
  public static void setList(){
sortPls.put("Jackson",8.2,12,6000,"A");

  }
</code>```

I expected the Hashmaps to hold more than 2 elements, but it returned an error that limits the elements to 2, with the data types after the first 2 being returned as cannot find symbol.

答:
You can still use a HashMap, you just need to make the values lists that store your values. Note that I defined the list to hold Object data types so that it's most inclusive, but you can change this to hold any data type.

```<code>   Map<String, List<Object>> data = new java.util.HashMap<>();
   data.put("Jackson", new ArrayList<>(Arrays.asList(8.2,12,6000,"A")));

   for (Map.Entry<String, java.util.List<Object>> entry : data.entrySet()) {
       System.out.printf("Entry %s contains:%n", entry.getKey());
       for (List<Object> value : data.values()) {
           System.out.println(value.toString());
       }
   }
</code>```

Body:
Sorry If the question was asked wrong, but I am doing a final project in CSP where I want to make a sorting system in Java 1.8.

I have used Hashmaps to store elements in the past, but apparently, they can only store up to 2 elements,
I want to do something along the line of:

<String,Double,Int,Int,String>
sortPls.put("Jackson",8.2,12,6000,"A");

how do I do it and is there an easier way?

I am using this website called Repl.it, this is my first year coding.

public static HashMap<String,Double,Int,Int,String> sortPls= new HashMap<String,Double,Int,Int,String>();
  public static void setList(){
sortPls.put("Jackson",8.2,12,6000,"A");

  }

I expected the Hashmaps to hold more than 2 elements, but it returned an error that limits the elements to 2, with the data types after the first 2 being returned as cannot find symbol.

Body:
You can still use a HashMap, you just need to make the values lists that store your values. Note that I defined the list to hold Object data types so that it's most inclusive, but you can change this to hold any data type.

   Map<String, List<Object>> data = new java.util.HashMap<>();
   data.put("Jackson", new ArrayList<>(Arrays.asList(8.2,12,6000,"A")));

   for (Map.Entry<String, java.util.List<Object>> entry : data.entrySet()) {
       System.out.printf("Entry %s contains:%n", entry.getKey());
       for (List<Object> value : data.values()) {
           System.out.println(value.toString());
       }
   }


问:
How to stub a variable defined on html before test run
In my index.html, i'm creating an instance of my Widget. I wanna be able to stub the options before test runs to try different initializations.

  ...

```

How can i do this with Cypress?

答:
The best approach that i found was expose the variables to cypress using the window object. Then, in the test i can manipulate those.

```

<p>Then in my test i can run this on <code>beforeEach()</code> or where I need it </p>

<pre class="lang-js prettyprint-override"><code>  cy.window()
    .then(win => {
      win.options['search_on_slash'] = false
    })
</code>```

Body:
In my index.html, i'm creating an instance of my Widget. I wanna be able to stub the options before test runs to try different initializations.

  ...

How can i do this with Cypress?

Body:
The best approach that i found was expose the variables to cypress using the window object. Then, in the test i can manipulate those.

<script>
    document.addEventListener('DOMContentLoaded', function() {
      var options = {...}
      var widget = new MyWidget(options)

      if (window.Cypress) {
        window.options = options
        window.widget = widget
      }

      widget.start()
    })
</script>

Then in my test i can run this on beforeEach() or where I need it

  cy.window()
    .then(win => {
      win.options['search_on_slash'] = false
    })


问:
Accessing Object Property Values Within an Array - JavaScript
Learning how to access property values.

If I have

```<code>let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];
</code>```

How would I console.log just the value of the second object? I.e. "website developer".

I know how to console.log the entire key-value pair (or object) by using .find():

let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];

console.log(object1.find(function(element) {
    return element.hasOwnProperty("job");
}
));```

But what about just the value of this pair?

答:
You can destructure the value:

let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];
const { job: res } = object1.find(({ job }) => job);
console.log(res);```

Body:
Learning how to access property values.

If I have

let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];

How would I console.log just the value of the second object? I.e. "website developer".

I know how to console.log the entire key-value pair (or object) by using .find():

let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];

console.log(object1.find(function(element) {
    return element.hasOwnProperty("job");
}
));

But what about just the value of this pair?


问:
Looping an array together with a jquery hover function
I'm trying to... God, this is hard to explain.

I got an array called 'triggers' with these variables:

"#1_trigger","#2_trigger","#3_trigger"

And inside a jQuery each(), I create another variable called 'targets' that copies everything from 'triggers' and replaces all _trigger to _target. I then append the 'triggers' to anchor IDs, and 'targets' to hidden div IDs.

What I want to do, is this: When hovering a _trigger, the _target will show up. I've managed to make it work with only one variable, but not with multiple.

As I said, it's kind of hard to explain what I want to do in text, so here's a demo and my progress so far:
http://jsfiddle.net/WJWe3/6/

I've been stuck with this one for too many hours now, please help!

答:
Here's another that works....  Just for fun.

http://jsfiddle.net/WJWe3/18/

ContentLicense: CC BY-SA 3.0


问:
SELECT with SELECT within IF statement with saving state
I try to found some solution to avoid third SELECT, but I don't even know, how google it.

My try (working, but looks ugly):

```<code>SELECT
    u.*,
    IF(
        (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'first_name') = '',
        'John',
        (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'first_name')
    ) as first_name,
    (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'reputation') as reputation
FROM wp_users AS u
</code>```

And I'd like something like this:

```<code>SELECT
    u.*,
    IF(
        (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'first_name') = '',
        'John',
        SOME_STATE0
    ) as first_name,
    (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'reputation') as reputation
FROM wp_users AS u
</code>```

答:
Join the tables rather than using a subquery.</p>

```<code>SELECT u.*, IF(meta_value = '' OR meta_value IS NULL, 'John', meta_value) AS first_name
FROM wp_users AS u
LEFT JOIN usermeta AS m ON m.user_id = u.id AND m.meta_key = 'first_name'
</code>```

Body:
I try to found some solution to avoid third SELECT, but I don't even know, how google it.

My try (working, but looks ugly):

SELECT
    u.*,
    IF(
        (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'first_name') = '',
        'John',
        (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'first_name')
    ) as first_name,
    (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'reputation') as reputation
FROM wp_users AS u

And I'd like something like this:

SELECT
    u.*,
    IF(
        (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'first_name') = '',
        'John',
        SOME_STATE0
    ) as first_name,
    (SELECT meta_value from usermeta WHERE user_id = u.ID AND meta_key = 'reputation') as reputation
FROM wp_users AS u

Body:
Join the tables rather than using a subquery.

SELECT u.*, IF(meta_value = '' OR meta_value IS NULL, 'John', meta_value) AS first_name
FROM wp_users AS u
LEFT JOIN usermeta AS m ON m.user_id = u.id AND m.meta_key = 'first_name'


问:
Multiply every third element of a list by two
Input data: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

Output data: [1, 2, 6, 4, 5, 12, 7, 8, 18, 10]

I have read many answers where were suggested to use slice notation with including first element. But in my case I should take each 3rd element of the list.

Is it possible to improve my realization?

for index in range(len(data)):
    if (index + 1) % 3 == 0:
        data[index] = data[index] * 2
```

答:
Yes, you can do it with slice notation:

```<code>data[2::3] = [x*2 for x in data[2::3]]
</code>```

The data[2::3] means every 3rd element starting at element index 2 (i.e. the third element). You can reassign back to a slice as well, allowing for this very succinct syntax.

Body:
Yes, you can do it with slice notation:

data[2::3] = [x*2 for x in data[2::3]]

The data[2::3] means every 3rd element starting at element index 2 (i.e. the third element). You can reassign back to a slice as well, allowing for this very succinct syntax.


问:
Setting extra bits in a bool makes it true and false at the same time
If I get a bool variable and set its second bit to 1, then variable evaluates to true and false at the same time. Compile the following code with gcc6.3 with -g option, (gcc-v6.3.0/Linux/RHEL6.0-2016-x86_64/bin/g++ -g main.cpp -o mytest_d)  and run the executable. You get the following.

How can T be equal to true and false at the same time?

```<code>       value   bits
       -----   ----
    T:   1     0001
after bit change
    T:   3     0011
T is true
T is false
</code>```

This can happen when you call a function in a different language (say fortran) where true and false definition is different than C++. For fortran if any bits are not 0 then the value is true, if all bits are zero then the value is false.

```<code>#include <iostream>
#include <bitset>

using namespace std;

void set_bits_to_1(void* val){
  char *x = static_cast<char *>(val);

  for (int i = 0; i<2; i++ ){
    *x |= (1UL << i);
  }
}

int main(int argc,char *argv[])
{

  bool T = 3;

  cout <<"       value   bits " <<endl;
  cout <<"       -----   ---- " <<endl;
  cout <<"    T:   "<< T <<"     "<< bitset<4>(T)<<endl;

  set_bits_to_1(&T);

  bitset<4> bit_T = bitset<4>(T);
  cout <<"after bit change"<<endl;
  cout <<"    T:   "<< T <<"     "<< bit_T<<endl;

  if (T ){
    cout <<"T is true" <<endl;
  }

  if ( T == false){
    cout <<"T is false" <<endl;
  }

}
</code>```

///////////////////////////////////
// Fortran function that is not compatible with C++ when compiled with ifort.

```<code>       logical*1 function return_true()
         implicit none

         return_true = 1;

       end function return_true
</code>```

答:
This is what happens when you violate your contract with both the language and the compiler.

You probably heard somewhere that "zero is false", and "non-zero is true". That holds when you stick to the language's parameters, statically converting an int to bool or vice versa.

It does not hold when you start messing with bit representations. In that case, you break your contract, and enter the realm of (at the very least) implementation-defined behaviour.

Simply don't do that.

It's not up to you how a bool is stored in memory. It's up to the compiler. If you want to change a bool's value, either assign true/false, or assign an integer and use the proper conversion mechanisms provided by C++.

The C++ standard used to actually give a specific call-out to how using bool in this manner is naughty and bad and evil ("Using a bool value in ways described by this document as 'undefined',such as by examining the value of an uninitialized automatic object, might cause it to behave as if it is neither true nor false."), though it was removed in C++20 for editorial reasons.

Body:
If I get a bool variable and set its second bit to 1, then variable evaluates to true and false at the same time. Compile the following code with gcc6.3 with -g option, (gcc-v6.3.0/Linux/RHEL6.0-2016-x86_64/bin/g++ -g main.cpp -o mytest_d)  and run the executable. You get the following.

How can T be equal to true and false at the same time?

       value   bits
       -----   ----
    T:   1     0001
after bit change
    T:   3     0011
T is true
T is false

This can happen when you call a function in a different language (say fortran) where true and false definition is different than C++. For fortran if any bits are not 0 then the value is true, if all bits are zero then the value is false.

#include <iostream>
#include <bitset>

using namespace std;

void set_bits_to_1(void* val){
  char *x = static_cast<char *>(val);

  for (int i = 0; i<2; i++ ){
    *x |= (1UL << i);
  }
}

int main(int argc,char *argv[])
{

  bool T = 3;

  cout <<"       value   bits " <<endl;
  cout <<"       -----   ---- " <<endl;
  cout <<"    T:   "<< T <<"     "<< bitset<4>(T)<<endl;

  set_bits_to_1(&T);

  bitset<4> bit_T = bitset<4>(T);
  cout <<"after bit change"<<endl;
  cout <<"    T:   "<< T <<"     "<< bit_T<<endl;

  if (T ){
    cout <<"T is true" <<endl;
  }

  if ( T == false){
    cout <<"T is false" <<endl;
  }

}

///////////////////////////////////
// Fortran function that is not compatible with C++ when compiled with ifort.

       logical*1 function return_true()
         implicit none

         return_true = 1;

       end function return_true


答:
There is a "step" parameter on the range iterator:

```<code>for index in range(2, len(data), 3):
    data[index] *= 2
</code>```

Would that do?

Title: Multiply every third element of a list by two

Body:
Input data: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

Output data: [1, 2, 6, 4, 5, 12, 7, 8, 18, 10]

I have read many answers where were suggested to use slice notation with including first element. But in my case I should take each 3rd element of the list.

Is it possible to improve my realization?

for index in range(len(data)):
    if (index + 1) % 3 == 0:
        data[index] = data[index] * 2

Body:
There is a "step" parameter on the range iterator:

for index in range(2, len(data), 3):
    data[index] *= 2

Would that do?


问:
how to sort a datagridview by 2 columns
How do I sort a DataGridView by two columns (ascending)? I have two columns: day and status.

If I need to sort by one column, I do:

```<code>this.dataGridView1.Sort (this.dataGridView1.Columns["day"], ListSortDirection.Ascending);
</code>```

But for two?

答:
I use this solution when working with bound data.  This works for our users, and displays the current sort criteria.  All sorts are only in ascending order.

Add a CheckBox, a TextBox, a ColumnHeaderMouseClick event, and code as shown.  The CheckBox will toggle the TextBox's visibility, and clicking on any column header will add the sort criteria to the TextBox. To clear the TextBox, just double click in it.

```<code>        private void FooDataGridView_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if(chkMultiSort.Checked == true)
            {
                string columnHeader = FooDataGridView.Columns[e.ColumnIndex].DataPropertyName;
                txtMultiSort.Text += (columnHeader + ", ");
                try
                {
                    FooBindingSource.Sort = txtMultiSort.Text.Remove(txtMultiSort.Text.Length - 2);
                }
                catch
                {
                    MessageBox.Show("Invalid Sort Data", "Information", MessageBoxButtons.OK, MessageBoxIcon.None);
                    txtMultiSort.Text = String.Empty;
                }
            }

        }

        private void ChkMultiSort_CheckedChanged(object sender, EventArgs e)
        {
            if(chkMultiSort.Checked == true)
            {
                txtMultiSort.Visible = true;
            }
            else
            {
                txtMultiSort.Visible = false;
                txtMultiSort.Text = String.Empty;
            }
        }

        private void TxtMultiSort_DoubleClick(object sender, EventArgs e)
        {
            txtMultiSort.Text = String.Empty;
        }

</code>```

Title: how to sort a datagridview by 2 columns

Body:
How do I sort a DataGridView by two columns (ascending)? I have two columns: day and status.

If I need to sort by one column, I do:

this.dataGridView1.Sort (this.dataGridView1.Columns["day"], ListSortDirection.Ascending);

But for two?

Body:
I use this solution when working with bound data.  This works for our users, and displays the current sort criteria.  All sorts are only in ascending order.

Add a CheckBox, a TextBox, a ColumnHeaderMouseClick event, and code as shown.  The CheckBox will toggle the TextBox's visibility, and clicking on any column header will add the sort criteria to the TextBox. To clear the TextBox, just double click in it.

        private void FooDataGridView_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if(chkMultiSort.Checked == true)
            {
                string columnHeader = FooDataGridView.Columns[e.ColumnIndex].DataPropertyName;
                txtMultiSort.Text += (columnHeader + ", ");
                try
                {
                    FooBindingSource.Sort = txtMultiSort.Text.Remove(txtMultiSort.Text.Length - 2);
                }
                catch
                {
                    MessageBox.Show("Invalid Sort Data", "Information", MessageBoxButtons.OK, MessageBoxIcon.None);
                    txtMultiSort.Text = String.Empty;
                }
            }

        }

        private void ChkMultiSort_CheckedChanged(object sender, EventArgs e)
        {
            if(chkMultiSort.Checked == true)
            {
                txtMultiSort.Visible = true;
            }
            else
            {
                txtMultiSort.Visible = false;
                txtMultiSort.Text = String.Empty;
            }
        }

        private void TxtMultiSort_DoubleClick(object sender, EventArgs e)
        {
            txtMultiSort.Text = String.Empty;
        }


答:
Your solution isn't too bad.  It can be improved by simply iterating over the desired indices directly, rather than iterating over all indices and skipping the ones you don't want:

```<code>for index in range(2, len(data), 3):
    data[index] *= 2
</code>```

This produces the desired result:

```<code>[1, 2, 6, 4, 5, 12, 7, 8, 18, 10]
</code>```

Body:
Your solution isn't too bad.  It can be improved by simply iterating over the desired indices directly, rather than iterating over all indices and skipping the ones you don't want:

for index in range(2, len(data), 3):
    data[index] *= 2

This produces the desired result:

[1, 2, 6, 4, 5, 12, 7, 8, 18, 10]


问:
Struggling to figure out database structure for multiple relationships
I am creating a laravel application to track campaigns in a game. The database structure situation I am struggling to figure out involves the following tables:

Campaign

Id

Name

Character

Id

Name

Item

Id

Name

The campaign table is empty.
The Character table is populated with character data from the game.
The Item table is populated with item data from the game.

Both the Character and Item tables hold data from the game itself and should not be populated with user data.

A user can create a new campaign which will create an entry in the Campaign table.
The user can then select characters to use in the campaign. I can create this relationship with a campaign_character link table.

The issue arises when it comes to items. Items can be purchased, at which point they need to be added to the current campaign. I could use a campaign_items link table for this, but items can also be optionally equipped by characters in the campaign. I'm not sure how to handle this relationship.

I can't create a character_item link table because the relationship is not between a character and an item directly, it's between a character in a campaign and an item in a campaign.

My current thinking is to make a campaign_character link table, a campaign_item link table, and a campaign_character_campaign_item link table, but this scenario appears many times (not just with characters and items, for example characters can then add attachments to items) and I feel like this will just lead to a ton of tables and complex relationships.

Is there a better way to handle this or is this the right way?

Thank you for any guidance

答:
Here is a suggestion

Apart from 3 main tables create your link tables as

```<code>campaign_character
campaign_item
</code>```

In your campaign_item create a nullable column called equipped_by and store character_id.

If this column has a character_id that means this item_id is equipped by that character, if it has null item is not attached to a particular character.

For attachments create a link table item_attachment to hold only the link between items and attachments.

Since the connection between character and item is already established in campaign_item table, the link between attachment and character can be derived from campaign_item and item_attachment tables.

Please let me know if this helps

Body:
Here is a suggestion

Apart from 3 main tables create your link tables as

campaign_character
campaign_item

In your campaign_item create a nullable column called equipped_by and store character_id.

If this column has a character_id that means this item_id is equipped by that character, if it has null item is not attached to a particular character.

For attachments create a link table item_attachment to hold only the link between items and attachments.

Since the connection between character and item is already established in campaign_item table, the link between attachment and character can be derived from campaign_item and item_attachment tables.

Please let me know if this helps


问:
How to calculate MSE criteria in RandomForestRegression?
I'm now using RandomForestRegressor from sklearn.ensemble to analyze a dataset and I select "mse" as the function to measure the quality of a split. But I'm not quite clear how the mse is calculated. Could anyone explain it to me here (better with equations) or provide me some references on that? Thank you in advance.</p>

答:
Short answer:

Mean Squared Error (MSE) is calculated by squaring all of the errors (to make them positive) and then taking the mean value of those squares. It is a single value; "for each tree, we get a difference between two MSE values. Averaging over trees gives the mean difference between the two MSE values". ref

... However, the Random Forest calculates the MSE using the predictions obtained from evaluating the same data. Train in every tree but only considering the data is not taken from bootstrapping to construct the tree, wether the data that it is in the OOB (OUT-OF-BAG). Then it averages the predictions for all the OOB predictions for each sample of data. ... ref

MSE, metric is one of the cost function methods. Consider that your model green line is in the following picture, and those blue points are data (observations). MSE, as its name suggests, is the mean summation of square areas of all data points with respect to a line, which all in all represents your model errors.

MSE can be calculated by:

It shows how good or bad the model is. The smaller MSE, the better model!

More info:

Understanding Regression Error Metrics in Python

Introduction to Loss Functions

Update 30.05.2019: To verify things, you can dig into the documentation and sometimes in codes as well, based on its documentation RandomForestRegressor() , MSE is nothing else than variance reduction as feature selection criterion even when you check the source code it is used for measuring the quality of a split. On the other hand, if you are doubtful instead of the MSE approach in RandomForestRegressor(), you can use it independently by customizing criterion like this:

from sklearn.metrics import mean_squared_error
from sklearn.ensemble import RandomForestRegressor
#Feature Selection
criterion = mean_squared_error(y, predictions)
RandomForestRegressor( ...,criterion= criterion,...)
```
<p>or using numpy:</p>
<pre class="lang-py prettyprint-override"><code>import numpy as np
criterion = np.mean((y_test - est.predict(X_test))**2)
</code>```

More info

Body:
Short answer:

Mean Squared Error (MSE) is calculated by squaring all of the errors (to make them positive) and then taking the mean value of those squares. It is a single value; "for each tree, we get a difference between two MSE values. Averaging over trees gives the mean difference between the two MSE values". ref

... However, the Random Forest calculates the MSE using the predictions obtained from evaluating the same data. Train in every tree but only considering the data is not taken from bootstrapping to construct the tree, wether the data that it is in the OOB (OUT-OF-BAG). Then it averages the predictions for all the OOB predictions for each sample of data. ... ref

MSE, metric is one of the cost function methods. Consider that your model green line is in the following picture, and those blue points are data (observations). MSE, as its name suggests, is the mean summation of square areas of all data points with respect to a line, which all in all represents your model errors.

MSE can be calculated by:

It shows how good or bad the model is. The smaller MSE, the better model!

More info:

Understanding Regression Error Metrics in Python

Introduction to Loss Functions

Update 30.05.2019: To verify things, you can dig into the documentation and sometimes in codes as well, based on its documentation RandomForestRegressor() , MSE is nothing else than variance reduction as feature selection criterion even when you check the source code it is used for measuring the quality of a split. On the other hand, if you are doubtful instead of the MSE approach in RandomForestRegressor(), you can use it independently by customizing criterion like this:

from sklearn.metrics import mean_squared_error
from sklearn.ensemble import RandomForestRegressor
#Feature Selection
criterion = mean_squared_error(y, predictions)
RandomForestRegressor( ...,criterion= criterion,...)

or using numpy:

import numpy as np
criterion = np.mean((y_test - est.predict(X_test))**2)

More info


问:
Centering featured image in Wordpress
I would like to center Wordpress featured image on my site: https://link.do/ibXD8

I cannot find suitable option in theme properties.

答:
Add this rule to your CSS:

```<code>.page-header-image.grid-parent {
  text-align: center;
}
</code>```

This element contains the image and has full width: Since the image is an inline element, text-align: center; will center it inside its container.

Title: Centering featured image in Wordpress

Body:
Add this rule to your CSS:

.page-header-image.grid-parent {
  text-align: center;
}

This element contains the image and has full width: Since the image is an inline element, text-align: center; will center it inside its container.


答:
A solution would be to store somewhere the keys of each object and then access the object the usual way.

    let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];

    for(let i=0; i<3; i++){
       name = Object.keys(object1[i]);
       console.log(object1[i][name])
    }```

In this case I've assumed that you don't know the name of the key you want to access.

Title: Accessing Object Property Values Within an Array - JavaScript

Body:
A solution would be to store somewhere the keys of each object and then access the object the usual way.

    let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];

    for(let i=0; i<3; i++){
       name = Object.keys(object1[i]);
       console.log(object1[i][name])
    }

In this case I've assumed that you don't know the name of the key you want to access.


问:
Getting error while build the VisualStudio Cordova application in release mode
Getting Fatal error while building the Visual-studio Cordova application in release mode.

I'm using Visual-studio 2017 and the Cordova tool-set version - 6.3.1

```<code>1>MSBUILD : cordova-build error : [Fatal Error] :3:214: The value of attribute "name" associated with an element type "item" must not contain the '<' character.
1>MSBUILD : cordova-build error : Could not read D:\Android\Sdk\platform-tools\api\annotations.zip
1>MSBUILD : cordova-build error : java.io.IOException: Could not parse XML from android/accounts/annotations.xml
</code>```

答: I solved this error by simply removing the Android platform directory, then build, clean the project and build again.</p>

Body:
Getting Fatal error while building the Visual-studio Cordova application in release mode.

I'm using Visual-studio 2017 and the Cordova tool-set version - 6.3.1

1>MSBUILD : cordova-build error : [Fatal Error] :3:214: The value of attribute "name" associated with an element type "item" must not contain the '<' character.
1>MSBUILD : cordova-build error : Could not read D:\Android\Sdk\platform-tools\api\annotations.zip
1>MSBUILD : cordova-build error : java.io.IOException: Could not parse XML from android/accounts/annotations.xml


问:
What is the correct way to pass data to a running thread
In most cases when you create your thread you can prepare the data beforehand and pass it into the constructor or method.

However in cases like an open socket connection you will typically already have a thread created but wish to tell it to perform some action.

Basic idea:

```<code>C#

private Thread _MyThread = new Thread(MyMethod);
this._MyThread.Start(param);

Java

private Thread _MyThread = new Thread(new MyRunnableClass(param));
this._MyThread.start();

Now what?
</code>```

So what is the correct way to pass data to a running thread in C# and Java?

答:
Java

You could basically have a LinkedList (a LIFO) and proceed (with something) like this (untested) :

```<code> class MyRunnable<T> implements Runnable {
     private LinkedList<T> queue;
     private boolean stopped;
     public MyRunnable(LinkedList<T> queue) {
         this.queue = queue;
         this.stopped = false;
     }
     public void stopRunning() {
         stopped = true;
         synchronized (queue) {
             queue.notifyAll();
         }
     }
     public void run() {
         T current;
         while (!stopped) {
             synchronized (queue) {
                 queue.wait();
             }
             if (queue.isEmpty()) {
                 try { Thread.sleep(1); } catch (InterruptedException e) {}
             } else {
                 current = queue.removeFirst();

                 // do something with the data from the queue

             }
             Thread.yield();
         }
     }
 }
</code>```

As you keep a reference to the instance of the LinkedList given in argument, somewhere else, all you have to do is :

```<code> synchronized (queue) {
    queue.addLast(T);  // add your T element here. You could even handle some
                       // sort of priority queue by adding at a given index
    queue.notifyAll();
 }
</code>```

Body:
In most cases when you create your thread you can prepare the data beforehand and pass it into the constructor or method.

However in cases like an open socket connection you will typically already have a thread created but wish to tell it to perform some action.

Basic idea:

C#

private Thread _MyThread = new Thread(MyMethod);
this._MyThread.Start(param);

Java

private Thread _MyThread = new Thread(new MyRunnableClass(param));
this._MyThread.start();

Now what?

So what is the correct way to pass data to a running thread in C# and Java?

Body:
Java

You could basically have a LinkedList (a LIFO) and proceed (with something) like this (untested) :

 class MyRunnable<T> implements Runnable {
     private LinkedList<T> queue;
     private boolean stopped;
     public MyRunnable(LinkedList<T> queue) {
         this.queue = queue;
         this.stopped = false;
     }
     public void stopRunning() {
         stopped = true;
         synchronized (queue) {
             queue.notifyAll();
         }
     }
     public void run() {
         T current;
         while (!stopped) {
             synchronized (queue) {
                 queue.wait();
             }
             if (queue.isEmpty()) {
                 try { Thread.sleep(1); } catch (InterruptedException e) {}
             } else {
                 current = queue.removeFirst();

                 // do something with the data from the queue

             }
             Thread.yield();
         }
     }
 }

As you keep a reference to the instance of the LinkedList given in argument, somewhere else, all you have to do is :

 synchronized (queue) {
    queue.addLast(T);  // add your T element here. You could even handle some
                       // sort of priority queue by adding at a given index
    queue.notifyAll();
 }


问:
Assigning values in a predicate
Is there a way to assing 2 times a different value in a variable inside a predicate?For example can we somehow make

```<code>X is 10,
X is 3.
</code>```

produce true?

答:
For example,

```<code>p(X) :- X is 3;X is 5.
</code>```

is true of X = 3 or 5.

Title: Assigning values in a predicate

Body:
Is there a way to assing 2 times a different value in a variable inside a predicate?For example can we somehow make

X is 10,
X is 3.

produce true?

Body:
For example,

p(X) :- X is 3;X is 5.

is true of X = 3 or 5.


问:
Large (6 million rows) pandas df causes memory error with `to_sql ` when chunksize =100, but can easily save file of 100,000 with no chunksize
I created a large database in Pandas, about 6 million rows of text data. I wanted to save this as a SQL database file, but when I try to save it, I get an out of memory RAM error. I even reduced the chuck size to 100 and it still crashes.

However, if I just have smaller version of that dataframe with 100,000 rows, and save it to a database with no chucksize specified, I have no issues saving tha dataframe.

This is my code

```<code>from sqlalchemy import create_engine
engine = sqlalchemy.create_engine("sqlite:///databasefile.db")
dataframe.to_sql("CS_table", engine, chunksize = 100)
</code>```

My understanding was that since it's only processing 100 rows at a time, the RAM usage should reflect that of a save of 100 rows. Is there something else happening behind the scenes? Perhaps multi-threading?

Before I run this code, I am using 4.8 GB RAM, out of the 12.8 GB RAM available in Google Colab. Running the above code eats up all the RAM until the enviroment crashes.

I would like to be able to save my pandas dataframe to a SQL file without my environment crashing. The environment I am in is Google Colab. The pandas datafame is 2 columns, ~6 million rows. Each cell contains about this much text:

"The dominant sequence transduction models are based on complex
  recurrent or convolutional neural networks in an encoder-decoder
  configuration. The best performing models also connect the encoder and
  decoder through an attention mechanism. We propose a new simple
  network architecture, the Transformer, based solely on attention
  mechanisms, dispensing with recurrence and convolutions entirely.
  Experiments on two machine translation tasks show these models to be
  superior in quality while being more parallelizable and requiring
  significantly less time to train. Our model achieves 28.4 BLEU on the
  WMT 2014 English-to-German translation task, improving over the
  existing best results, including ensembles by over 2 BLEU. On the WMT
  2014 English-to-French translation task, our model establishes a new
  single-model state-of-the-art BLEU score of 41.8 after training for
  3.5 days on eight GPUs, a small fraction of the training costs of the best models from the literature. We show that the Transformer
  generalizes well to other tasks by applying it successfully to English
  constituency parsing both with large and limited training data."

Edit:

I did a keyboard interrupts at various stages. Here is the results of a keyboard interrupt after the first jump in RAM

```<code>---------------------------------------------------------------------------
KeyboardInterrupt                         Traceback (most recent call last)
<ipython-input-22-51b6e444f80d> in <module>()
----> 1 dfAllT.to_sql("CS_table23", engine, chunksize = 100)

12 frames
/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2529         sql.to_sql(self, name, con, schema=schema, if_exists=if_exists,
   2530                    index=index, index_label=index_label, chunksize=chunksize,
-> 2531                    dtype=dtype, method=method)
   2532
   2533     def to_pickle(self, path, compression='infer',

/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
    458     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
    459                       index_label=index_label, schema=schema,
--> 460                       chunksize=chunksize, dtype=dtype, method=method)
    461
    462

/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
   1172                          schema=schema, dtype=dtype)
   1173         table.create()
-> 1174         table.insert(chunksize, method=method)
   1175         if (not name.isdigit() and not name.islower()):
   1176             # check for potentially case sensitivity issues (GH7815)

/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in insert(self, chunksize, method)
    684
    685                 chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])
--> 686                 exec_insert(conn, keys, chunk_iter)
    687
    688     def _query_iterator(self, result, chunksize, columns, coerce_float=True,

/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in _execute_insert(self, conn, keys, data_iter)
    597         """
    598         data = [dict(zip(keys, row)) for row in data_iter]
--> 599         conn.execute(self.table.insert(), data)
    600
    601     def _execute_insert_multi(self, conn, keys, data_iter):

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in execute(self, object_, *multiparams, **params)
    986             raise exc.ObjectNotExecutableError(object_)
    987         else:
--> 988             return meth(self, multiparams, params)
    989
    990     def _execute_function(self, func, multiparams, params):

/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    285     def _execute_on_connection(self, connection, multiparams, params):
    286         if self.supports_execution:
--> 287             return connection._execute_clauseelement(self, multiparams, params)
    288         else:
    289             raise exc.ObjectNotExecutableError(self)

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1105             distilled_params,
   1106             compiled_sql,
-> 1107             distilled_params,
   1108         )
   1109         if self._has_events or self.engine._has_events:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1246         except BaseException as e:
   1247             self._handle_dbapi_exception(
-> 1248                 e, statement, parameters, cursor, context
   1249             )
   1250

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1466                 util.raise_from_cause(sqlalchemy_exception, exc_info)
   1467             else:
-> 1468                 util.reraise(*exc_info)
   1469
   1470         finally:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    127         if value.__traceback__ is not tb:
    128             raise value.with_traceback(tb)
--> 129         raise value
    130
    131     def u(s):

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1222                 if not evt_handled:
   1223                     self.dialect.do_executemany(
-> 1224                         cursor, statement, parameters, context
   1225                     )
   1226             elif not parameters and context.no_parameters:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py in do_executemany(self, cursor, statement, parameters, context)
    545
    546     def do_executemany(self, cursor, statement, parameters, context=None):
--> 547         cursor.executemany(statement, parameters)
    548
    549     def do_execute(self, cursor, statement, parameters, context=None):

KeyboardInterrupt:
</code>```

Here is the result if I do a keyboard interrupt just before it crashes

```<code>ERROR:root:Internal Python error in the inspect module.
Below is the traceback from this internal error.

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py", line 2882, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-24-68b60fe221fe>", line 1, in <module>
    dfAllT.to_sql("CS_table22", engine, chunksize = 100)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py", line 2531, in to_sql
    dtype=dtype, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 460, in to_sql
    chunksize=chunksize, dtype=dtype, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 1174, in to_sql
    table.insert(chunksize, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 686, in insert
    exec_insert(conn, keys, chunk_iter)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 599, in _execute_insert
    conn.execute(self.table.insert(), data)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1468, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1224, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 547, in do_executemany
    cursor.executemany(statement, parameters)
KeyboardInterrupt

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py", line 1823, in showtraceback
    stb = value._render_traceback_()
AttributeError: 'KeyboardInterrupt' object has no attribute '_render_traceback_'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 1132, in get_records
    return _fixed_getinnerframes(etb, number_of_lines_of_context, tb_offset)
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 313, in wrapped
    return f(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 358, in _fixed_getinnerframes
    records = fix_frame_records_filenames(inspect.getinnerframes(etb, context))
  File "/usr/lib/python3.6/inspect.py", line 1488, in getinnerframes
    frameinfo = (tb.tb_frame,) + getframeinfo(tb, context)
  File "/usr/lib/python3.6/inspect.py", line 1446, in getframeinfo
    filename = getsourcefile(frame) or getfile(frame)
  File "/usr/lib/python3.6/inspect.py", line 696, in getsourcefile
    if getattr(getmodule(object, filename), '__loader__', None) is not None:
  File "/usr/lib/python3.6/inspect.py", line 739, in getmodule
    f = getabsfile(module)
  File "/usr/lib/python3.6/inspect.py", line 708, in getabsfile
    _filename = getsourcefile(object) or getfile(object)
  File "/usr/lib/python3.6/inspect.py", line 693, in getsourcefile
    if os.path.exists(filename):
  File "/usr/lib/python3.6/genericpath.py", line 19, in exists
    os.stat(path)
KeyboardInterrupt
</code>```

I did another run right before it crashed, and this seemed to give another different result

```<code>ERROR:root:Internal Python error in the inspect module.
Below is the traceback from this internal error.

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py", line 2882, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-28-f18004debe33>", line 1, in <module>
    dfAllT.to_sql("CS_table25", engine, chunksize = 100)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py", line 2531, in to_sql
    dtype=dtype, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 460, in to_sql
    chunksize=chunksize, dtype=dtype, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 1174, in to_sql
    table.insert(chunksize, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 686, in insert
    exec_insert(conn, keys, chunk_iter)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 598, in _execute_insert
    data = [dict(zip(keys, row)) for row in data_iter]
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 598, in <listcomp>
    data = [dict(zip(keys, row)) for row in data_iter]
KeyboardInterrupt

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py", line 1823, in showtraceback
    stb = value._render_traceback_()
AttributeError: 'KeyboardInterrupt' object has no attribute '_render_traceback_'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 1132, in get_records
    return _fixed_getinnerframes(etb, number_of_lines_of_context, tb_offset)
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 313, in wrapped
    return f(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 358, in _fixed_getinnerframes
    records = fix_frame_records_filenames(inspect.getinnerframes(etb, context))
  File "/usr/lib/python3.6/inspect.py", line 1488, in getinnerframes
    frameinfo = (tb.tb_frame,) + getframeinfo(tb, context)
  File "/usr/lib/python3.6/inspect.py", line 1446, in getframeinfo
    filename = getsourcefile(frame) or getfile(frame)
  File "/usr/lib/python3.6/inspect.py", line 696, in getsourcefile
    if getattr(getmodule(object, filename), '__loader__', None) is not None:
  File "/usr/lib/python3.6/inspect.py", line 742, in getmodule
    os.path.realpath(f)] = module.__name__
  File "/usr/lib/python3.6/posixpath.py", line 388, in realpath
    path, ok = _joinrealpath(filename[:0], filename, {})
  File "/usr/lib/python3.6/posixpath.py", line 421, in _joinrealpath
    newpath = join(path, name)
KeyboardInterrupt
---------------------------------------------------------------------------
</code>```

Other things I have tried:

Using dropna to drop all none/nan values

dfAllT = dfAllT.applymap(str) to make sure all my values are strings

dfAllT.reset_index(drop=True, inplace=True) to make sure index is not out of alignment.

Edit:

Like what is mentioned in the comment, I have now tried to use to_sql in a loop.

```<code>for i in range(586147):
    print(i)
    dfAllT.iloc[i*10000:(i+1)*10000].to_sql('CS_table', engine, if_exists= 'append')
</code>```

This operation eventually eats at my RAM, and eventually causes a crash about halfway through. I wonder if this suggests that sqlite is holding everything in memory, and if there is a work around.

Edit:

I tried a few more things, shorter chucks, disposing the engine after each step and creating a new one. Still eventually ate of all RAM and crashed.

```<code>for i in range(586147):
    print(i)
    engine = sqlalchemy.create_engine("sqlite:///CSTitlesSummariesData.db")
    dfAllT.iloc[i*10:(i+1)*10].to_sql('CS_table', engine, index = False, if_exists= 'append')
    engine.dispose()
    gc.collect
</code>```

My thoughts:

So it looks like somehow the entire database is somehow kept in active memory somehow.

The pandas dataframe from which this is made from was 5 gigs (or at least that's how much RAM is being before I try to convert it to sqlite). My system crashes at around 12.72 gigs. I would imagine the sqlite database takes of less RAM than the pandas dataframe.

答:
From stepping through the code I think it's this line, which reads creates a bunch of DataFrames:

```<code>chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])
</code>```

Which looks like it's probably a bug. Specifically this happens prior to database insertion, in preparation.

One trick you can do is CTRL-C whilst the memory is rapidly increasing, and see which line is paused on (my bet is this one).

User Edit:

Problem was solved by using

explicit loop (rather than using chunk), ie. for i in range(100): df.iloc[i * 100000:(i+1):100000].to_sql(...)

Which still resulted in memory errors, but allowed the user to continue where loop left off before the crash.

A more robust solution is to "perhaps try a raw connection, rather than using SQLEngine?\ "
But user didn't have a chance to try this

Body:
I created a large database in Pandas, about 6 million rows of text data. I wanted to save this as a SQL database file, but when I try to save it, I get an out of memory RAM error. I even reduced the chuck size to 100 and it still crashes.

However, if I just have smaller version of that dataframe with 100,000 rows, and save it to a database with no chucksize specified, I have no issues saving tha dataframe.

This is my code

from sqlalchemy import create_engine
engine = sqlalchemy.create_engine("sqlite:///databasefile.db")
dataframe.to_sql("CS_table", engine, chunksize = 100)

My understanding was that since it's only processing 100 rows at a time, the RAM usage should reflect that of a save of 100 rows. Is there something else happening behind the scenes? Perhaps multi-threading?

Before I run this code, I am using 4.8 GB RAM, out of the 12.8 GB RAM available in Google Colab. Running the above code eats up all the RAM until the enviroment crashes.

I would like to be able to save my pandas dataframe to a SQL file without my environment crashing. The environment I am in is Google Colab. The pandas datafame is 2 columns, ~6 million rows. Each cell contains about this much text:

"The dominant sequence transduction models are based on complex
  recurrent or convolutional neural networks in an encoder-decoder
  configuration. The best performing models also connect the encoder and
  decoder through an attention mechanism. We propose a new simple
  network architecture, the Transformer, based solely on attention
  mechanisms, dispensing with recurrence and convolutions entirely.
  Experiments on two machine translation tasks show these models to be
  superior in quality while being more parallelizable and requiring
  significantly less time to train. Our model achieves 28.4 BLEU on the
  WMT 2014 English-to-German translation task, improving over the
  existing best results, including ensembles by over 2 BLEU. On the WMT
  2014 English-to-French translation task, our model establishes a new
  single-model state-of-the-art BLEU score of 41.8 after training for
  3.5 days on eight GPUs, a small fraction of the training costs of the best models from the literature. We show that the Transformer
  generalizes well to other tasks by applying it successfully to English
  constituency parsing both with large and limited training data."

Edit:

I did a keyboard interrupts at various stages. Here is the results of a keyboard interrupt after the first jump in RAM

---------------------------------------------------------------------------
KeyboardInterrupt                         Traceback (most recent call last)
<ipython-input-22-51b6e444f80d> in <module>()
----> 1 dfAllT.to_sql("CS_table23", engine, chunksize = 100)

12 frames
/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py in to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
   2529         sql.to_sql(self, name, con, schema=schema, if_exists=if_exists,
   2530                    index=index, index_label=index_label, chunksize=chunksize,
-> 2531                    dtype=dtype, method=method)
   2532
   2533     def to_pickle(self, path, compression='infer',

/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
    458     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
    459                       index_label=index_label, schema=schema,
--> 460                       chunksize=chunksize, dtype=dtype, method=method)
    461
    462

/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
   1172                          schema=schema, dtype=dtype)
   1173         table.create()
-> 1174         table.insert(chunksize, method=method)
   1175         if (not name.isdigit() and not name.islower()):
   1176             # check for potentially case sensitivity issues (GH7815)

/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in insert(self, chunksize, method)
    684
    685                 chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])
--> 686                 exec_insert(conn, keys, chunk_iter)
    687
    688     def _query_iterator(self, result, chunksize, columns, coerce_float=True,

/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in _execute_insert(self, conn, keys, data_iter)
    597         """
    598         data = [dict(zip(keys, row)) for row in data_iter]
--> 599         conn.execute(self.table.insert(), data)
    600
    601     def _execute_insert_multi(self, conn, keys, data_iter):

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in execute(self, object_, *multiparams, **params)
    986             raise exc.ObjectNotExecutableError(object_)
    987         else:
--> 988             return meth(self, multiparams, params)
    989
    990     def _execute_function(self, func, multiparams, params):

/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    285     def _execute_on_connection(self, connection, multiparams, params):
    286         if self.supports_execution:
--> 287             return connection._execute_clauseelement(self, multiparams, params)
    288         else:
    289             raise exc.ObjectNotExecutableError(self)

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1105             distilled_params,
   1106             compiled_sql,
-> 1107             distilled_params,
   1108         )
   1109         if self._has_events or self.engine._has_events:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1246         except BaseException as e:
   1247             self._handle_dbapi_exception(
-> 1248                 e, statement, parameters, cursor, context
   1249             )
   1250

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1466                 util.raise_from_cause(sqlalchemy_exception, exc_info)
   1467             else:
-> 1468                 util.reraise(*exc_info)
   1469
   1470         finally:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    127         if value.__traceback__ is not tb:
    128             raise value.with_traceback(tb)
--> 129         raise value
    130
    131     def u(s):

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1222                 if not evt_handled:
   1223                     self.dialect.do_executemany(
-> 1224                         cursor, statement, parameters, context
   1225                     )
   1226             elif not parameters and context.no_parameters:

/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py in do_executemany(self, cursor, statement, parameters, context)
    545
    546     def do_executemany(self, cursor, statement, parameters, context=None):
--> 547         cursor.executemany(statement, parameters)
    548
    549     def do_execute(self, cursor, statement, parameters, context=None):

KeyboardInterrupt:

Here is the result if I do a keyboard interrupt just before it crashes

ERROR:root:Internal Python error in the inspect module.
Below is the traceback from this internal error.

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py", line 2882, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-24-68b60fe221fe>", line 1, in <module>
    dfAllT.to_sql("CS_table22", engine, chunksize = 100)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py", line 2531, in to_sql
    dtype=dtype, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 460, in to_sql
    chunksize=chunksize, dtype=dtype, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 1174, in to_sql
    table.insert(chunksize, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 686, in insert
    exec_insert(conn, keys, chunk_iter)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 599, in _execute_insert
    conn.execute(self.table.insert(), data)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1468, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 129, in reraise
    raise value
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1224, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 547, in do_executemany
    cursor.executemany(statement, parameters)
KeyboardInterrupt

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py", line 1823, in showtraceback
    stb = value._render_traceback_()
AttributeError: 'KeyboardInterrupt' object has no attribute '_render_traceback_'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 1132, in get_records
    return _fixed_getinnerframes(etb, number_of_lines_of_context, tb_offset)
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 313, in wrapped
    return f(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 358, in _fixed_getinnerframes
    records = fix_frame_records_filenames(inspect.getinnerframes(etb, context))
  File "/usr/lib/python3.6/inspect.py", line 1488, in getinnerframes
    frameinfo = (tb.tb_frame,) + getframeinfo(tb, context)
  File "/usr/lib/python3.6/inspect.py", line 1446, in getframeinfo
    filename = getsourcefile(frame) or getfile(frame)
  File "/usr/lib/python3.6/inspect.py", line 696, in getsourcefile
    if getattr(getmodule(object, filename), '__loader__', None) is not None:
  File "/usr/lib/python3.6/inspect.py", line 739, in getmodule
    f = getabsfile(module)
  File "/usr/lib/python3.6/inspect.py", line 708, in getabsfile
    _filename = getsourcefile(object) or getfile(object)
  File "/usr/lib/python3.6/inspect.py", line 693, in getsourcefile
    if os.path.exists(filename):
  File "/usr/lib/python3.6/genericpath.py", line 19, in exists
    os.stat(path)
KeyboardInterrupt

I did another run right before it crashed, and this seemed to give another different result

ERROR:root:Internal Python error in the inspect module.
Below is the traceback from this internal error.

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py", line 2882, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-28-f18004debe33>", line 1, in <module>
    dfAllT.to_sql("CS_table25", engine, chunksize = 100)
  File "/usr/local/lib/python3.6/dist-packages/pandas/core/generic.py", line 2531, in to_sql
    dtype=dtype, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 460, in to_sql
    chunksize=chunksize, dtype=dtype, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 1174, in to_sql
    table.insert(chunksize, method=method)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 686, in insert
    exec_insert(conn, keys, chunk_iter)
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 598, in _execute_insert
    data = [dict(zip(keys, row)) for row in data_iter]
  File "/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py", line 598, in <listcomp>
    data = [dict(zip(keys, row)) for row in data_iter]
KeyboardInterrupt

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/interactiveshell.py", line 1823, in showtraceback
    stb = value._render_traceback_()
AttributeError: 'KeyboardInterrupt' object has no attribute '_render_traceback_'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 1132, in get_records
    return _fixed_getinnerframes(etb, number_of_lines_of_context, tb_offset)
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 313, in wrapped
    return f(*args, **kwargs)
  File "/usr/local/lib/python3.6/dist-packages/IPython/core/ultratb.py", line 358, in _fixed_getinnerframes
    records = fix_frame_records_filenames(inspect.getinnerframes(etb, context))
  File "/usr/lib/python3.6/inspect.py", line 1488, in getinnerframes
    frameinfo = (tb.tb_frame,) + getframeinfo(tb, context)
  File "/usr/lib/python3.6/inspect.py", line 1446, in getframeinfo
    filename = getsourcefile(frame) or getfile(frame)
  File "/usr/lib/python3.6/inspect.py", line 696, in getsourcefile
    if getattr(getmodule(object, filename), '__loader__', None) is not None:
  File "/usr/lib/python3.6/inspect.py", line 742, in getmodule
    os.path.realpath(f)] = module.__name__
  File "/usr/lib/python3.6/posixpath.py", line 388, in realpath
    path, ok = _joinrealpath(filename[:0], filename, {})
  File "/usr/lib/python3.6/posixpath.py", line 421, in _joinrealpath
    newpath = join(path, name)
KeyboardInterrupt
---------------------------------------------------------------------------

Other things I have tried:

Using dropna to drop all none/nan values

dfAllT = dfAllT.applymap(str) to make sure all my values are strings

dfAllT.reset_index(drop=True, inplace=True) to make sure index is not out of alignment.

Edit:

Like what is mentioned in the comment, I have now tried to use to_sql in a loop.

for i in range(586147):
    print(i)
    dfAllT.iloc[i*10000:(i+1)*10000].to_sql('CS_table', engine, if_exists= 'append')

This operation eventually eats at my RAM, and eventually causes a crash about halfway through. I wonder if this suggests that sqlite is holding everything in memory, and if there is a work around.

Edit:

I tried a few more things, shorter chucks, disposing the engine after each step and creating a new one. Still eventually ate of all RAM and crashed.

for i in range(586147):
    print(i)
    engine = sqlalchemy.create_engine("sqlite:///CSTitlesSummariesData.db")
    dfAllT.iloc[i*10:(i+1)*10].to_sql('CS_table', engine, index = False, if_exists= 'append')
    engine.dispose()
    gc.collect

My thoughts:

So it looks like somehow the entire database is somehow kept in active memory somehow.

The pandas dataframe from which this is made from was 5 gigs (or at least that's how much RAM is being before I try to convert it to sqlite). My system crashes at around 12.72 gigs. I would imagine the sqlite database takes of less RAM than the pandas dataframe.

Body:
From stepping through the code I think it's this line, which reads creates a bunch of DataFrames:

chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])

Which looks like it's probably a bug. Specifically this happens prior to database insertion, in preparation.

One trick you can do is CTRL-C whilst the memory is rapidly increasing, and see which line is paused on (my bet is this one).

User Edit:

Problem was solved by using

explicit loop (rather than using chunk), ie. for i in range(100): df.iloc[i * 100000:(i+1):100000].to_sql(...)

Which still resulted in memory errors, but allowed the user to continue where loop left off before the crash.

A more robust solution is to "perhaps try a raw connection, rather than using SQLEngine?\ "
But user didn't have a chance to try this


问:
Is there a .NET library that can sign a request with AWS V4 Signature?
I have an API Gateway where I'm setting the authentication to use AWS_IAM.  This requires that I sign each request with the AWS V4 signature and attach the HMAC in the header.  I've found libraries to sign a request with the V4 signature in nodejs.  But I cannot find a library to sign for me.  Even the aws-sdk for .NET has this abstracted for their own specific use case.  Is there a library out there (i've done a quick google search and found no results)?  Or do I need to write out the hmac myself?</p>

答: An alternative to Aws4RequestSigner is AwsSignatureVersion4. I think its API is easier to work with, but I am biased since I am the author of the latter.</p>


答:
Just add a property access to the end of it.

let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];

console.log(object1.find(function(element) {
    return element.hasOwnProperty("job");
}).job);```

Body:
Learning how to access property values.

If I have

let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];

How would I console.log just the value of the second object? I.e. "website developer".

I know how to console.log the entire key-value pair (or object) by using .find():

let object1 = [{name: "HappyHands31"}, {job: "website developer"}, {city: "Chicago"}];

console.log(object1.find(function(element) {
    return element.hasOwnProperty("job");
}
));

But what about just the value of this pair?


问:
Is it possible to combine HotKey and mouse click to select a menu item?
I have an app that requires multiple key press AND mouse click at same time, in order to access a menu item.
I need to automate this part and I tried

```<code>pyautogui.click(image1.x, image1.y) AND pyautogui.hotkey('ctrl', 'shft', 'alt')
</code>```

I also tried

```<code>            pyautogui.keyDown('shift')  # hold down the shift key
            pyautogui.keyDown('alt')  # hold down the shift key
            pyautogui.keyDown('ctrl')  # hold down the shift key
            pyautogui.click(image1.x, image1.y)
</code>```

I see click operation but not with keys. I need click and keys pressed action at the same time.
Note that I have access to window.
Is there any other tool or package I can use for this?
Is there a way to for me to see what key press is actually happening or events?
Thank you for help

答: Does the code also releases the pressing of the buttons? It seems right to me for the most part, but I don't know if I am getting this right.</p>

Body:
I have an app that requires multiple key press AND mouse click at same time, in order to access a menu item.
I need to automate this part and I tried

pyautogui.click(image1.x, image1.y) AND pyautogui.hotkey('ctrl', 'shft', 'alt')

I also tried

            pyautogui.keyDown('shift')  # hold down the shift key
            pyautogui.keyDown('alt')  # hold down the shift key
            pyautogui.keyDown('ctrl')  # hold down the shift key
            pyautogui.click(image1.x, image1.y)

I see click operation but not with keys. I need click and keys pressed action at the same time.
Note that I have access to window.
Is there any other tool or package I can use for this?
Is there a way to for me to see what key press is actually happening or events?
Thank you for help


问:
Difference between UTF-8 and UTF-16?
Difference between UTF-8 and UTF-16?
Why do we need these?</p>

```<code>MessageDigest md = MessageDigest.getInstance("SHA-256");
String text = "This is some text";

md.update(text.getBytes("UTF-8")); // Change this to "UTF-16" if needed
byte[] digest = md.digest();
</code>```

答:
Security: Use only UTF-8

Difference between UTF-8 and UTF-16? Why do we need these?

There have been at least a couple of security vulnerabilities in implementations of UTF-16. See Wikipedia for details.

CVE-2008-2938

CVE-2012-2135

WHATWG and W3C have now declared that only UTF-8 is to be used on the Web.

The [security] problems outlined here go away when exclusively using UTF-8, which is one of the many reasons that is now the mandatory encoding for all things.

Other groups are saying the same.

So while UTF-16 may continue being used internally by some systems such as Java and Windows, what little use of UTF-16 you may have seen in the past for data files, data exchange, and such, will likely fade away entirely.

ContentLicense: CC BY-SA 2.5

Title: Difference between UTF-8 and UTF-16?

Body:
Difference between UTF-8 and UTF-16?
Why do we need these?

MessageDigest md = MessageDigest.getInstance("SHA-256");
String text = "This is some text";

md.update(text.getBytes("UTF-8")); // Change this to "UTF-16" if needed
byte[] digest = md.digest();