Data makes sense. | Page 2 of 3 | It does. Let us tell you how.

Example MySQL to Gmail script

This is a usage example for the MySQL to HTML table combined with the BI mailer. We will make use of the two previous scripts to easily create a scheduled email sending from a MySQL database through a Gmail address with minimal effort.

The idea behind it was to be able to create a new email with only the base building blocks: the MySQL query for data source, subject/recipients as envelope/destination.

The advantage of this combination is the necessity of only 3(!) lines of code for a new email, allowing you to leverage email as an easy and flexible distribution channel for BI data.

In the example below we also add a greeting along with a relevant subject line, which raises our grand total to 4 easily readable lines of code to send an email from MySQL to Gmail. The connection string I used for the query is an open MySQL database with DNA info (this should go in your sql_to_html file).

	cnx = mysql.connector.connect(user='anonymous', password='',
					host='ensembldb.ensembl.org',port='3306',
					database='drosophila_melanogaster_core_48_43b',
					charset="utf8", use_unicode = True)

cnx = mysql.connector.connect(user='anonymous', password='',

host='ensembldb.ensembl.org',port='3306',

database='drosophila_melanogaster_core_48_43b',

charset="utf8", use_unicode = True)

The email contains a random sample of DNA shortened to 64 characters length. It takes a MySQL query and sends an HTML table via Gmail.

from bimailer import *
from sql_to_html import *
from datetime import datetime

dnaquery = """
	select id, sequence_64 from 
		(select seq_region_id as id, left(sequence,64) as sequence_64 
		from dna 
		order by rand() 
		limit 10) unsorted
	order by 1 asc;
"""

dnamail = Bimail('Daily random fruit fly DNA ' +datetime.now().strftime('%Y/%m/%d'), ['testmailer5432@gmail.com', 'adrian.brudaru@gmail.com'])
dnamail.htmladd('Good morning, find the relevant table below.')
dnamail.htmladd(sql_html(dnaquery))
dnamail.send()

from bimailer import *

from sql_to_html import *

from datetime import datetime

dnaquery = """

select id, sequence_64 from

(select seq_region_id as id, left(sequence,64) as sequence_64

from dna

order by rand()

limit 10) unsorted

order by 1 asc;

"""

dnamail = Bimail('Daily random fruit fly DNA ' +datetime.now().strftime('%Y/%m/%d'), ['testmailer5432@gmail.com', 'adrian.brudaru@gmail.com'])

dnamail.htmladd('Good morning, find the relevant table below.')

dnamail.htmladd(sql_html(dnaquery))

dnamail.send()

Result:

While this example is Gmail specific, it can be easily changed to use any email service. Keep in mind that even when using Gmail you should be able to reach any other email, and if you choose to avoid Gmail altogether, you can just change the bimailer.py to use any other email server.

MySQL query to HTML table with Python

For easy email reporting, I like to send plain HTML tables. An easy implementation is to take the query output and HTML-ize it.

The script below does just that – it connects to MySQL, sends a query, and turns the result into an HTML table. This table can further be used to be sent with the bimailer, or in any other way.

The MySQL connector for Python can be found on the MySQL official website here.

import mysql.connector

def query_mysql(query):
	cnx = mysql.connector.connect(user='myusername', password='mypass',
								  host='myip',port='myport',
								  database='dwh',charset="utf8", use_unicode = True)
	cursor = cnx.cursor()
	cursor.execute(query)
	#get header and rows
	header = [i[0] for i in cursor.description]
	rows = [list(i) for i in cursor.fetchall()]
	#append header to rows
	rows.insert(0,header)
	cursor.close()
	cnx.close()
	return rows

#take list of lists as argument	
def nlist_to_html(list2d):
	#bold header
	htable=u'<table border="1" bordercolor=000000 cellspacing="0" cellpadding="1" style="table-layout:fixed;vertical-align:bottom;font-size:13px;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:1px solid rgb(130,130,130)" >'
	list2d[0] = [u'<b>' + i + u'</b>' for i in list2d[0]] 
	for row in list2d:
		newrow = u'<tr>' 
		newrow += u'<td align="left" style="padding:1px 4px">'+unicode(row[0])+u'</td>'
		row.remove(row[0])
		newrow = newrow + ''.join([u'<td align="right" style="padding:1px 4px">' + unicode(x) + u'</td>' for x in row])  
		newrow += '</tr>' 
		htable+= newrow
	htable += '</table>'
	return htable
	


def sql_html(query):
	return nlist_to_html(query_mysql(query))
	
#usage example
#query = "select date, sum(sales) as sum_of_sales from table order by 1 desc limit 10"
#print sql_html(query)

import mysql.connector

def query_mysql(query):

cnx = mysql.connector.connect(user='myusername', password='mypass',

host='myip',port='myport',

database='dwh',charset="utf8", use_unicode = True)

cursor = cnx.cursor()

cursor.execute(query)

#get header and rows

header = [i[0] for i in cursor.description]

rows = [list(i) for i in cursor.fetchall()]

#append header to rows

rows.insert(0,header)

cursor.close()

cnx.close()

return rows

#take list of lists as argument

def nlist_to_html(list2d):

#bold header

htable=u'<table border="1" bordercolor=000000 cellspacing="0" cellpadding="1" style="table-layout:fixed;vertical-align:bottom;font-size:13px;font-family:verdana,sans,sans-serif;border-collapse:collapse;border:1px solid rgb(130,130,130)" >'

list2d[0] = [u'<b>' + i + u'</b>' for i in list2d[0]]

for row in list2d:

newrow = u'<tr>'

newrow += u'<td align="left" style="padding:1px 4px">'+unicode(row[0])+u'</td>'

row.remove(row[0])

newrow = newrow + ''.join([u'<td align="right" style="padding:1px 4px">' + unicode(x) + u'</td>' for x in row])

newrow += '</tr>'

htable+= newrow

htable += '</table>'

return htable

def sql_html(query):

return nlist_to_html(query_mysql(query))

#usage example

#query = "select date, sum(sales) as sum_of_sales from table order by 1 desc limit 10"

#print sql_html(query)

Scheduled emails with Python for BI

As part of reporting in business intelligence, scheduled emails are a very useful tool. They allow you to send various daily summaries, or attach larger reports.

For these purposes I put together a Python BI-mailer class to allow for easy management of all things you might normally require to send, such as text, summary/KPI html tables, embedded images or charts, or attach larger reports.

I will put the code here, and in the next articles I will give examples of how to use this easy Python emailer to accomplish daily reporting chores.

With this script you are able to:
send an email
optionally with text or multiple tables in body
optionally with attachments
optionally use the attachments as embedded images.

#to query:
import sys
import ast
from datetime import datetime

import smtplib
import mimetypes
from email.mime.multipart import MIMEMultipart
from email import encoders
from email.message import Message
from email.mime.audio import MIMEAudio
from email.mime.base import MIMEBase
from email.mime.image import MIMEImage
from email.mime.text import MIMEText



class Bimail:
	def __init__(self,subject,recipients):
		self.subject = subject
		self.recipients = recipients
		self.htmlbody = ''
		self.sender = "mailsender135@gmail.com"
		self.senderpass = 'mailsender135.'
		self.attachments = []

	def send(self):
		msg = MIMEMultipart('alternative')
		msg['From']=self.sender
		msg['Subject']=self.subject
		msg['To'] = ", ".join(self.recipients) # to must be array of the form ['mailsender135@gmail.com']
		msg.preamble = "preamble goes here"
		#check if there are attachments if yes, add them
		if self.attachments:
			self.attach(msg)
		#add html body after attachments
		msg.attach(MIMEText(self.htmlbody, 'html'))
		#send
		s = smtplib.SMTP('smtp.gmail.com:587')
		s.starttls()
		s.login(self.sender,self.senderpass)
		s.sendmail(self.sender, self.recipients, msg.as_string())
		#test
		print msg
		s.quit()
	
	def htmladd(self, html):
		self.htmlbody = self.htmlbody+'<p></p>'+html

	def attach(self,msg):
		for f in self.attachments:
		
			ctype, encoding = mimetypes.guess_type(f)
			if ctype is None or encoding is not None:
				ctype = "application/octet-stream"
				
			maintype, subtype = ctype.split("/", 1)


			if maintype == "text":
				fp = open(f)
				# Note: we should handle calculating the charset
				attachment = MIMEText(fp.read(), _subtype=subtype)
				fp.close()
			elif maintype == "image":
				fp = open(f, "rb")
				attachment = MIMEImage(fp.read(), _subtype=subtype)
				fp.close()
			elif maintype == "audio":
				fp = open(f, "rb")
				attachment = MIMEAudio(fp.read(), _subtype=subtype)
				fp.close()
			else:
				fp = open(f, "rb")
				attachment = MIMEBase(maintype, subtype)
				attachment.set_payload(fp.read())
				fp.close()
				encoders.encode_base64(attachment)
			attachment.add_header("Content-Disposition", "attachment", filename=f)
			attachment.add_header('Content-ID', '<{}>'.format(f))
			msg.attach(attachment)
	
	def addattach(self, files):
		self.attachments = self.attachments + files



#example below
if __name__ == '__main__':	
	# subject and recipients
	mymail = Bimail('Sales email ' +datetime.now().strftime('%Y/%m/%d'), ['recipient1@gmail.com', 'recipient2@gmail.com'])
	#start html body. Here we add a greeting. 
	mymail.htmladd('Good morning, find the daily summary below.')
	#Further things added to body are separated by a paragraph, so you do not need to worry about newlines for new sentences
	#here we add a line of text and an html table previously stored in the variable
	mymail.htmladd('Daily sales')
	mymail.htmladd(htmlsalestable)
	#another table name + table
	mymail.htmladd('Daily bestsellers')
	mymail.htmladd(htmlbestsellertable)
	# add image chart title
	mymail.htmladd('Weekly sales chart')
	#attach image chart
	mymail.addattach(['saleschartweekly.jpg'])
	#refer to image chart in html
	mymail.htmladd('<img src="cid:saleschartweekly.jpg"/>') 
	#attach another file
	mymail.addattach(['bimail.py'])
	#send!
	mymail.send()

100

101

102

103

104

105

106

107

108

109

110

#to query:

import sys

import ast

from datetime import datetime

import smtplib

import mimetypes

from email.mime.multipart import MIMEMultipart

from email import encoders

from email.message import Message

from email.mime.audio import MIMEAudio

from email.mime.base import MIMEBase

from email.mime.image import MIMEImage

from email.mime.text import MIMEText

class Bimail:

def __init__(self,subject,recipients):

self.subject = subject

self.recipients = recipients

self.htmlbody = ''

self.sender = "mailsender135@gmail.com"

self.senderpass = 'mailsender135.'

self.attachments = []

def send(self):

msg = MIMEMultipart('alternative')

msg['From']=self.sender

msg['Subject']=self.subject

msg['To'] = ", ".join(self.recipients) # to must be array of the form ['mailsender135@gmail.com']

msg.preamble = "preamble goes here"

#check if there are attachments if yes, add them

if self.attachments:

self.attach(msg)

#add html body after attachments

msg.attach(MIMEText(self.htmlbody, 'html'))

#send

s = smtplib.SMTP('smtp.gmail.com:587')

s.starttls()

s.login(self.sender,self.senderpass)

s.sendmail(self.sender, self.recipients, msg.as_string())

#test

print msg

s.quit()

def htmladd(self, html):

self.htmlbody = self.htmlbody+'<p></p>'+html

def attach(self,msg):

for f in self.attachments:

ctype, encoding = mimetypes.guess_type(f)

if ctype is None or encoding is not None:

ctype = "application/octet-stream"

maintype, subtype = ctype.split("/", 1)

if maintype == "text":

fp = open(f)

# Note: we should handle calculating the charset

attachment = MIMEText(fp.read(), _subtype=subtype)

fp.close()

elif maintype == "image":

fp = open(f, "rb")

attachment = MIMEImage(fp.read(), _subtype=subtype)

fp.close()

elif maintype == "audio":

fp = open(f, "rb")

attachment = MIMEAudio(fp.read(), _subtype=subtype)

fp.close()

else:

fp = open(f, "rb")

attachment = MIMEBase(maintype, subtype)

attachment.set_payload(fp.read())

fp.close()

encoders.encode_base64(attachment)

attachment.add_header("Content-Disposition", "attachment", filename=f)

attachment.add_header('Content-ID', '<{}>'.format(f))

msg.attach(attachment)

def addattach(self, files):

self.attachments = self.attachments + files

#example below

if __name__ == '__main__':

# subject and recipients

mymail = Bimail('Sales email ' +datetime.now().strftime('%Y/%m/%d'), ['recipient1@gmail.com', 'recipient2@gmail.com'])

#start html body. Here we add a greeting.

mymail.htmladd('Good morning, find the daily summary below.')

#Further things added to body are separated by a paragraph, so you do not need to worry about newlines for new sentences

#here we add a line of text and an html table previously stored in the variable

mymail.htmladd('Daily sales')

mymail.htmladd(htmlsalestable)

#another table name + table

mymail.htmladd('Daily bestsellers')

mymail.htmladd(htmlbestsellertable)

# add image chart title

mymail.htmladd('Weekly sales chart')

#attach image chart

mymail.addattach(['saleschartweekly.jpg'])

#refer to image chart in html

mymail.htmladd('<img src="cid:saleschartweekly.jpg"/>')

#attach another file

mymail.addattach(['bimail.py'])

#send!

mymail.send()

MySQL Federated database creation

MySQL does not support entire federated databases, but it does support federated tables – as long as the option is enabled in my.ini or my.cnf.

To create an entire federated DB, in a non manual, and easy to maintain way (refresh table definition if source table structure changes), we can use the following procedure.

First, the caveats:

1. Federated tables do not use indices in the usual way, so you may need to create a table locally and index before expecting any performance.

2. When using a client such as Heidi, that does show table status, the connection between your DB and the source DB of the fed table remains open. An issue can arise if the number of tables is significant, while max connections on source db is low. To get around this, periodically use flush tables on the destination DB to close the connections. There are alternatives to this, but this is the simplest workaround (may not be the best).

Personally, to get around this issue I scheduled a MySQL event that flushes every federated table (not FLUSH TABLES, but FLUSH TABLE TABLENAME;FLUSH TABLE TABLENAME2), to avoid blocking other queries. FLUSH TABLES flushes all tables, so it has to wait for other queries to finish, and any new queries have to wait for it to finish, causing no possibility of concurrent queries.

1. Get a nodata mysqldump in the same way as you get a normal dump, but with `-d` switch for no data

mysqldump -d -P3306 -h 1.1.1.1 -u username -pPASS dbname > mydump.sql

1	mysqldump -d -P3306 -h 1.1.1.1 -u username -pPASS dbname > mydump.sql

2. Convert the engine part of the tables into federated+connection (here done with Python)

# (.*?) is a capturing group, to allow the contents between that pattern to be reused later
# ´.*?´ which is the non greedy version of ´.*´
# ´\1´ is a backreference for the first capturing group for each occurrence, which will be for example "tablename1", "tablename2" and so on
# the patterns as raw string with the prefix ´r´ (which is a good habit to take)
# the re.DOTALL flag which allows "." to match newlines

import re

def convertdump(input, output, conn):
fin = open(input,'r')
filedata = fin.read()
fin.close()
pattern = r"CREATE TABLE `(.*?)`(.*?)ENGINE=.*?;"
replace_pattern = r"CREATE TABLE `\1`\2ENGINE=FEDERATED DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CONNECTION='"+conn+r"\1';"
newdata = re.sub(pattern, replace_pattern, filedata, flags=re.DOTALL)
fout = open(output,'w')
fout.write(newdata)
fout.close()

if __name__ == '__main__': # only run this part when the file is run, and not when file is imported.
convertdump('mydump.sql', 'fed_dump.sql','mysql://user:pass@ip:port/db/' )

# (.*?) is a capturing group, to allow the contents between that pattern to be reused later

# ´.*?´ which is the non greedy version of ´.*´

# ´\1´ is a backreference for the first capturing group for each occurrence, which will be for example "tablename1", "tablename2" and so on

# the patterns as raw string with the prefix ´r´ (which is a good habit to take)

# the re.DOTALL flag which allows "." to match newlines

import re

def convertdump(input, output, conn):

fin = open(input,'r')

filedata = fin.read()

fin.close()

pattern = r"CREATE TABLE `(.*?)`(.*?)ENGINE=.*?;"

replace_pattern = r"CREATE TABLE `\1`\2ENGINE=FEDERATED DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CONNECTION='"+conn+r"\1';"

newdata = re.sub(pattern, replace_pattern, filedata, flags=re.DOTALL)

fout = open(output,'w')

fout.write(newdata)

fout.close()

if __name__ == '__main__': # only run this part when the file is run, and not when file is imported.

convertdump('mydump.sql', 'fed_dump.sql','mysql://user:pass@ip:port/db/' )

3. Load up your new dump

mysql --host=127.0.0.1 --port=3306 --user=username--password=pass --database=fed_db_name < "fed_dump.sql"

1	mysql --host=127.0.0.1 --port=3306 --user=username--password=pass --database=fed_db_name < "fed_dump.sql"

You can run this periodically or on demand to refresh the table definitions if the source definitions change

MySQL Rank() function

UPDATE: On July 18 2018 the MySql server team announced the introduction of window functions. The article was written before this, offering an workaround to gain some similar functionality. I updated this article now with the latest and best practice way to do this correctly, but sill leave the old article for reference.

AS of Mysql 8, you can now use window functions such as rownum() OVER(PARTITION BY country) . Interestingly, you can just reference your partition by an alias, and define the partition in a separate window clause section (like where clause), allowing reuse:

mysql> SELECT
         val,
         ROW_NUMBER()   OVER w AS 'row_number',
         rank() OVER w AS 'rank'
       FROM numbers
       WINDOW w AS (ORDER BY val);

mysql> SELECT

val,

ROW_NUMBER() OVER w AS 'row_number',

rank() OVER w AS 'rank'

FROM numbers

WINDOW w AS (ORDER BY val);

Happy window aggregating!

OLD ARTICLE FROM 2014 BELOW:

A quick and dirty custom MySQL Rank() function to emulate rank from PostgreSQL into mySQL. Same disclaimers as the custom rownum() function. In this case, I am using it to rank decimals, but feel free to change the input type.


DROP FUNCTION IF EXISTS rank;
DELIMITER $$
CREATE FUNCTION rank(input DECIMAL(11,4))
  RETURNS int(11)
BEGIN

  set @prvinput=if(@prvinput is null, input, @prvinput);
  set @prv=if(@ranklastrun=CURTIME(6) and @prvinput=input,@prv,if(@ranklastrun=CURTIME(6), @prv+1, 1));
  set @ranklastrun=CURTIME(6);
  set @prvinput=input;
  RETURN @prv;
END $$
$$
DELIMITER ;

DROP FUNCTION IF EXISTS rank;

DELIMITER $$

CREATE FUNCTION rank(input DECIMAL(11,4))

RETURNS int(11)

BEGIN

set @prvinput=if(@prvinput is null, input, @prvinput);

set @prv=if(@ranklastrun=CURTIME(6) and @prvinput=input,@prv,if(@ranklastrun=CURTIME(6), @prv+1, 1));

set @ranklastrun=CURTIME(6);

set @prvinput=input;

RETURN @prv;

END $$

DELIMITER ;

to invoke, use rank(column to rank),such as below:


select nr, rank(nr) from (
select 2 as nr
union all select 2
union all select 2
union all select 3
union all select 34
union all select 4)a
order by 1

select nr, rank(nr) from (

select 2 as nr

union all select 2

union all select 3

union all select 34

union all select 4)a

order by 1

output:

|nr|      |rank(nr)|
|2|       |1|
|2|       |1|
|2|       |1|
|3|       |2|
|4|       |3|
|34|      |4|

|nr| |rank(nr)|

|2| |1|

|3| |2|

|4| |3|

|34| |4|

MySQL rownum() row number function

mysql> SELECT
         val,
         ROW_NUMBER()   OVER w AS 'row_number',
         rank() OVER w AS 'rank'
       FROM numbers
       WINDOW w AS (ORDER BY val);

mysql> SELECT

val,

ROW_NUMBER() OVER w AS 'row_number',

rank() OVER w AS 'rank'

FROM numbers

WINDOW w AS (ORDER BY val);

Happy window aggregating!

OLD ARTICLE FROM 2014 BELOW:

MySQL does not have a built in row number function, which makes it an ugly hack to get the row number – an ugly hack that needs to be repeated over and over, every time you need it.

I have taken the liberty of writing a rownum() function, that is just as bad, but at least it will keep your code clean.

DISCLAIMER:

does not work on older versions of mySQL
It is based on using variables that only reset when time changes. If the function is invoked in 2 separate places in the same microsecond(1 millionth of a second), the output will not be as expected. The variable will not reset, the output will not start counting from 1.
If you run the function more than once in the same query, it will increment every time it is run, so possibly multiple times per row.

For the purposes of BI, I find the associated risks, when used as intended, non existent.
For older versions of mySQL server, you can try finding another creative way to reset the counter for the row number.

Row number function


DROP FUNCTION IF EXISTS rownum;
DELIMITER $$
CREATE FUNCTION rownum()
  RETURNS int(11)
BEGIN
  set @prvrownum=if(@ranklastrun=CURTIME(6),@prvrownum+1,1);
  set @ranklastrun=CURTIME(6);
  RETURN @prvrownum;
END $$
$$
DELIMITER ;

DROP FUNCTION IF EXISTS rownum;

DELIMITER $$

CREATE FUNCTION rownum()

RETURNS int(11)

BEGIN

set @prvrownum=if(@ranklastrun=CURTIME(6),@prvrownum+1,1);

set @ranklastrun=CURTIME(6);

RETURN @prvrownum;

END $$

DELIMITER ;

to invoke it, use rownum(), such as below


select cols, rownum()
from table

select cols, rownum()

from table

Good luck!

Get yesterday’s date in a windows batch file – correct version

Hi all,

If you use a windows server and get yesterday’s date for something like a download, you might probably use this popular script that can be found here and there. Unfortunately, the script is somewhat broken when dealing with getting the last day of the month. The logic is solid, but somewhere between the idea and implementation, something was lost.

see below the working, correct version:


@setlocal enableextensions enabledelayedexpansion
rem Get the date from WMI (on one line).

for /f "skip=2 tokens=2-7 delims=," %%A in ('wmic
path win32_localtime get day^,month^,year^ /format:csv') do (
set /a "yest_yyyy = %%C"
set /a "yest_mm = %%B"
set /a "yest_dd = %%A"
)

rem Not the first of the month, just decrement day.

if not %yest_dd%==1 (
set /a yest_dd = yest_dd - 1
goto done
)

rem Jan 1, set to Dec 31 previous year.

if %yest_mm%==1 (
set /a "yest_dd = 31"
set /a "yest_mm = 12"
set /a "yest_yyyy = yest_yyyy - 1"
goto :done
)

rem Any other day, decrement month.

set /a "yest_mm = yest_mm - 1"

rem Need to find last day, default to 31.

set dim=31

rem Apr/Jun/Sep/Nov all have 30 days. Feb gets special handling.

if %yest_mm%==4 set dim=30
if %yest_mm%==6 set dim=30
if %yest_mm%==9 set dim=30
if %yest_mm%==11 set dim=30
if not %yest_mm%==2 goto :got_dim

rem Default Feb to 28 then use rules to override.

set dim=28

set /a "divid=yest_yyyy%%400"
if "%divid%"=="0" goto daysinmonth_29days
set /a "divid=yest_yyyy%%100"
if "%divid%"=="0" goto :done
set /a "divid=yest_yyyy%%4"
if not "%divid%"=="0" goto :got_dim

rem Adjust to 29 days.

:daysinmonth_29days

set dim=29

:got_dim
set yest_dd=%dim%

:done

 

rem Pad out and return value.

if %yest_mm% lss 10 set yest_mm=0%yest_mm%
if %yest_dd% lss 10 set yest_dd=0%yest_dd%

set yesterday=%yest_yyyy%-%yest_mm%-%yest_dd%

endlocal &amp;&amp; set yesterday=%yesterday%

@setlocal enableextensions enabledelayedexpansion

rem Get the date from WMI (on one line).

for /f "skip=2 tokens=2-7 delims=," %%A in ('wmic

path win32_localtime get day^,month^,year^ /format:csv') do (

set /a "yest_yyyy = %%C"

set /a "yest_mm = %%B"

set /a "yest_dd = %%A"

)

rem Not the first of the month, just decrement day.

if not %yest_dd%==1 (

set /a yest_dd = yest_dd - 1

goto done

)

rem Jan 1, set to Dec 31 previous year.

if %yest_mm%==1 (

set /a "yest_dd = 31"

set /a "yest_mm = 12"

set /a "yest_yyyy = yest_yyyy - 1"

goto :done

)

rem Any other day, decrement month.

set /a "yest_mm = yest_mm - 1"

rem Need to find last day, default to 31.

set dim=31

rem Apr/Jun/Sep/Nov all have 30 days. Feb gets special handling.

if %yest_mm%==4 set dim=30

if %yest_mm%==6 set dim=30

if %yest_mm%==9 set dim=30

if %yest_mm%==11 set dim=30

if not %yest_mm%==2 goto :got_dim

rem Default Feb to 28 then use rules to override.

set dim=28

set /a "divid=yest_yyyy%%400"

if "%divid%"=="0" goto daysinmonth_29days

set /a "divid=yest_yyyy%%100"

if "%divid%"=="0" goto :done

set /a "divid=yest_yyyy%%4"

if not "%divid%"=="0" goto :got_dim

rem Adjust to 29 days.

:daysinmonth_29days

set dim=29

:got_dim

set yest_dd=%dim%

:done

rem Pad out and return value.

if %yest_mm% lss 10 set yest_mm=0%yest_mm%

if %yest_dd% lss 10 set yest_dd=0%yest_dd%

set yesterday=%yest_yyyy%-%yest_mm%-%yest_dd%

endlocal && set yesterday=%yesterday%

Stream gzip with Ruby

Here is a quick overview on how to stream gzip with Ruby: For the purpose of an OS-agnostic script to gzip a SQL dump, I needed a Ruby script. Unfortunately, for large files Ruby would return an error if the file size is too large to fit in the RAM. The error looks as below:

"failed to allocate memory (<span style="color: #000000;">NoMemoryError</span>)"

1	"failed to allocate memory (<span style="color: #000000;">NoMemoryError</span>)"

So I decided to do a sort of stream, combining some basic examples online. As I was not able to find something ready made, I combined file streaming with gzipping.

The script below does a SQL dump with date, gzips it in chunks with Ruby, and deletes the non-zipped file. (It is written for a Windows server, so change the system commands if moving to Linux.)

Additionally I wanted to check performance, so I also added timing.

Stream gzip with Ruby

require 'zlib'

time=Time.new
date=time.to_s[0,10]
database='mydatabasename'
source=database+'_'+date+'.sql'
dest=source+'.gz'
MEGABYTE = 1024*1024 # or change size of chunk if you find it to run faster in larger chunks.

system 'mysqldump --port=3307 --host=127.0.0.1 -u root --password=mypassword '+database+'&gt; '+source #do not use exec to start the mysql dump, or the ruby script will terminate.

dump_time=Time.new

class File
def each_chunk(chunk_size=MEGABYTE)
yield read(chunk_size) until eof?
end
end

File.open(dest, 'w') do |d|
gz = Zlib::GzipWriter.new(d)
gz.mtime = File.mtime(source)
gz.orig_name = source
open(source, "rb") do |f| f.each_chunk() {|chunk| gz.write chunk } end
gz.close
end

system 'del '+source # delete original dump

#show some stats:

puts source
end_time=Time.new
duration=end_time-time
dump_duration=dump_time-time
gzip_duration=end_time-dump_time

puts 'Dump duration:'+dump_duration.to_s+' seconds'
puts 'Gzip duration:'+gzip_duration.to_s+' seconds'
puts 'Total duration:'+duration.to_s+' seconds'

require 'zlib'

time=Time.new

date=time.to_s[0,10]

database='mydatabasename'

source=database+'_'+date+'.sql'

dest=source+'.gz'

MEGABYTE = 1024*1024 # or change size of chunk if you find it to run faster in larger chunks.

system 'mysqldump --port=3307 --host=127.0.0.1 -u root --password=mypassword '+database+'> '+source #do not use exec to start the mysql dump, or the ruby script will terminate.

dump_time=Time.new

class File

def each_chunk(chunk_size=MEGABYTE)

yield read(chunk_size) until eof?

end

File.open(dest, 'w') do |d|

gz = Zlib::GzipWriter.new(d)

gz.mtime = File.mtime(source)

gz.orig_name = source

open(source, "rb") do |f| f.each_chunk() {|chunk| gz.write chunk } end

gz.close

end

system 'del '+source # delete original dump

#show some stats:

puts source

end_time=Time.new

duration=end_time-time

dump_duration=dump_time-time

gzip_duration=end_time-dump_time

puts 'Dump duration:'+dump_duration.to_s+' seconds'

puts 'Gzip duration:'+gzip_duration.to_s+' seconds'

puts 'Total duration:'+duration.to_s+' seconds'

There you go, an easy way to stream gzip with Ruby. Enjoy!

Using Cost Revenue Ratio to measure Campaign Efficiency

The Cost Revenue Ratio (CRR), also known as efficiency ratio, is a way of comparing expenses to revenue. While the CRR is mainly used in banking businesses, it is also an interesting key performance indicator for performance marketers.

The calculation is very straightforward: Just divide the spendings of your campaign by revenue of that campaign. The result will be a percentage, telling you how much you have spent to generate one euro/dollar of revenue, meaning: the lower the percentage, the more efficient at creating revenue. If your result is larger than 100%, your costs are higher than the created revenue – optimization of the campaign is urgently needed!

To use this metric for measuring your Google Adwords campaigns, make sure to assign a value to each conversion action. This will enable you to use the “Total conv. Value” column in your campaign overview, you will need it to calculate the CRR.

The result could look like in the following example:

Here you see an extract from a campaign performance report with the Cost Revenue Ratio in the last column. Cost of the respective campaign gets divided by total conversion value. When not having any CRR benchmarks from your financial department, it might be a good idea to use the brand campaign as orientation. This campaign will most likely reach the best cost revenue ratio you can find in your account, so the rest of your campaigns should be somewhere between this and 100%, the closer to the brand campaign benchmark, the better.

Normalizing codes, mySQL extracting alphanumeric functions

Wanted to share an sql function I end up using quite frequently when dealing with user input data – normalization to alphanumeric. It can also be modified into a function to extract numeric values. If you do modify it to that, make sure you keep returns varchar for the cases of extracting numeric values beginning with 0. You can also add other characters, such as the minus sign, if required.

Example use cases:

Matching producer codes:

select producer_id, lcase(alphanumeric(producer_id)) from table
&gt;'BLA-bla-541' ; 'blabla541'
&gt;'bla Bla+/541'; 'blabla541'

select producer_id, lcase(alphanumeric(producer_id)) from table

>'BLA-bla-541' ; 'blabla541'

>'bla Bla+/541'; 'blabla541'

Normalizing/matching phone numbers:

select phone_number, alphanumeric(replace(phone_number, '+', '00') from table
&gt;'+45 097 . 4521/4761' ; '004509745214761'
&gt;'0045/(0)97.4521.4761'; '004509745214761'

select phone_number, alphanumeric(replace(phone_number, '+', '00') from table

>'+45 097 . 4521/4761' ; '004509745214761'

>'0045/(0)97.4521.4761'; '004509745214761'

function:

DELIMITER !

DROP FUNCTION IF EXISTS alphanumeric!

CREATE FUNCTION alphanumeric ( str VARCHAR(255) )
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret VARCHAR(255) DEFAULT '';
DECLARE c VARCHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF  c REGEXP '[[:alpha:]]' or c REGEXP '[[:digit:]]' THEN -- Change matching here
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END !
DELIMITER ;

DELIMITER !

DROP FUNCTION IF EXISTS alphanumeric!

CREATE FUNCTION alphanumeric ( str VARCHAR(255) )

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN

DECLARE i, len SMALLINT DEFAULT 1;

DECLARE ret VARCHAR(255) DEFAULT '';

DECLARE c VARCHAR(1);

SET len = CHAR_LENGTH( str );

REPEAT

BEGIN

SET c = MID( str, i, 1 );

IF c REGEXP '[[:alpha:]]' or c REGEXP '[[:digit:]]' THEN -- Change matching here

SET ret=CONCAT(ret,c);

END IF;

SET i = i + 1;

END;

UNTIL i > len END REPEAT;

RETURN ret;

END !

DELIMITER ;

1. Get a nodata mysqldump in the same way as you get a normal dump, but with -d switch for no data

2. Convert the engine part of the tables into federated+connection (here done with Python)

3. Load up your new dump

to invoke, use rank(column to rank),such as below:

output:

Row number function

Stream gzip with Ruby

1. Get a nodata mysqldump in the same way as you get a normal dump, but with `-d` switch for no data