Using HiveSQL with Python on Mac

This is a short tutorial on how to get started with using HiveSQL in python scripts on a Mac computer. HiveSQL is an awesome SQL database service created and maintained by @arcange. It makes getting data from Hive blockchain fast and easy. Now that HiveSQL is funded by Decentralized Hive Fund this service is free.

To use HiveSQL you will need to get login credentials to access the database. To get access the database, you will need to transfer 0.01 HBD to @hivesql account. No memo is needed. @hivesql will automatically send back a transfer with an encrypted memo that will contain information needed to access the database. Since the memo will be encrypted, to see the memo the wallet need to accessed with a memo key. The information contained in the memo will be server url, database name, login user name, and a password.

Now, we can install necessary drivers and dependencies on our mac machine. Follow the following steps.

1. Open the terminal and run the following command to install the homebrew:
   /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
2. Next run the following command:
   brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
3. Next run:
   brew update
4. And run the following to install the driver:
   brew install msodbcsql17 mssql-tools
5. Lastly, we need to pip install pyodbc module. You may need to use pip, pip3, or pip3.8 command depending on the version of the python you are using. Run this:
   pip3 install pyodbc

Now we are ready to use HiveSQL within our python scripts. The following is a sample script to test if we can properly connect to HiveSQL and get data back.

import pyodbc
import pprint

connection = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                            'Server=vip.hivesql.io;'
                            'Database=DBHive;'
                            'uid=Hive-geekgirl;'
                            'pwd=XXXXXXXXXXXXX')

cursor = connection.cursor()

SQLCommand = '''
SELECT delegator, delegatee, vesting_shares, timestamp
FROM TxDelegateVestingShares
WHERE delegator = 'geekgirl'
ORDER BY timestamp DESC
'''

result = cursor.execute(SQLCommand)
result = result.fetchmany(100)
pprint.pprint(result)
connection.close()

We use ODBC Driver 17 for SQL Server for the driver. Server, database, uid, and pwd information will be sent by @hivesql. Within SQLCommand variable will contain our SQL query commands. Run the script to confirm that python script is working properly, connects to HiveSQL and returns the correct data. Once everything works properly you should be able to replace the contents of the SQLCommand variable and run any SQL queries as needed.