SQLLite

gistfile1.md

apt-get install libsqlite3-0 libsqlite3-dev sqlite3

[saconfig]
recipe = collective.recipe.template
sqlalchemy_url = sqlite:///${buildout:directory}/var/albo.db
sqlalchemy_extras = 
input = ${buildout:directory}/templates/arpa.albo.zcml.in
output = ${buildout:directory}/etc/arpa.albo.zcml

[ale@padme templates]$cat arpa.albo.zcml.in 
<configure
    xmlns="http://namespaces.zope.org/zope"
    xmlns:db="http://namespaces.zope.org/db">
    <include package="z3c.saconfig" file="meta.zcml" />
    <db:engine
       name="albo" url="${saconfig:sqlalchemy_url}"
       ${saconfig:sqlalchemy_extras} />
    <db:session name="albo" engine="albo" />
</configure>

Dentro buildout.cfg

environment-vars =
    PTS_LANGUAGES it en
    LANG it_IT.utf8
    TZ Europe/Rome
    zope_i18n_compile_mo_files true
    BUILDOUT_DIR ${buildout:directory}
    ALBO_SACONFIG ${saconfig:output}

saconfig.py

# -*- coding: utf-8 -*-
from DateTime import DateTime
from datetime import date
from sqlalchemy import Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relation, relationship
from sqlalchemy.schema import ForeignKey
from sqlalchemy.types import Boolean, Date, Float, Integer, Unicode
from transaction import commit
from z3c.saconfig import named_scoped_session
from z3c.saconfig.interfaces import IEngineFactory
from zope.component._api import getUtility
from zope.configuration import xmlconfig
import os
import sys
import z3c.saconfig


ALBO_SACONFIG = os.environ.get('ALBO_SACONFIG', '')
xmlconfig.XMLConfig('meta.zcml', z3c.saconfig)()
xmlconfig.xmlconfig(file(ALBO_SACONFIG))
Base = declarative_base()
DBAlbo = named_scoped_session("albo")


class QA(Base):
    """
    Mapping for QA
    """
    __tablename__ = 'qa'
    qa_id = Column(Integer, primary_key=True)
    nome = Column(Unicode, nullable=False)
    cap = Column(Unicode)
    provincia = Column(Unicode)
    sigla = Column(Unicode)
    codice = Column(Unicode, default=u'XX')
    lat = Column(Float, default=0.0)
    lon = Column(Float, default=0.0)
    valore = Column(Integer, default=0)
    iqa = relationship("IQA")


    def from_json(self, json_data):
        ''' Fill the object with the data passed by json
        '''
        self.nome = json_data["NOME"]
        self.codice = json_data["COD_ISTAT"]
        self.lat = json_data["LAT"]
        self.lon = json_data["LON"]
        self.cap = json_data["CAP"]
        self.provincia = json_data["PROVINCIA"]
        self.sigla = json_data["SIGLA_PROVINCIA"]

        dati = json_data['DATI']
        for dato in dati:
            iqa = IQA().from_json(dato)
            self.iqa.append(IQA().from_json(dato))
        return self


class IQA(Base):
    ''' Mapping for IQA
    '''
    __tablename__ = 'iqa'
    iqa_id = Column(Integer, primary_key=True)
    valore = Column(Integer)
    data = Column(Date)
    qa_id = Column(ForeignKey(QA.qa_id))
    qa = relation(QA)

    def from_json(self, json_data):
        ''' Fill the object with the data passed by json
        '''
        self.valore = json_data["IQA"]
        self.data = DateTime(json_data["DATA_VALIDITA"]).asdatetime()
        return self

engine_factory = getUtility(IEngineFactory, name='albo')
engine = engine_factory()
Base.metadata.bind = engine
Base.metadata.create_all(engine)

./bin/instance run script/fillqa.py

from arpaer.helloworld.saconfig import DBAlbo, QA
from json import loads
from transaction import commit
from urllib2 import urlopen

json_source = "http://localhost/arpa.json"


def json_data():
    ''' Reads some data to insert in the template
    '''
    data = urlopen(json_source).read()
    return loads(data)


for row in json_data():
    DBAlbo.add(QA().from_json(row))

commit()

./bin/instance run script/testqa.py

import arpaer.helloworld.saconfig as sa

# query "base"
query_qa = sa.DBAlbo.query(sa.QA)
query_iqa = sa.DBAlbo.query(sa.IQA)

print "la prima riga di iqa"
print query_iqa.first()
print ""
print 'numero righe di qa con sigla == "PC"'
print len(query_qa.filter(sa.QA.sigla=='PC').all())
print ""
print 'numero righe di iqa che si riferiscono a qa_id 1'
print len(query_iqa.filter(sa.IQA.qa_id==1).all())
print ""
print 'gli iqa che si riferiscono ad un oggetto qa'
qa = query_qa.filter(sa.QA.qa_id==5).one()
print qa.iqa
print ""
print 'data per un oggetto di iqa'
iqa = query_iqa.filter(sa.IQA.iqa_id==5).one()
iqa.data.strftime('%Y/%m/%d')

print "select QA id e sigla quando qa_id compreso tra 1 e 10"
sa.DBAlbo.query(sa.QA.qa_id, sa.QA.sigla).filter(sa.QA.qa_id <=20).filter(sa.QA.qa_id >10).all()