2015年2月 – ats-min.com

Month: 2月 2015 (page 1 of 2)

SQL: Count Rows Correctly

2015年2月26日 / atsmin / 0 Comments

In T-SQL, aggregate functions(ex. COUNT(), SUM()) return NULL value or raise Error if all values are NULL or no rows. Here’s query count rows correctly even if all column values are NULL or no rows hit.

-- Count rows(exclude NULL value)
-- even if add "WHERE foo > 1", return value is also"0"(not NULL)
SELECT
    ISNULL(SUM(CASE WHEN bar IS NULL THEN 0 ELSE 1 END), 0)
FROM (
    SELECT 1 foo, NULL bar
) X
>> 0

-- Count rows(include NULL value)
-- if add "WHERE foo > 1", return value turns to "0"
SELECT
    COUNT(ISNULL(bar, 1))
FROM (
    SELECT 1 foo, NULL bar
) X
>> 1

-- Count rows(exclude NULL value)

-- even if add "WHERE foo > 1", return value is also"0"(not NULL)

SELECT

ISNULL(SUM(CASE WHEN bar IS NULL THEN 0 ELSE 1 END), 0)

FROM (

SELECT 1 foo, NULL bar

) X

>> 0

-- Count rows(include NULL value)

-- if add "WHERE foo > 1", return value turns to "0"

SELECT

COUNT(ISNULL(bar, 1))

FROM (

SELECT 1 foo, NULL bar

) X

>> 1

* please change “ISNULL” to “IFNULL” in MySQL or SQLite.

Python: format()

2015年2月26日 / atsmin / 0 Comments

In Python3, using format() for string formatting is standard instead using traditional “%” formatting.

'FOO{0}BAR'.format(10)
>>'FOO10BAR'

# parameters can be indexed or named
'FOO{0}{1}{2}BAR'.format('a', 'b', 'c')
>>'FOOabcBAR'
'FOO{a}{b}{c}BAR'.format(a=1, b=2, c=3)
>>'FOO123BAR'

# with separater
'FOO{0:,d}BAR'.format(1000000)
>>'FOO1,000,000BAR'

'FOO{0}BAR'.format(10)

>>'FOO10BAR'

# parameters can be indexed or named

'FOO{0}{1}{2}BAR'.format('a', 'b', 'c')

>>'FOOabcBAR'

'FOO{a}{b}{c}BAR'.format(a=1, b=2, c=3)

>>'FOO123BAR'

# with separater

'FOO{0:,d}BAR'.format(1000000)

>>'FOO1,000,000BAR'

https://docs.python.org/2.7/library/stdtypes.html#str.format

>This method of string formatting is the new standard in Python 3, and should be preferred to the % formatting described in String Formatting Operations in new code.

Python: Clear list

2015年2月21日 / atsmin / 0 Comments

L1 = [1, 2, 3]
L2 = L1
print(L1)
>>[1, 2, 3]
print(L2)
>>[1, 2, 3]
L1 = [] # create new empty list, so L2 values still exist
print(L1)
>>[]
print(L2)
>>[1, 2, 3] # not cleared
L1 = L2
L1[:] = [] # substitute empty list to referenced list
print(L1)
>>[]
print(L2)
>>[]       # also cleared

L1 = [1, 2, 3]

L2 = L1

print(L1)

>>[1, 2, 3]

print(L2)

>>[1, 2, 3]

L1 = [] # create new empty list, so L2 values still exist

print(L1)

>>[]

print(L2)

>>[1, 2, 3] # not cleared

L1 = L2

L1[:] = [] # substitute empty list to referenced list

print(L1)

>>[]

print(L2)

>>[] # also cleared

http://stackoverflow.com/questions/1400608/how-to-empty-a-list-in-python

Python: tempfile read

2015年2月18日 / atsmin / 0 Comments

Before reading tempfile contents, execute “seek(0)” is needed to point beginning of the file.

http://stackoverflow.com/questions/1202848/python-tempfile-temporaryfile-cannot-be-read-why

# -*- coding:utf-8 -*-
from tempfile import NamedTemporaryFile
from os import SEEK_END


with NamedTemporaryFile() as f:
    #before seek(0) f.read() returns nothing
    f.write('Foo')
    print f.read()
    >>
    # after seeking we can read the contents
    f.seek(0)
    print f.read()
    >>Foo
    # by seek(0, os.SEEK_END) file pointer moves to end of the file
    f.seek(0, SEEK_END)
    f.write('Bar')
    f.seek(0)
    print f.read()
    >>FooBar

# -*- coding:utf-8 -*-

from tempfile import NamedTemporaryFile

from os import SEEK_END

with NamedTemporaryFile() as f:

#before seek(0) f.read() returns nothing

f.write('Foo')

print f.read()

# after seeking we can read the contents

f.seek(0)

print f.read()

>>Foo

# by seek(0, os.SEEK_END) file pointer moves to end of the file

f.seek(0, SEEK_END)

f.write('Bar')

f.seek(0)

print f.read()

>>FooBar

Git: checkout from remote branch

2015年2月17日 / atsmin / 0 Comments

git fetch

git checkout -b {branch_name} origin/{branch_name}

Python: String encode

2015年2月17日 / atsmin / 0 Comments

string encode from ‘utf-8’ to ‘cp932’ avoiding “UnicodeDecodeError”

# -*- coding: utf-8 -*-

ENCODE_TO = 'cp932'

L = ['a', '漢字', u'漢字', 1, None]

for c in L:
    if isinstance(c, unicode):
        print c.encode(ENCODE_TO)
    elif isinstance(c, str):  
        # when element is str, firstly decode to unicode object and then encode
        print c.decode('utf-8').encode(ENCODE_TO, 'replace')
    else:
        print str(c)

# -*- coding: utf-8 -*-

ENCODE_TO = 'cp932'

L = ['a', '漢字', u'漢字', 1, None]

for c in L:

if isinstance(c, unicode):

print c.encode(ENCODE_TO)

elif isinstance(c, str):

# when element is str, firstly decode to unicode object and then encode

print c.decode('utf-8').encode(ENCODE_TO, 'replace')

else:

print str(c)

CheckIO: Count “Striped words”

2015年2月11日 / atsmin / 0 Comments

I learned re.split(pattern, text) in CheckIO Count Striped words question.

*Striped word is a word its alphabets sequenced vowels or consonants alternately.

import string
import re

VOWELS = "AEIOUY"
CONSONANTS = "BCDFGHJKLMNPQRSTVWXZ"
DELIMITER = string.punctuation + string.whitespace

def split_text(text, delimiters):
    pattern = "|".join(re.escape(d) for d in delimiters)
    return re.split(pattern, text)

def is_striped(word):
    if not word:
        return False
        
    compared = word[1:]
    for c1, c2 in zip(word, compared):
        if c1.upper() in VOWELS and c2.upper() in VOWELS \
           or c1.upper() in CONSONANTS and c2.upper() in CONSONANTS:
           return False
    return True

text == "My name is ..."
splitted = list(filter(lambda w: w and len(w) > 1 and re.search('\d', w) is None,
                           split_text(text, DELIMITER)))
print(len([w for w in splitted if is_striped(w)]))
>>
3

import string

import re

VOWELS = "AEIOUY"

CONSONANTS = "BCDFGHJKLMNPQRSTVWXZ"

DELIMITER = string.punctuation + string.whitespace

def split_text(text, delimiters):

pattern = "|".join(re.escape(d) for d in delimiters)

return re.split(pattern, text)

def is_striped(word):

if not word:

return False

compared = word[1:]

for c1, c2 in zip(word, compared):

if c1.upper() in VOWELS and c2.upper() in VOWELS \

or c1.upper() in CONSONANTS and c2.upper() in CONSONANTS:

return False

return True

text == "My name is ..."

splitted = list(filter(lambda w: w and len(w) > 1 and re.search('\d', w) is None,

split_text(text, DELIMITER)))

print(len([w for w in splitted if is_striped(w)]))

Git: ignore file permission change

2015年2月11日 / atsmin / 0 Comments

By default, git detects file permission change in workspace.

To ignore this, exec the following command.

git config core.filemode false

Python: format

2015年2月9日 / atsmin / 0 Comments

I learned string.format() today.

template = "{foo}, {bar}, {baz}"
print template.format(foo="FOO", bar="BAR", baz="BAZ")
>>
FOO, BAR, BAZ

template = "{foo}, {bar}, {baz}"

print template.format(foo="FOO", bar="BAR", baz="BAZ")

FOO, BAR, BAZ

Python: True or False

2015年2月9日 / atsmin / 0 Comments

I learned all python objects can be classified “True” or “False”.

“”, [], {}, None are evaluated “False”.

def check(data):
    if(
        filter(unicode.uppercase, data) 
        and filter(unicode.lowercase, data)
    ):
        return True
    else:
        return False

str = "aaa"
print check(str)
>>False
str = "Aaa"
print check(str)
>>True

def check(data):

if(

filter(unicode.uppercase, data)

and filter(unicode.lowercase, data)

return True

else:

return False

str = "aaa"

print check(str)

>>False

str = "Aaa"

print check(str)

>>True

ats-min.com

Just another programmer's site

Month: 2月 2015 (page 1 of 2)

SQL: Count Rows Correctly

Python: format()

Python: Clear list

Python: tempfile read

Git: checkout from remote branch

Python: String encode

CheckIO: Count “Striped words”

Git: ignore file permission change

Python: format

Python: True or False

ats-min.com

最近の投稿

最近のコメント

アーカイブ

メタ情報