A simple and useful visual of joins in SQL by Taylor Brownlow from this post https://towardsdatascience.com/take-your-sql-from-good-to-great-part-3-687d797d1ede

A simple and useful visual of joins in SQL by Taylor Brownlow from this post https://towardsdatascience.com/take-your-sql-from-good-to-great-part-3-687d797d1ede
A self note for querying json data in SQLite. BTW, I think SQLite is an under utilized and under appreciated swiss army tool for data storage and manipulation. And thanks to Richard Hipp, it is free.
If you have a column defined as a json type in your SQLite database, quickest way to search for the data is json_extract
. A full set of functions available are documented at https://www.sqlite.org/json1.html
If you have a column named family_details
in a table family
with the following json in it as an example
{
"father": {
"name": "dad",
"birthday": "1/1/2000",
"pet_name": "daddy"
},
"mother": {
"name": "mom",
"birthday": "1/1/2001",
"pet_name": "mommy"
},
"sons": [
{
"name": "son_one",
"birthday": "1/2/2020",
"pet_name": "sonny_one"
},
{
"name": "son_two",
"birthday": "1/2/2021",
"pet_name": "sonny_two"
}
],
"daughters": [
{
"name": "princess_one",
"birthday": "1/2/2020",
"pet_name": "princy_one"
},
{
"name": "princess_two",
"birthday": "1/2/2021",
"pet_name": "princy_two"
}
]
}
and you want to print the name of the father, you can use
select json_extract(family_details, '$.father.name') as father_name
from family
json_extract
uses the name of the column and the json node as parameters. In this case, we used $
(which denotes the root), father
and name
(under father) as the json node.
Quick self-note 🙂
different ways to search for content in a string in python
if 'content' in string:
if string.find('content'):
import re
if re.search('content', string)
Good discussion here : https://stackoverflow.com/questions/4901523/whats-a-faster-operation-re-match-search-or-str-find
Search for ^([^\s]*)(\s)
Replace with $1 AND_WHAT_EVER_STRING_YOU_WANT
Here is an example of searching for first space in a line and adding “',
” to the string
Thanks for this great nugget from Sumama Waheed
Many a time, you get some data as a CSV file and need to copy some of that data and include it in a SQL statement. For instance one of the rows in the CSV was first name in the format below
employee_id 1234 8765 9808 1235 8734 6723
And you need to put it in a SQL statement as below
SELECT * FROM employee_table WHERE employee_table.employee_id IN (1234, 8765, 9808, 1235, 8734, 6723)
That’s a lot of adding commas (,) at the end of every line. You can do it quickly in Notepad++ (you can do the same in any editor that supports regex) using the regex capability in search and replace using ($) as the search string and $, as the replace string.
That was a pretty long title for the post :). I love nginx for it’s flexibility and ease of use. It is like a swiss army knife.. can do a lot of things :).
We needed to serve some dynamic content for one of our use cases. If user visits a site using the following URL format http://example.com/23456789/678543
, we want to respond with some html content that is customized using the 23456789
and 678543
strings.
A picture might help here
Here’s how this was achieved
location ~ "^/(?<param1>[0-9]{8})/(?<param2>[0-9]{6})" {
root /var/www/html/test/;
index template.html;
sub_filter_once off;
sub_filter '_first_param_' '$param1';
sub_filter '_second_param_' '$param2';
rewrite ^.*$ /template.html break;
}
create a file named template.html with the following content in /var/www/html/test
Breaking down the config one line at a time
location ~ "^/(?<param1>[0-9]{8})/(?<param2>[0-9]{6})"
: The regex is essentially matching for the first set of digits after the / and adding that as the value for variable $param1. The first match is a series of 8 digits with each digit in the range 0-9. The second match is for a series of 6 digits with each digit in the range 0-9 and it will be added as the value for variable $param2
root /var/www/html/test/;
: Specifying the root location for the location.
index template.html;
: Specifying the home page for the location.
sub_filter_once off;
: Specify to the sub_filter module to not stop after the first match for replacing response content. By default it processes the first match and stops.
sub_filter 'first_param' '$param1';
: Direct the sub_filter module to replace any text matching first_param in the response html with value in variable $param1.
sub_filter 'second_param' '$param2';
: Direct the sub_filter module to replace any text matching second_param in the response html with value in variable $param1.
rewrite ^.*$ /template.html break;
: Specify nginx to server template.html regardless of the URI specified.
Big thanks to Igor for help with the configs!!
Great guide by Michael Bazzell on freezing your credit and removing your personal data from data consolidation sites. A good project for the entrepreneurial and capable to build a service for removing your personal data from all the sites listed on Michael’s guide.
Been dabbling in go recently and I was surprised that the default install doesn’t setup the $GOPATH environment variable.
If you are running it on a Linux box, here is how your can set the $GOPATH variable
Add the following lines to ~/.bashrc file
export GOPATH=$HOME/go
export PATH=$PATH:$GOROOT/bin:$GOPATH/bin
Great slide deck by Uwe Friedsrichsen on resilient patterns to use when designing applications
Good blog post by the engineering team at Stripe on using Kubernetes to run a distributed cron scheduler
https://stripe.com/blog/operating-kubernetes