Archive
ValueError: Unsupported format character ‘Y’
Problem: Getting above value error in python when I use the following.
query = “””SELECT DATE_FORMAT(date_time,’%Y-%m-%d’) AS date
FROM table
WHERE col = %s”””cursor.execute(query, [pram1])
Solution:
Escape the % signs in the date.
query = “””SELECT DATE_FORMAT(date_time,’%%Y-%%m-%%d’) AS date
FROM table
WHERE col = %s”””cursor.execute(query, [pram1])
tsql – Query MSSQL from the command line
Problem:
Run the same query on a MSSQL database several times.
Solution:
tsql
Background.
I had to get data for various months from a table on MSSQL. Rather than sit down in front of the computer and manually change each value and re-run the query using SQL Query Tool(?) I opted to use the command line. So I moved to a linux box that could:
1. Access the Windows box that had MSSQL server installed.
2. Had tsql installed.
First I wrote the query.
$cat query
use cars go SELECT LEFT(RecordTime, 11) AS Date, COUNT(CarType) FROM sales WHERE datepart(yyyy, RecordTime) = '2010' AND datepart(mm, RecordTime) = '$month' GROUP BY LEFT(RecordTime, 11) ORDER BY LEFT(RecordTime, 11) go
$month is the variable that is going to change
On the linux box.
Check that I could connect remotely
shell>tsql -S [server-ip-address] -U [username] -P [password]
locale is “C”
locale charset is “ANSI_X3.4-1968”
1> exit
shell>
Once I knew I could connect I just needed to run the queries. So on the command line I did
shell>for month in 01 05 10 11 12; do sed “s/\$month/$month/” query | tsql -S [server-ip-address] -U [username] -P [password] > results_$month.csv; done
Thats it.
Find command – examples
Examples of how to use find command.
http://www.thegeekstuff.com/2009/03/15-practical-linux-find-command-examples/
http://ss64.com/bash/find.html#examples
mutt – Send e-mail as html instead of plain text.
$cat message.html | mutt -e "set content_type=text/html" -s "RE: Sample Message" user@domain.com
You need to install and configure mutt for this to work. Below is a sample .muttrc file.
set from="User <user@gmail.com>" set imap_user=user@gmail.com set imap_pass=<password> set folder=imaps://imap.gmail.com set spoolfile=imaps://imap.gmail.com/INBOX set record=imaps://imap.gmail.com/Sent set postponed=imaps://imap.gmail.com/Drafts set header_cache=~/.mutt_cache set smtp_url="smtps://user\@gmail.com:<password>@smtp.gmail.com/"
UPDATE:
The above .muttrc configuration may not work. If it fails try the below:
set imap_user = "username@gmail.com" set imap_pass = "password" set smtp_url = "smtp://username@smtp.gmail.com:587/" set smtp_pass = "password" set from = "username@gmail.com" set realname = "Your Real Name" set folder = "imaps://imap.gmail.com:993" set spoolfile = "+INBOX" set postponed="+[Gmail]/Drafts" set header_cache=~/.mutt/cache/headers set message_cachedir=~/.mutt/cache/bodies set certificate_file=~/.mutt/certificates set move = no
Source:
awk – Formatting numbers to be comma separated
Problem. How I can format numbers so that they come out comma separated?
From:
$less data.csv
Name, Day Total, Week Earnings, Ratio, Percentage(%) abc, 12, 94090, 34.9, 558765.09 def, 14, 34802, 44.9, 558769.01 ghi, 15, 2403, 14.7, 558767.09
To:
$less results.data
Name| Day Total| Week Earnings| Ratio| Percentage(%) abc|12|94,090|34.90|558,765.09| def|14|34,802|44.90|558,769.01| ghi|15|2,403|14.70|558,767.00|
Solution:
$awk -v FS="," -f comma.awk data.csv > results.data
$less comma.awk
#!/usr/bin/awk
{
if (NR == 1) # Skip the first record which is the heading.
{
gsub(",","|",$0); # Replace the commas with pipes.
print $0"|"
}
else
{
for (i=1; i <= NF; i++)
if ( i == 1)
# Print the operator name
printf ("%s|", $i);
else if ( i > 3)
# For the Ratios and Percentages show
# decimal points and commas
# at the thousands.
printf ("%'.2f|", $i);
else
# For the others show commas at the thousands.
printf ("%'d|", $i);
# Adds a new line to the end.
print ""
}
}
printf (“%’d|”, $i) – The quote after the % sign is what tells awk to add commas to the number.
- ‘ For decimal conversion (i, d, u, f, F, g, G) the output is to be grouped with thousands’ grouping characters if the locale information indicates any. Note that many versions of gcc(1) cannot parse this option and will issue a warning. SUSv2 does not include %’F. glibc 2.2 adds.
source: http://real-world-systems.com/docs/awk.1.html
NB
If you get the following error when running the given awk command.
awk: run time error: improper conversion(number 1) in printf(“%’d|”)
FILENAME=”data.csv” FNR=2 NR=2
Check if you are running mawk. If you are install gawk.
On Ubuntu you may use:
$sudo apt-get install gawk
NB http://www.gnu.org/s/gawk/manual/html_node/Format-Modifiers.html
A single quote or apostrophe character is a POSIX extension to ISO C. It indicates that the integer part of a floating point value, or the entire part of an integer decimal value, should have a thousands-separator character in it. This only works in locales that support such characters. For example:
$ cat thousands.awk #Show source program
-| BEGIN { printf “%’d\n”, 1234567 }
$ LC_ALL=C gawk -f thousands.awk
-| 1234567 #Results in “C” locale
$ LC_ALL=en_US.UTF-8 gawk -f thousands.awk
-| 1,234,567 #Results in US English UTF locale
Trouble shooting.
If the code above does not work, try changing the locale. I am using GNU awk 3.1.5.
$ LC_ALL=en_US.utf8 env awk -v FS="," -f comma.awk data.csv > results.data
Bash – Pattern matching operators
${variable#pattern} – If the pattern matches the beginning of the variable’s value, delete the shortest part that matches and return the rest.
user@computer:~/scripts$ path="/home/user/scripts"
user@computer:~/scripts$ echo ${path#/*/}
user/scripts
${variable##pattern} – If the pattern matches the beginning of the variable’s value, delete the longest part that matches and return the rest.
user@computer:~/scripts$ path="/home/user/scripts"
user@computer:~/scripts$ echo ${path##/*/}
scripts
${variable%pattern} – If the pattern matches the end of the variable’s value, delete the shortest part that matches and return the rest.
user@computer:~/scripts$ path="/home/user/scripts/very.big.file"
user@computer:~/scripts$ echo ${path%.*}
/home/user/scripts/very.big
${variable%%pattern} – If the pattern matches the end of the variable’s value, delete the longest part that matches and return the rest.
user@computer:~/scripts$ path="/home/user/scripts/very.big.file"
user@computer:~/scripts$ echo ${path%%.*}
/home/user/scripts/very
${variable/pattern/string} or ${variable//pattern/string} – The longest match to pattern in variable is replaced by string. In the first form, only the first match is replaced. In the second form, all matches are replaced. If the pattern begins with a #, it must match at the start of the variable. If it begins with a %, it must match with the end of the variable. If string is null, the matches are deleted. If variable is @ or *, the operation is applied to each positional parameter in turn and the expansion is the resultant list.
user@computer:~$_date="2011-04-13"
user@computer:~$echo ${_date/-/_}
2011_04-13
user@computer:~$echo ${_date//-/_}
2011_04_13
sed mimic tr
Problem: Use sed to replace new lines in a file instead of tr.
Solution:
$ cat file
apples peaches pears mangoes
$ cat file | tr ‘\n’ ‘, ‘
apples, peaches, pears, mangoes
$ sed ‘:mylbl;N;s/\n/,/;t mylbl’ file
apples, peaches, pears, mangoes
Sed explained. (Atleast that I how I understand it)
# Create a label. - :mylbl
# Move to the next line - N
# substitute new line with a comma. - s/\n/,/
# Move to a label only if a substitution was done. - t mylbl
sed ':mylbl;N;s/\n/,/;t mylbl' file
source: www.unix.com
Bash Arrays – Dynamic indexes
Problem:
Add items to an array without giving an index.
Solution:
#!/bin/bash
# Instead of this
#name_id[0]="Peter|487147"
#name_id[1]="Jane|5843695"
#name_id[2]="Sally|254148569"
# Use this
name_id[${#name_id[@]}]="Peter|487147"
name_id[${#name_id[@]}]="Jane|5843695"
name_id[${#name_id[@]}]="Sally|254148569"
for data in "${name_id[@]}"
do
echo $data;
name=$(echo $data | cut -d"|" -f1)
id=$(echo $data | cut -d"|" -f2)
echo $name
echo $id
done
${#name_id[@]} — gives the total number of items in the array.
This means that in future if you would like to add an item after Peter, you would not need to change the array indexes for the remaining items.
Learning Emacs – Ch 3 (Word Abbreviation)
Chapter 3 – Word Abbreviation
M-/ (dabbrev-expand) [Complete the word you are typing based on the nearest word that starts with this string (press M-/ again if that’s not the word you want)]
NB. There is a whole lot more. Just felt very advanced, so I left it out.
Learning Emacs – Ch 3 (Search)
Chapter 3 – Search
A. Searching
1. C-s (isearch-forward) [Incremental search forward]
2. C-s C-s (isearch-repeat-forward) [Find the next occurance of the ]
3. C-r (isearch-backward) [Incremental search backward]
4. C-s C-s (isearch-repeat-backward) [Find the previous occurance of the ]
5. C-g [Cancel command, ie give up searching]
6. C-s C-w (isearch-yank-word ) [Start incremental search using the word that the cursor is on]
7. C-s C-y (isearch-yank-line) [Start incremental search using the text from the cursor position to the end of the line]B. Search and Replace
1. M-x replace-string Enter “old-word” Enter “new-word” Enter [Replace all occurances of “old-word” with “new-word”]2. To replace one word at a time.
M-< [move to the beginning of the buffer]
M-% (query-replace) [prompt Query replace: appears in the minibuffer]
Type the search string and press Enter. Emacs searchs for the first occurence of the string. It waits for user input which can be any offi) Space or y – Replace searchstring with newstring and go to the next instance of the string.
ii) Del or n – Don’t replace; move to next instance.
iii) . (ie fullstop) – Replace the current instance and quit.
iv) , (ie comma) – Replace and let me see the result before moving on. (Press Space or y to move on.)
v) ! (ie exclaimation mark) – Replace all the rest and don’t ask.
vi) ^ (ie carrat) – Back up to the previous instance.
vii) Enter or q – Exit query-replace.
viii) E – Modify the replacement string.
pseudo-scripter 