PyQ: q.upsert to splayed table

Question

I'm trying to upsert data to a splayed kdb database by running the following code in pyq...

from bittrex.bittrex import Bittrex, API_V2_0
from datetime import datetime
import time
from pyq import q, K

get_bittrex = Bittrex(None, None)
starttime = time.time()

q.set(':db/alpha/', q('.Q.en', ':db', q('([]Name:`$(); Ask:`float$(); Bid:`float$(); Last:`float$(); Volume:`float$(); Time:`timestamp$())')))

while True:
    market_result = get_bittrex.get_market_summaries()['result']
    for res in market_result:
        market_name = res['MarketName']
        ask = float(res['Ask'])
        bid = float(res['Bid'])
        last = float(res['Last'])
        volume = float(res['Volume'])
        dt = res['TimeStamp']
        if market_name in ['USDT-BTC', 'USDT-ETH', 'USDT-LTC', 'USDT-XRP', 'USDT-NEO', 'USDT-BCC', 'USDT-ZEC', 'USDT-XMR', 'USDT-DASH']:
            ts = datetime.strptime(dt, '%Y-%m-%dT%H:%M:%S.%f')
            data = [market_name[5:], ask, bid, last, volume, ts]
            q.upsert(':db/alpha/', q('.Q.en', ':db', [data]))
            q.get(':db/alpha/').show()
    time.sleep(30.0)

It keeps throwing the same error: _k.error: type. I believe this is because I'm not enlisting the data that is being appended, one row at a time, in my q.upsert.

When I use the same script on a non-splayed table it works fine...

from bittrex.bittrex import Bittrex, API_V2_0
from datetime import datetime
import time
from pyq import q, K

get_bittrex = Bittrex(None, None)
starttime = time.time()

q.set(':alpha', q('([]Name:`$(); Ask:`float$(); Bid:`float$(); Last:`float$(); Volume:`float$(); Time:`timestamp$())'))

while True:
    market_result = get_bittrex.get_market_summaries()['result']
    for res in market_result:
        market_name = res['MarketName']
        ask = float(res['Ask'])
        bid = float(res['Bid'])
        last = float(res['Last'])
        volume = float(res['Volume'])
        dt = res['TimeStamp']
        if market_name in ['USDT-BTC', 'USDT-ETH', 'USDT-LTC', 'USDT-XRP', 'USDT-NEO', 'USDT-BCC', 'USDT-ZEC', 'USDT-XMR', 'USDT-DASH']:
            ts = datetime.strptime(dt, '%Y-%m-%dT%H:%M:%S.%f')
            data = [market_name[5:], ask, bid, last, volume, ts]
            q.upsert(':alpha', [data])
            q.get(':alpha').show()
    time.sleep(30.0)

And, when I try manually setting and upserting 2 rows at the same time, it returns the expected results...

(pyq36-64) ❯ pyq
Python 3.6.3 (default, Nov 30 2017, 16:33:45)
[GCC 4.2.1 Compatible Apple LLVM 8.0.0 (clang-800.0.42.1)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> q.set(':db/alpha/', q('.Q.en', ':db', q('([]Name:`$(); Ask:`float$(); Bid:`float$(); Last:`float$(); Volume:`float$(); Time:`timestamp$())')))
k('`:db/alpha/')
>>> q.upsert(':db/alpha/', q('.Q.en', ':db', q('([]Name:`f`g; Ask:70.0 80.0; Bid:60.0 70.0; Last:50.0 60.0; Volume:645.32 745.33; Time:2017.12.19D19:10:09.697000000 2017.12.19D19:10:09.697000000)')))
k('`:db/alpha/')
>>> q.get(':db/alpha/').show()
Name Ask Bid Last Volume Time
------------------------------------------------------
f    70  60  50   645.32 2017.12.19D19:10:09.697000000
g    80  70  60   745.33 2017.12.19D19:10:09.697000000
>>> q()
q)\ls -l -a db/alpha
"total 64"
"drwxr-xr-x  10 marrowgari  staff  340 Dec 19 16:42 ."
"drwxr-xr-x   4 marrowgari  staff  136 Dec 19 15:59 .."
"-rw-r--r--   1 marrowgari  staff   38 Dec 19 16:42 .d"
"-rw-r--r--   1 marrowgari  staff   32 Dec 19 16:42 Ask"
"-rw-r--r--   1 marrowgari  staff   32 Dec 19 16:42 Bid"
"-rw-r--r--   1 marrowgari  staff   32 Dec 19 16:42 Last"
"-rw-r--r--   1 marrowgari  staff   32 Dec 19 16:42 Name"
"-rw-r--r--   1 marrowgari  staff   32 Dec 19 16:42 Time"
"-rw-r--r--   1 marrowgari  staff   16 Dec 19 16:19 Vol"
"-rw-r--r--   1 marrowgari  staff   32 Dec 19 16:42 Volume"
q)

How do I upsert the variable data from my code above to the splayed table?

q.set(':db/alpha/', q('.Q.en', ':db', q('([]Name:`$(); Ask:`float$(); Bid:`float$(); Last:`float$(); Volume:`float$(); Time:`timestamp$())')))
q.upsert(':db/alpha/', q('.Q.en', ':db', [data]))

score 4 · Accepted Answer · answered Dec 21 '17 at 05:15

When you ask questions on SO, please make an effort to present your issue with the code that can be easily reproduced. Ideally, try to reproduce your problem without 3rd party packages. In your specific case here, the problem has nothing to do with the Bittrex API and could be presented simply as

>>> q.set(':db/alpha/', q('.Q.en[`:db]', q('([]Name:`$(); Ask:`float$())')))
k('`:db/alpha/')
>>> data = ['A', 70.0]
>>> q.upsert(':db/alpha/', q('.Q.en', ':db', [data]))
Traceback (most recent call last):
  ..
_k.error: type

The cause of the error is that .Q.en expects a table which in q is a list of dictionaries and you pass a list of lists.

One solution is to convert your data to a dictionary before passing it to q:

>>> data_dict = {a:b for a, b in zip(['Name', 'Ask'], data)}
>>> q.upsert(':db/alpha/', q('.Q.en', ':db', [data_dict]))
k('`:db/alpha/')

Another (and possibly faster) solution is to enumerate the string element in data:

>>> data[0] = q('`:db/sym?', data[0])
>>> data
[k('`sym$`A'), 70.0]

and use the insert function to append the data to the table:

>>> q.insert(':db/alpha/', data)
k(',1')

Sorry for the overly verbose question and thank you for the concise answer. As usual, that solved it. — marrowgari, Dec 21 '17 at 21:10

PyQ: q.upsert to splayed table

1 Answers1