Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- def make_JSON_creds(filename="./.usercreds.json"):
- """Creates a JSON credentials file with name <filename>"""
- # Get keys (without them showing up on screen)
- print("Enter the following credentials:")
- SSH_USERNAME = getpass("SSH USERNAME")
- SSH_PRIVATE_KEY = getpass("SSH PASSWORD")
- DB_USERNAME = getpass("DATABASE ACCESS USERNAME")
- DB_PASSWORD = getpass("DATABASE ACCESS PASSWORD")
- # Write to file. TODO: Encrypt this.
- with open(filename, "w") as the_file:
- json.dump({"SSH_USERNAME" : SSH_USERNAME,
- "SSH_PRIVATE_KEY" : SSH_PRIVATE_KEY,
- "DB_USERNAME" : DB_USERNAME,
- "DB_PASSWORD" : DB_PASSWORD}, the_file)
- def get_JSON_creds(filename="./.usercreds.json", ask=True):
- """Loads JSON credentials from a credentials file.
- Creates the file if it doesn't exist.
- Returns an OAuth1 header object.
- """
- # Make the file if it isn't there. Or if you just want to.
- if ask or not os.path.isfile(filename): # Note: This is technically unsafe
- make_JSON_creds(filename)
- # Then it's there. Get it.
- with open(filename, "r") as the_file:
- creds = json.load(the_file)
- return(creds)
- #SSH tunnelling with port forwarding to database server
- def query(q):
- with SSHTunnelForwarder(
- (host, 22),
- ssh_username=ssh_username,
- ssh_password=ssh_private_key,
- remote_bind_address=(localhost, 3306),
- ) as server:
- conn = db.connect(host=localhost,
- port=server.local_bind_port,
- user=user,
- passwd=password,
- db=database)
- return pd.read_sql_query(q, conn)
- creds = get_JSON_creds(filename="./.usercreds_tide.json", ask=True)
- # ssh variables
- host = 'Hostname'
- localhost = '127.0.0.1'
- ssh_username = creds.get("SSH_USERNAME").strip()
- ssh_private_key = creds.get("SSH_PRIVATE_KEY").strip()
- # database variables
- user=creds.get("DB_USERNAME").strip()
- password=creds.get("DB_PASSWORD").strip()
- database='tidal'
- df = query("SELECT comm.Art,comm.Text,COUNT(comm.Text),
- comm.Info,commurl.Title,commurl.Source FROM comm INNER JOIN commurl ON
- comm.Id=commurl.ID GROUP BY Id HAVING COUNT(comm.Text)>100")
Add Comment
Please, Sign In to add comment