Archive

Archive for April, 2011

ValueError: Unsupported format character ‘Y’

April 26, 2011 Leave a comment

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])

Advertisements
Categories: python Tags: , , ,

tsql – Query MSSQL from the command line

April 22, 2011 Leave a comment

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.

Categories: MSSQL Tags: ,

Find command – examples

April 19, 2011 Leave a comment
Categories: bash Tags: ,

mutt – Send e-mail as html instead of plain text.

April 18, 2011 Leave a comment
$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:
http://shreevatsa.wordpress.com/2007/07/31/using-gmail-with-mutt-the-minimal-way/

Categories: bash Tags:

awk – Formatting numbers to be comma separated

April 15, 2011 6 comments

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
Categories: awk, bash Tags: , ,

Bash – Pattern matching operators

April 13, 2011 Leave a comment

${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

source: “Learning the bash Shell, Third Edition, by Cameron Newham and Bill Rosenblatt. Copyright 2005 O’Reilly Media, Inc., ISBN 0-596-00965-8.”

Categories: bash Tags:

sed mimic tr

April 11, 2011 Leave a comment

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

Categories: bash Tags: