A few weeks ago I plunged into learning python and sql programming with a desire to automate my curating activities on Steem. I was introduced to steemsql.com, which created and maintained by arcange. This gave me a great opportunity to interact with Steem blockchain data programmatically. Before I start I would like to thank arcange for creating and continuously improving steemsql and making this tool publicly available for free. I would like to express my gratitude to Locikll, whom I see as my mentor in my journey of learning python programming. I would also like to thank carlgnash who introduced me to steemsql, helped me in understanding sql, shared his knowledge and his sql scripts he made for curation purposes.
The biggest challenge I had was initial setup and installations of needed drivers and api to make my scripts connect to steemsql. arcange has a detailed tutorials with updates on how to use steemsql. He even has a sample python to connect to steemsql. However, that didn't work on my mac computer. Asking questions and trying to come up with a solution I quickly realized that majority of devs are using PC, hence there was little to no support for how to make it work on mac. I even couldn't find a decent GUI program that would flawlessly connect to MSSQL server. Since I was already learning python, I decided to continue my quest on finding a solution and that is I would like to share, in hopes this can be beneficial to other mac users.
- Initial setup and installations on macOS
- Writing a simple python script with sql command that connects to steemsql
First we need to make sure homebrew is installed. If homebrew is not installed please go to the terminal app and type the following command. Url is also part of the command line.
ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
Next, enter the following command:
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
Then,
brew update
Now, we need to install ODBC driver. Enter the following in the terminal:
ACCEPT_EULA=y brew install --no-sandbox msodbcsql
We are almost done with the terminal commands. Last thing we need to install is pypyodbc.py api to use in our python scripts. Fist make sure you have python 3 installed, if you haven't installed python 3 yet, you can go to python.org and download it from there. Now lets install pypyodbc.py. Go back to the terminal and enter the following command.
pip3 install pypyodbc
Now we are ready to move on writing our python. All of these preparation work was necessary to make our queries work using python on macOS.
import pypyodbc
import pprint
connection = pypyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
'Server=sql.steemsql.com;'
'Database=DBSteem;'
'uid=steemit;'
'pwd=steemit')
cursor = connection.cursor()
SQLCommand = '''
SELECT delegator, delegatee, vesting_shares, timestamp
FROM TxDelegateVestingShares
WHERE delegator = 'ned'
ORDER BY timestamp desc'''
result = cursor.execute(SQLCommand)
result = result.fetchmany(100)
pprint.pprint(result)
connection.close()
Now your macOS is ready to use steemsql with python. The sample script above will return list of people Ned has delegated SP to. SQL query can be changed to any other kind of query you wish simply by changing the contents of SQLCommand variable. It is important to keep triple single quotes at the beginning and end of your query command. There can be many useful queries written to get all kinds of blockchain data from steemsql. For this tutorial purposes I decided to go with shorter one, so code is smaller and easier to understand. That one in particular is one of the sql commands carlgnash shared with me few weeks ago.
Contents of connection variable describe how the connection will be made, server name, database, username, and password. I doubt there will ever be a need to make any changes there in your scripts. Result variable stores the results of the query and only gets list of upto 100. That number can be changes to higher number if there is a need. Pprint prints out the results. I used pprint, because it prints out list types in a more reader friendly way.
Congratulations mac user, now you can also make queries to steemsql and take full advantage of it as PC user have been doing for a while.
I have multiple scripts written that server various purposes. The main ones I often use as a filtering tool for curation purposes. Some of my scripts run on a timer, every hour or so, get the results, save them on a file, and also email the files. Auto-email feature is pretty good, especially when I am away from my desk and on mobile. I am still learning and I am grateful for this platform for motivating me to learn new skills.
If you have any questions, please leave them in the comments or I can be reached at steemit.chat #librarian channel. By the way, l have set up a librarian bot in #librarian channel that can answer to some cool query commands. Librarian is still in early learning stages as I am. It is getting smarter everyday though.
Posted on Utopian.io - Rewarding Open Source Contributors
Awesome this should be super helpful to the Mac users. My friend @buckydurddle figured out a different way to connect to SteemSQL with a Mac (and wrote a tutorial post about it) but honestly the way he worked it out involved jumping through a lot more hoops! Using python to connect on a Mac seems to be pretty simple.
Very nicely written and easily understandable post. I am glad that my sharing some SQL queries with you sparked your interest - you have taken the baton and are off and running!
Much love - Carl
Thank you. So much to learn and so much fun at the same time. I will be bugging you a lot with questions. Sorry and thank you 😊 in advance.
Thank you for the contribution. It has been approved. :)
You can contact us on Discord.
[utopian-moderator]
This Techie codes made me follow you, i love Geeks Ladies....can i chat you up on discord or steemchat pls?
Thank you. I am usually on steemit.chat, occasionally show up on discord too. My steemit.chat residence is in #curie channel :)
Ouch, am scared to chat in there...i have been cautioned back in March or April when am still very new on Steemit...But i'll be dropping by to say hello and Weldone to you often
Awesome tutorial! I have been learning some python too with definitely intention of playing with steemsql. Will be bookmarking this post :)
Awesome @geekgirl!
I'm a mac user so I think that this may be something very useful for me. I don't know python (I've only recently learned SQL) so I think that'll be the next thing I pick up. Thanks for sharing!
I only started learning python a few weeks ago myself. It is fun. Once you get the basics, it is super easy to make queries. For query purposes, your knowledge of SQL would be more important. Let me know how it goes. I would like to learn more SQL myself.
thank you very much :)
Hi, do you use any tools to draw graphs for analyzing these data?
You're perfect... Thank you for the useful contribution
Thank you for the compliment.
Upvtd and reblogged
Thank you
Hey @geekgirl I am @utopian-io. I have just upvoted you!
Achievements
Suggestions
Get Noticed!
Community-Driven Witness!
I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!
Up-vote this comment to grow my power and help Open Source contributions like this one. Want to chat? Join me on Discord https://discord.gg/Pc8HG9x
Come and learn how AI processes images :)
this is so hard to comprehend, all I know how to do is draw...but I am determined to learn...python, here I come...