Python DB.query方法代碼示例

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
	def updateBlacklist(self, regex):
		mdb = DB()

		groupname = regex['groupname']
		if groupname == '':
			groupname = 'null'
		else:
			groupname = re.sub('a\.b\.' 'alt.binaries.', groupname, re.IGNORECASE)
			groupname = mdb.escapeString(groupname)

		mdb.query("update binaryblacklist set groupname=%s, regex=%s, status=%d, description=%s, optype=%d, msgcol=%d where ID = %d ",
			(groupname, regex['regex'], regex['description'], regex['optype'], regex['msgcol'], regex['id']))

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
	def search(self, search, limit=1000, excludedcats={}):
		mdb = DB()

		# if the query starts with a ^ it indicates the search is looking for items which start with the term
		# still do the like match, but mandate that all items returned must start with the provided word

		words = search.split(' ')
		searchsql = ''
		intwordcount = 0
		if len(words) > 0:
			for word in words:
				# see if the first word has a caret, which indicates search must start with term
				if intwordcount == 0 and word[0] == '^':
					searchsql += ' and b.name like %s%' % (mdb.escapeString(word[1:]))
				else:
					searchsql += ' and b.name like %s' % (mdb.escapeString('%'+word+'%'))

				intwordcount += 1

		exccatlist = ''
		if len(excludedcats) > 0:
			exccatlist = 'and b.categoryID not in ('+','.join(excludedcats)+') '

		res = mdb.query('''SELECT b.*, g.name AS group_name, r.guid,
					(SELECT COUNT(ID) FROM parts p where p.binaryID = b.ID) as 'binnum'
					FROM binaries b
					INNER JOIN groups g ON g.ID = b.groupID
					LEFT OUTER JOIN releases r ON r.ID = b.releaseID
					WHERE 1=1 %s %s order by DATE DESC LIMIT %d ''', (searchsql, exccatlist, limit))

		return res

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
class DBPYReader(object):
    '''Uses db.py as a generic interface to a database'''

    required_config = ['DB_USER', 'DB_PWD', 'DB_HOST', 'DB_NAME']

    def __init__(self, config, module, custom_settings=None, db=None):

        for var in self.required_config:
            if var not in config:
                raise ValueError("missing config var: %s" % var)

        self.config = config
        reader_settings = {'username':self.config.get('DB_USER'),
                           'password':self.config.get('DB_PWD'),
                           'hostname':self.config.get('DB_HOST'),
                           'dbname':self.config.get('DB_NAME'),
                           'dbtype':'redshift',
                           'schemas':['']
                          }
        if custom_settings:
            reader_settings.update(custom_settings)
            
        if db:
            reader_settings['dbtype'] = db 

        self.module = module
        print('INIT DB.PY READER FOR MODULE {}'.format(module))
        self.db =  DB(**reader_settings)

    def read(self, query):
        df = self.db.query(query)
        return df

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
class PostgresReader(object):
    '''Write to a postgres database'''

    required_config = ['DB_USER', 'DB_PWD', 'DB_HOST', 'DB_NAME', ]

    def __init__(self, config, module, custom_settings=None):

        for var in self.required_config:
            if var not in config:
                raise ValueError("missing config var: %s" % var)

        self.config = config

        reader_settings = {'username': self.config.get('DB_USER'),
                               'password': self.config.get('DB_PWD'),
                               'hostname': self.config.get('DB_HOST'),
                               'dbname': self.config.get('DB_NAME'),
                               'dbtype':'redshift',
                               'schemas':[''],
                               }
        if custom_settings:
            reader_settings.update(custom_settings)

        self.module = module
        print('INIT POSTGRES READER FOR MODULE {}'.format(module))
        self.db =  DB(**reader_settings)

    def read(self, query):
        df = self.db.query(query)
        return df

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
def list_recent_fetches(interval_min, dbi = None):
  """輸出最近抓取的的新聞列表，以計算 revisit 對象

  i_created_on = 0
  i_last_seen_on = 1
  i_pub_ts = 2
  i_feed_url = 3
  i_canonical_url = 4
  i_title = 5
  i_meta = 6
  i_ctlr = 7

  @see: lib.proc.do_revisit()
  """
  from lib import conf, Ctlr_Base

  sql = "SELECT " \
    "a.`created_on`, a.`last_seen_on`, a.`pub_ts`, f.`url`, au.`body`, a.`title`, am.`body`, ( "\
    "  SELECT `c`.`classname` FROM `ctlrs` c NATURAL JOIN `ctlr_feed` `cf` " \
    "  WHERE `cf`.`feed_id` = `a`.`feed_id` ORDER BY `c`.`created_on` DESC LIMIT 1 " \
    ") `classname` " \
    "FROM `articles` a " \
    "LEFT JOIN `feeds` f on f.`feed_id` = a.`feed_id` " \
    "LEFT JOIN `article__urls` au on au.`hash` = a.`url_canonical_hash` " \
    "LEFT JOIN `article__meta` am on am.`hash` = a.`meta_hash` " \
    'WHERE `created_on` > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL %d MINUTE)' % \
    interval_min

  return DB.query(sql, dbi = dbi)

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
	def delete(self, id):
		mdb = DB()
		bins = mdb.query('SELECT ID FROM binaries WHERE collectionID = %d', (id,))
		for bin in bins:
			mdb.query('delete from parts where binaryID = %d', (bin['ID']))
		mdb.query('delete from binaries where collectionID = %d', (id,))
		mdb.query('delete from collections where ID = %d', (id,))

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
    def on_button_pressed(self):
        # Connect to the sqlite database using DB.py
        db = DB(filename="/home/pybokeh/Dropbox/data_sets/nba", dbtype="sqlite")

        sql = """
        select *

        from player_game_stats

        where
        team_name like '{{ name }}';"""

        # Get the text the user entered into the textinput widget
        token = self.txt.get_text()

        # To prevent sql injection attacks, parameterize the sql string
        parameter = '%' + token + '%'

        params = [
                  {"name": parameter}
                 ]

        # Execute the query and store the results into a pandas dataframe
        df = db.query(sql, data=params)

        # Get the column names of the query result and the query row_data
        column_names = df.columns
        row_data = df.values

        # Create the table widget with the specified dimensions
        self.table = gui.Table(1200, 800)
       
        # Generate the table row containing the table column names
        row = gui.TableRow()
        for column in column_names:
            item = gui.TableTitle()  # TableTitle refers to the column header/names
            item.append(str(id(item)), str(column))
            row.append(str(id(item)), item)

        # Now add the row to the table
        self.table.append(str(id(row)), row)

        # Generate rows that will contain the table data
        for _row in row_data:
            row = gui.TableRow()
            for row_item in _row:
                item = gui.TableItem() # TableItem refers to the table data
                item.append(str(id(item)), str(row_item))
                row.append(str(id(item)), item)

                self.table.append(str(id(row)), row)

        # Now render / add the bottom container to the master container
        self.masterContainer.append('2', self.bottomContainer)

        # Now add the table widget to the bottom container        
        self.bottomContainer.append('1', self.table)

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
	def partRepair(self, nntp, groupArr):
		n = self.n

		mdb = DB()
		missingParts = mdb.query("SELECT * FROM partrepair WHERE groupID = %s AND attempts < 5 ORDER BY numberID ASC LIMIT %s", (groupArr['ID'], self.partrepairlimit))
		partsRepaired = partsFailed = 0

		if len(missingParts) > 0:
			print 'Attempting to repair %d parts.' % len(missingParts)

			# loop through each part to group into ranges
			ranges = dict()
			lastnum = lastpart = 0
			for part in missingParts:
				if lastnum+1 == part['numberID']:
					ranges[lastpart] = part['numberID']
				else:
					lastpart = part['numberID']
					ranges[lastpart] = part['numberID']
				lastnum = part['numberID']

			num_attempted = 0

			# download missing parts in ranges.
			for partfrom, partto in ranges.iteritems():
				self.startLoop = time.time()

				num_attempted += partto - partfrom + 1
				# print some output here

				# get article from newsgroup
				nntp.group(groupArr['name'])
				self.scan(nntp, groupArr, partfrom, partto, 'partrepair')

				# check if articles were added
				if partfrom == partto:
					articles = partfrom
				else:
					articles = ','.join("%d" % i for i in range(partfrom, partto))
	
				sql = "SELECT pr.ID, pr.numberID, p.number from partrepair pr LEFT JOIN parts p ON p.number = pr.numberID WHERE pr.groupID=%s AND pr.numberID IN (%s) ORDER BY pr.numberID ASC"

				result = mdb.queryDirect(sql, (groupArr['ID'], articles))
				for r in result:
					if r['number'] == r['numberID']:
						partsRepaired += 1
						# article was added, delete from partrepair
						mdb.query('DELETE FROM partrepair WHERE ID=%s', (r['ID'],))
					else:
						partsFailed += 1
						# article was not added, increment attempts:
						mdb.query('UPDATE partrepair SET attempts=attempts+1 WHERE ID = %s', (r['ID'],))

			print '%d parts repaired.' % (partsRepaired)

		# remove articles that we can't fetch after 5 attempts
		mdb.query('DELETE FROM partrepair WHERE attempts >= 5 AND groupID = %s', (groupArr['ID'],))
		mdb.commit()

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
def count_num_processed_only_filtered2_False(account_id):
    database = DB(profile='data_warehouse')
    q = database.query(
    """
    SELECT count(*)
    FROM (SELECT max(id) as max_id FROM kim.weights_outlier GROUP BY weight_id) max_ids
    JOIN kim.weights_outlier wo on max_ids.max_id = wo.id
    WHERE account_id = {}
    AND filtered2 = False;
    """.format(account_id))
    return q.values[0][0]

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
	def getBlacklist(self, activeonly=True):
		mdb = DB()
		where = ''

		if activeonly:
			where = ' where binaryblacklist.status = 1 '
		else:
			where = ''

		return mdb.query('SELECT binaryblacklist.ID, binaryblacklist.optype, binaryblacklist.status, binaryblacklist.description, binaryblacklist.groupname AS groupname, \
			binaryblacklist.regex, groups.ID AS groupID, binaryblacklist.msgcol FROM binaryblacklist \
			left outer JOIN groups ON groups.name = binaryblacklist.groupname'+where+'ORDER BY coalesce(groupname, "zzz")')

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
def get_reported_weight(account_id):
    database = DB(profile='kairos')
    try:
        q = database.query(
        """
        SELECT rd.weight  AS risk_data_weight
          FROM registration_program_applications rpa
          JOIN risk_data rd
            ON rpa.risk_data_id = rd.id
          JOIN risk_assessments ra
            ON rd.risk_assessment_id = ra.id
         WHERE rpa.account_id is not NULL
           AND rpa.account_id = {}
        """.format(account_id))
        return q.values[0][0]
    except IndexError:
        return 0

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
def get_fresh_urls(urls, dbi = None):
  """篩選出 urls 中未曾成功抓取過者並回傳"""

  from lib.util.text import md5
  from lib.util.net import normalize_url

  if (len(urls) == 0): return set()

  url_md5 = [{'url': x, 'md5': md5(normalize_url(x))} for x in urls]
  hashes = "(" + (",".join(["UNHEX('%s')" % x['md5'] for x in url_md5 ])) + ")"
  sql = "SELECT HEX(`hash`) FROM `article__urls` WHERE `hash` IN %s" % hashes

  ret = set(DB.query(sql, dbi = dbi))

  output = []
  for x in url_md5:
    if not (x['md5'].upper(),) in ret:
      output.append(x['url'])

  return output

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
class DBPYReader(object):
    '''Uses db.py as a generic interface to a database'''

    def __init__(self, module, custom_settings=None):
        reader_settings = {'username':DB_USER,
                               'password':DB_PWD,
                               'hostname':DB_HOST,
                               'dbname':DB_NAME,
                               'dbtype':'redshift',
                               'schemas':['']
                               }
        if custom_settings:
            reader_settings.update(custom_settings)

        self.module = module
        print('INIT DB.PY READER FOR MODULE {}'.format(module))
        self.db =  DB(**reader_settings)

    def read(self, query):
        df = self.db.query(query)
        return df

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
def get_latest_rows_and_filtered2_false(account_id, window = 10):
    database = DB(profile='data_warehouse')
    # database = DB(profile='kairos')
    q = database.query(
    """
    SELECT id, weight_id, account_id, weighed_at, value, confirmed, manual, filtered, filtered2, value_prediction_w16, feature_col ,processed, model_version
    FROM (SELECT max(id) as max_id from kim.weights_outlier GROUP BY weight_id) max_ids
      JOIN kim.weights_outlier wo on max_ids.max_id = wo.id
    WHERE id BETWEEN
        (
        SELECT min(lastX.id)
        FROM (
          SELECT id
           FROM (SELECT max(id) as max_id from kim.weights_outlier GROUP BY weight_id) max_ids
             JOIN kim.weights_outlier wo on max_ids.max_id = wo.id
          WHERE account_id = {0}
                and filtered2 = False
          ORDER BY id DESC
          limit {1}
             ) lastX
        )
    AND
        (
        SELECT max(lastX.id)
        FROM (
          SELECT id
           FROM (SELECT max(id) as max_id from kim.weights_outlier GROUP BY weight_id) max_ids
             JOIN kim.weights_outlier wo on max_ids.max_id = wo.id
          WHERE account_id = {0}
                and filtered2 = False
          ORDER BY id DESC
          limit {1}
             ) lastX
        )
    AND
        account_id = {0}
    ORDER BY id ASC;
    """.format(account_id,window)
    )
    return q

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import query [as 別名]
def _update_ctlr_feed_cache(dbi = None):
  cache = _cache_ctlr_feed
  sql = 'SELECT `f`.`url`, `c`.`classname` FROM ' \
  '`ctlr_feed` `cf` NATURAL JOIN `feeds` `f` NATURAL JOIN `ctlrs` `c`'
  cache.update(DB.query(sql, dbi = dbi))