simonthompson99’s gists

simonthompson99 / close-postgres-connections.sql

Last active January 15, 2021 11:39

[Close postgres connections] Terminate postgres connections to database #sql #database

	select pg_terminate_backend(pg_stat_activity.pid)
	from pg_stat_activity
	where pg_stat_activity.datname = '<db_name>' -- ← change this to your db
	and pid <> pg_backend_pid() and usename = '<user>'
	;

simonthompson99 / python-one-liners.py

Last active January 15, 2021 11:36

[Python One-liners] One liner python snippets #python #oneliner

	# get listing of files in file tree with glob-like searching
	files = [f for f in glob.glob(SOURCE_DIR + '/*/af.xlsx', recursive=True)]

simonthompson99 / random-data-query.sql

Last active January 15, 2021 11:35

[Random data query] Generate random data query #sql #database

	-- generates 5000000 rows of random data
	select i
	,now() - (random() * (interval '100 years'))
	,(array['1', '2'])[floor(random() * 2 + 1)]
	from pg_catalog.generate_series(100000001, 105000000, 1) s(i)
	;

simonthompson99 / concat-flags.r

Last active January 15, 2021 11:33

[Concatenate flag columns] Concatenate flag columns into a single column #r

	# take a load of boolean flag columns (names beginning with f_) and produce a column that lists which flags the row is true for
	concat_flags <- function(x){
	separator = ';'
	a <- paste0(names(x)[as.logical(x)], sep = separator)
	ifelse(a == separator, NA, a)
	}

	data$concat_flags <- apply(data[,colnames(data)[startsWith(colnames(data), "f_")]], 1, concat_flags)

simonthompson99 / extract-excel-cell-color.vba

Last active January 15, 2021 11:32

[Extract Excel cell color] Convert the fill color in excel to a numeric value #excel

	Function GetFillColor(Rng As Range) As Long
	GetFillColor = Rng.Interior.ColorIndex
	End Function

	# to use this:
	# 1. open the visual basic editor (alt + F11 or fn + alt + f11)
	# 2. paste the above code
	# 3. save then quit and return to the workbook

simonthompson99 / create-indx-db.sql

Last active January 15, 2021 11:50

[Create new Indx Db] Create new database on index and grant priviliges to cdt_user, needs [login details from Confluence](https://cnfl.extge.co.uk/display/CDT/_Logins) #sql #database #workflow #genomics_england

	/* use user:password for superuser on https://cnfl.extge.co.uk/display/CDT/_Logins */
	psql metrics -h localhost -p 5441 -U postgres
	create database <db_name>;
	grant all privileges on database <db_name> to cdt_user;

simonthompson99 / restart-local-postgres.sh

Last active January 15, 2021 11:29

[Restart local Postgres] Restart local postgres after a crash #database #environment

	brew services stop postgresql
	rm /usr/local/var/postgres/postmaster.pid
	brew services start postgresql

simonthompson99 / progress_bar.py

Last active January 13, 2021 12:44

[Iterator Progress Bar] Print a progress bar whilst processing an iterable #python

	def progress_bar(iterable, prefix='Progress', suffix='Complete', decimals=1,
	length=50, fill='=', printEnd="\r"):
	"""
	shows a terminal progreess bar as iterations are passed through, use as
	for i in progress_bar(range(1000)):
	pass
	:params: iteration: current iteration
	:params: total: total iterations
	:params: prefix: prefix string
	:params: suffix: suffix string

simonthompson99 / pseudo-random-integer.sql

Last active January 13, 2021 12:43

[Pseudo random integer generator] Create pseudo random integer from sequence ([from Postgres wiki](https://wiki.postgresql.org/wiki/Pseudo_encrypt)) #sql #database

	create sequence testes.test_id_seq
	;

	create or replace function pseudo_encrypt(value int) returns int as $$
	declare
	l1 int;
	l2 int;
	r1 int;
	r2 int;
	i int:=0;

simonthompson99 / schema_dump.sh

Last active January 13, 2021 12:41

[Timestamped db dump] Make a timestamped pg_dump of schema in database #sql #bash #database

	#!/bin/bash
	current_time=$(date "+%Y%m%d-%H%M")
	file_name="db_bu_$current_time.sql"
	echo $file_name
	pg_dump -h 10.1.24.39 -U cdt_user --schema consent_audit metrics > $file_name

Simon Thompson simonthompson99