Using SteemSQL and Python for efficient consumption of my feed

One of the benefits of Steem platform for me is that I gained interest in learning a little bit of coding. In particular I started learning Python programming language with an intention of learning how to get data from the blockchain. I thank @locikll for helping me in this endeavor. Later I was introduced to SteemSQL by @carlgnash, who shared a lot of his knowledge and usage of SteemSQL for curation purposes. Combining these two: SteemSQL and Python, I was able to automate some of the actions which I would otherwise do manually.

I use iMac. Initially it wasn't easy for me to set up proper python libraries so I could use SteemSQL. After couple weeks of research I was able to find a solution. Thinking it may benefit someone else as well, I published a post three months ago on this topic. If you are someone who has an issue getting started with Python and SteemSQL on mac, please read Making SQL queries to steemsql.com with python scripts on macOS. I hope it will help you. Moreover, @arcange has detailed tutorials with updates on how to use SteemSQL.

When I first started using SteemSQL it was free and open for public to use. Now it is only available for monthly subscription of 10 SBD. For those who just want to try it out first, there is also a daily subscription available for a smaller fee. Read more about daily SteemSQL subscription.

Today, I decided to write up a little code to automate consuming my feed. I am not sure it this will more efficient way of consuming posts by those who I follow. At the very least this will give me an opportunity to practice a little bit of coding.

Instead of scrolling through the feed and just seeing thumbnails and titles of the posts, I want the program to automatically open up all posts in my feed published within last 2-24 hours (can change in the code) on a browser in separate tabs. That way I can go through tabs one by one and give a full attention to each post that they need. Once done with a post hit Command-W to close the tab. In some cases it maybe even better to leave the post tab open for follow up discussions.

One challenge of this experiment is that number of posts published within 24 hours. My browser can handle 75 open tabs without freezing. So, I will let the program to open 50 posts at a time and code will wait for further instructions. When ready, I can hit enter to get the next 50 posts opened up. Also, this can be done to see any other user's feed by simply changing the username for the feed.

---

import pypyodbc
import webbrowser

# FUNCTION TO CONNECT TO STEEMSQL DATABASE WITH CREDENTIALS
def sqlConnection():
       connection = pypyodbc.connect('Driver={ODBC Driver 13 for SQL Server};'
                                      'Server=vip.steemsql.com;'
                                      'Database=DBSteem;'
                                      'uid=********************;'
                                      'pwd=*******************;')
       return connection

# FUNCTION TO GET THE LIST OF FOLLOWED USERS 
def getFollowing(user):
       connection = sqlConnection()
       cursor = connection.cursor()
       SQLCommand = '''
        SELECT
            following
        FROM
            followers (NOLOCK)
        WHERE
            follower IN('{}')
       '''.format(user)
       result = cursor.execute(SQLCommand)
       result = result.fetchmany(10000)
       connection.close()
       return result

# FUNCTION TO GET POSTS BY FOLLOWED USERS
def getPosts(hours, following):
       connection = sqlConnection()
       cursor = connection.cursor()
       SQLCommand = '''
        SELECT
            'https://steemit.com' + url
        FROM
            comments (NOLOCK)
        WHERE
            parent_author = '' and
            datediff(minute, created, GETDATE()) between 2*60 and ({}+2)*60 and
            {}
        ORDER BY
            created desc
       '''.format(hours, following)
       result = cursor.execute(SQLCommand)
       result = result.fetchmany(10000)
       connection.close()
       return result

# FUNCTION TO FORMAT LIST OF FOLLOWED USERS AS STRING TO PLUG INTO SQL CODE
def formatFollowing(following):
       if len(following) == 0:
              return ''
       my_string = 'author IN ('
       combiner = ","
       for i in range(len(following)):
              if i == len(following)-1:
                     combiner = ')\n'
              my_string += "'"+following[i][0]+"'"+combiner
       return my_string

#FUNCTION TO OPEN ALL POSTS ON A CHROME BROWSER
def openBrowser(result):
    chrome_path = chrome_path = 'open -a /Applications/Google\ Chrome.app %s'
    for i in range(len(result)):
        url = result[i][0]
        webbrowser.get(chrome_path).open(url)
        if i > 49 and i % 50 == 1:
            toContinue = input('Press Enter to Continue - ' + str(i))
    print('DONE!')

# MAIN EXECUTION PART
username = 'geekgirl'
hours = 24
following = getFollowing(username)
print(len(following))
following = formatFollowing(following)
feed = getPosts(hours, following)
print(len(feed))
openBrowser(feed)

There are two global variables username and hours. Username can be changed to any user whose feed I would like to see and hours is the timeframe of post I would like to see. I set username by default for my own and time for 24 hours to see all published posts within last 24 hours.

Then we need to get the list of users followed and that list will be formatted into a string so it can be plugged into SQL query command code. Then SQL query is made to get all the posts from the feed. Lastly, posts are opened automatically on a Chrome browser on their own separate tabs. If there is more than 50 posts in the feed, program opens up first 50 then waits until user pressed enter to open next 50 in the browser and so on until all posts are opened or program is stopped.

I am not sure how I often I would use this, as I have just tried it today. I can see if being helpful to use once in a while. It can make consuming feed with full focus on each post and in more efficient manner. Moreover, it was just a fun experiment. Thank you for reading. Let me know if you have questions, or if you would like to share your own fun python-SteemSQL projects you worked on.