db.py

Nicolas Longépé, Oct 18, 2016 11:14 AM

Download (2.86 KB)

 
1

    
2
from sqlalchemy.ext.declarative import declarative_base
3
from sqlalchemy import Column, Integer, MetaData, String, Unicode, DateTime, Float, ForeignKeyConstraint
4
from sqlalchemy.orm import relationship
5
from cls.osgeo.vector.db.sqlalchemy.spatialdb import Geometry, gismapper
6
from sqlalchemy import create_engine
7
from pyspatialite import dbapi2 as sqlite
8
from sqlalchemy.orm import sessionmaker, scoped_session
9
from zope.sqlalchemy import ZopeTransactionExtension
10
from cls.osgeo.vector.db.sqlalchemy.spatialdb import GISTable as Table
11

    
12

    
13
#######################################################
14
# configuration de la DB
15
#######################################################
16

    
17
metadata = MetaData()
18
Base = declarative_base(metadata=metadata)
19
maker = sessionmaker(autoflush=True, autocommit=False,
20
                     extension=ZopeTransactionExtension())
21
DBSession = scoped_session(maker)
22

    
23
fname = "/net/results/nlongepe/BlueBridge_Greece/Export_results/FishCages_Greece_v2.db"
24
dburi = 'sqlite:///{0}'.format(fname)
25
engine = create_engine(dburi, module=sqlite,
26
                       echo=False, encoding="utf8")
27

    
28
metadata.bind = engine
29
DBSession.configure(bind=engine)
30
v = DBSession.execute('SELECT spatialite_version();').scalar()
31
vM, vm = [int(e) for e in v.split('.')[:2]]
32
param = ''
33
if vM >= 4 and vm >= 1:
34
    param = 1
35
engine.execute('SELECT InitSpatialMetaData(%s);' % param)
36

    
37

    
38
#######################################################
39
# declaration des tables
40
#######################################################
41
class Farm(object):
42
    pass
43

    
44
farm_colums= [ Column("id", Integer, primary_key=True),
45
               Column('internal_id', Unicode),
46
               Column('design', Unicode),
47
               Column('material', Unicode),
48
               Column('feed_system', Unicode),
49
               Column('status', Unicode),
50
               Column('species', Unicode),
51
               Column('confidence', Integer),
52
               Column('start_date', DateTime),
53
               Column('end_date', DateTime),
54
               Column('bbox', Geometry(4326)),
55
               Column('centroid', Geometry(4326)),
56
               Column('total_surface', Float)]
57

    
58

    
59
table = Table("farm", metadata, *farm_colums, extend_existing=True)
60
table.create(checkfirst=True)
61
gismapper(Farm, table)
62

    
63
class Cage(object):
64
    pass
65

    
66
cage_columns = [Column("id", Integer, primary_key=True),
67
                Column("shape", String),
68
                Column('farm_id', Integer, nullable=False),
69
                Column('area', Float),
70
                Column('geometry', Geometry(4326), nullable=False, index=True),
71
                ForeignKeyConstraint(['farm_id'],['farm.id']) 
72
                ]
73
cage_mapping_properties = {'farm' : relationship(Farm, backref='cages') }
74

    
75
table = Table("cage", metadata, *cage_columns, extend_existing=True)
76
table.create(checkfirst=True)
77
gismapper(Cage, table, properties=cage_mapping_properties)
78

    
79
metadata.create_all(engine)