Python 数据库

sqlite数据库

import sqlite3
conn = sqlite3.connect('/companydata')
cursor = conn.cursor()
cursor.execute('SELECT name, age, title, department, paygrade FROM employees')
for emp in map(EmployeeRecord._make, cursor.fetchall()):
print(emp.name, emp.title)

MySQL 数据库

import mysql
from mysql import connector
from collections import namedtuple
user = 'herbert'
pwd = '######'
host = '127.0.0.1'
db = 'world'
cnx = mysql.connector.connect(user=user, password=pwd, host=host,database=db)
cur.execute("SELECT Name, CountryCode, District, Population FROM CITY where CountryCode = 'CHN' AND Population > 500000")
CityRecord = namedtuple('City', 'Name, Country, Dsitrict, Population')
for city in map(CityRecord._make, cur.fetchall()):
print(city.Name, city.Population)

defaultdict
-----------

`class collections.defaultdict([default_factory[, ...]])`

If the default_factory attribute is None, this raises a KeyError exception with the key as argument.

If default_factory is not None, it is called without arguments to provide a default value for the given key, this value is inserted in the dictionary for the key, and returned.

This method is called by the `__getitem__()` method of the dict class when the requested key is not found; whatever it returns or raises is then returned or raised by `__getitem__()`.

Note that `__missing__()` is not called for any operations besides `__getitem__()`. This means that `get()` will, like normal dictionaries, return None as a default rather than using `default_factory`.

比较晕,什么是**工厂函数**: 来自python 核心编程的解释:

Python 2.2 统一了类型和类, 所有的内建类型现在也都是类, 在这基础之上, 原来的 所谓内建转换函数象`int()`, `type()`, `list()` 等等, 现在都成了工厂函数。 也就是说虽然他 们看上去有点象函数, 实质上他们是类。当你调用它们时, 实际上是生成了该类型的一个实 例, 就象工厂生产货物一样。 下面这些大家熟悉的工厂函数在老的Python 版里被称为内建函数:

```python
int(), long(), float(), complex()
str(), unicode(), basestring()
list(), tuple()
type()

以前没有工厂函数的其他类型,现在也都有了工厂函数。除此之外,那些支持新风格的类 的全新的数据类型,也添加了相应的工厂函数。下面列出了这些工厂函数:

dict()
bool()
set(), frozenset()
object()
classmethod()
staticmethod()
super()
property()
file()
>>> s = [('yellow', 1), ('blue', 2), ('yellow', 3), ('blue', 4), ('red', 1)]
>>> d = defaultdict(list)
>>> for k, v in s:
...     d[k].append(v)
...
>>> d.items()
[('blue', [2, 4]), ('red', [1]), ('yellow', [1, 3])]
>>> s = 'mississippi'
>>> d = defaultdict(int)
>>> for k in s:
...     d[k] += 1
...
>>> d.items()
[('i', 4), ('p', 2), ('s', 4), ('m', 1)]

The function int() which always returns zero is just a special case of constant functions. A faster and more flexible way to create constant functions is to use itertools.repeat() which can supply any constant value (not just zero):

>>> def constant_factory(value):
...     return itertools.repeat(value).next
>>> d = defaultdict(constant_factory('<missing>'))
>>> d.update(name='John', action='ran')
>>> '%(name)s %(action)s to %(object)s' % d
'John ran to <missing>'

还可以这样写!!'%(name)s %(action)s to %(object)s' % d

树(One-line Tree in Python)

from collections import defaultdict
def tree(): return defaultdict(tree)

Examples

>>> taxonomy = tree()
>>> taxonomy['Plantae']='aa'
>>> print taxonomy
defaultdict(<function tree at 0x102c61398>, {'Plantae': 'aa'})

JSON-esque

Now we can create JSON-esque nested dictionaries without explicitly creating sub-dictionaries—they magically come into existence as we reference them:

users = tree()
users['harold']['username'] = 'hrldcpr'
users['handler']['username'] = 'matthandlersux'

We can print this as json with print(json.dumps(users)) and we get the expected:

{"harold": {"username": "hrldcpr"}, "handler": {"username": "matthandlersux"}}

Without assignment

We can even create structure with no assignment at all, since merely referencing an entry creates it:

taxonomy = tree()
taxonomy['Animalia']['Chordata']['Mammalia']['Carnivora']['Felidae']['Felis']['cat']
taxonomy['Animalia']['Chordata']['Mammalia']['Carnivora']['Felidae']['Panthera']['lion']
taxonomy['Animalia']['Chordata']['Mammalia']['Carnivora']['Canidae']['Canis']['dog']
taxonomy['Animalia']['Chordata']['Mammalia']['Carnivora']['Canidae']['Canis']['coyote']
taxonomy['Plantae']['Solanales']['Solanaceae']['Solanum']['tomato']
taxonomy['Plantae']['Solanales']['Solanaceae']['Solanum']['potato']
taxonomy['Plantae']['Solanales']['Convolvulaceae']['Ipomoea']['sweet potato']

We'll prettyprint this time, which requires us to convert to standard dicts first:

def dicts(t): return {k: dicts(t[k]) for k in t}

Now we can prettyprint the structure with pprint(dicts(taxonomy)):

{'Animalia': {'Chordata': {'Mammalia': {'Carnivora': {'Canidae': {'Canis': {'coyote': {},'dog': {}}},
                                                      'Felidae': {'Felis': {'cat': {}},'Panthera': {'lion': {}}}}}}},
 'Plantae': {'Solanales': {'Convolvulaceae': {'Ipomoea': {'sweet potato': {}}},
                           'Solanaceae': {'Solanum': {'potato': {},'tomato': {}}}}}}

So the substructures we referenced now exist as dicts, with empty dicts at the leaves.

Iteration

This tree can be fun to iteratively walk through, again because structure comes into being simply by referring to it.

For example, suppose we are parsing a list of new animals to add to our taxonomy above, so we want to call a function like:

add(taxonomy,\
'Animalia>Chordata>Mammalia>Cetacea>Balaenopteridae>Balaenoptera>blue whale'\
.split('>'))

We can implement this simply as:

def add(t, path):
  for node in path:
    t = t[node]

Again we are never assigning to the dictionary, but just by referencing the keys we have created our new structure:

{'Animalia': {'Chordata': {'Mammalia': {'Carnivora': {'Canidae': {'Canis': {'coyote': {},'dog': {}}},
                                                      'Felidae': {'Felis': {'cat': {}},'Panthera': {'lion': {}}}},
                                        'Cetacea': {'Balaenopteridae': {'Balaenoptera': {'blue whale': {}}}}}}},
 'Plantae': {'Solanales': {'Convolvulaceae': {'Ipomoea': {'sweet potato': {}}},
                           'Solanaceae': {'Solanum': {'potato': {},'tomato': {}}}}}}