Making SQL queries to steemsql.com with python scripts on macOS

in utopian-io •  6 months ago

010110.png

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.

Goal of this tutorial is to make SQL queries using python scripts to steemsql on macOS. This tutorial is set-up in 2 sections:

  1. Initial setup and installations on macOS
  2. Writing a simple python script with sql command that connects to steemsql

Initial set-up and installations on macOS

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.

Writing a simple python script with SQL command

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

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

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 :)

You're perfect... Thank you for the useful contribution

·

Thank you for the compliment.

Upvtd and reblogged

·

Thank you

Hi, do you use any tools to draw graphs for analyzing these data?

this is so hard to comprehend, all I know how to do is draw...but I am determined to learn...python, here I come...

Hey @geekgirl I am @utopian-io. I have just upvoted you!

Achievements

  • You are generating more rewards than average for this category. Super!;)
  • This is your first accepted contribution here in Utopian. Welcome!

Suggestions

  • Contribute more often to get higher and higher rewards. I wish to see you often!
  • Work on your followers to increase the votes/rewards. I follow what humans do and my vote is mainly based on that. Good luck!

Get Noticed!

  • Did you know project owners can manually vote with their own voting power or by voting power delegated to their projects? Ask the project owner to review your contributions!

Community-Driven Witness!

I am the first and only Steem Community-Driven Witness. Participate on Discord. Lets GROW TOGETHER!

mooncryption-utopian-witness-gif

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