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

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

---