Python dumptruck.DumpTruck类代码示例

 def setUp(self):
     self.cleanUp()
     h = DumpTruck(dbname=u"/tmp/test.db")
     h.save_var(u"birthday", u"November 30, 1888")
     h.close()
     connection = sqlite3.connect(u"/tmp/test.db")
     self.cursor = connection.cursor()

 def setUp(self):
   self.cleanUp()
   h = DumpTruck(dbname = u'/tmp/test.db')
   h.save_var(u'birthday', u'November 30, 1888')
   h.close()
   connection=sqlite3.connect(u'/tmp/test.db')
   self.cursor=connection.cursor()

def build_table_from_db(dbname = '/tmp/table_info.db'):
    dt = DumpTruck(dbname = dbname)
    tableIds = [row['tableId'] for row in dt.execute('''
SELECT tableId, count(*)
FROM table_info
GROUP BY tableId
ORDER BY count(*) DESC
limit 10;
''')]

    try:
        os.mkdir('geneology')
    except OSError:
        pass
    json.dump(tableIds, open(os.path.join('geneology', 'index.json'), 'w'))

    for tableId in tableIds:
        result = {
            'source': dt.execute('SELECT * FROM table_info WHERE tableId = ? ORDER BY createdAt ASC LIMIT 1', [tableId])[0],
            'datasets': {},
        }
        for dataset in dt.execute('SELECT * FROM table_info WHERE tableId = ?', [tableId]):
            if dataset['id'] not in result['datasets']:
                result['datasets'][dataset['id']] = dataset
                result['datasets'][dataset['id']]['portals'] = []

            result['datasets'][dataset['id']]['portals'].append(dataset['portal'])

        result['datasets'] = result['datasets'].values()
        for dataset in result['datasets']:
            del dataset['portal']
        json.dump(result, open(os.path.join('geneology', '%d.json' % tableId), 'w'))

 def test_select(self):
   shutil.copy(u'fixtures/landbank_branches.sqlite', u'.')
   h = DumpTruck(dbname = u'landbank_branches.sqlite')
   data_observed = h.execute(u'SELECT * FROM `branches` WHERE Fax is not null ORDER BY Fax LIMIT 3;')
   data_expected = [{'town': u'\r\nCenturion', 'date_scraped': 1327791915.618461, 'Fax': u' (012) 312 3647', 'Tel': u' (012) 686 0500', 'address_raw': u'\r\n420 Witch Hazel Ave\n\r\nEcopark\n\r\nCenturion\n\r\n0001\n (012) 686 0500\n (012) 312 3647', 'blockId': 14, 'street-address': None, 'postcode': u'\r\n0001', 'address': u'\r\n420 Witch Hazel Ave\n\r\nEcopark\n\r\nCenturion\n\r\n0001', 'branchName': u'Head Office'}, {'town': u'\r\nCenturion', 'date_scraped': 1327792245.787187, 'Fax': u' (012) 312 3647', 'Tel': u' (012) 686 0500', 'address_raw': u'\r\n420 Witch Hazel Ave\n\r\nEcopark\n\r\nCenturion\n\r\n0001\n (012) 686 0500\n (012) 312 3647', 'blockId': 14, 'street-address': u'\r\n420 Witch Hazel Ave\n\r\nEcopark', 'postcode': u'\r\n0001', 'address': u'\r\n420 Witch Hazel Ave\n\r\nEcopark\n\r\nCenturion\n\r\n0001', 'branchName': u'Head Office'}, {'town': u'\r\nMiddelburg', 'date_scraped': 1327791915.618461, 'Fax': u' (013) 282 6558', 'Tel': u' (013) 283 3500', 'address_raw': u'\r\n184 Jan van Riebeeck Street\n\r\nMiddelburg\n\r\n1050\n (013) 283 3500\n (013) 282 6558', 'blockId': 17, 'street-address': None, 'postcode': u'\r\n1050', 'address': u'\r\n184 Jan van Riebeeck Street\n\r\nMiddelburg\n\r\n1050', 'branchName': u'Middelburg'}]
   self.assertListEqual(data_observed, data_expected)
   os.remove('landbank_branches.sqlite')

def check_timeouts():
    import requests
    from unidecode import unidecode

    dt = DumpTruck('/tmp/open-data.sqlite', auto_commit = False)
    dt.execute('''
CREATE TABLE IF NOT EXISTS link_speeds (
  url TEXT NOT NULL,
  elapsed FLOAT,
  error_type TEXT NOT NULL,
  error TEXT NOT NULL,
  UNIQUE(url)
);''')
    urls = Queue()
    url_list = [row['url'] for row in dt.execute('SELECT DISTINCT url FROM links WHERE status_code = -42 and URL NOT IN (SELECT url from link_speeds)')]
    for url in url_list:
        urls.put(url)

    # Sink to the database
    def _db(queue):
        dt = DumpTruck('/tmp/open-data.sqlite')
        while True:
            dt.execute(*queue.get())
    db_updates = Queue()
    db_thread = Thread(None, target = _db, args = (db_updates,))
    db_thread.start()

#   def signal_handler(signal, frame):
#       db_thread.terminate()
#       sys.exit(0)
#   signal.signal(signal.SIGINT, signal_handler)

    # Check links
    def _check_link(url_queue):
        while not urls.empty():
            url = url_queue.get()
            if url == None:
                raise ValueError('url is None')
            try:
                r = requests.head(url, allow_redirects=True, timeout = 30)
            except Exception as e:
                try:
                    msg = unicode(e)
                except:
                    msg = ''
                sql = 'INSERT INTO link_speeds (url, error_type, error) VALUES (?,?,?)'
                db_updates.put((sql, (url, unicode(type(e)), msg))) # ew python 2
            else:
                sql = 'INSERT INTO link_speeds (url, elapsed, error_type, error) VALUES (?,?,\'\',\'\')'
                db_updates.put((sql, (url, r.elapsed.total_seconds())))

    threads = {}
    for i in range(500):
        threads[i] = Thread(None, target = _check_link, args = (urls,))

    for thread in threads.values():
        thread.start()

  def save_and_check(self, dataIn, tableIn, dataOut, tableOut = None, twice = True):
    if tableOut == None:
      tableOut = quote(tableIn)

    # Insert
    h = DumpTruck(dbname = '/tmp/test.db')
    h.insert(dataIn, tableIn)
    h.close()

    # Observe with pysqlite
    connection=sqlite3.connect('/tmp/test.db')
    cursor=connection.cursor()
    cursor.execute(u'SELECT * FROM %s' % tableOut)
    observed1 = cursor.fetchall()
    connection.close()

    if twice:
      # Observe with DumpTruck
      h = DumpTruck(dbname = '/tmp/test.db')
      observed2 = h.execute(u'SELECT * FROM %s' % tableOut)
      h.close()
 
      #Check
      expected1 = dataOut
      expected2 = [dataIn] if type(dataIn) in (dict, OrderedDict) else dataIn
 
      self.assertListEqual(observed1, expected1)
      self.assertListEqual(observed2, expected2)

 def savegetvar(self, var):
     h = DumpTruck(dbname="/tmp/test.db")
     h.save_var(u"weird", var)
     h.close()
     h = DumpTruck(dbname="/tmp/test.db")
     t = os.stat("/tmp/test.db").st_mtime
     self.assertEqual(h.get_var(u"weird"), var)
     h.close()
     assert os.stat("/tmp/test.db").st_mtime == t

 def savegetvar(self, var):
   h = DumpTruck(dbname = '/tmp/test.db')
   h.save_var(u'weird', var)
   h.close()
   h = DumpTruck(dbname = '/tmp/test.db')
   t=os.stat('/tmp/test.db').st_mtime
   self.assertEqual(h.get_var(u'weird'), var)
   h.close()
   assert os.stat('/tmp/test.db').st_mtime==t

  def test_create_table(self):
    h = DumpTruck(dbname = '/tmp/test.db')
    h.create_table({'foo': 0, 'bar': 1, 'baz': 2}, 'zombies')
    h.close()

    connection=sqlite3.connect('/tmp/test.db')
    cursor=connection.cursor()
    cursor.execute('SELECT foo, bar, baz FROM zombies')
    observed = cursor.fetchall()
    connection.close()

    expected = []
    self.assertListEqual(observed, expected)

    def test_create_table(self):
        h = DumpTruck(dbname="/tmp/test.db")
        h.create_table({"foo": 0, "bar": 1, "baz": 2}, "zombies")
        h.close()

        connection = sqlite3.connect("/tmp/test.db")
        cursor = connection.cursor()
        cursor.execute("SELECT foo, bar, baz FROM zombies")
        observed = cursor.fetchall()
        connection.close()

        expected = []
        self.assertListEqual(observed, expected)

def extract_dataset_table_info():
    dt = DumpTruck(dbname = '/tmp/table_info.db')
    dt.create_table({'portal': 'abc', 'id': 'abcd-efgh'}, 'table_info')
    dt.create_index(['portal', 'id'], 'table_info', unique = True)
    dt.create_index(['tableId'], 'table_info', unique = False)
    done = set([tuple(row.keys()) for row in dt.execute('SELECT portal, id FROM table_info')])
    for portal in os.listdir('data'):
        for viewid in os.listdir(os.path.join('data', portal, 'views')):
            if (portal, viewid) in done:
                continue
            d = _dataset_table_info(portal, viewid)
            if d == None:
                continue
            dt.upsert(d, 'table_info')

 def test_empty_row_second_insert(self):
     "An empty row acts like any other row."
     dt = DumpTruck(dbname="/tmp/test.db")
     dt.create_table({"foo": "uhtnh", "bar": "aoue"}, "nine")
     dt.insert({}, "nine")
     c = dt.execute("select count(*) as c from nine")[0]["c"]
     dt.close()
     self.assertEqual(c, 1)

 def test_no_rows_second_insert(self):
   "Nothing happens if no rows are inserted to a table that is there."
   dt = DumpTruck(dbname = '/tmp/test.db')
   dt.create_table({'foo': 'uhtnh', 'bar': 'aoue'}, 'ninety')
   dt.insert([], 'ninety')
   c = dt.execute('select count(*) as c from ninety')[0]['c']
   dt.close()
   self.assertEqual(c, 0)

 def test_no_rows_second_insert(self):
     "Nothing happens if no rows are inserted to a table that is there."
     dt = DumpTruck(dbname="/tmp/test.db")
     dt.create_table({"foo": "uhtnh", "bar": "aoue"}, "ninety")
     dt.insert([], "ninety")
     c = dt.execute("select count(*) as c from ninety")[0]["c"]
     dt.close()
     self.assertEqual(c, 0)

 def test_empty_row_second_insert(self):
   "An empty row acts like any other row."
   dt = DumpTruck(dbname = '/tmp/test.db')
   dt.create_table({'foo': 'uhtnh', 'bar': 'aoue'}, 'nine')
   dt.insert({}, 'nine')
   c = dt.execute('select count(*) as c from nine')[0]['c']
   dt.close()
   self.assertEqual(c, 1)