Example #1
0
def adapt_macaddr(maddr):
    from psycopg2.extensions import adapt, AsIs

    return AsIs("{0}::macaddr".format(adapt(str(maddr))))
def addapt_numpy_float32(numpy_float32):
    return AsIs(numpy_float32)
Example #3
0
                'ELSE last_value + %s '
                'END '
                'FROM "%s"' % (self.flavor.param, name), (increment, ))
        else:
            cursor.execute('SELECT CASE WHEN NOT is_called THEN last_value '
                           'ELSE last_value + increment_by '
                           'END '
                           'FROM "%s"' % name)
        return cursor.fetchone()[0]


register_type(UNICODE)
if PYDATE:
    register_type(PYDATE)
if PYDATETIME:
    register_type(PYDATETIME)
if PYTIME:
    register_type(PYTIME)
if PYINTERVAL:
    register_type(PYINTERVAL)
register_adapter(float, lambda value: AsIs(repr(value)))
register_adapter(Decimal, lambda value: AsIs(str(value)))


def convert_json(value):
    return json.loads(value, object_hook=JSONDecoder())


register_default_json(loads=convert_json)
register_default_jsonb(loads=convert_json)
cursor.execute("""  VACUUM ANALYZE target_instances;
""")
connection.commit()

#some sort of magic
connection.set_isolation_level(0)
cursor.execute("""  VACUUM ANALYZE strat_phrases;
""")
connection.commit()

#some sort of magic
connection.set_isolation_level(0)
cursor.execute(
    """  VACUUM ANALYZE %(my_app)s_sentences_%(my_product)s;
""", {
        "my_app": AsIs(config['app_name']),
        "my_product": AsIs(config['product'].lower())
    })
connection.commit()

#==============================================================================
# FIND STRATIGRAPHIC PHRASES NEAREST TO ORPHAN TARGET INSTANCES
#==============================================================================

#how many sentences back from orphan to look for stratigraphic phrases
strat_distance = 3

#initialize the dump variable
strat_target_distant = []

#list of docids with orphaned targets
Example #5
0
 def _adapt_datetime(dt):
     return AsIs("%s" % adapt(DateTime.iso8601(dt)))
Example #6
0
    def with_query(self, **kwargs):
        results = kwargs.get('results', [None])[0]
        page = kwargs.get('page', [None])[0]
        sort = kwargs.get('sortField', ['id'])
        sort = 'q.' + sort[0]
        order = kwargs.get('sortOrder', ['ASC'])[0]

        if order == 'ascend':
            order = 'ASC'
        elif order == 'descend':
            order = 'DESC'

        if results and page:
            questions_range = int(results) * (int(page) -
                                              1), int(results) * (int(page))
        else:
            questions_range = None

        course_condition = self._course_query(kwargs.get('courses[]', None))
        quizzes_query = self._question_query_quizzes(
            kwargs.get('quizzes[]', None))
        author_condition = self._question_query_author(
            kwargs.get('authors[]', None))
        tags_query = self._question_query_tags(kwargs.get('tags[]', None))
        having_query = self._having_clause(quizzes_query, tags_query, kwargs)

        from django.db import connection
        with connection.cursor() as cursor:
            cursor.execute(
                """
                WITH q(id,last_modify_date,title,author_id,responses) AS (
                SELECT pq.id, pq.last_modify_date, pq.title, pq.author_id, COUNT(pr.id) AS responses
                FROM polls_question pq LEFT JOIN polls_response pr ON pr.question_id = pq.id
                WHERE true {} {}
                GROUP BY pq.id
                )
                SELECT  q.id
                FROM q
                LEFT JOIN polls_quizquestion pqq ON pqq.question_id=q.id
                LEFT JOIN polls_question_tags pqt ON pqt.question_id=q.id
                WHERE true {} {}
                GROUP BY q.id, %s
                {}
                ORDER BY %s %s
                ;""".format(course_condition, author_condition, quizzes_query,
                            tags_query, having_query),
                [AsIs(sort), AsIs(sort), AsIs(order)])

            result_list = []
            length = 0
            for index, row in enumerate(cursor.fetchall()):
                if questions_range:
                    if index + 1 <= questions_range[
                            1] and index + 1 > questions_range[0]:
                        question = self.model.objects.get(pk=row[0])
                        result_list.append(question)
                else:
                    question = self.model.objects.get(id=row[0])
                    result_list.append(question)
                length += 1
        return result_list, length
Example #7
0
 def adapt_money(m):
     return AsIs('(%s,%s)::currency_amount' % (adapt(m.amount), adapt(m.currency)))
Example #8
0
 def itemID(self, itemID):
     self.__itemID = AsIs(self.__convert(itemID, 'itemID'))
Example #9
0
 def table(self, table):
     self.__table = AsIs(self.__convert(table, 'table'))
Example #10
0
 def timestamp(self, timestamp):
     self.__timestamp = AsIs(self.__convert(timestamp, 'timestamp'))
Example #11
0
 def userID(self, userID):
     self.__userID = AsIs(self.__convert(userID, 'userID'))
Example #12
0
 def delete(cls, id):
     """ Delete record from table cls.table with id of cls.table_key """
     cls.test_connection()
     cls._cur.execute("DELETE FROM %s WHERE %s = %s;",
                      (AsIs(cls.table), AsIs(cls.table_key), id))
Example #13
0
    def query_property(self,
                       select_list,
                       query_list,
                       orderby,
                       paging_size=20,
                       page_num=1):
        """
        Run query from a list of GET param, plus version
        NOTE: only for property
        :param select_list: str array
        :param query_list: str array
        :param orderby: str, default is "id"
        :param paging_size: int, default is 20
        :param page_num: int, default is 1
        :return:
        """
        def streetnameHandler(streetname):
            """
            For sql like, replace space with % and remove suffix
            e.g.: 1615 Greencastle Ave -> 1615%Greencastle%
            """
            res = '%' + "%".join(streetname.split()[:-1]) + '%'
            return res

        table = "properties"  # NOTE: DO NOT TOUCH IT!!!
        offset = (page_num - 1) * paging_size
        # orderby handler
        orderby = "id" if not orderby else orderby
        if query_list.get('descend') == 'true':  # will ignore other val
            orderby += " DESC"
        # or handler,
        if query_list.get('or') == 'true':
            or_condition = []
            # remove 'or', streetname, city, postalcode from query_list
            for field in ["streetname", "city", "postalcode"]:
                if query_list.get(field):
                    tmp_val = query_list.pop(field)
                    tmp_val = '%' + "%".join(tmp_val.split()) + '%'
                    query_str = "upper(%s) like upper('%s')"
                    or_condition.append(query_str % (
                        field,
                        tmp_val,
                    ))
            # generate AND (streetname like '%x%' OR city like '%x%' OR postalcode=x)
            or_condition = "(" + " OR ".join(or_condition) + ")"
        else:
            or_condition = ""

        # streetname
        if query_list.get('streetname'):
            query_list['streetname'] = streetnameHandler(
                query_list['streetname'])
        # generate SQL WHERE
        condition = self.__gencondition(query_list)
        if or_condition:
            if condition:
                condition += " AND " + or_condition
            else:
                condition = or_condition
        # when query list is empty (do not consider 'descend'), return everything
        condition = "id>0" if not condition else condition

        cur = self.__connect()
        query = "SELECT %s \
                FROM %s \
                WHERE %s \
                ORDER BY %s \
                LIMIT %s \
                OFFSET %s;"

        try:
            cur.execute(query, (
                AsIs(','.join(select_list)),
                AsIs(table),
                AsIs(condition),
                AsIs(orderby),
                AsIs(str(paging_size)),
                AsIs(str(offset)),
            ))
            total_count = self.__gettotal(table, condition)
            return self.__getresult(cur, paging=True, total=total_count)
        except Exception as e:
            return {
                "query": query,
                "success": "False",
                "count": 0,
                "results": None,
                "colnames": None
            }
Example #14
0
from psycopg2.extensions import AsIs
import psycopg2

import obd
import sys
import os
sys.path.insert(0, os.path.realpath(os.path.dirname(__file__)))
from datetime import datetime
from smartOBD.dbconnect import dbconn, cur
import smartOBD
from smartOBD import asynco
import time
dbtable = asynco.userGet()
speed = 25
while True:  # infinite loop
    speed += 1
    data = [datetime.now(), speed, 25, 25, 25, 25, 25]
    cur.execute("delete from %s;", [AsIs(dbtable)])
    cur.execute("insert into %s VALUES(%s, %s, %s, %s, %s, %s,%s);",
                (AsIs(dbtable), data[0], data[1], data[2], data[3], data[5],
                 data[4], data[6]))
    dbconn.commit()
    time.sleep(0.5)
Example #15
0
def drop_table_model_company_country(cr):
    tablename = 'company_country_config_settings'
    if tools.table_exists(cr, tablename):
        _logger.info("Dropping table %s", tablename)
        cr.execute("DROP TABLE IF EXISTS %s;", (AsIs(tablename), ))
def get_all_nyc_growth_scores(params):
    conn = connection
    cur = conn.cursor()
    cur.execute(
        """
        select z.rank, z.count, z.percentile, z.per_proficient, 
        z.dbn, z.school_name, z.subject, z.grade, 
        %(calculation)s as growth,
        z.total_tested, z.total_mean,
        z.num_level_1, z.per_level_1,
        z.num_level_2, z.per_level_2,
        z.num_level_3, z.per_level_3,
        z.num_level_4, z.per_level_4

        FROM (
            select (count - rank)/count::numeric * 100 as percentile, *
            FROM (
                select
                rank() OVER (PARTITION BY nys.year, nys.subject ORDER BY nys.per_proficient desc),
                count(*) OVER (PARTITION BY nys.year, nys.subject), nys.*, b.sum

                FROM (
                    select distinct this.id, this.year, this.dbn, this.school_name, 
                    this.subject, this.grade, this.total_tested, this.total_mean, 
                    this.num_level_1, this.per_level_1,
                    this.num_level_2, this.per_level_2,
                    this.num_level_3, this.per_level_3,
                    this.num_level_4, this.per_level_4,
                    this.num_proficient,
                    this.per_proficient - that.per_proficient as per_proficient

                    FROM city_data.nys_test_results_all_nyc_schools as this

                    JOIN (
                        select *
                        from city_data.nys_test_results_all_nyc_schools
                        where year = %(academic_year)s - 1
                    ) as that
                        on that.dbn = this.dbn
                        and that.subject = this.subject
                        and that.grade = this.grade
                    where this.year = %(academic_year)s
                ) as nys

                JOIN (
                    select dbn, sum(grade::numeric)
                    from city_data.nys_test_results_all_nyc_schools
                    where (grade != 'All Grades' and grade is not null)
                    and year = %(academic_year)s
                    group by dbn
                ) as b 
                    on b.dbn = nys.dbn

                where b.sum >= %(three_eight_cohort)s
                and nys.year = %(academic_year)s
                and nys.grade = %(grade_level)s
                and substring(nys.dbn, 1,2) = '84'
            ) as a
            order by a.subject, a.rank
        ) as z

        """, {
            'academic_year': params['academic_year'],
            'grade_level': params['grade_level'],
            'three_eight_cohort': params['three_eight_cohort'],
            'calculation': AsIs(params['calculation'])
        })

    rows = dictfetchall(cur)
    cur.close()
    return rows
Example #17
0
    def get_variable_data_from_intersection_table(self, var):

        query = """SELECT %s FROM intersection_table_birth;"""

        return (self.c.db.get_regular_cursor_query(query, (AsIs(var), )))
Example #18
0
def remove_from_time_series(time_series_table, file_name):
    curs = conn.cursor()
    query = "DELETE FROM %(table)s WHERE location = %(filename)s;"
    data = {"table": AsIs(time_series_table), "filename": file_name}
    curs.execute(query, data)
    conn.commit()
Example #19
0
def bootstrap_4_migration(env):
    """Convert customized website views to Bootstrap 4 and multiwebsite.

    This process is a bit complex, so here's the big picture:

    In v11, there's no upstream support for multi-websites, but databases
    can actually have several websites and some records (pages, menus, views)
    get multi-website features behind the scenes, so we have to treat them
    like semi-supported. Usually it follows this logic:

    - Website-specific records only appear in their website.
    - Website-agnostic records appear in all websites.
    - View changes affect all websites.

    In v12, there's full multi-website support, usually following this logic:

    - Website-specific records override website-agnostic ones.
    - View changes create a website-specific copy (Copy-On-Write, COW) always.

    This migration tries to make v11 changes look like they were done in v12:

    - Unmodified views (noupdate=0) are not touched.
    - Website-agnostic modified (noupdate=1) views are copied and
      marked as noupdate=0. Those copied views are made website-specific
      and migrated to Bootstrap 4.

    This must be done in the "pre" stage, because the "mid" stage could
    update or even delete missing views now that they are marked as
    noupdate=0, but that's exactly what you want because when you create a
    new website with the UI in v12, that website will be "virgin",
    including only the raw views that come directly from the modules,
    without any modifications made for other websites.
    """
    # Preserve old dummy images that have changed
    image_replacements = _preserve_v11_dummy_images(env)
    # Create a column to remember where do new views come from
    oldview_id_col = openupgrade.get_legacy_name("bs4_migrated_from")
    table_name = env["ir.ui.view"]._table
    openupgrade.logged_query(
        env.cr,
        "ALTER TABLE %s ADD COLUMN %s INTEGER",
        (AsIs(table_name), AsIs(oldview_id_col)),
    )
    # Create another column to store migration metadata
    old_metadata_col = openupgrade.get_legacy_name("bs4_migration_metadata")
    openupgrade.logged_query(
        env.cr,
        "ALTER TABLE %s ADD COLUMN %s TEXT",
        (AsIs(table_name), AsIs(old_metadata_col)),
    )
    # Find report views, which should never be converted here
    report_names = env["ir.actions.report"].search([
        ("report_name", "!=", False),
        ("report_type", "=like", "qweb-%"),
    ]).mapped("report_name")
    # Find updatable views, to be excluded; standard addon update is enough
    udpatable_ids = env["ir.model.data"].search([
        ("model", "=", "ir.ui.view"),
        ("noupdate", "=", False),
    ]).mapped("res_id")
    # In Odoo v11, a page can be related to multiple websites, and the related
    # view doesn't need to be related to a website (although it could be);
    # in v12, a page is related to a view, which is related (or not) to a sigle
    # website. Usually it would be impossible to link a page to multiple
    # websites using UI in v11, so it's not supported
    env.cr.execute(
        """SELECT r.website_id, p.view_id
           FROM website_website_page_rel AS r
           INNER JOIN website_page AS p ON r.website_page_id = p.id
           INNER JOIN ir_ui_view AS v ON p.view_id = v.id
           WHERE v.website_id IS NULL
           ORDER BY r.website_id DESC""", )
    for page_website_id, view_id in env.cr.fetchall():
        # If a page happens to be in different websites, only the first one
        # will be stored in the view; the others will be lost, which isn't a
        # problem since v11 didn't support multi-websites
        openupgrade.logged_query(
            env.cr,
            "UPDATE ir_ui_view SET website_id = %s WHERE id = %s",
            (page_website_id, view_id),
        )
    # Find views to convert
    all_views = env['ir.ui.view'].with_context(active_test=False).search([
        ("id", "not in", udpatable_ids),
        ("key", "not in", report_names),
        ("type", "=", "qweb"),
    ])
    # Find available websites
    env.cr.execute("SELECT id FROM website")
    website_ids = list(chain.from_iterable(env.cr.fetchall()))
    # Convert in place views that already have a website
    env.cr.execute(
        "SELECT id FROM %s WHERE id IN %s AND website_id IS NOT NULL",
        (AsIs(table_name), tuple(all_views.ids)),
    )
    views_with_website = env["ir.ui.view"].browse(
        list(chain.from_iterable(env.cr.fetchall())),
        prefetch=all_views._prefetch,
    )
    for view in views_with_website:
        new_arch = convert_string_bootstrap_3to4(view.arch_db)
        new_arch = _apply_v11_dummy_image_replacement(
            image_replacements,
            new_arch,
        )
        view.arch_db = new_arch
    # Convert a website-specific copy of the view for the rest
    views_without_website = all_views - views_with_website
    for website_id, oldview in product(website_ids, views_without_website):
        # Skip if the view is a page and already has a website-specific copy
        env.cr.execute(
            """SELECT v.id
               FROM ir_ui_view AS v
               INNER JOIN website_page AS p
               ON p.view_id = v.id
               WHERE v.website_id = %s AND p.url IN (
                    SELECT url
                    FROM website_page
                    WHERE view_id = %s
               )""",
            (website_id, oldview.id),
        )
        if env.cr.fetchall():
            continue
        # Skip if a website-specific copy already exists
        env.cr.execute(
            "SELECT id FROM %s WHERE key = %s AND website_id = %s",
            (AsIs(table_name), oldview.key, website_id),
        )
        if env.cr.fetchall():
            continue
        # Create the copy and convert it, otherwise
        new_arch = convert_string_bootstrap_3to4(oldview.arch_db)
        new_arch = _apply_v11_dummy_image_replacement(
            image_replacements,
            new_arch,
        )
        newview = oldview.copy({
            "arch_db": new_arch,
            "key": oldview.key,  # Avoid automatic deduplication
        })
        # Website-specific copy of the related website.page record
        openupgrade.logged_query(
            env.cr,
            """INSERT INTO website_page (
                    create_date, create_uid, write_date, write_uid,
                    date_publish, is_published, url, website_indexed, view_id)
               SELECT
                    create_date, create_uid, write_date, write_uid,
                    date_publish, is_published, url, website_indexed, %(new)s
               FROM website_page
               WHERE view_id = %(old)s""",
            {
                "new": newview.id,
                "old": oldview.id
            },
        )
        # Obtain related website.menu details
        env.cr.execute(
            """SELECT
                    wm.create_date, wm.create_uid, wm.write_date, wm.write_uid,
                    wm.name, wm.url, wm.new_window, wm.sequence, wm.parent_id
               FROM website_menu AS wm
               INNER JOIN website_page AS wp ON wm.page_id = wp.id
               INNER JOIN ir_ui_view AS v ON wp.view_id = v.id
               WHERE view_id = %s AND wm.website_id IS NULL""", (oldview.id, ))
        menus = []
        # Use only JSON-serializable types
        for menu in env.cr.dictfetchall():
            for key in menu:
                if not isinstance(menu[key],
                                  (str, int, float, bool, type(None))):
                    menu[key] = str(menu[key])
            menus.append(menu)
        # Store needed info in the migration columns
        openupgrade.logged_query(
            env.cr,
            "UPDATE %s SET %s = %s, %s = %s, website_id = %s WHERE id = %s", (
                AsIs(table_name),
                AsIs(oldview_id_col),
                oldview.id,
                AsIs(old_metadata_col),
                json.dumps({
                    "menus": menus,
                }),
                website_id,
                newview.id,
            ))
    # Inherit translated SEO metadata from original views
    openupgrade.copy_fields_multilang(
        env.cr,
        "ir.ui.view",
        env["ir.ui.view"]._table,
        [
            "website_meta_title", "website_meta_description",
            "website_meta_keywords"
        ],
        oldview_id_col,
    )
    # Inherit arch translations from parent views
    openupgrade.copy_fields_multilang(
        env.cr,
        "ir.ui.view",
        env["ir.ui.view"]._table,
        ["arch_db"],
        oldview_id_col,
        translations_only=True,
    )
    # Set website-agnostic views as updatable
    model_data = env["ir.model.data"].search([
        ("model", "=", "ir.ui.view"),
        ("res_id", "in", views_without_website.ids),
    ])
    model_data.write({"noupdate": False})
Example #20
0
def remove_from_daily_six_anomaly(table_name, date_string, phenophase):
    curs = conn.cursor()
    query = "DELETE FROM %(table)s WHERE rast_date = to_date(%(rast_date)s, 'YYYYMMDD') AND phenophase = %(phenophase)s;"
    data = {"table": AsIs(table_name), "rast_date": date_string, "phenophase": phenophase}
    curs.execute(query, data)
    conn.commit()
Example #21
0
 def adapt_money_basket(b):
     return AsIs(
         "_wrap_amounts('%s'::jsonb)" %
         json.dumps({k: str(v) for k, v in b.amounts.items() if v}).replace("'", "''")
     )
Example #22
0
def get_raster_info(table_name, date):
    query = "SELECT ST_AsGDALRaster(ST_Union(rast), 'Gtiff') FROM %s WHERE rast_date = %s;"
    data = (AsIs(table_name), date.strftime("%Y-%m-%d"))

    return get_raster_info_from_query(query, data)
Example #23
0
def adapt_point(point):
    return AsIs("'(%s, %s)'::point" % (adapt(point.x), adapt(point.y)))
Example #24
0
def get_raster_array(table_name, column_name, value):

    query = "SELECT ST_AsGDALRaster(ST_Union(rast), 'Gtiff') FROM %s WHERE %s = %s;"
    data = (AsIs(table_name), AsIs(column_name), value)
    return get_raster_from_query(query, data)
Example #25
0
def addapt_numpy_float64(numpy_float64):
    return AsIs(numpy_float64)
Example #26
0
def remove_from_table_by_filename(raster_path, table_name):
    curs = conn.cursor()
    query = "DELETE FROM %(table)s WHERE filename = %(filename)s;"
    data = {"table": AsIs(table_name), "filename": os.path.basename(raster_path)}
    curs.execute(query, data)
    conn.commit()
def dict_insert_string(table, _dict):
    columns = _dict.keys()
    values = [_dict[column] for column in columns]
    return 'insert into %s (%s) values %s' % (table, AsIs(
        ','.join(columns)), tuple(values))
Example #28
0
def sync_menu_views_pages_websites(env):
    # Main menu and children must be website-agnostic
    main_menu = env.ref('website.main_menu')
    child_menus = env["website.menu"].search([
        ("id", "child_of", main_menu.ids),
        ("website_id", "!=", False),
    ])
    child_menus.write({"website_id": False})
    # Duplicate the main menu for all websites
    for website in env["website"].search([]):
        website.copy_menu_hierarchy(main_menu)
    # Find views that were website-specified in pre stage
    old_metadata_col = openupgrade.get_legacy_name("bs4_migration_metadata")
    env.cr.execute("SELECT %s, id FROM %s WHERE %s IS NOT NULL", (
        AsIs(old_metadata_col),
        AsIs(env["ir.ui.view"]._table),
        AsIs(old_metadata_col),
    ))
    for data, specific_view_id in env.cr.fetchall():
        data = json.loads(data)
        specific_view = env["ir.ui.view"].browse(specific_view_id)
        for page in specific_view.page_ids:
            menus = env["website.menu"].search([
                ("id", "child_of", page.website_id.menu_id.ids),
                ("url", "=", page.url),
            ])
            # If menus exist, it means the agnostic view wasn't removed and
            # they already contain all the needed information, except that they
            # are linked to the website-agnostic page. Let's fix that.
            if menus:
                menus.write({
                    "page_id": page.id,
                })
            # In case the menus disappeared, it's possibly because the
            # website-agnostic view was removed during the normal module update
            # and the cascade FK removed the pages and menus. In such
            # case, let's recreate them.
            else:
                for menu in data["menus"]:
                    if menu["url"] != page.url:
                        continue
                    # Find the new website-specific parent menu
                    agnostic_parent = \
                        env["website.menu"].browse(menu["parent_id"])
                    specific_parent = env["website.menu"].search([
                        ("id", "child_of", page.website_id.menu_id.ids),
                        ("name", "=", agnostic_parent.name),
                        ("url", "=", agnostic_parent.url), "|",
                        ("parent_id", "=", page.website_id.menu_id.id), "&",
                        ("parent_id.name", "=",
                         agnostic_parent.parent_id.name),
                        ("parent_id.url", "=", agnostic_parent.parent_id.url)
                    ]) or page.website_id.menu_id
                    # Re-create the menu
                    menus.create(
                        dict(
                            menu,
                            page_id=page.id,
                            parent_id=specific_parent.id,
                            website_id=page.website_id.id,
                        ))
Example #29
0
 def get_current_sensor_data(self, sid):
     self.curr.execute(
         "SELECT value FROM %(data_table_str)s ORDER BY event_time DESC LIMIT 1;",
         {'data_table_str': AsIs(sid)})
     return self.curr.fetchone()
Example #30
0
def addapt_numpy_int64(numpy_int64):
    return AsIs(numpy_int64)