Hive Comments: I Built A Web App Using HiveSQL And Streamlit - All In Python

in LeoFinance5 months ago (edited)

hivesqlapp.jpeg

UPDATE: I have updated the App so that you don't need your own credentials to use it. Please ignore any reference to getting or using your own HiveSQL credentials in the post. Thank You.

UPDATE-2: I made the github repo to the code private because it was leaking the private credentials. I am just learning how these things work. If you need the code I will be happy to share. To make the github repo public I need to learn how to secure sensitive data first.

UPDATE-3: https://leofinance.io/@geekgirl/update-on-my-experimental-app-hive-librarian

If you followed my previous posts, you probably already know I have been experimenting with HiveSQL and Streamlit in python. Today I decided to attempt to build something more useful and ended up deploying my very first simple App. Let's call it Hive Comments. Because that's all it does, it retrieves the historical comments made by one user to another.

Have you ever had a situation when you tried to remember an information that was shared in the comments sometime in the past? Or perhaps you were/are interested in seeing all the comments or conversations you had with a specific user? Or maybe even a conversations other users had with each other. If so, you may find it interesting to browse through the history of Hive comments among users.

Before I bore you with all the details how to build an App using Streamlit and HiveSQL, feel free to go the following link and try it yourself. I would appreciate any and all feedbacks.

https://hive-user-responses.herokuapp.com

To connect to the HiveSQL database login credentials are required. Since I wasn't sure about the terms of service and because HiveSQL is free for all users I chose to let Hive users use their of login credentials for HiveSQL. If you do not have one, it is easy to get. Simple transfer 0.01 HBD to @hivesql, and it will automatically send a transfer to your account with login and password information. If you don't have 0.01 HBD, I will be happy to send you. Just let me know in the comments.

Once you have the login/password for HiveSQL, just enter your username and password to the top two fields. Next two input fields ask for user-1 and user-1. After you click the Get Responses button, the results will output all the comments made by user-1 to user-2. For context, you can click the (source) and it will take to that comment on hive.blog.

Writing the Code and Deploying to Heroku

The App itself is not complicated. It only has one simple task as described above. I use Streamlit because it makes making web apps easy and fast. It also makes deploying apps easy as well. I chose to deploy it on Heroku.

Initially I had issues deploying it, because I used pyodbc module which requires specific Microsoft sql driver. Even with pyodbc the App worked fine on the local machine. But problem happened when I tried to deploy it on Heroku.

Then I switched to a different module - pymssql and everything worked fine locally and was able to deploy without any issues. It works almost the same way as pyodbc.

The code itself contains lines to connect to HiveSQL using pymssql which takes an SQL command and returns the results. I store the SQL query in a SQLCommand variable.

Other lines of code use Streamlit functions to output labels, inputs, divs, p, and text on a webpage. Most of them are self-explanatory. To get started with Streamlit, feel free to read my previous post on the topic.

Then, there is also long lines of code that has to do with styling and CSS. With basic CSS knowledge and using Chrome's inspector we can identify and style all the elements displayed on the webpage.

Before deploying the App to Heroku we will need to place all files used and needed to github. First, we need the python files used. In this case, I only had one python file for the App. I also use an image, so I need to include that as well.

After that we need to create three more files with specific information in them. The file names will be:

  • Procfile
  • requirements.text
  • setup.sh

The Procfile will have one line as following:

web: sh setup.sh && streamlit run hive_responses-app.py

Replace the .py filename with a filename of your App.

Inside the requirement.txt we need to place all the modules/dependencies we used in our python script with their version numbers. Like this:

streamlit==0.74.1
pymssql==2.1.5
Pillow==8.1.0

We can get that information with typing the following in the command prompt or terminal

pip freeze or in my case pip3.8 freeze

Last file setup.sh will contain the following.

mkdir -p ~/.streamlit/

echo "\
[server]\n\
port = $PORT\n\
enableCORS = false\n\
headless = true\n\
\n\
" > ~/.streamlit/config.toml

We don't need to make any changes in this file.

Here is the link to my github repository for this App:

https://github.com/librarian-hive/hive-comments

Now that everything is ready in github, we go to Heroku. In Heroku, we need to click New --> Create new app.

First we need to give our new app a name. After that, under Deploy tab and Deployment method we need to choose Github. It will ask for repository name, searches for it and connects to the repository.

Lastly, we click the Deploy Branch at the bottom of the page. It will take few minutes to configure and install everything properly. If everything goes well, at the end it will show a View link to the App. That's it.

Feel free to visit my super boring first app.

https://hive-user-responses.herokuapp.com

Posted Using LeoFinance Beta

Sort:  

Very cool, fun idea. I really enjoy write ups like these. I'll try to do some similar posts.

One suggestion: I've seen other repos include an example config file like config.toml.example. Then it's easier to modify and rename the file to get started.

Posted using Dapplr

Thank you @hivetrending!

Thanks for the suggestion. I will take a look. I didn't know that. I am new to using repositories and heroku. I normally do simple things that run on my computer.

Posted Using LeoFinance Beta

I must try this at some point :)

Did you try the App? Please do. I need feedbacks. :)

Not yet .... on the phone now, cant make it running :)

Great post :) Tried it and works smoothly . Keep up the good work , bring out more applications like above . It helps .

!WINE

Posted Using LeoFinance Beta


Cheers, @amr008 You Successfully Shared 0.100 WINE With @geekgirl.
You Earned 0.100 WINE As Curation Reward.
You Utilized 2/3 Successful Calls.

wine-greeting


WINE Current Market Price : 1.100 HIVE

Thanks for your help!

Place don't mention. All credits to you. Great job.

I should try out streamlit.
I have couple of ideas on my mind.

What's heroku actually? I am not sure .
Posted Using LeoFinance Beta

Heroku is a web app hosting service. For small apps like this it is free. So, don't need to have your own server to publish a website to share.

I see . Thanks for sharing :) I will check that out , maybe I can do something too.

Posted Using LeoFinance Beta

Amazing stuff!

We need to bring more geeks to HIVE

Posted Using LeoFinance Beta

Thanks You!

Posted Using LeoFinance Beta

I tried it a little, and it¿s great. This way, we can look into other people's conversation, lol! I remember there was a set back on STEEM where you could see which was the account that supported you the most (in votes). It shut down because, at that time, the SQL was not free. I'll keep using your app and let you know if I have any feedback.

-@eddiespino.

Posted Using LeoFinance Beta

I remember there was that app. I can try to add those features as I learn more. Thanks.

Posted Using LeoFinance Beta

I made the github repo to the code private because it was leaking the private credentials. I am just learning how these things work. If you need the code I will be happy to share. To make the github repo public I need to learn how to secure sensitive data first.

This is a very common issue, in fact there has been a few issues with private keys. I had one person who had over 50,000 liquid Hive exposed on Github back in the Steem days.

Anyway, the best practice is structuring your app like this.

Config File

  • Put a config.js/config.yml into your project
  • Add the config file to your .gitignore
  • Store all secrets in the config file

Env variables

  • Use environment variables for secrets
  • If using a script to launch and set env var, add it to .gitignore

Either of these two solutions will prevent leaking secrets to GitHub.

Seeing as you likely (I haven't seen your code) put secrets in your core files, you can't scrub your Git repo to just remove that file from the history. Seeing as this is a small project, I would remove the .git folder and just fix your problem then make a new repo.

If you need the history, it gets a lot more complicated.

Posted Using LeoFinance Beta

Awesome. Thank You. I will try these options.

To be honest, I would prefer that you use your own HiveSQL credentials in your app rather than having to manage hundreds of one-time-use new credentials.

Awesome. I will try to update to that then. Thank You @arcange.

Posted Using LeoFinance Beta

Thank you.

I made the github repo to the code private because it was leaking the private credentials.

The solution to keeping your repo public is:

  • put your credential in a separate .php file (let's call it credentials.php) that you import into your main app code
  • Add a credential.example.php with dummy credentials to your project and tell users to update it with their own credentials and rename it.
  • add credentials.php to your .gitignore file to avoid it being pushed to your repo when you synch your project.

I'll try to make a post soon about using environmental variables and a configuration file in python and where to put things in Heroku and on the local machine as I just worked this out for my little project.

Posted Using LeoFinance Beta

Perfect! Thanks.

Posted Using LeoFinance Beta

Thank You. I will try that.

Posted Using LeoFinance Beta

There isn't any security issue right? In letting others use my credentials?

Posted Using LeoFinance Beta

You should never share your credentials!
Shall someone else uses them and does something harmful to HiveSQL, they could be permanently revoked and lock you out.

All right got it .

I used HiveSQL for everything related to hive but I learnt that custom json is not reliable so only for those transactions I had to shift to Hive-Engine API , any advice on this?

Posted Using LeoFinance Beta

Hive-Engine API is the way to go as their data is maintained off-chain.

Yup , doing that now.

Still i have to go through every block to retrieve the transaction details .

Right now I have stored one month data in persistent dictionary ( Shelve ) .

Awesome contribution my dear. This is going to be very helpful!

Thank You!

Posted Using LeoFinance Beta

Simple transfer 0.01 HBD to @hivesql, and it will automatically send a transfer to your account with login and password information.

How do I read that information ?

@tipu curate

Thank You!

You're welcome

You built a good app for Hive users. Thank you @geekgirl for the Hive Comments app

Thank You!

Cool app. I would check it out but first I need to go get my hivesql login details. Wait, this works for mobile too right?

Yes, it does work on mobile.

Great!! Thanks again would try it out

Somehow I can't seem to find where to login @geekgirl

IMG_20210129_123139.jpg

Check the post update. User's credentials requirement has been removed

Okay then.. Thanks.

Great idea you had there! I love to do small projects, because you learn so much on the way. I hope you will be creating more awesome projects like this :)

!gif hive

Thank You! Yes, it is fun to work on something and learn at the same time.

For the sensitive data have you tried heroku config vars (https://devcenter.heroku.com/articles/config-vars)
I'm having similar problems and I wonder if that's a good solution

Thank you. I will read that.

Very cool, some nice work @geekgirl

Thank you! I am glad you liked.

Impressive! I love the motive!

Thank You!

Posted Using LeoFinance Beta

this could be useful. bookmarked.

also simple and it works.

ned commented on dan posts only 9 times. wow :D

lol yea. that last one was funny and that was the one that made Dan to completely stop using Steem.

Posted Using LeoFinance Beta

Thats very cool would be fun to see who you comment with most out of all your followers and the pecentage of it

Right now it just shows interactions with one user. It is a great idea to implement. Thank you.

Posted Using LeoFinance Beta

@geekgirl , your coding is amazing, thank you for helping programmers.

Thank You. I am just learning and experimenting.

Posted Using LeoFinance Beta

Congratulations @geekgirl! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :

You got more than 6500 replies. Your next target is to reach 6750 replies.

You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Check out the last post from @hivebuzz:

Next Hive Power Up Day is February 1st 2021

Great work

Cool :)

so cool!