def __init__(self): super().__init__() self.table = sql.Table(self.tableName.lower()) self.cols = (self.table.dbid, self.table.dlstate, self.table.sourcesite, self.table.sourceurl, self.table.retreivaltime, self.table.lastupdate, self.table.sourceid, self.table.seriesname, self.table.filename, self.table.originname, self.table.downloadpath, self.table.flags, self.table.tags, self.table.note) self.colMap = { "dbid": self.table.dbid, "dlstate": self.table.dlstate, "sourcesite": self.table.sourcesite, "sourceurl": self.table.sourceurl, "retreivaltime": self.table.retreivaltime, "lastupdate": self.table.lastupdate, "sourceid": self.table.sourceid, "seriesname": self.table.seriesname, "filename": self.table.filename, "originname": self.table.originname, "downloadpath": self.table.downloadpath, "flags": self.table.flags, "tags": self.table.tags, "note": self.table.note } self.log.info("Loading %s Runner BaseClass", self.pluginName) self.checkInitPrimaryDb()
def _row_db(self, cur, condicion=True, cantidad=1000000): '''Busca y escribe los data de la Base de data''' table = sql.Table(self.name) columns = [sql.Column(table, c['field']) for c in self._col] #Crear la Condicion if condicion is False: condicion = sql.operators.Equal(1,2) elif condicion is True: condicion = sql.operators.And([]) elif bool(condicion) is True: c = [] for col in columns: if condicion.get(col.name, None) is None: continue if isinstance(condicion.get(col.name), int): c.append(sql.operators.Equal(col, condicion[col.name])) if isinstance(condicion.get(col.name), str): c.append(sql.operators.Like(col, '%{}%'.format(condicion[col.name]))) condicion = sql.operators.And(c) elif bool(condicion) is False: condicion = sql.operators.Equal(1,2) statement = table.select(*columns) statement.where = condicion query, data = tuple(statement) query = query.replace('"', '`') log.debug('Ejecutando SQL: {}'.format((query, data))) cur.execute(query, data) con = cur.fetchmany(cantidad) self._rows = con log.debug('data Importados directamente {}: {}'.format(self.name, repr(self._rows)))
def copy_from(self, from_table): Warning = Pool().get('res.user.warning') fields = {x.name for x in self.fields} from_fields = {x.name for x in from_table.fields} missing = sorted(list(from_fields - fields)) existing = fields & from_fields fields = {} for field in self.fields: if field.name in existing: fields[field.name] = field.type different_types = [] for field in from_table.fields: if field.name in existing: if (FIELD_TYPE_TRYTON[field.type] != FIELD_TYPE_TRYTON[fields[field.name]]): different_types.append( "%s (%s -> %s)" % (field.name, field.type, fields[field.name])) existing.remove(field.name) if missing or different_types: message = ['- %s' % x for x in (missing + different_types)] key = 'shine_copy_from_warning.%s.%s' % (self.name, from_table.id), if Warning.check(key): raise UserWarning( key, gettext('shine.copy_from_warning', fields='\n'.join(message), from_table=from_table.rec_name, table=self.rec_name)) if not existing: return existing = sorted(list(existing)) table = sql.Table(from_table.name) subquery = table.select() subquery.columns = [sql.Column(table, x) for x in existing] table = sql.Table(self.name) query = table.insert([sql.Column(table, x) for x in existing], subquery) cursor = Transaction().connection.cursor() cursor.execute(*query)
def compute_sheet(self): cursor = Transaction().connection.cursor() table = sql.Table(self.data_table_name) cursor.execute(*table.delete()) #fields = dict([(x.alias, x) for x in self.formulas]) direct_fields = [x.alias for x in self.formulas if not x.expression] formula_fields = [x.alias for x in self.formulas if x.expression] sql_fields = [ sql.Column(table, x) for x in direct_fields + formula_fields ] parser = formulas.Parser() formula_fields = [(x, parser.ast(x.expression)[1].compile() if x.expression.startswith('=') else '') for x in self.formulas if x.expression] insert_values = [] checker = TimeoutChecker(self.timeout, self.timeout_exception) if not formula_fields: # If there are no formula_fields we can make the loop faster as # we don't use OrderedDict and don't evaluate formulas for records in self.dataset.get_data(): checker.check() for record in records: insert_values.append( [getattr(record, x) for x in direct_fields]) else: for records in self.dataset.get_data(): checker.check() for record in records: values = OrderedDict() if direct_fields: values.update( OrderedDict([(x, getattr(record, x)) for x in direct_fields])) if formula_fields: for field, ast in formula_fields: if field.expression.startswith('='): inputs = [] for input_ in ast.inputs.keys(): # TODO: Check if input_ exists and raise # proper user error Indeed, we should check # de formulas when we move to active state inputs.append(values[input_.lower()]) value = ast(inputs)[0] else: value = field.expression ftype = FIELD_TYPE_PYTHON[field.type] values[field] = ftype(value) insert_values.append(list(values.values())) if insert_values: cursor.execute(*table.insert(sql_fields, insert_values))
def insert_db(self, columns=None): '''Exporta los data de la table a la base de data, regresa lasrowid por conveniencia''' cur = self.conection.cursor() table = sql.Table(self.name) cols = [col['field'] for col in self._col] columns = [sql.Column(table, c) for c in (cols if columns is None else columns)] data = {'':None,} values = [[fil[col.name] if not fil[col.name] in data else data[fil[col.name]] for col in columns] for fil in self.values] statement = table.insert(columns, values) query, data = tuple(statement) query = query.replace('"', '`') log.debug('Ejecutando SQL: {}'.format((query, data))) cur.execute(query, data) return cur.lastrowid
async def on_message(message): global symbol message.content = message.content.lower() if message.author == client.user: return # Sends a message with the current time if message.content.startswith(symbol + 'time'): await message.channel.send(str(datetime.datetime.now())) # Set a new symbol for the bot elif message.content.startswith(symbol + 'set'): content = message.content.split() if len(content) <= 1: await message.channel.send( 'Please enter a symbol. Usage "<current symbol>set <new symbol>"' ) else: symbol = content[1] await message.channel.send( f'The new symbol has been set to "{symbol}"') elif message.content.startswith(symbol + 'createdb'): guild = message.channel.guild await message.channel.send( f'There are {guild.member_count} members in the server. Creating a new table' ) # Initialise table here database = sql.Table('data', [('id', 'INT'), ('tot_msg', 'INT'), ('marg_msg', 'INT'), ('score', 'REAL')]) for member in guild.members: # member.name has the string value # Add individual members here database.add_element(member.id) elif message.content.startswith(symbol + 'prune'): # This estimates how many members will be pruned pruned_members = await message.guild.estimate_pruned_members(7) await message.channel.send( f'{pruned_members} members were pruned due to inactivity.') # await message.guild.prune_members(7) # Actually pruning members elif message.content.startswith(symbol): # A catchall. await message.channel.send('Hello This bot has been called v0.1.2')
def remove_db(self, keys=[]): '''Realisa un DELETE usando automaticamente las keys PRI para la clausula WHERE, se pueden usar columns propias como keys a responsabilidad del usuario, regresa ROW_COUNT() por conveniencia''' #Busca las keys PRI keys = [i['field'] for i in self._col if (i['key'] == 'PRI')] + list(keys) assert keys, "La table no tiene ninguna column PRI o column que usar como key" cur = self.conection.cursor() table = sql.Table(self.name) keys = [sql.Column(table, l) for l in keys] for fil in self.values: condicion = sql.operators.And([sql.operators.Equal(l, fil[l.name]) for l in keys]) statement = table.delete(where=condicion) query, data = tuple(statement) query = query.replace('"', '`') log.debug('Ejecutando SQL: {}'.format((query, data))) cur.execute(query, data) return cur.rowcount
def generate(self): for table in self.get_schema(): qtable = sql.Table(table.name) fields = [ getattr(qtable, column.field.name) for column in table.columns ] columns = [column.values for column in table.columns] with open('sql/insert_{}.template.sql'.format(table.name), 'w') as f: q = qtable.insert(columns=fields, values=[[0] * len(fields)]) print(str(q), file=f) if cursor: with open('sql/insert_{}.mysql.sql'.format(table.name), 'w') as f: for values in zip(*columns): q = qtable.insert(columns=fields, values=[values]) print(cursor.mogrify(str(q), q.params), file=f)
def update_db(self, columns=None, keys=[]): '''Realisa un UPDATE de todas la columns usando automaticamente las keys PRI para la clausula WHERE, las keys no tienen que estar dentro de la columns a update, se pueden usar columns propias como keys a responsabilidad del usuario, regresa ROW_COUNT() por conveniencia''' #Busca las keys PRI keys = [i['field'] for i in self._col if (i['key'] == 'PRI')] + list(keys) assert keys, "La table no tiene ninguna column PRI o column que usar como key" cur = self.conection.cursor() table = sql.Table(self.name) keys = [sql.Column(table, l) for l in keys] cols = [col['field'] for col in self._col if col['type'] != "timestamp"] columns = [sql.Column(table, c) for c in (cols if columns is None else columns)] for fil in self.values: data = {'':None,} values = [fil[col.name] if not fil[col.name] in data else data[fil[col.name]] for col in columns] condicion = sql.operators.And([sql.operators.Equal(l, fil[l.name]) for l in keys]) statement = table.update(columns = columns, values=values, where=condicion) query, data = tuple(statement) query = query.replace('"', '`') log.debug('Ejecutando SQL: {}'.format((query, data))) cur.execute(query, data) return cur.rowcount
class ToolsTestCase(unittest.TestCase): 'Test tools' table = sql.Table('test') def test_reduce_ids_empty(self): 'Test reduce_ids empty list' self.assertEqual(reduce_ids(self.table.id, []), sql.Literal(False)) def test_reduce_ids_continue(self): 'Test reduce_ids continue list' self.assertEqual( reduce_ids(self.table.id, list(range(10))), sql.operators.Or(((self.table.id >= 0) & (self.table.id <= 9), ))) def test_reduce_ids_one_hole(self): 'Test reduce_ids continue list with one hole' self.assertEqual( reduce_ids(self.table.id, list(range(10)) + list(range(20, 30))), ((self.table.id >= 0) & (self.table.id <= 9)) | ((self.table.id >= 20) & (self.table.id <= 29))) def test_reduce_ids_short_continue(self): 'Test reduce_ids short continue list' self.assertEqual( reduce_ids(self.table.id, list(range(4))), sql.operators.Or((self.table.id.in_(list(range(4))), ))) def test_reduce_ids_complex(self): 'Test reduce_ids complex list' self.assertEqual( reduce_ids( self.table.id, list(range(10)) + list(range(25, 30)) + list(range(15, 20))), (((self.table.id >= 0) & (self.table.id <= 14)) | (self.table.id.in_(list(range(25, 30)))))) def test_reduce_ids_complex_small_continue(self): 'Test reduce_ids complex list with small continue' self.assertEqual( reduce_ids( self.table.id, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 15, 18, 19, 21]), (((self.table.id >= 1) & (self.table.id <= 12)) | (self.table.id.in_([15, 18, 19, 21])))) @unittest.skipIf(sys.flags.optimize, "assert removed by optimization") def test_reduce_ids_float(self): 'Test reduce_ids with integer as float' self.assertEqual( reduce_ids(self.table.id, [ 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 15.0, 18.0, 19.0, 21.0 ]), (((self.table.id >= 1.0) & (self.table.id <= 12.0)) | (self.table.id.in_([15.0, 18.0, 19.0, 21.0])))) self.assertRaises(AssertionError, reduce_ids, self.table.id, [1.1]) def test_reduce_domain(self): 'Test reduce_domain' clause = ('x', '=', 'x') tests = ( ([clause], ['AND', clause]), ([clause, [clause]], ['AND', clause, clause]), (['AND', clause, [clause]], ['AND', clause, clause]), ([clause, ['AND', clause]], ['AND', clause, clause]), ([clause, ['AND', clause, clause]], ['AND', clause, clause, clause]), (['AND', clause, ['AND', clause]], ['AND', clause, clause]), ([[[clause]]], ['AND', clause]), (['OR', clause], ['OR', clause]), (['OR', clause, [clause]], ['OR', clause, ['AND', clause]]), (['OR', clause, [clause, clause]], ['OR', clause, ['AND', clause, clause]]), (['OR', clause, ['OR', clause]], ['OR', clause, clause]), (['OR', clause, [clause, ['OR', clause, clause]]], ['OR', clause, ['AND', clause, ['OR', clause, clause]]]), (['OR', [clause]], ['OR', ['AND', clause]]), ([], []), (['OR', clause, []], ['OR', clause, []]), (['AND', clause, []], ['AND', clause, []]), ) for i, j in tests: self.assertEqual(reduce_domain(i), j, '%s -> %s != %s' % (i, reduce_domain(i), j)) def test_is_instance_method(self): 'Test is_instance_method' class Foo(object): @staticmethod def static(): pass @classmethod def klass(cls): pass def instance(self): pass self.assertFalse(is_instance_method(Foo, 'static')) self.assertFalse(is_instance_method(Foo, 'klass')) self.assertTrue(is_instance_method(Foo, 'instance')) def test_file_open(self): "Test file_open" with file_open('__init__.py', subdir=None) as fp: self.assertTrue(fp) with file_open('ir/__init__.py') as fp: self.assertTrue(fp) with self.assertRaisesRegex(IOError, "File not found :"): with file_open('ir/noexist'): pass with self.assertRaisesRegex(IOError, "Permission denied:"): with file_open('/etc/passwd'): pass with self.assertRaisesRegex(IOError, "Permission denied:"): with file_open('../../foo'): pass def test_file_open_suffix(self): "Test file_open from same root name but with a suffix" with self.assertRaisesRegex(IOError, "Permission denied:"): file_open('../trytond_suffix', subdir=None) def test_strip_wildcard(self): 'Test strip wildcard' for clause, result in [ ('%a%', 'a'), ('%%%%a%%%', 'a'), ('\\%a%', '\\%a'), ('\\%a\\%', '\\%a\\%'), ('a', 'a'), ('', ''), (None, None), ]: self.assertEqual(strip_wildcard(clause), result, msg=clause) def test_strip_wildcard_different_wildcard(self): 'Test strip wildcard with different wildcard' self.assertEqual(strip_wildcard('___a___', '_'), 'a') def test_lstrip_wildcard(self): 'Test lstrip wildcard' for clause, result in [ ('%a', 'a'), ('%a%', 'a%'), ('%%%%a%', 'a%'), ('\\%a%', '\\%a%'), ('a', 'a'), ('', ''), (None, None), ]: self.assertEqual(lstrip_wildcard(clause), result, msg=clause) def test_lstrip_wildcard_different_wildcard(self): 'Test lstrip wildcard with different wildcard' self.assertEqual(lstrip_wildcard('___a', '_'), 'a') def test_rstrip_wildcard(self): 'Test rstrip wildcard' for clause, result in [ ('a%', 'a'), ('%a%', '%a'), ('%a%%%%%', '%a'), ('%a\\%', '%a\\%'), ('a', 'a'), ('', ''), (None, None), ]: self.assertEqual(rstrip_wildcard(clause), result, msg=clause) def test_rstrip_wildcard_diferent_wildcard(self): self.assertEqual(rstrip_wildcard('a___', '_'), 'a') def test_escape_wildcard(self): self.assertEqual(escape_wildcard('foo%bar_baz\\'), 'foo\\%bar\\_baz\\\\') def test_unescape_wildcard(self): "Test unescape_wildcard" self.assertEqual(unescape_wildcard('foo\\%bar\\_baz\\\\'), 'foo%bar_baz\\') def test_is_full_text(self): "Test is_full_text" for value, result in [ ('foo', True), ('%foo bar%', True), ('foo%', False), ('foo_bar', False), ('foo\\_bar', True), ]: with self.subTest(value=value): self.assertEqual(is_full_text(value), result) def test_slugify(self): "Test slugify" self.assertEqual(slugify('unicode ♥ is ☢'), 'unicode-is') def test_slugify_hyphenate(self): "Test hyphenate in slugify" self.assertEqual(slugify('foo bar', hyphenate='_'), 'foo_bar') def test_sortable_values(self): def key(values): return values values = [ (('a', 1), ('b', None)), (('a', 1), ('b', 3)), (('a', 1), ('b', 2)), ] with self.assertRaises(TypeError): sorted(values, key=key) self.assertEqual(sorted(values, key=sortable_values(key)), [ (('a', 1), ('b', 2)), (('a', 1), ('b', 3)), (('a', 1), ('b', None)), ]) def test_firstline(self): "Test firstline" for text, result in [ ("", ""), ("first line\nsecond line", "first line"), ("\nsecond line", "second line"), ("\n\nthird line", "third line"), (" \nsecond line", "second line"), ]: with self.subTest(text=text, result=result): self.assertEqual(firstline(text), result) def test_remove_forbidden_chars(self): "Test remove_forbidden_chars" for string, result in [ ("", ""), (None, None), ("\ttest", "test"), (" test ", "test"), ]: with self.subTest(string=string): self.assertEqual(remove_forbidden_chars(string), result)
class ToolsTestCase(unittest.TestCase): 'Test tools' table = sql.Table('test') def test_reduce_ids_empty(self): 'Test reduce_ids empty list' self.assertEqual(reduce_ids(self.table.id, []), sql.Literal(False)) def test_reduce_ids_continue(self): 'Test reduce_ids continue list' self.assertEqual( reduce_ids(self.table.id, list(range(10))), sql.operators.Or(((self.table.id >= 0) & (self.table.id <= 9), ))) def test_reduce_ids_one_hole(self): 'Test reduce_ids continue list with one hole' self.assertEqual( reduce_ids(self.table.id, list(range(10)) + list(range(20, 30))), ((self.table.id >= 0) & (self.table.id <= 9)) | ((self.table.id >= 20) & (self.table.id <= 29))) def test_reduce_ids_short_continue(self): 'Test reduce_ids short continue list' self.assertEqual( reduce_ids(self.table.id, list(range(4))), sql.operators.Or((self.table.id.in_(list(range(4))), ))) def test_reduce_ids_complex(self): 'Test reduce_ids complex list' self.assertEqual( reduce_ids( self.table.id, list(range(10)) + list(range(25, 30)) + list(range(15, 20))), (((self.table.id >= 0) & (self.table.id <= 14)) | (self.table.id.in_(list(range(25, 30)))))) def test_reduce_ids_complex_small_continue(self): 'Test reduce_ids complex list with small continue' self.assertEqual( reduce_ids( self.table.id, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 15, 18, 19, 21]), (((self.table.id >= 1) & (self.table.id <= 12)) | (self.table.id.in_([15, 18, 19, 21])))) @unittest.skipIf(sys.flags.optimize, "assert removed by optimization") def test_reduce_ids_float(self): 'Test reduce_ids with integer as float' self.assertEqual( reduce_ids(self.table.id, [ 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 15.0, 18.0, 19.0, 21.0 ]), (((self.table.id >= 1.0) & (self.table.id <= 12.0)) | (self.table.id.in_([15.0, 18.0, 19.0, 21.0])))) self.assertRaises(AssertionError, reduce_ids, self.table.id, [1.1]) def test_reduce_domain(self): 'Test reduce_domain' clause = ('x', '=', 'x') tests = ( ([clause], ['AND', clause]), ([clause, [clause]], ['AND', clause, clause]), (['AND', clause, [clause]], ['AND', clause, clause]), ([clause, ['AND', clause]], ['AND', clause, clause]), ([clause, ['AND', clause, clause]], ['AND', clause, clause, clause]), (['AND', clause, ['AND', clause]], ['AND', clause, clause]), ([[[clause]]], ['AND', clause]), (['OR', clause], ['OR', clause]), (['OR', clause, [clause]], ['OR', clause, ['AND', clause]]), (['OR', clause, [clause, clause]], ['OR', clause, ['AND', clause, clause]]), (['OR', clause, ['OR', clause]], ['OR', clause, clause]), (['OR', clause, [clause, ['OR', clause, clause]]], ['OR', clause, ['AND', clause, ['OR', clause, clause]]]), (['OR', [clause]], ['OR', ['AND', clause]]), ([], []), (['OR', clause, []], ['OR', clause, []]), (['AND', clause, []], ['AND', clause, []]), ) for i, j in tests: self.assertEqual(reduce_domain(i), j, '%s -> %s != %s' % (i, reduce_domain(i), j)) def test_is_instance_method(self): 'Test is_instance_method' class Foo(object): @staticmethod def static(): pass @classmethod def klass(cls): pass def instance(self): pass self.assertFalse(is_instance_method(Foo, 'static')) self.assertFalse(is_instance_method(Foo, 'klass')) self.assertTrue(is_instance_method(Foo, 'instance')) def test_file_open(self): "Test file_open" with file_open('__init__.py', subdir=None) as fp: self.assertTrue(fp) with file_open('ir/__init__.py') as fp: self.assertTrue(fp) with self.assertRaisesRegex(IOError, "File not found :"): with file_open('ir/noexist'): pass with self.assertRaisesRegex(IOError, "Permission denied:"): with file_open('/etc/passwd'): pass with self.assertRaisesRegex(IOError, "Permission denied:"): with file_open('../../foo'): pass def test_file_open_suffix(self): "Test file_open from same root name but with a suffix" with self.assertRaisesRegex(IOError, "Permission denied:"): file_open('../trytond_suffix', subdir=None) def test_strip_wildcard(self): 'Test strip wildcard' for clause, result in [ ('%a%', 'a'), ('%%%%a%%%', 'a'), ('\\%a%', '\\%a'), ('\\%a\\%', '\\%a\\%'), ('a', 'a'), ('', ''), (None, None), ]: self.assertEqual(strip_wildcard(clause), result, msg=clause) def test_strip_wildcard_different_wildcard(self): 'Test strip wildcard with different wildcard' self.assertEqual(strip_wildcard('___a___', '_'), 'a') def test_lstrip_wildcard(self): 'Test lstrip wildcard' for clause, result in [ ('%a', 'a'), ('%a%', 'a%'), ('%%%%a%', 'a%'), ('\\%a%', '\\%a%'), ('a', 'a'), ('', ''), (None, None), ]: self.assertEqual(lstrip_wildcard(clause), result, msg=clause) def test_lstrip_wildcard_different_wildcard(self): 'Test lstrip wildcard with different wildcard' self.assertEqual(lstrip_wildcard('___a', '_'), 'a') def test_rstrip_wildcard(self): 'Test rstrip wildcard' for clause, result in [ ('a%', 'a'), ('%a%', '%a'), ('%a%%%%%', '%a'), ('%a\\%', '%a\\%'), ('a', 'a'), ('', ''), (None, None), ]: self.assertEqual(rstrip_wildcard(clause), result, msg=clause) def test_rstrip_wildcard_diferent_wildcard(self): self.assertEqual(rstrip_wildcard('a___', '_'), 'a')
class ToolsTestCase(unittest.TestCase): 'Test tools' table = sql.Table('test') def test_reduce_ids_empty(self): 'Test reduce_ids empty list' self.assertEqual(reduce_ids(self.table.id, []), sql.Literal(False)) def test_reduce_ids_continue(self): 'Test reduce_ids continue list' self.assertEqual( reduce_ids(self.table.id, range(10)), sql.operators.Or(((self.table.id >= 0) & (self.table.id <= 9), ))) def test_reduce_ids_one_hole(self): 'Test reduce_ids continue list with one hole' self.assertEqual(reduce_ids(self.table.id, range(10) + range(20, 30)), ((self.table.id >= 0) & (self.table.id <= 9)) | ((self.table.id >= 20) & (self.table.id <= 29))) def test_reduce_ids_short_continue(self): 'Test reduce_ids short continue list' self.assertEqual(reduce_ids(self.table.id, range(4)), sql.operators.Or((self.table.id.in_(range(4)), ))) def test_reduce_ids_complex(self): 'Test reduce_ids complex list' self.assertEqual( reduce_ids(self.table.id, range(10) + range(25, 30) + range(15, 20)), (((self.table.id >= 0) & (self.table.id <= 14)) | (self.table.id.in_(range(25, 30))))) def test_reduce_ids_complex_small_continue(self): 'Test reduce_ids complex list with small continue' self.assertEqual( reduce_ids( self.table.id, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 15, 18, 19, 21]), (((self.table.id >= 1) & (self.table.id <= 12)) | (self.table.id.in_([15, 18, 19, 21])))) def test_reduce_ids_float(self): 'Test reduce_ids with integer as float' self.assertEqual( reduce_ids(self.table.id, [ 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 15.0, 18.0, 19.0, 21.0 ]), (((self.table.id >= 1.0) & (self.table.id <= 12.0)) | (self.table.id.in_([15.0, 18.0, 19.0, 21.0])))) self.assertRaises(AssertionError, reduce_ids, self.table.id, [1.1]) def test_datetime_strftime(self): 'Test datetime_strftime' self.assert_(datetime_strftime(datetime.date(2005, 3, 2), '%Y-%m-%d'), '2005-03-02') self.assert_(datetime_strftime(datetime.date(1805, 3, 2), '%Y-%m-%d'), '1805-03-02') def test_reduce_domain(self): 'Test reduce_domain' clause = ('x', '=', 'x') tests = ( ([clause], ['AND', clause]), ([clause, [clause]], ['AND', clause, clause]), (['AND', clause, [clause]], ['AND', clause, clause]), ([clause, ['AND', clause]], ['AND', clause, clause]), ([clause, ['AND', clause, clause]], ['AND', clause, clause, clause]), (['AND', clause, ['AND', clause]], ['AND', clause, clause]), ([[[clause]]], ['AND', clause]), (['OR', clause], ['OR', clause]), (['OR', clause, [clause]], ['OR', clause, ['AND', clause]]), (['OR', clause, [clause, clause]], ['OR', clause, ['AND', clause, clause]]), (['OR', clause, ['OR', clause]], ['OR', clause, clause]), (['OR', clause, [clause, ['OR', clause, clause]]], ['OR', clause, ['AND', clause, ['OR', clause, clause]]]), (['OR', [clause]], ['OR', ['AND', clause]]), ([], []), (['OR', clause, []], ['OR', clause, []]), (['AND', clause, []], ['AND', clause, []]), ) for i, j in tests: self.assertEqual(reduce_domain(i), j, '%s -> %s != %s' % (i, reduce_domain(i), j)) def test_is_instance_method(self): 'Test is_instance_method' class Foo(object): @staticmethod def static(): pass @classmethod def klass(cls): pass def instance(self): pass self.assertFalse(is_instance_method(Foo, 'static')) self.assertFalse(is_instance_method(Foo, 'klass')) self.assertTrue(is_instance_method(Foo, 'instance'))
def get_sql_table(cls): table = cls.get_table() if table: return sql.Table(table.name) return super(Data, cls).__table__()
def __init__(self): # If we're running as a multiprocessing thread, inject that into # the logger path threadName = multiprocessing.current_process().name if threadName: self.log = logging.getLogger("%s.%s" % (self.loggerPath, threadName)) else: self.log = logging.getLogger(self.loggerPath) self.connect() # self.conn.autocommit = True with self.transaction() as cur: # print("DB opened.") cur.execute( "SELECT * FROM information_schema.tables WHERE table_name=%s", (self.tableName, )) # print("table exists = ", cur.rowcount) tableExists = bool(cur.rowcount) if not tableExists: self.log.info("Need to create table!") cur.execute('''CREATE TABLE IF NOT EXISTS {table} ( dbId SERIAL PRIMARY KEY, fsPath text NOT NULL, internalPath text NOT NULL, itemHash text, pHash bigint, itemKind text, imgx INTEGER, imgy INTEGER, scantime DOUBLE PRECISION DEFAULT 0 );'''.format(table=self.tableName)) # dHash bigint, self.log.info("Creating indexes") cur.execute( '''CREATE UNIQUE INDEX {table}_name_index ON {table}(fsPath, internalPath);''' .format(table=self.tableName)) cur.execute( '''CREATE INDEX {table}_path_index ON {table}(fsPath text_pattern_ops);''' .format(table=self.tableName)) cur.execute( '''CREATE INDEX {table}_ihash_index ON {table}(itemHash);''' .format(table=self.tableName)) cur.execute( '''CREATE INDEX {table}_phash_index ON {table}(pHash);''' .format(table=self.tableName)) # cur.execute('''CREATE INDEX {table}_dhash_index ON {table}(dHash);'''.format(table=self.tableName)) cur.execute( '''CREATE INDEX {table}_scantime_index ON {table}(scantime);''' .format(table=self.tableName)) self.log.info("Done!") # CREATE INDEX dedupitems_scantime_index ON dedupitems(scantime) self.table = sql.Table(self.tableName.lower()) self.cols = ( self.table.dbid, self.table.fspath, self.table.internalpath, self.table.itemhash, self.table.phash, self.table.itemkind, self.table.imgx, self.table.imgy, # self.table.phash_text, # self.table.dhash_text ) self.colMap = { "dbid": self.table.dbid, "fspath": self.table.fspath, "internalpath": self.table.internalpath, "itemhash": self.table.itemhash, "phash": self.table.phash, "itemkind": self.table.itemkind, "imgx": self.table.imgx, "imgy": self.table.imgy, # "phash_text" : self.table.phash_text, # "dhash_text" : self.table.dhash_text }
def process_tweet(tweet): """ Process one tweet. This function is run by a worker. """ global processed_count, inserted_count, errored_count, db try: with processed_count.get_lock(): processed_count.value += 1 # decompose timestamp, user, content, tweet_id, total = tweet # print progress if tweet_id % 2000 == 0: helpers.log('Processed %.3f%% of tweets' % (tweet_id / total * 100)) # remove empty tweets if content == 'No Post Title': return # guess the tweet language language = gl.guess_language(content) if language != 'en': return # make stats url_regex = re.compile('^https?://') length = len(content) words = list(filter(lambda word: word, content.split(' '))) word_count = len(words) hashtags = list(map(lambda word: word[1:], filter(lambda word: word[0] == '#', words))) mentions = list(map(lambda word: word[1:], filter(lambda word: word[0] == '@', words))) urls = list(filter(lambda word: url_regex.match(word), words)) # store in the database if not db: db = pg.connect(host = 'localhost') cur = db.cursor() table = sql.Table('tweets') query = tuple( table.insert( columns = [table.timestamp, table.user, table.length, table.words, table.hashtags, table.mentions, table.urls], values = [[timestamp, user, length, word_count, hashtags, mentions, urls]])) cur.execute(query[0], query[1]) db.commit() with inserted_count.get_lock(): inserted_count.value += 1 except Exception as e: helpers.log('Error processing tweet:') helpers.log(e) with errored_count.get_lock(): errored_count.value += 1
def count(self): table = sql.Table(self.name) cursor = Transaction().connection.cursor() query = table.select(sql.aggregate.Count(1)) cursor.execute(*query) return cursor.fetchone()[0]
class ToolsTestCase(unittest.TestCase): 'Test tools' table = sql.Table('test') def test_reduce_ids_empty(self): 'Test reduce_ids empty list' self.assertEqual(reduce_ids(self.table.id, []), sql.Literal(False)) def test_reduce_ids_continue(self): 'Test reduce_ids continue list' self.assertEqual(reduce_ids(self.table.id, range(10)), sql.operators.Or(((self.table.id >= 0) & (self.table.id <= 9),))) def test_reduce_ids_one_hole(self): 'Test reduce_ids continue list with one hole' self.assertEqual(reduce_ids(self.table.id, range(10) + range(20, 30)), ((self.table.id >= 0) & (self.table.id <= 9)) | ((self.table.id >= 20) & (self.table.id <= 29))) def test_reduce_ids_short_continue(self): 'Test reduce_ids short continue list' self.assertEqual(reduce_ids(self.table.id, range(4)), sql.operators.Or((self.table.id.in_(range(4)),))) def test_reduce_ids_complex(self): 'Test reduce_ids complex list' self.assertEqual(reduce_ids(self.table.id, range(10) + range(25, 30) + range(15, 20)), (((self.table.id >= 0) & (self.table.id <= 14)) | (self.table.id.in_(range(25, 30))))) def test_reduce_ids_complex_small_continue(self): 'Test reduce_ids complex list with small continue' self.assertEqual(reduce_ids(self.table.id, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 15, 18, 19, 21]), (((self.table.id >= 1) & (self.table.id <= 12)) | (self.table.id.in_([15, 18, 19, 21])))) def test_reduce_ids_float(self): 'Test reduce_ids with integer as float' self.assertEqual(reduce_ids(self.table.id, [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 15.0, 18.0, 19.0, 21.0]), (((self.table.id >= 1.0) & (self.table.id <= 12.0)) | (self.table.id.in_([15.0, 18.0, 19.0, 21.0])))) self.assertRaises(AssertionError, reduce_ids, self.table.id, [1.1]) def test_datetime_strftime(self): 'Test datetime_strftime' self.assert_(datetime_strftime(datetime.date(2005, 3, 2), '%Y-%m-%d'), '2005-03-02') self.assert_(datetime_strftime(datetime.date(1805, 3, 2), '%Y-%m-%d'), '1805-03-02') self.assert_(datetime_strftime(datetime.datetime(2005, 3, 2, 0, 0, 0), '%Y-%m-%d'), '2005-03-02') with self.assertRaises(TypeError): datetime_strftime(None, '%Y-%m-%d') with self.assertRaises(TypeError): datetime_strftime(2, '%Y-%m-%d') def test_reduce_domain(self): 'Test reduce_domain' clause = ('x', '=', 'x') tests = ( ([clause], ['AND', clause]), ([clause, [clause]], ['AND', clause, clause]), (['AND', clause, [clause]], ['AND', clause, clause]), ([clause, ['AND', clause]], ['AND', clause, clause]), ([clause, ['AND', clause, clause]], ['AND', clause, clause, clause]), (['AND', clause, ['AND', clause]], ['AND', clause, clause]), ([[[clause]]], ['AND', clause]), (['OR', clause], ['OR', clause]), (['OR', clause, [clause]], ['OR', clause, ['AND', clause]]), (['OR', clause, [clause, clause]], ['OR', clause, ['AND', clause, clause]]), (['OR', clause, ['OR', clause]], ['OR', clause, clause]), (['OR', clause, [clause, ['OR', clause, clause]]], ['OR', clause, ['AND', clause, ['OR', clause, clause]]]), (['OR', [clause]], ['OR', ['AND', clause]]), ([], []), (['OR', clause, []], ['OR', clause, []]), (['AND', clause, []], ['AND', clause, []]), ) for i, j in tests: self.assertEqual(reduce_domain(i), j, '%s -> %s != %s' % (i, reduce_domain(i), j)) def test_is_instance_method(self): 'Test is_instance_method' class Foo(object): @staticmethod def static(): pass @classmethod def klass(cls): pass def instance(self): pass self.assertFalse(is_instance_method(Foo, 'static')) self.assertFalse(is_instance_method(Foo, 'klass')) self.assertTrue(is_instance_method(Foo, 'instance')) def test_file_open(self): "Test file_open" self.assertTrue(file_open('__init__.py', subdir=None)) self.assertTrue(file_open('ir/__init__.py')) with self.assertRaisesRegexp(IOError, "File not found :"): file_open('ir/noexist') with self.assertRaisesRegexp(IOError, "Permission denied:"): file_open('/etc/passwd') with self.assertRaisesRegexp(IOError, "Permission denied:"): file_open('../../foo') def test_file_open_suffix(self): "Test file_open from same root name but with a suffix" with self.assertRaisesRegexp(IOError, "Permission denied:"): file_open('../trytond_suffix', subdir=None)
def checkInitPrimaryDb(self): with self.transaction() as cur: cur.execute('''CREATE TABLE IF NOT EXISTS {tableName} ( dbid SERIAL PRIMARY KEY, src TEXT NOT NULL, dlstate INTEGER DEFAULT 0, url CITEXT UNIQUE NOT NULL, title text, series CITEXT, contents text, istext boolean DEFAULT TRUE, fhash CITEXT, mimetype CITEXT, fspath text DEFAULT '', distance INTEGER DEFAULT 2147483648, walklimit INTEGER DEFAULT -1, netloc CITEXT );'''.format(tableName=self.tableName)) cur.execute('''CREATE TABLE IF NOT EXISTS {tableName} ( dbid SERIAL PRIMARY KEY, src TEXT NOT NULL, url CITEXT NOT NULL, change real NOT NULL, title text, changeDate double precision NOT NULL );'''.format(tableName=self.changeTableName)) cur.execute("SELECT relname FROM pg_class;") haveIndexes = cur.fetchall() haveIndexes = [index[0] for index in haveIndexes] indexes = [ ("%s_source_index" % self.tableName, self.tableName, '''CREATE INDEX %s ON %s (src );'''), ("%s_istext_index" % self.tableName, self.tableName, '''CREATE INDEX %s ON %s (istext );'''), ("%s_dlstate_index" % self.tableName, self.tableName, '''CREATE INDEX %s ON %s (dlstate );'''), ("%s_url_index" % self.tableName, self.tableName, '''CREATE INDEX %s ON %s (url );'''), ("%s_title_index" % self.tableName, self.tableName, '''CREATE INDEX %s ON %s (title );'''), ("%s_fhash_index" % self.tableName, self.tableName, '''CREATE INDEX %s ON %s (fhash );'''), ("%s_title_coll_index" % self.tableName, self.tableName, '''CREATE INDEX %s ON %s USING BTREE (title COLLATE "en_US" text_pattern_ops);''' ), ("%s_date_index" % self.changeTableName, self.changeTableName, '''CREATE INDEX %s ON %s (changeDate);'''), ("%s_src_index" % self.changeTableName, self.changeTableName, '''CREATE INDEX %s ON %s (src );'''), ("%s_url_index" % self.changeTableName, self.changeTableName, '''CREATE INDEX %s ON %s (url );'''), ("%s_change_index" % self.changeTableName, self.changeTableName, '''CREATE INDEX %s ON %s (change );'''), ("%s_netloc_index" % self.changeTableName, self.changeTableName, '''CREATE INDEX %s ON %s (change );'''), ("%s_title_trigram" % self.changeTableName, self.changeTableName, '''CREATE INDEX %s ON %s USING gin (title gin_trgm_ops);'''), ] # CREATE INDEX book_series_name_trigram ON book_series USING gin (itemname gin_trgm_ops); # CREATE INDEX book_title_trigram ON book_items USING gin (title gin_trgm_ops); # ALTER INDEX book_title_trigram RENAME TO book_items_title_trigram; # CREATE INDEX book_items_title_coll_index ON book_items USING BTREE (title COLLATE "en_US" text_pattern_ops); # CREATE INDEX book_items_fhash_index ON book_items (fhash); # CREATE INDEX title_collate_index ON book_items USING BTREE (title COLLATE "en_US" text_pattern_ops); # EXPLAIN ANALYZE SELECT COUNT(*) FROM book_items WHERE title LIKE 's%'; for name, table, nameFormat in indexes: if not name.lower() in haveIndexes: cur.execute(nameFormat % (name, table)) self.log.info("Retreived page database created") self.validKwargs = [ 'dbid', 'src', 'dlstate', 'url', 'title', 'netloc', 'series', 'contents', 'istext', 'fhash', 'mimetype', 'fspath', 'distance', 'walklimit' ] self.table = sql.Table(self.tableName.lower()) self.cols = ( self.table.dbid, self.table.src, self.table.dlstate, self.table.url, self.table.title, self.table.netloc, self.table.series, self.table.contents, self.table.istext, self.table.fhash, self.table.mimetype, self.table.fspath, self.table.distance, self.table.walklimit, ) self.colMap = { "dbid": self.table.dbid, "src": self.table.src, "dlstate": self.table.dlstate, "url": self.table.url, "netloc": self.table.netloc, "title": self.table.title, "series": self.table.series, "contents": self.table.contents, "istext": self.table.istext, "fhash": self.table.fhash, "mimetype": self.table.mimetype, "fspath": self.table.fspath, "distance": self.table.distance, "walklimit": self.table.walklimit, }
def __init__(self): # If we're running as a multiprocessing thread, inject that into # the logger path threadName = multiprocessing.current_process().name if threadName: self.log = logging.getLogger("%s.%s" % (self.loggerPath, threadName)) else: self.log = logging.getLogger(self.loggerPath) self.connect() # self.conn.autocommit = True with self.transaction() as cur: cur.execute( "SELECT * FROM information_schema.tables WHERE table_name=%s", (self.tableName, )) have = cur.fetchall() mainTableExists = bool(have) cur.execute( "SELECT * FROM information_schema.tables WHERE table_name=%s", (self.tableName + "_plink", )) have = cur.fetchall() linkTableExists = bool(have) if not mainTableExists: self.log.info("Need to create table!") cur.execute('''CREATE TABLE IF NOT EXISTS {table} ( dbId SERIAL PRIMARY KEY, fsPath text NOT NULL, internalPath text NOT NULL, itemHash text, pHash bigint, wHash bigint, itemKind text, imgx INTEGER, imgy INTEGER, scantime DOUBLE PRECISION DEFAULT 0 );'''.format(table=self.tableName)) # dHash bigint, self.log.info("Creating indexes") cur.execute( '''CREATE UNIQUE INDEX {table}_name_index ON {table}(fsPath, internalPath);''' .format(table=self.tableName)) cur.execute( '''CREATE INDEX {table}_path_index ON {table}(fsPath text_pattern_ops);''' .format(table=self.tableName)) cur.execute( '''CREATE INDEX {table}_ihash_index ON {table}(itemHash);''' .format(table=self.tableName)) cur.execute( '''CREATE INDEX {table}_phash_index ON {table}(pHash);''' .format(table=self.tableName)) # cur.execute('''CREATE INDEX {table}_dhash_index ON {table}(dHash);'''.format(table=self.tableName)) cur.execute( '''CREATE INDEX {table}_scantime_index ON {table}(scantime);''' .format(table=self.tableName)) self.log.info("Done!") # CREATE INDEX dedupitems_scantime_index ON dedupitems(scantime) if not linkTableExists: self.log.info("Checking plink table") cur.execute('''CREATE TABLE IF NOT EXISTS {table}_plink ( item_1_link BIGINT REFERENCES {table}(dbId) ON DELETE CASCADE, item_2_link BIGINT REFERENCES {table}(dbId) ON DELETE CASCADE, distance INT, PRIMARY KEY (item_1_link, item_2_link, distance), UNIQUE (item_1_link, item_2_link), CHECK (item_1_link < item_2_link) );'''.format(table=self.tableName)) self.table = sql.Table(self.tableName.lower()) self.cols = ( self.table.dbid, self.table.fspath, self.table.internalpath, self.table.itemhash, self.table.phash, self.table.itemkind, self.table.imgx, self.table.imgy, # self.table.phash_text, # self.table.dhash_text ) self.colMap = { "dbid": self.table.dbid, "fspath": self.table.fspath, "internalpath": self.table.internalpath, "itemhash": self.table.itemhash, "phash": self.table.phash, "itemkind": self.table.itemkind, "imgx": self.table.imgx, "imgy": self.table.imgy, # "phash_text" : self.table.phash_text, # "dhash_text" : self.table.dhash_text }