def calling_code(country=False): """Produces a calling code from a list of global calling codes. Returns: country (str): The country which corresponds to the calling code. calling_code (str): A variable length calling code. Example: >>> calling_code() AttrDict({u'calling_code': '961', 'country': u'Lebanon'}) >>> calling_code("Denmark") AttrDict({u'calling_code': '45', 'country': u'Denmark'}) >>> calling_code().country u'Guinea' """ if country: cmd = 'SELECT country, calling_code FROM calling_codes WHERE ' \ 'country LIKE :_country LIMIT 1;' res = engine.execute(text(cmd), _country=country) else: res = engine.execute( 'SELECT country, calling_code FROM calling_codes ORDER BY ' 'random() LIMIT 1;' ) return AttrDict([dict(d) for d in res.fetchall()][0])
def putDataForUserGermany(): global engine api = configscript.setupTwitterAuth() places = api.geo_search(query="Germany", granularity="country") place_id = places[0].id tweets = tw.Cursor(api.search, q="place:%s" % place_id, tweet_mode='extended', lang='en').items() for tweet in tweets: username = tweet.user.screen_name allTweets = tw.Cursor(api.user_timeline, screen_name=username, tweet_mode="extended", exclude_replies=False, include_rts=False, lang='en').items(150) listAllTweets = list(allTweets) if (len(listAllTweets) < 1): continue tweetsDict = m.getTweetsDict(listAllTweets) score = m.getOverallScore(tweetsDict) if (score == -1): continue tweetsonlyscore = m.tweetsOnlyScore(tweetsDict) scoremax = m.getHappiestTweet(tweetsonlyscore) scoremin = m.getSaddestTweet(tweetsonlyscore) dict = { username: { "score": score, "min": scoremin["score"], "max": scoremax["score"] } } df = pd.DataFrame.from_dict(dict, orient='index') df.index.name = 'username' df.to_sql('germany_users', con=engine, if_exists='append') engine.execute( "DELETE FROM germany_users T1 USING germany_users T2 WHERE T1.ctid < T2.ctid AND T1.username = T2.username;" ) engine.dispose()
def delete(): ''' Delete images ''' # if img['dmca'] == 1 or img['illegal'] == 1: # print board + '/' + img['threadID'] + "/" + img['local_thumbnail'] # query = '''UPDATE {0}_mod SET imgur_thumbnail_url = 'test' WHERE local_thumbnail = %s'''.format(board) # engine.execute(query, img['local_thumbnail']) # to_delete = ["692LfMR1HKotuic","nbxl2ZAcOTO2eoz", "nrsNcrYqUV0A9rH"] to_delete = engine.execute("SELECT * FROM sci_mod") # for row in to_delete: # if row['dmca'] == 1 or row['illegal'] == 1: # print "something" for row in to_delete: if (row['dmca'] == 1 or row['illegal'] == 1) and (row['imgur_thumbnail_url'] is not None or row['imgur_image_url'] is not None): # client.delete_image(img) # # These code snippets use an open-source library. # These code snippets use an open-source library. http://unirest.io/python response = unirest.delete("https://imgur-apiv3.p.mashape.com/3/image/{}".format(row['imgur_deletehash']), headers={ "X-Mashape-Key": "y4mVnnNiZBmshBb9s7rh4DSw6K53p1T5SDujsnxxEUEvw62m4L", "Authorization": "Client-ID 999ee789e084f2e", "Accept": "text/plain" } ) print response.body['status'] if response.body['status'] == 200: print "was successful"
def delete(): ''' Delete images ''' to_delete = engine.execute("SELECT * FROM sci_mod") for row in to_delete: if (row['dmca'] == 1 or row['illegal'] == 1) and (row['imgur_thumbnail_url'] is not None or row['imgur_image_url'] is not None): # client.delete_image(img) # # These code snippets use an open-source library. # These code snippets use an open-source library. http://unirest.io/python response = unirest.delete("https://imgur-apiv3.p.mashape.com/3/image/{}".format(row['imgur_deletehash']), headers={ "X-Mashape-Key": "y4mVnnNiZBmshBb9s7rh4DSw6K53p1T5SDujsnxxEUEvw62m4L", "Authorization": "Client-ID 999ee789e084f2e", "Accept": "text/plain" } ) print response.body['status'] if response.body['status'] == 200: print "was successful"
def state_abbreviated(): """ This function produces just a state abbreviation. eg - state_abbreviated() = 'NY' """ res = engine.execute("SELECT abbreviation FROM states " "ORDER BY random() LIMIT 1;") return AttrDict([dict(d) for d in res.fetchall()][0])
def city_with_state(): """ This function produces a city with a state. ie - city_with_state() = 'New York, NY' """ res = engine.execute("SELECT city, state FROM us_cities " "ORDER BY random() LIMIT 1;") return ', '.join(res.fetchall()[0])
def putDataDB(): global engine api = configscript.setupTwitterAuth() places = api.geo_search(query="Denmark", granularity="country") place_id = places[0].id tweets = tw.Cursor(api.search, q="place:%s" % place_id, tweet_mode='extended', lang='en').items() df = pd.DataFrame.from_dict(m.getTweetsDictRaw(tweets), orient='index') df.set_index('id', inplace=True) df.to_sql('tweets', con=engine, if_exists='append') engine.execute( "DELETE FROM tweets T1 USING tweets T2 WHERE T1.ctid < T2.ctid AND T1.id = T2.id AND T1.score = T2.score AND T1.created = T2.created;" ) engine.dispose()
def _populate_lineage(self) -> None: url = self.config.get_sql_alchemy_url() logger.debug(f"sql_alchemy_url={url}") engine = create_engine( url, connect_args=self.config.get_sql_alchemy_connect_args(), **self.config.options, ) query: str = """ WITH table_lineage_history AS ( SELECT r.value:"objectName" AS upstream_table_name, r.value:"objectDomain" AS upstream_table_domain, r.value:"columns" AS upstream_table_columns, w.value:"objectName" AS downstream_table_name, w.value:"objectDomain" AS downstream_table_domain, w.value:"columns" AS downstream_table_columns, t.query_start_time AS query_start_time FROM (SELECT * from snowflake.account_usage.access_history) t, lateral flatten(input => t.DIRECT_OBJECTS_ACCESSED) r, lateral flatten(input => t.OBJECTS_MODIFIED) w WHERE r.value:"objectId" IS NOT NULL AND w.value:"objectId" IS NOT NULL AND w.value:"objectName" NOT LIKE '%.GE_TMP_%' AND w.value:"objectName" NOT LIKE '%.GE_TEMP_%' AND t.query_start_time >= to_timestamp_ltz({start_time_millis}, 3) AND t.query_start_time < to_timestamp_ltz({end_time_millis}, 3)) SELECT upstream_table_name, downstream_table_name, upstream_table_columns, downstream_table_columns FROM table_lineage_history WHERE upstream_table_domain in ('Table', 'External table') and downstream_table_domain = 'Table' QUALIFY ROW_NUMBER() OVER (PARTITION BY downstream_table_name, upstream_table_name ORDER BY query_start_time DESC) = 1 """.format( start_time_millis=int(self.config.start_time.timestamp() * 1000), end_time_millis=int(self.config.end_time.timestamp() * 1000), ) num_edges: int = 0 self._lineage_map = defaultdict(list) try: for db_row in engine.execute(query): # key is the down-stream table name key: str = db_row[1].lower().replace('"', "") self._lineage_map[key].append( # (<upstream_table_name>, <json_list_of_upstream_columns>, <json_list_of_downstream_columns>) (db_row[0].lower().replace('"', ""), db_row[2], db_row[3])) num_edges += 1 logger.debug( f"Lineage[Table(Down)={key}]:Table(Up)={self._lineage_map[key]}" ) except Exception as e: logger.warning( f"Extracting lineage from Snowflake failed." f"Please check your premissions. Continuing...\nError was {e}." ) logger.info( f"A total of {num_edges} Table->Table edges found" f" for {len(self._lineage_map)} downstream tables.", ) self.report.num_table_to_table_edges_scanned = num_edges
def rowExists(engine, primKey, tableName, primColName): primKey = str(primKey) query = "SELECT * FROM %s WHERE %s=\'%s\'" % (tableName, primColName, primKey) result = engine.execute(query) count = 0 for row in result: count += 1 return bool(count)
def batchInsert(engine): connection = engine.connect() fd = open('sampledata.sql', 'r') sqlFile = fd.read() fd.close() # all SQL commands (split on ';') sqlCommands = sqlFile.split(';') # Execute every command from the input file for command in sqlCommands: # This will skip and report errors # For example, if the tables do not yet exist, this will skip over # the DROP TABLE commands try: engine.execute(command) except: print "error" connection.close()
def db_name(db_host: str, db_user: str) -> typing.Iterable[str]: admin_user = os.environ.get('TRIBBLE_DB_ADMIN_USER', 'root') admin_password = os.environ.get('TRIBBLE_DB_ADMIN_PASSWORD') creds = database.Creds(host=db_host, user=admin_user, password=admin_password, database='mysql') with warnings.catch_warnings(): warnings.filterwarnings('ignore', ".*\'@@tx_isolation\' is deprecated.*") engine = database.connect_db(creds) connection = engine.connect() db_names = connection.execute('SHOW DATABASES;').fetchall() for (db_name, ) in db_names: if db_name.startswith('tribble_test_'): engine.execute(f'DROP SCHEMA {db_name}') database_name = 'tribble_test_{0:0>6}'.format( random.randrange(1, 1000000)) connection.execute(f'CREATE SCHEMA {database_name};') connection.execute(f'USE {database_name};') connection.execute( f'GRANT ALL ON {database_name}.* TO {db_user}@{db_host};') connection.execute('FLUSH PRIVILEGES;') connection.close() yield database_name connection = engine.connect() connection.execute(f'DROP SCHEMA {database_name};') connection.execute( f'REVOKE ALL ON {database_name}.* FROM {db_user}@{db_host};') connection.execute('FLUSH PRIVILEGES;') connection.close()
def zipcode(state=None): """This function will pick a zipcode randomnly from a list. eg - zipcode() = '11221'. """ range_gen = [] state = state or state_abbreviated().abbreviation cmd = 'SELECT min, max FROM zipcodes WHERE st = :_state' res = engine.execute(text(cmd), _state=state) _range = res.fetchall() for r in _range: range_gen.extend(range(int(r[0]), int(r[1] + 1))) return '%05d' % choice(range_gen)
def male(): """Generate a 'male' name. Returns: A string. Example: >>> name() 'Anthony' """ res = engine.execute("SELECT name FROM male ORDER BY random() LIMIT 1;") return AttrDict([dict(d) for d in res.fetchall()][0])
def test_retval_flag(self): canary = [] def tracker(name): def go(conn, *args, **kw): canary.append(name) return go def execute(conn, clauseelement, multiparams, params): canary.append("execute") return clauseelement, multiparams, params def cursor_execute( conn, cursor, statement, parameters, context, executemany ): canary.append("cursor_execute") return statement, parameters engine = engines.testing_engine() assert_raises( tsa.exc.ArgumentError, event.listen, engine, "begin", tracker("begin"), retval=True, ) event.listen(engine, "before_execute", execute, retval=True) event.listen( engine, "before_cursor_execute", cursor_execute, retval=True ) with testing.expect_deprecated( r"The argument signature for the " r"\"ConnectionEvents.before_execute\" event listener", ): engine.execute(select(1)) eq_(canary, ["execute", "cursor_execute"])
def get_threads(url, board, dir): engine = create_engine('mysql+pymysql://root:magical18' '@localhost/chanarive') print engine # metadata = MetaData() # chanarive = Table('b_temp', metadata, # Column('id', Integer(), primary_key=True), # Column('threadID', String()), # Column('position', Integer()), # Column('previous_position', Integer()), # # ) # metadata.create_all(engine) # ins = insert('b_temp').values( # # post_id is auto inserted # threadID='324325', # position=4, # previous_position=64, # # ) # insert into database the parsed logic # engine.execute("INSERT INTO b_temp (threadID,position,previous_position) VALUES ('2343', 34, 54)") # rows = 0 # item = engine.execute("SELECT * FROM b_temp WHERE threadID = '689718729'") # num_rows = engine.execute("SELECT COUNT(*) FROM b_temp") # for i in num_rows: # rows = i[0] engine.execute("UPDATE b_temp SET remove = 1 WHERE remove = 0") engine.execute("UPDATE b_temp SET bot = 0 WHERE bot = 1") engine.execute("UPDATE b_temp SET previous_position = current_position") # creat a for each loop of all parsed threads items = engine.execute("SELECT IF ( EXISTS( SELECT * FROM b_temp WHERE threadID = '{}'), 1, 0)".format('690s617627')) # items = engine.execute("SELECT remove FROM b_temp") # print len(items) for i in items: if i[0]: # update rank print "It exists!" else: # insert into database print "404!"
def gender(extended=False): """Returns a random gender. Argument: extended (bool): Returns from Female or Male if False. if True, returns from 50+ genders. """ if extended: res = engine.execute("SELECT gender FROM gender_extended " "ORDER BY random() LIMIT 1;") return AttrDict([dict(x) for x in res.fetchall()][0]) else: return choice(['Male', 'Female'])
def lat_long(state="NY", radius=150000): """Generates a random latitude, and longitude. The lat and long will be within the radius, of the state of your choice. Note: The radius seems to need to be over 150,000 to produce a difference from the original lat and long. Arguments: state (str): The 2 letter abbreviation for the state of your choosing. radius (int): The radius which will be used to generate a lat and long inside of. Returns: abbrev (string): The 2 letter abbreviation of the chosen state. lat (string): A generated latitude of varying length. long (string): A generated longetude of varying length. Examples: >>> d_gen.lat_long("NY", 150000) AttrDict({ u'lat': '41.9656885445', u'abbrev': u'NY', u'long': '-75.9459285158' }) >>> lat_long("NY", 175000).lat '43.0438318157' """ cmd = 'SELECT abbrev,lat,long FROM us_s_ll WHERE abbrev = :_st LIMIT 1;' res = engine.execute(text(cmd), _st=state) data = AttrDict([dict(d) for d in res.fetchall()][0]) radius_in_degrees = radius / 111300 x0 = float(data["long"]) y0 = float(data["lat"]) u = round(uniform(0.1, 1.0), 6) v = round(uniform(0.1, 1.0), 6) w = radius_in_degrees * sqrt(u) t = 2 * pi * v x = w * cos(t) y1 = w * sin(t) x1 = x / cos(y0) obj = LatLon(y0 + y1, x0 + x1).to_string() data["lat"] = obj[0] data["long"] = obj[1] return data
def mime_type(): """Generates a random mime type. Returns: name (str): The full name of the mime type. extension (str): The file extension of the mime type. Examples: >>> mime_type() AttrDict({u'name': u'application.x-excel', u'extension': u'.xlv'}) """ res = engine.execute("SELECT name, extension FROM mimes ORDER BY \ random() LIMIT 1;") return AttrDict([dict(d) for d in res.fetchall()][0])
def celebrityScore(username): print(username) global engine api = configscript.setupTwitterAuth() allTweets = tw.Cursor(api.user_timeline, screen_name=username, tweet_mode="extended", exclude_replies=False, include_rts=False, lang='en').items() listAllTweets = list(allTweets) if (len(listAllTweets) == 0): return tweetsDict = m.getTweetsDict(listAllTweets) score = m.getOverallScore(tweetsDict) if (score == -1): return user = api.get_user(username) # Remove _normal from profile image URL profile_image_url = user.profile_image_url_https url = re.sub('_normal', '', profile_image_url) dict = {username: {"score": score, "pic": url}} df = pd.DataFrame.from_dict(dict, orient='index') df.index.name = 'username' df.to_sql('celebrity', con=engine, if_exists='append') engine.execute( "DELETE FROM celebrity T1 USING celebrity T2 WHERE T1.ctid < T2.ctid AND T1.username = T2.username;" ) engine.dispose()
def areacode(state=None): """Returns a random zipcode from a list of US zipcodes. Argument: state (str): 2 letter state abbreviation. Returns: areacode (unicode): 3 digit area code. Examples: >>> areacode() '810' >>> areacode('NY') '718' """ if state: cmd = 'SELECT areacode, state FROM areacodes WHERE state = :_state ' \ 'ORDER BY RANDOM() LIMIT 1;' res = engine.execute(text(cmd), _state=state) else: res = engine.execute( 'SELECT areacode, state FROM areacodes ORDER BY random() LIMIT 1;' ) return AttrDict([dict(d) for d in res.fetchall()][0])
def timezone_offset(dst=True, utc=True): # Todo: Set both to false by default. """Generates a random timezone offset. Arguments: dst (bool): Enable dst selection. utc (bool): Enable utc offset. Returns: dst: Returns a dst offset. utc: Returns a utc offset. Examples: >>> timezone_offset() {'utc': u'+06:30', 'dst': u'-03:00'} >>> timezone_offset(dst=False) {'utc': u'+10:00'} >>> timezone_offset(utc=False) {'dst': u'-03:00'} >>> timezone_offset(utc=False, dst=False) {} >>> timezone_offset(utc=False).dst '-4:30' """ data = {} if dst: res = engine.execute("SELECT DISTINCT(dst) FROM timezones ORDER BY " "random() LIMIT 1;") data["dst"] = res.fetchall()[0][0] if utc: res = engine.execute("SELECT DISTINCT(utc) FROM timezones ORDER BY " "random() LIMIT 1;") data["utc"] = res.fetchall()[0][0] return AttrDict(data)
def timezone_offset_country(): """Generates a random country from the timezone country list. Returns: country (str): Name of the generated country. Examples: >>> timezone_offset_country() AttrDict({'country': u'Maldives'}) >>> timezone_offset_country().country AttrDict({'country': u'Maldives'}) """ res = engine.execute("SELECT country FROM timezones ORDER BY random() " "LIMIT 1;") return AttrDict({"country": res.fetchall()[0][0]})
def _mysql_get_effective_sql_mode(engine): """Returns the effective SQL mode for connections from the engine pool. Returns ``None`` if the mode isn't available, otherwise returns the mode. """ # Get the real effective SQL mode. Even when unset by # our own config, the server may still be operating in a specific # SQL mode as set by the server configuration. # Also note that the checkout listener will be called on execute to # set the mode if it's registered. row = engine.execute("SHOW VARIABLES LIKE 'sql_mode'").fetchone() if row is None: return return row[1]
def _populate_view_upstream_lineage(self, engine: sqlalchemy.engine.Engine) -> None: # NOTE: This query captures only the upstream lineage of a view (with no column lineage). # For more details see: https://docs.snowflake.com/en/user-guide/object-dependencies.html#object-dependencies # and also https://docs.snowflake.com/en/sql-reference/account-usage/access_history.html#usage-notes for current limitations on capturing the lineage for views. view_upstream_lineage_query: str = """ SELECT concat( referenced_database, '.', referenced_schema, '.', referenced_object_name ) AS view_upstream, concat( referencing_database, '.', referencing_schema, '.', referencing_object_name ) AS downstream_view FROM snowflake.account_usage.object_dependencies WHERE referencing_object_domain in ('VIEW', 'MATERIALIZED VIEW') """ assert self._lineage_map is not None num_edges: int = 0 try: for db_row in engine.execute(view_upstream_lineage_query): # Process UpstreamTable/View/ExternalTable/Materialized View->View edge. view_upstream: str = db_row["view_upstream"].lower() view_name: str = db_row["downstream_view"].lower() if not self._is_dataset_allowed(dataset_name=view_name, is_view=True): continue # key is the downstream view name self._lineage_map[view_name].append( # (<upstream_table_name>, <empty_json_list_of_upstream_table_columns>, <empty_json_list_of_downstream_view_columns>) (view_upstream, "[]", "[]") ) num_edges += 1 logger.debug( f"Upstream->View: Lineage[View(Down)={view_name}]:Upstream={view_upstream}" ) except Exception as e: self.warn( logger, "view_upstream_lineage", "Extracting the upstream view lineage from Snowflake failed." + f"Please check your permissions. Continuing...\nError was {e}.", ) logger.info(f"A total of {num_edges} View upstream edges found.") self.report.num_table_to_view_edges_scanned = num_edges
def update_all_slugs_in_mtgmetaio(card_tname=MTGMETA_CARDS_TNAME, engine=engine) -> None: logger.info("Updating card slugs") for old, new in get_missing().items(): logger.debug(f"{old}: {new}") query = f""" UPDATE "{card_tname}" SET "card_slug" = REPLACE ( "card_slug", '{old}', '{new}' ) WHERE "card_slug"='{old}' """ a = engine.execute(query) logger.debug(a)
def html_name(): """Generates a random html color name. Returns: name (str): The name of the picked color. hex (str): The corresponding hex code of the color. Examples: >>> html_name() AttrDict({u'hex': u'80008', u'name': u'purple'}) >>> html_name().hex '808080' """ res = engine.execute( "SELECT name, hex FROM html_colors ORDER BY RANDOM() LIMIT 1;" ) return AttrDict([dict(d) for d in res.fetchall()][0])
def phone_number(state=None): """Generates a phone number in multiple formats. Conforms to NANP standards. Argument: state (string): Returns a phone number from an areacode in this specified state. Returns: areacode (string): 3 digit area code. domestic (string): Phone number formatted to the domestic dial standard. international (string): Phone number formatted to the international dial standard. local (string): Phone number formatted to the local dial standard. plain (string): Phone number without formatting. standard (string): Phone number formatted to the standard dial standard. state (string): The state the phone number corresponds to. Examples: >>> phone_number() AttrDict({ 'areacode': '562', 'domestic': '(562) 422-9802', 'international': '+1-562-422-9802', 'local': '422-9802', 'plain': '5624229802', 'standard': '562-422-9802' 'state': u'CA', }) >>> phone_number().state 'NY' >>> phone_number().international '+1-574-720-9722' >>> phone_number("NY").domestic '718-288-1000' """ data = {} valid_number = False invalid_prefixes = ["911", "555", "311", "411"] # If the number that we generate appears in the invalid_prefixes # list, then we will regenerate until the chosen number is not. a, b, c = (False,) * 3 while not valid_number: a = str(areacode(state).areacode) if state else str( areacode().areacode) b = str(randint(2, 9)) + str(number(2)) if a or b not in invalid_prefixes: break # Tack on 4 digits to the end. c = number(4) # Enter our data in to a dict. data["areacode"] = a data["local"] = "{0}-{1}".format(b, c) data["domestic"] = "({0}) {1}-{2}".format(a, b, c) data["international"] = "+1-{0}-{1}-{2}".format(a, b, c) data["standard"] = "{0}-{1}-{2}".format(a, b, c) data["plain"] = a + b + c if state: cmd = 'SELECT state FROM areacodes WHERE state = :_state' res = engine.execute(text(cmd), _state=state) for d in res.fetchall(): data["state"] = d[0] return AttrDict(data)
def language(): """Picks a random language.""" res = engine.execute( "SELECT name FROM languages ORDER BY random() LIMIT 1;") return AttrDict([dict(d) for d in res.fetchall()][0])
# initialize columns self.item_price_total = item_price_total self.item_quantity_total = item_quantity_total def __repr__(self): return '<shoppingCart %r>' % self.shoppingCart_id class Shoppingcartitems(db.Model): __tablename__ = 'Shopping_cart_items' shopping_cartId = db.Column(db.Integer, db.ForeignKey('shoppingCart.shoppingCart_id'), nullable=False, primary_key=True) shoppingCart = db.relationship("shoppingcart", backref=db.backref("shoppingCart", uselist=False)) item_id = db.Column(db.Integer, db.ForeignKey('Items.item_id'), nullable=False, primary_key=True) Items = db.relationship("Items", backref=db.backref("Items", uselist=False)) def __init__(self, shopping_cartId, item_id): # initialize columns self.shopping_cartId = shopping_cartId self.item_id = item_id # connect to server engine = create_engine('mysql://%s:%s@%s' % (USER, PASSWORD, HOSTNAME)) # create db if not exists engine.execute("CREATE DATABASE IF NOT EXISTS %s " % (DATABASE)) db.create_all() if __name__ == '__main__': manager.run()
def do_provision_role_internal(self): provision_role_block = self.config.provision_role if provision_role_block is None: return self.report.provision_role_done = not provision_role_block.dry_run role = self.config.role if role is None: role = "datahub_role" self.warn( logger, "role-grant", f"role not specified during provision role using {role} as default", ) self.report.role = role warehouse = self.config.warehouse logger.info("Creating connection for provision_role") engine = self.get_metadata_engine(database=None) sqls: List[str] = [] if provision_role_block.drop_role_if_exists: sqls.append(f"DROP ROLE IF EXISTS {role}") sqls.append(f"CREATE ROLE IF NOT EXISTS {role}") if warehouse is None: self.warn(logger, "role-grant", "warehouse not specified during provision role") else: sqls.append( f"grant operate, usage on warehouse {warehouse} to role {role}" ) for inspector in self.get_inspectors(): db_name = self.get_db_name(inspector) sqls.extend([ f"grant usage on DATABASE {db_name} to role {role}", f"grant usage on all schemas in database {db_name} to role {role}", f"grant usage on future schemas in database {db_name} to role {role}", ]) if self.config.profiling.enabled: sqls.extend([ f"grant select on all tables in database {db_name} to role {role}", f"grant select on future tables in database {db_name} to role {role}", f"grant select on all external tables in database {db_name} to role {role}", f"grant select on future external tables in database {db_name} to role {role}", f"grant select on all views in database {db_name} to role {role}", f"grant select on future views in database {db_name} to role {role}", ]) else: sqls.extend([ f"grant references on all tables in database {db_name} to role {role}", f"grant references on future tables in database {db_name} to role {role}", f"grant references on all external tables in database {db_name} to role {role}", f"grant references on future external tables in database {db_name} to role {role}", f"grant references on all views in database {db_name} to role {role}", f"grant references on future views in database {db_name} to role {role}", ]) if self.config.username is not None: sqls.append(f"grant role {role} to user {self.config.username}") if self.config.include_table_lineage or self.config.include_view_lineage: sqls.append( f"grant imported privileges on database snowflake to role {role}" ) dry_run_str = "[DRY RUN] " if provision_role_block.dry_run else "" for sql in sqls: logger.info(f"{dry_run_str} Attempting to run sql {sql}") if provision_role_block.dry_run: continue try: engine.execute(sql) except Exception as e: self.error(logger, "role-grant", f"Exception: {e}") self.report.provision_role_success = not provision_role_block.dry_run
def surname(): """Returns a randomly chosen surname.""" res = engine.execute("SELECT name FROM surname " "ORDER BY random() LIMIT 1;") return AttrDict([dict(d) for d in res.fetchall()][0])
def career(): """This function will produce a career.""" res = engine.execute("SELECT name FROM careers ORDER BY random() LIMIT 1;") return AttrDict([dict(d) for d in res.fetchall()][0])
deletehash = response.body['data']['deletehash'] query_string = '''UPDATE {0}_mod set imgur_id = %s, imgur_image_url = %s, imgur_deletehash = %s WHERE local_thumbnail = %s'''.format(board) engine.execute(query_string, imgur_image_id, imgur_image_url, deletehash, image_name) except Exception as e: print e pass for board in boards: # Runs through and determines if a row either needs an upload of an image or a deletion board_rows = engine.execute("SELECT * FROM {0}_mod".format(board)) for img in board_rows: # print "another row" if (img['moderated'] == 1 and img['approved'] == 1) and img['imgur_thumbnail_url'] is None and img['local_thumbnail'] is not None: # print "Upload the images and update the database" upload(img['local_thumbnail'], board) # delete() # Runs through entire database for any deletables # delete()
def company_name(): """This function will return a company name""" res = engine.execute( "SELECT name FROM companies ORDER BY random() LIMIT 1;") return AttrDict([dict(d) for d in res.fetchall()][0])
def _populate_view_downstream_lineage( self, engine: sqlalchemy.engine.Engine) -> None: # NOTE: This query captures both the upstream and downstream table lineage for views. # We need this query to populate the downstream lineage of a view, # as well as to delete the false direct edges between the upstream and downstream tables of a view. # See https://docs.snowflake.com/en/sql-reference/account-usage/access_history.html#usage-notes for current limitations on capturing the lineage for views. # Eg: For viewA->viewB->ViewC->TableD, snowflake does not yet log intermediate view logs, resulting in only the viewA->TableD edge. view_lineage_query: str = """ WITH view_lineage_history AS ( SELECT vu.value : "objectName" AS view_name, vu.value : "objectDomain" AS view_domain, vu.value : "columns" AS view_columns, r.value : "objectName" AS upstream_table_name, r.value : "objectDomain" AS upstream_table_domain, r.value : "columns" AS upstream_table_columns, w.value : "objectName" AS downstream_table_name, w.value : "objectDomain" AS downstream_table_domain, w.value : "columns" AS downstream_table_columns, t.query_start_time AS query_start_time FROM ( SELECT * FROM snowflake.account_usage.access_history ) t, lateral flatten(input => t.DIRECT_OBJECTS_ACCESSED) vu, lateral flatten(input => t.BASE_OBJECTS_ACCESSED) r, lateral flatten(input => t.OBJECTS_MODIFIED) w WHERE vu.value : "objectId" IS NOT NULL AND r.value : "objectId" IS NOT NULL AND w.value : "objectId" IS NOT NULL AND t.query_start_time >= to_timestamp_ltz({start_time_millis}, 3) AND t.query_start_time < to_timestamp_ltz({end_time_millis}, 3) ) SELECT view_name, view_columns, upstream_table_name, upstream_table_domain, upstream_table_columns, downstream_table_name, downstream_table_domain, downstream_table_columns FROM view_lineage_history WHERE view_domain in ('View', 'Materialized view') AND view_name != upstream_table_name AND upstream_table_name != downstream_table_name AND view_name != downstream_table_name QUALIFY ROW_NUMBER() OVER ( PARTITION BY view_name, upstream_table_name, downstream_table_name ORDER BY query_start_time DESC ) = 1 """.format( start_time_millis=int(self.config.start_time.timestamp() * 1000), end_time_millis=int(self.config.end_time.timestamp() * 1000), ) assert self._lineage_map is not None num_edges: int = 0 num_false_edges: int = 0 try: for db_row in engine.execute(view_lineage_query): # We get two edges here. # (1) False UpstreamTable->Downstream table that will be deleted. # (2) View->DownstreamTable that will be added. view_name: str = db_row[0].lower().replace('"', "") upstream_table: str = db_row[2].lower().replace('"', "") downstream_table: str = db_row[5].lower().replace('"', "") # (1) Delete false direct edge between upstream_table and downstream_table prior_edges: List[Tuple[ str, str, str]] = self._lineage_map[downstream_table] self._lineage_map[downstream_table] = [ entry for entry in self._lineage_map[downstream_table] if entry[0] != upstream_table ] for false_edge in set(prior_edges) - set( self._lineage_map[downstream_table]): logger.debug( f"False Table->Table edge removed: Lineage[Table(Down)={downstream_table}]:Table(Up)={false_edge}." ) num_false_edges += 1 # (2) Add view->downstream table lineage. self._lineage_map[downstream_table].append( # (<upstream_view_name>, <json_list_of_upstream_view_columns>, <json_list_of_downstream_columns>) (view_name, db_row[1], db_row[7])) logger.debug( f"View->Table: Lineage[Table(Down)={downstream_table}]:View(Up)={self._lineage_map[downstream_table]}, downstream_domain={db_row[6]}" ) num_edges += 1 except Exception as e: logger.warning( f"Extracting the view lineage from Snowflake failed." f"Please check your permissions. Continuing...\nError was {e}." ) logger.info( f"Found {num_edges} View->Table edges. Removed {num_false_edges} false Table->Table edges." ) self.report.num_view_to_table_edges_scanned = num_edges
def _populate_view_downstream_lineage( self, engine: sqlalchemy.engine.Engine) -> None: # This query captures the downstream table lineage for views. # See https://docs.snowflake.com/en/sql-reference/account-usage/access_history.html#usage-notes for current limitations on capturing the lineage for views. # Eg: For viewA->viewB->ViewC->TableD, snowflake does not yet log intermediate view logs, resulting in only the viewA->TableD edge. view_lineage_query: str = """ WITH view_lineage_history AS ( SELECT vu.value : "objectName" AS view_name, vu.value : "objectDomain" AS view_domain, vu.value : "columns" AS view_columns, w.value : "objectName" AS downstream_table_name, w.value : "objectDomain" AS downstream_table_domain, w.value : "columns" AS downstream_table_columns, t.query_start_time AS query_start_time FROM ( SELECT * FROM snowflake.account_usage.access_history ) t, lateral flatten(input => t.DIRECT_OBJECTS_ACCESSED) vu, lateral flatten(input => t.OBJECTS_MODIFIED) w WHERE vu.value : "objectId" IS NOT NULL AND w.value : "objectId" IS NOT NULL AND w.value : "objectName" NOT LIKE '%.GE_TMP_%' AND w.value : "objectName" NOT LIKE '%.GE_TEMP_%' AND t.query_start_time >= to_timestamp_ltz({start_time_millis}, 3) AND t.query_start_time < to_timestamp_ltz({end_time_millis}, 3) ) SELECT view_name, view_columns, downstream_table_name, downstream_table_columns FROM view_lineage_history WHERE view_domain in ('View', 'Materialized view') QUALIFY ROW_NUMBER() OVER ( PARTITION BY view_name, downstream_table_name ORDER BY query_start_time DESC ) = 1 """.format( start_time_millis=int(self.config.start_time.timestamp() * 1000) if not self.config.ignore_start_time_lineage else 0, end_time_millis=int(self.config.end_time.timestamp() * 1000), ) assert self._lineage_map is not None self.report.num_view_to_table_edges_scanned = 0 try: db_rows = engine.execute(view_lineage_query) except Exception as e: self.warn( logger, "view_downstream_lineage", f"Extracting the view lineage from Snowflake failed." f"Please check your permissions. Continuing...\nError was {e}.", ) else: for db_row in db_rows: view_name: str = db_row["view_name"].lower().replace('"', "") downstream_table: str = ( db_row["downstream_table_name"].lower().replace('"', "")) # Capture view->downstream table lineage. self._lineage_map[downstream_table].append( # (<upstream_view_name>, <json_list_of_upstream_view_columns>, <json_list_of_downstream_columns>) ( view_name, db_row["view_columns"], db_row["downstream_table_columns"], )) self.report.num_view_to_table_edges_scanned += 1 logger.debug( f"View->Table: Lineage[Table(Down)={downstream_table}]:View(Up)={self._lineage_map[downstream_table]}" ) logger.info( f"Found {self.report.num_view_to_table_edges_scanned} View->Table edges." )
# print header_html # thread = "" # for i in header_html: # print i # print str(i[0]) # thread += str(i[0]) # # thread_to_generate = engine.execute("SELECT post_message FROM sci WHERE threadID = '{}'".format(thread_num)) # for i in thread_to_generate: # thread += i[0] thread_number = engine.execute("SELECT DISTINCT threadID FROM sci ORDER BY threadID DESC") for i in thread_number: thread_num = i[0] posts = engine.execute("SELECT position_in_thread, post_message FROM sci WHERE threadID = {} ORDER BY position_in_thread ASC".format(i[0])) thread = "" header_html = engine.execute("SELECT header_html FROM header WHERE boardname = 'sci'") for i in header_html: thread += i[0] for j in posts: # print j[1] thread += j[1] file = open('/var/www/{}/{}.html'.format(board,thread_num), 'w')
def upload(image_name, board): # This will upload the file query_string = '''SELECT * FROM {0}_mod WHERE local_thumbnail = %s'''.format(board) row = engine.execute(query_string, image_name) for item in row: # necessary variables threadID = item['threadID'] thumbnail = item['local_thumbnail'] image = item['local_image'] directory_thumb = '/' + board + '/' + threadID + '/' + thumbnail directory_image = '/' + board + '/' + threadID + '/' + image """ Upload the thumbnail """ try: response = unirest.post("https://imgur-apiv3.p.mashape.com/3/image", headers={ "X-Mashape-Key": "y4mVnnNiZBmshBb9s7rh4DSw6K53p1T5SDujsnxxEUEvw62m4L", "Authorization": "Client-ID 999ee789e084f2e", "Content-Type": "application/x-www-form-urlencoded", "Accept": "application/json" }, params={ "image": open(os.path.dirname(os.path.realpath(__file__)) + directory_thumb, mode='r') } ) # print response.body['data']['id'] print response.body['data']['link'] print response.body['data']['deletehash'] # try: image_thumbnail_id = response.body['data']['id'] imgur_thumbnail_url = response.body['data']['link'] deletehash = response.body['data']['deletehash'] query_string = '''UPDATE {0}_mod set imgur_thumbnail_id = %s, imgur_thumbnail_url = %s, imgur_thumbnail_deletehash = %s WHERE local_thumbnail = %s'''.format(board) engine.execute(query_string, image_thumbnail_id, imgur_thumbnail_url, deletehash, image_name) except Exception as e: print e pass except: pass """ Upload the main image """ try: response = unirest.post("https://imgur-apiv3.p.mashape.com/3/image", headers={ "X-Mashape-Key": "y4mVnnNiZBmshBb9s7rh4DSw6K53p1T5SDujsnxxEUEvw62m4L", "Authorization": "Client-ID 999ee789e084f2e", "Content-Type": "application/x-www-form-urlencoded", "Accept": "application/json" }, params={ "image": open(os.path.dirname(os.path.realpath(__file__)) + directory_image, mode='r') } ) # print response.body['data']['id'] print response.body['data']['link'] print response.body['data']['deletehash'] # # try: imgur_image_id = response.body['data']['id'] imgur_image_url = response.body['data']['link'] deletehash = response.body['data']['deletehash'] query_string = '''UPDATE {0}_mod set imgur_id = %s, imgur_image_url = %s, imgur_deletehash = %s WHERE local_thumbnail = %s'''.format(board) engine.execute(query_string, imgur_image_id, imgur_image_url, deletehash, image_name) except Exception as e: print e pass
def unit_type(): res = engine.execute( "SELECT name, abbreviation FROM us_unit_types " "ORDER BY random() LIMIT 1;" ) return AttrDict([dict(d) for d in res.fetchall()][0])
# boards = ['a','c','w','m','cgl','cm','n','jp','v','vg','vp','vr','co','g','tv','k', # 'o','an','tg','sp','asp','sci','his','int','out','toy','i','po','p','ck','ic','wg','mu','fa', # '3','gd','diy','wsg','qst','biz','fit','x','lit','adv','lgbt','mlp','news','wsr','b','r9k','pol','soc','s4s', # 's','hc','hm','h','e','u','d','y','t','hr','gif','aco','r'] boards = ['b','sci','s'] for board in boards: catalog_ranker_check.check_catalog(board) threads_that_need_to_be_botted = engine.execute("SELECT threadID FROM {}_catalog WHERE bot = 1 LIMIT 100".format(board)) for urls in threads_that_need_to_be_botted: if not os.path.exists(board + '/' + urls[0]): os.makedirs(board + '/' + urls[0]) browser = webdriver.PhantomJS(executable_path='/root/anaconda2/bin/phantomjs') # or add to your PATH browser.get('http://boards.4chan.org/{}/thread/{}/'.format(board, urls[0])) browser.set_page_load_timeout(3000) response = browser.page_source
def create_vendors(): sql = 'DROP TABLE IF EXISTS Vendors;' engine = create_engine(os.getenv('DATABASE_URL')) result = engine.execute(sql) #sql = 'DROP TABLE IF EXISTS post;' #engine = create_engine(os.getenv('DATABASE_URL')) #result = engine.execute(sql) #sql = 'DROP TABLE IF EXISTS user;' #engine = create_engine(os.getenv('DATABASE_URL')) #result = engine.execute(sql) db.drop_all() db.create_all() u1 = User(username="******", email="*****@*****.**", password="******") db.session.add(u1) u2 = User(username="******", email="*****@*****.**", password="******") db.session.add(u2) db.session.commit() p1 = Post(title="my book", content="always true", user_id=u1.id) db.session.add(p1) p2 = Post(title="helen book", content="more true", user_id=u2.id) db.session.add(p2) db.session.commit() v1 = Vendors(vendor_active="y", vendor_type="e", vendor_company_name="Tiger Estates", vendor_contact_name="Sam Houston", vendor_email="*****@*****.**", vendor_mobile="07424454567", vendor_fax="01793 3224598", vendor_address_house_name="Villa Rosa", vendor_address_house_number="433a", vendor_address_line_1="Central Drive", vendor_address_line_2="South Shore", vendor_address_town="Blackpool", vendor_address_city="Not Applicapble", vendor_address_county="Lancashire", vendor_address_post_code="FY1 6LD", vendor_address_country="England", vendor_address_latitude="53.34", vendor_address_longitude="-1.456") db.session.add(v1) v2 = Vendors(vendor_active="n", vendor_type="p", vendor_company_name="Not Relevant", vendor_contact_name="Jade Menham", vendor_email="*****@*****.**") db.session.add(v2) v3 = Vendors(vendor_active="y", vendor_type="p", vendor_company_name="Not Relevant", vendor_contact_name="Helen Menham", vendor_email="*****@*****.**") db.session.add(v3) v4 = Vendors(vendor_active="y", vendor_type="p", vendor_company_name="Not Relevant", vendor_contact_name="Mario E Wakeham", vendor_email="*****@*****.**") db.session.add(v4) db.session.commit() #vendors = Vendors.query.filter_by(vendor_contact_name = "Jade Menham").first() vendors = Vendors.query.filter_by() # v1.print_vendors() #v2.print_vendors() #return f'The vendor type is {vendors.vendor_type} First Came On Market {vendors.vendor_onmarketdate}' return render_template('vendors.html', vendors=vendors)
# boards = ['a','c','w','m','cgl','cm','n','jp','v','vg','vp','vr','co','g','tv','k', # 'o','an','tg','sp','asp','sci','his','int','out','toy','i','po','p','ck','ic','wg','mu','fa', # '3','gd','diy','wsg','qst','biz','fit','x','lit','adv','lgbt','mlp','news','wsr','b','r9k','pol','soc','s4s', # 's','hc','hm','h','e','u','d','y','t','hr','gif','aco','r'] boards = ['asp','sci','his','int','out','toy','i','po','p','ck','ic','wg','mu','fa'] for board in boards: catalog_ranker_check.check_catalog(board) threads_that_need_to_be_botted = engine.execute("SELECT threadID FROM {}_catalog WHERE bot = 1".format(board)) for urls in threads_that_need_to_be_botted: if not os.path.exists(board + '/' + urls[0]): os.makedirs(board + '/' + urls[0]) browser = webdriver.PhantomJS(executable_path='/root/anaconda2/bin/phantomjs') # or add to your PATH browser.get('http://boards.4chan.org/{}/thread/{}/'.format(board, urls[0])) browser.set_page_load_timeout(3000) response = browser.page_source
def _populate_external_lineage(self) -> None: engine = self.get_metadata_engine(database=None) # Handles the case where a table is populated from an external location via copy. # Eg: copy into category_english from 's3://acryl-snow-demo-olist/olist_raw_data/category_english'credentials=(aws_key_id='...' aws_secret_key='...') pattern='.*.csv'; query: str = """ WITH external_table_lineage_history AS ( SELECT r.value:"locations" as upstream_locations, w.value:"objectName" AS downstream_table_name, w.value:"objectDomain" AS downstream_table_domain, w.value:"columns" AS downstream_table_columns, t.query_start_time AS query_start_time FROM (SELECT * from snowflake.account_usage.access_history) t, lateral flatten(input => t.BASE_OBJECTS_ACCESSED) r, lateral flatten(input => t.OBJECTS_MODIFIED) w WHERE r.value:"locations" IS NOT NULL AND w.value:"objectId" IS NOT NULL AND t.query_start_time >= to_timestamp_ltz({start_time_millis}, 3) AND t.query_start_time < to_timestamp_ltz({end_time_millis}, 3)) SELECT upstream_locations, downstream_table_name, downstream_table_columns FROM external_table_lineage_history WHERE downstream_table_domain = 'Table' QUALIFY ROW_NUMBER() OVER (PARTITION BY downstream_table_name ORDER BY query_start_time DESC) = 1""".format( start_time_millis=int(self.config.start_time.timestamp() * 1000) if not self.config.ignore_start_time_lineage else 0, end_time_millis=int(self.config.end_time.timestamp() * 1000), ) num_edges: int = 0 self._external_lineage_map = defaultdict(set) try: for db_row in engine.execute(query): # key is the down-stream table name key: str = db_row[1].lower().replace('"', "") self._external_lineage_map[key] |= {*json.loads(db_row[0])} logger.debug( f"ExternalLineage[Table(Down)={key}]:External(Up)={self._external_lineage_map[key]}" ) except Exception as e: logger.warning( f"Populating table external lineage from Snowflake failed." f"Please check your premissions. Continuing...\nError was {e}." ) # Handles the case for explicitly created external tables. # NOTE: Snowflake does not log this information to the access_history table. external_tables_query: str = "show external tables" try: for db_row in engine.execute(external_tables_query): key = ( f"{db_row.database_name}.{db_row.schema_name}.{db_row.name}" .lower()) self._external_lineage_map[key].add(db_row.location) logger.debug( f"ExternalLineage[Table(Down)={key}]:External(Up)={self._external_lineage_map[key]}" ) num_edges += 1 except Exception as e: self.warn( logger, "external_lineage", f"Populating external table lineage from Snowflake failed." f"Please check your premissions. Continuing...\nError was {e}.", ) logger.info(f"Found {num_edges} external lineage edges.") self.report.num_external_table_edges_scanned = num_edges
# print response.code # print response.headers # print response.body # print response.raw_body # 3nNpeJh ''' Delete images ''' # to_delete = ["692LfMR1HKotuic","nbxl2ZAcOTO2eoz", "nrsNcrYqUV0A9rH"] to_delete = engine.execute("SELECT deletehash FROM imgur") for img in to_delete: # client.delete_image(img) # # These code snippets use an open-source library. # These code snippets use an open-source library. http://unirest.io/python response = unirest.delete("https://imgur-apiv3.p.mashape.com/3/image/{}".format(img[0]), headers={ "X-Mashape-Key": "huYA3ztRaxmshy95Mcj4dTmVrMTHp1iQ858jsn3jpASEst4dig", "Authorization": "Client-ID 67d854ceaa5af4c", "Accept": "text/plain" } ) print response.body['status'] if response.body['status'] == 200: # engine.execute("DELETE FROM imgur WHERE deletehash = {}".format(img[0]))
def create_tables(app): engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI']) if not engine.dialect.has_schema(engine, 'profesores'): engine.execute(sqlalchemy.schema.CreateSchema('profesores')) db.metadata.create_all(engine) return engine
base_url = 'http://boards.4chan.org' engine = create_engine('mysql+pymysql://root:Supermon12' '@localhost/chanarchive') boards = ['sci'] for board in boards: # DELETES any duplicate entries query = '''DELETE n1 FROM {0}_mod n1, {0}_mod n2 WHERE n1.id > n2.id AND n1.threadID = n2.threadID AND n1.position_in_thread = n2.position_in_thread'''.format(board) result = engine.execute(query) query = '''SELECT count(position_in_thread) FROM {0}_mod WHERE threadID = 8189766'''.format(board) result = engine.execute(query) for i in result: print i[0] query = '''SELECT post_message FROM {0}_mod WHERE threadID = 8180660'''.format(board) result = engine.execute(query) # # # for i in result: # soup = BeautifulSoup(str(i[0]), 'lxml') # item = soup.find_all('div') # for i in item: # print str(item) + '\n\n\n'
def check_catalog(board): boards = [board] for board in boards: browser = webdriver.PhantomJS(executable_path='/root/anaconda2/bin/phantomjs') # or add to your PATH browser.get("http://boards.4chan.org/{}/catalog".format(board)) browser.set_page_load_timeout(3000) # element = browser.find_element_by_name('html') response = browser.page_source print "Hello everyone!" engine = create_engine('mysql+pymysql://root:Supermon12' '@localhost/chanarchive') soup = BeautifulSoup(response.encode('utf-8'), "lxml") # print soup # print "This is soup:" + str(soup) links =soup.find_all("div", attrs={"class": "thread"}) # print str(links) # print len(links) # for l in links: # print l soup = BeautifulSoup(str(links), "lxml") links = soup.find_all("div", attrs={"class": "thread"}) # print links # print len(links) threadList = [] rank = 1 #Set initial rank to 1 for counting engine.execute("UPDATE {}_catalog SET remove = 1 WHERE remove = 0".format(board)) engine.execute("UPDATE {}_catalog SET bot = 0 WHERE bot = 1".format(board)) engine.execute("UPDATE {}_catalog SET previous_position = current_position".format(board)) engine.execute("UPDATE {}_catalog SET current_position = 255".format(board)) for i in links: threadList.append(i['id'].split('-')[1]) thread_num = i['id'].split('-')[1] # creat a for each loop of all parsed threads thread_in_db = engine.execute("SELECT IF ( EXISTS( SELECT * FROM {}_catalog WHERE threadID = '{}'), 1, 0)".format(board,thread_num)) # items = engine.execute("SELECT remove FROM b_catalog") # print len(items) for i in thread_in_db: if i[0]: #If this is true 1 # update rank # engine.execute("UPDATE {}_catalog SET previous_position = current_position".format(board)) engine.execute("UPDATE {}_catalog SET current_position = {}, remove = 0 WHERE threadID = {}".format(board,rank, thread_num)) print "It exists!" else: engine.execute("INSERT INTO {}_catalog (threadID,current_position, remove) VALUES ({}, {}, {})".format(board,thread_num, rank, 1)) print "404!" # Update rank rank += 1 # engine.execute("DELETE from {}_catalog WHERE remove = 1".format(board)) engine.execute("UPDATE {}_catalog SET bot = 1 WHERE current_position < previous_position".format(board)) engine.execute("DELETE FROM {}_catalog WHERE remove = 1 AND bot = 0 AND current_position = 255".format(board)) print rank
This creates an html index of all the files ''' engine = create_engine('mysql+pymysql://root:magical18' '@localhost/chanarive') thread = "8166911" board = 'sci' # Get the header html header_html = engine.execute("SELECT header_html FROM header WHERE boardname = 'index'") for i in header_html: thread += i[0] # this only adds the header once # Gets the distinct threads thread_number = engine.execute("SELECT DISTINCT threadID FROM sci ORDER BY threadID DESC") # Goes through each seperate post for num in thread_number: thread_num = num[0] count_in_thread = engine.execute("SELECT COUNT(*) FROM sci WHERE threadID = {}".format(num[0])) count_pos = 0 for i in count_in_thread: count_pos = i[0]