def _subserial_pgsql(self, map_name, **kwargs):
"""
subserializator for pgsql
"""
SQL = """
select query.map_cont
from (
{0}
) as query
where query.map_name = '{1}'
limit 1
""".format(kwargs['query'], map_name)
psql = pgsqldb(**kwargs['connect'])
psql.sql(SQL)
content = psql.fetchone()
psql.close()
if content is not None:
content = content[0]
ops = self._detect_cont_ops(content)
if ops is not None:
self._logging(
2, "INFO: From Database:{0}, load Map {1}".format(
kwargs['connect']['dbname'], map_name))
return ops, content
def _preserial_pgsql(self, **kwargs):
"""
Find names for serialization all pgsql sources
"""
if isinstance(kwargs['query'], list):
query = '\n'.join(kwargs['query'])
else:
query = kwargs['query']
SQL = """
select query.map_name
from (
{}
) as query
""".format(query)
psql = pgsqldb(**kwargs['connect'])
psql.sql(SQL)
names = psql.fetchall()
psql.close()
if names is not None:
names = [my[0] for my in names]
self.logging(
2,
"INFO: In Database:{0}, add Map name(s) {1}".format(
kwargs['connect']['dbname'],
names
)
)
return names
def _subserial_pgsql(self, map_name, **kwargs):
"""
subserializator for pgsql
"""
if isinstance(kwargs['query'], list):
query = '\n'.join(kwargs['query'])
else:
query = kwargs['query']
SQL = u"""
select query.map_cont
from (
{0}
) as query
where query.map_name = '{1}'
limit 1
""".format(query, map_name)
psql = pgsqldb(**kwargs['connect'])
psql.sql(SQL)
content = psql.fetchone()
psql.close()
if content is not None:
content = content[0]
cont_format = self._detect_cont_format(content)
if cont_format is not None:
self.logging(
2,
"INFO: From Database:{0}, load Map {1}".format(
kwargs['connect']['dbname'],
map_name
)
)
return cont_format, content
def _subserial_maptemp_pgsql(self, map_name, **kwargs):
"""
subserializator maptemp for pgsql source list
"""
cont_format = "maptemp"
maptemp = kwargs['template']
if self.serial_formats[cont_format]['test'](maptemp):
if isinstance(kwargs['query'], list):
query = '\n'.join(kwargs['query'])
else:
query = kwargs['query']
SQL = u"""
select query.map_cont
from (
{0}
) as query
where query.map_name = '{1}'
limit 1
""".format(query, map_name)
psql = pgsqldb(**kwargs['connect'])
psql.sql(SQL)
mapsrc = psql.fetchone()
psql.close()
if mapsrc is not None:
mapsrc = mapsrc[0]
content = self._create_maptemp_content(mapsrc, maptemp)
if content:
self.logging(
2,
u"INFO: From Database:{0}, load Map Source {1}".format(
kwargs['connect']['dbname'], map_name))
return cont_format, content
def save2pgsql(self, table, name, col_name, col_cont, **kwargs):
"""
save json build map to postgresql database
table - table name
name - map name
col_name - column for map name
col_cont - column for filename or content:
if not kwargs['path'] - to col_name save content
if kwargs['path']= hibrid data db/path:
path - to col_name save filename, content save to file path
kwargs['path'] - path for hibrid data db/path
kwargs['columns'] - additional column for database, Dict format:
{
"cilumn_name": "column_data(::column_type)"
}
column_type - if str or unicode to type
kwargs[all next keys] - connect **dict as interface.pgsqldb.pg_defaults
"""
# kwargs
if kwargs.has_key('path'):
cont = kwargs.pop('path').decode('utf-8')
cont_type = 'text'
self.save2file(path=cont)
else:
cont = self.get_json()
cont = cont.replace("'", "''")
cont_type = 'json'
# SQL data
SQL = {
'create_table':
u"""
create table if not exists "{0}" (
"id" serial primary key,
"{1}" text unique,
"{2}" text
)
""",
'find_name':
u"""
select *
from "{0}"
where "{1}" = '{2}';
""",
'insert_all':
u"""
insert into "{0}"
(
"{1}",
"{2}"{6}
)
values
(
'{3}',
'{4}'::{5}{7}
);
""",
'update_all':
u"""
update "{0}"
set "{2}" = '{4}'::{5}{6}
where "{1}" = '{3}';
""",
'find_extra':
u"""
select *
from information_schema.columns
where table_name = '{0}'
and column_name = '{1}'
""",
'alter_extra':
u"""
alter table "{0}"
add column "{1}" {2};
""",
}
# init db
psql = pgsqldb(**kwargs)
# create table
if self.create_res:
psql.sql(SQL['create_table'].format(table, col_name, col_cont))
psql.commit()
# extra columns
ins_ex_cols = ""
ins_ex_vals = ""
upd_ex_sets = ""
if kwargs.has_key('columns'):
for col_extra in kwargs['columns']:
cont_extra = kwargs['columns'][col_extra]
if isinstance(cont_extra, int):
type_extra = 'int'
elif isinstance(cont_extra, float):
type_extra = 'float'
elif isinstance(cont_extra, (dict, list, tuple)):
type_extra = 'text'
elif isinstance(cont_extra, (str, unicode)):
if "::" in cont_extra:
type_extra = cont_extra.split("::")[-1]
cont_extra = cont_extra.split("::")[0]
else:
type_extra = 'text'
# find & alter extra columns
if self.create_res:
psql.sql(SQL['find_extra'].format(table, col_extra))
if psql.fetchone() is None:
psql.sql(SQL['alter_extra'].format(
table, col_extra, type_extra))
psql.commit()
# text data for extra columns
ins_ex_cols = u'{0},\n{1}"{2}"'.format(ins_ex_cols, " " * 20,
col_extra)
ins_ex_vals = u"{0},\n{1}'{2}'".format(ins_ex_vals, " " * 20,
cont_extra)
upd_ex_sets = u'{0},\n{1}"{2}" = \'{3}\''.format(
upd_ex_sets, " " * 20, col_extra, cont_extra)
# query map name and insert/update all
psql.sql(SQL['find_name'].format(table, col_name, name))
if psql.fetchone() is None:
psql.sql(SQL['insert_all'].format(table, col_name, col_cont, name,
cont, cont_type, ins_ex_cols,
ins_ex_vals))
else:
psql.sql(SQL['update_all'].format(table, col_name, col_cont, name,
cont, cont_type, upd_ex_sets))
# end db work
psql.commit()
psql.close()