Python DB.execute方法代碼示例

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
class Cacher(object):
    u"""Система кеширования запросов"""

    # Соединение с БД
    __conn          =   {'connection':None, 'cursor':None}
    _log            = None
    _db             = None
    # Счетчик выполняющихся в данный момент запросов
    __query         = 0

    def __init__(self):
        self._log = Logger()
        self._db = DB()

    def getStations(self, stationName):
        u"""Возвращает станции начинающиеся со строки"""
        self._db.execute('''SELECT * FROM `Stations` WHERE `Name` LIKE %s ORDER BY `S` DESC LIMIT 100''',
            ("%" + stationName + "%"))
        return self._db.getFetchAll(True)

    def setStations(self, stations):
        u'''Сохранение станций в кеше'''
        data = []
        for station in stations:
            data.append('("%s","%s","%s","%s")' % (station['c'], station['n'], station['S'], station['L']))
        self._db.execute('''INSERT IGNORE INTO `Stations` (`Code`, `Name`, `S`, `L`) VALUES %s''' % ','.join(data))
        self._db.getConnDB().commit()

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
class FileManager(object):

    def __init__(self):
        self.db = DB()

    def add_files(self, files):
        #  sql = 'insert into songs (name, path) values '
        sql = 'insert into songs (name, path, title, album, artist, genre, track, date) values'
        for file in files:
            info = cp_get_metadata_py(file.decode('utf-8').encode('utf-8'))
            file_name = file.rsplit('/')[-1]
            info['name'] = file_name
            info['path'] = file
            sql += '("{name}", "{path}", "{title}", "{album}", "{artist}", "{genre}", "{track}", "{date}"),'.format(**info)
        sql = sql[:-1]
        self.db.execute(sql)

    def del_files(self, ids):
        """
        require: ids: list, tuple, set
        """
        if not ids:
            return
        query = str(tuple(ids))
        if len(ids)<2:
            query = query.replace(',', '')
        sql = 'delete from songs where id in {0}'.format(query)
        self.db.execute(sql)

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
def get_ugc(county, state):
    '''
    Return the UGC code(s) for the given county/state.

    For more info on UGC format, see: http://www.nws.noaa.gov/emwin/winugc.htm
    '''
    try:
        db = DB()
        db.execute("SELECT state,zone FROM ugc WHERE countyname = '%s' AND state = '%s';" % (county, state))
        ugc = []
        if db.cur is not None:
            for record in db.cur:
                code = record[0] + 'Z' + record[1]
                ugc.append(code)
        db.commit()
    except Exception as e:
        print e
        db.rollback()

    if len(ugc) > 0:
        print "UGC(s) found for %s, %s:" % (county, state)
        ugc = ','.join(ugc)
    else:
        print "UGC(s) not found for %s, %s" % (county, state)
        ugc = None

    return ugc

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
    def save(self):
        try:
            db = DB()
        except:
            return False

        for day, games in self.iteritems():
            for game in games:
                
                game['home_team_id'] = game['home']['id']
                game['home_team_full'] = game['home']['full']
                game['home_team_display_code'] = game['home']['display_code']
                game['home_probable_id'] = game['home']['probable_id']
                game['away_team_id'] = game['away']['id']
                game['away_team_full'] = game['away']['full']
                game['away_team_display_code'] = game['away']['display_code']
                game['away_probable_id'] = game['away']['probable_id']
                game['game_date'] = game['game_id'][0:10].replace('/', '-')
                
                if game['game_status'] == 'F':
                    game['home_score'] = game['home']['result']
                    game['away_score'] = game['away']['result']
                else:
                    game['home_score'] = None
                    game['away_score'] = None

                del(game['home'])
                del(game['away'])
                del(game['pitcher'])
                
                sql = 'REPLACE INTO schedule (%s) VALUES (%s)' % (','.join(game.keys()), ','.join(['%s'] * len(game.values())))
                db.execute(sql, game.values())
            
        db.close()

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
def save_fetch(url, src, category = 'unknown', dbi = None):
  """寫入 fetches 表; 不做 unique key 檢查，僅就 category 判斷是否儲存"""

  if (category in fetch_categories_ignored): return

  sql = "INSERT INTO `fetches` (`url`, `src`, `category`) VALUES" \
    "(%(url)s, %(src)s, %(category)s)"

  DB.execute(sql, {"url": url, "src": src, "category": category}, dbi = dbi)

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
def save_host(host, dbi = None):
  cache = _cache_host_urls
  _update_host_cache(dbi = dbi)
  if (host['url'] in cache): return

  sql = "INSERT IGNORE INTO `hosts` (`name`, `url`) " \
    "VALUES (%(name)s, %(url)s)"

  DB.execute(sql, host, dbi = dbi)
  cache.add(host['url'])

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
def save_ctlr(ctlr, dbi = None):
  cache = _cache_ctlr_classnames
  _update_ctlr_cache(dbi = dbi)
  if (ctlr['classname'] in cache): return

  sql = "INSERT IGNORE INTO `ctlrs` (`classname`, `created_on`" \
    ") VALUES (%(classname)s, %(created_on)s)"

  DB.execute(sql, ctlr, dbi = dbi)
  cache.add(ctlr['classname'])

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
def save_article(payload, dbi = None):
  """更新新聞內容"""
  from json import dumps
  from datetime import datetime
  from copy import deepcopy

  from lib.util.text import md5

  # deep copy so that we don't mess up the original payload
  _payload = deepcopy(payload)
  _payload["meta"] = dumps(payload['meta'])
  _payload["pub_ts"] = datetime.fromtimestamp(payload["pub_ts"]).isoformat()

  # hashtbl : html, text, meta
  _payload['html_md5'] = md5(_payload['html'])
  _payload['text_md5'] = md5(_payload['text'])
  _payload['meta_md5'] = md5(_payload['meta'])
  _payload['src_md5'] = md5(_payload['src'])


  _save_hashtbl('article__htmls', {
    'md5': _payload['html_md5'], 'body':_payload['html']}, dbi = dbi)
  _save_hashtbl('article__texts', {
    'md5': _payload['text_md5'], 'body':_payload['text']}, dbi = dbi)
  _save_hashtbl('article__meta', {
    'md5': _payload['meta_md5'], 'body': _payload['meta']}, dbi = dbi)
  _save_hashtbl('article__srcs', {
    'md5': _payload['src_md5'], 'body': _payload['src']}, dbi = dbi)

  # hashtbl: url
  _payload['url_md5'] = md5(_payload['url'])
  _payload['url_read_md5'] = md5(_payload['url_read'])
  _payload['url_canonical_md5'] = md5(_payload['url_canonical'])

  _save_hashtbl('article__urls', [
    {'md5': _payload['url_md5'], 'body': _payload['url']},
    {'md5': _payload['url_read_md5'], 'body': _payload['url_read']},
    {'md5': _payload['url_canonical_md5'], 'body': _payload['url_canonical']},
  ], dbi = dbi);

  # do the insert
  sql = "INSERT INTO `articles` (" \
      "`title`, `pub_ts`, `created_on`, " \
      "`feed_id`, `ctlr_id`, " \
      "`url_hash`, `url_read_hash`, `url_canonical_hash`, " \
      "`meta_hash`, `html_hash`, `text_hash`, `src_hash`" \
    ") VALUES(" \
      "%(title)s, %(pub_ts)s, CURRENT_TIMESTAMP, " \
      "(SELECT `feed_id` FROM `feeds` WHERE `url` = %(feed_url)s), " \
      "(SELECT `ctlr_id` FROM `ctlrs` WHERE `classname` = %(ctlr_classname)s), " \
      "UNHEX(%(url_md5)s), UNHEX(%(url_read_md5)s), UNHEX(%(url_canonical_md5)s)," \
      "UNHEX(%(meta_md5)s), UNHEX(%(html_md5)s), UNHEX(%(text_md5)s), UNHEX(%(src_md5)s)" \
    ") ON DUPLICATE KEY UPDATE last_seen_on = CURRENT_TIMESTAMP"

  DB.execute(sql, _payload, dbi = dbi)

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
def save_feed(source, dbi = None):
  cache = _cache_feed_urls
  _update_feed_cache(dbi = dbi)
  if (source['url'] in cache): return

  sql = "INSERT IGNORE INTO `feeds` (`url`, `title`, `host_id`" \
    ") VALUES (%(url)s, %(title)s, " \
    "(SELECT `host_id` FROM `hosts` WHERE `url` = %(host_url)s))"

  DB.execute(sql, source, dbi = dbi)
  cache.add(source['url'])

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
class Task(object):
    u"""
    Класс управления заданиями
    Позволяет добавлять и удалять задания из очереди
    """
    _db             = None
    # Логи
    _log            = None
    # РЖД модуль
    _rzd            = None
    # Словарь имен вызванных логов для partial
    _logNamesPart   = {}

    def __init__(self):
        self._log = Logger()
        self._db = DB()
        self._rzd = RZD()

    def __getattr__(self, name):
        try:
            return self._logNamesPart[name]
        except KeyError:
            obj = getattr(self._log, name)
            if obj is not None:
                part = partial(obj, moduleName=self.__class__.__name__)
                self._logNamesPart[name] = part
                return part
            else:
                self._log.error('Попытка вызова несуществующего типа лога "%s"',
                    name, moduleName=self.__class__.__name__)

    def cnt(self):
        u'''
        Получение количества активных заданий в системе
        @return int
        '''
        self._db.execute('''SELECT COUNT(*) FROM `Tasks` WHERE `Complete`=%s''',
            IN_PROGRESS)
        return int(self._db.getFetchOne()[0])

    def add(self, src, dst, date, phone, type):
        u'''Добавление нового задания в очередь'''
        limit = config.getint('system', 'taskLimit')
        if limit > self.cnt():
            self._rzd.getTrains(src, dst, date)
            self._db.execute('''INSERT INTO `Tasks` SET `Phone`=%s, `Src`=%s,
                `Dst`=%s, `Date`=%s, `Type`=%s, `DateTimeCreate`=NOW(), `DateTimeCheck`=null, `Complete`=0''',
                phone, int(src), int(dst), date, type)
            self._db.getConnDB().commit()
            self.debug('Клиент %s добавил новую заявку', phone)
        else:
            self.error('Система не может принять %d-ю заявку', self.cnt())
            raise Exception('Достигнут лимит количества активных заданий в системе')

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
 def trade_opened(self,  trade_id):
     """ Called by the daemon to notify the strategy that the order it suggested has been placed.
     Return type: void
     Parameters:
         trade_id        string
     """
     self.open_trade_ids.append(trade_id)
     Log.write('"strategy.py" trade_opened(): strat {} opened trade ({})'
         .format(self.get_name(), trade_id)) 
     # Write to db - mainly for backup in event of power failure
     DB.execute('INSERT INTO open_trades_live (trade_id, strategy, \
         broker) values ("{}", "{}", "{}")'
         .format(trade_id, self.get_name(), Config.broker_name))

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
 def test_trade_opened(self):
     """
     TODO: check db too
     """
     #import pdb; pdb.set_trace()
     self._strat.open_trade_ids = []
     self._strat.trade_opened('1')
     self.assertEqual(self._strat.open_trade_ids, ['1'])
     self._strat.trade_opened('666')
     self.assertEqual(self._strat.open_trade_ids, ['1','666'])
     self._strat.trade_opened('[email protected]#$%^&*()_+=-/|')
     self.assertEqual(self._strat.open_trade_ids, ['1','666','[email protected]#$%^&*()_+=-/|'])
     DB.execute("DELETE FROM open_trades_live WHERE trade_id in ('1', '666', '[email protected]#$%^&*()_+=-/|')")

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
def save_ctlr_feed(pair, dbi = None):
  cache = _cache_ctlr_feed
  _update_ctlr_feed_cache()
  if (pair['url'], pair['classname']) in cache: return

  sql = "INSERT IGNORE INTO `ctlr_feed` (" \
      "`feed_id`, `ctlr_id`" \
    ") VALUES ("\
      "(SELECT `feed_id` FROM `feeds` WHERE `url` = %(url)s), " \
      "(SELECT `ctlr_id` FROM `ctlrs` WHERE `classname` = %(classname)s)" \
    ")"

  DB.execute(sql, pair, dbi = dbi)
  cache.add((pair['url'], pair['classname']))

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
def get_fips(county,state):
    '''
    Return the FIPS6 county code for the given county/state.
    '''
    fips = None
    try:
        db = DB()
        db.execute("SELECT fips FROM fips WHERE countyname = '%s' AND state = '%s';" % (county, state))
        fips = db.cur.fetchone()[0]
        db.commit()
        print "FIPS %s found for %s, %s" % (fips, county, state)
    except Exception as e:
        print "FIPS not found for %s, %s" % (county, state)
        print e
        db.rollback()
    return fips

# 需要導入模塊: from db import DB [as 別名]
# 或者: from db.DB import execute [as 別名]
class Grabber(object):
    _conf   = None
    _log    = None
    _db     = None

    def __init__(self, configPath):
        self._conf = Config(configPath)
        self._log = Logger()
        self._db = DB()
        self._log.info('Запущен discounter')

        modules = self._getGrabModules()
        self._log.info('Предстоит выполнить %d модулей', len(modules))
        self._runGrabModules(modules)

    def _runGrabModules(self, modules):
        """
        Запуск требуемых модулей
        @param: modules Кортеж модулей
        @type: tuple
        """
        for module in modules:
            self._log.debug('Обрабатываю модуль "%s"', module[1])
            # Импорт модуля
            mod_pack = __import__('modules.' + module[1])
            # например <module 'modules' from '/srv/www/Discounter/grabber/modules/__init__.pyc'>
            moduleIndex = dir(mod_pack).index(module[1])
            modName = getattr(mod_pack, dir(mod_pack)[moduleIndex])
            # Определение имени модуля (стоит последним)
            # например ['__builtins__', '__doc__', '__file__', '__name__', '__package__', '__path__', 'abstractmodule', 'infoskidka']
            className = modName.__name__.split('.')[-1].capitalize()
            # Капитализация названия и получение класса
            classObj = getattr(modName, className)
            if issubclass(classObj, AbstractModule):
                parser = classObj()
                try:
                    parser.parse()
                except (Exception,), e:
                    self._log.debug('trace:\n%s', '\n'.join(format_list(extract_tb(sys.exc_info()[2]))))
                    self._log.error('Ошибка работы обработчика %s:%s', classObj, e)
                else:
                    query = """UPDATE `resource` SET `successGrab`=%s WHERE `id`=%s"""
                    self._db.execute(query, strftime("%Y-%m-%d %H:%M:%S", gmtime()), module[0])
            else:
                self._log.error('Класс %s не является потомком %s !', classObj, AbstractModule)