PyQ - casting datatypes to empty tables

Question

In PyQ I can create a dictionary and transpose it with flip to get a table...

q.set(':alpha', q('!', ["Name", "Ask", "Bid", "Time"], ()).flip)

However, as expected, when I load it in q and retrieve the meta data it shows the char type for all of the columns as symbols...

>>> q.get(':alpha').show()
Name  Ask              Bid            Time
---------------------------------------------------------------
"BTC" "16351.0"        "16350.0"      "2017-12-12T17:32:33.09"
"ETH" "589.89999993"   "585.25"       "2017-12-12T17:32:32.697"
"LTC" "297.0"          "296.29570358" "2017-12-12T17:32:32.353"
"BTC" "16355.0"        "16351.0"      "2017-12-12T17:32:44.777"
"ETH" "589.89999993"   "585.25"       "2017-12-12T17:32:42.15"
"LTC" "297.99940398"   "296.29570359" "2017-12-12T17:32:44.433"
"BTC" "16359.99999998" "16350.0"      "2017-12-12T17:32:53.713"
"ETH" "589.89999993"   "585.2500001"  "2017-12-12T17:32:53.197"
"LTC" "297.99940398"   "295.0"        "2017-12-12T17:32:51.37"
"BTC" "16355.0"        "16350.0"      "2017-12-12T17:33:02.433"
"ETH" "585.2500001"    "585.25"       "2017-12-12T17:33:03.497"
"LTC" "297.99940397"   "295.0"        "2017-12-12T17:33:01.463"
>>> q()
q)\l alpha
`alpha
q)meta alpha
c   | t f a
----| -----
Name| s
Ask | s
Bid | s
Time| s
q)

Which I believe is what's causing the 'type error to be thrown by the console when I perform the following queries...

q)select Name, max Ask, max Bid, Time from alpha
'type
q)select max Ask, max Bid, Time by Name from alpha
'type
q)select from alpha where Bid=(max;Bid) fby Name
'type
q)select from alpha where Name=`BTC
Name Ask Bid Time
-----------------

I know in q I can issue the following command to achieve this...

q)alpha:([]Name:`symbol$(); Ask:`float$(); Bid:`float$(); Time:`datetime$())
q)meta alpha
c   | t f a
----| -----
Name| s
Ask | f
Bid | f
Time| z
q)

What is the correct syntax to define the char type for each column in PyQ?

sashk · Answer 1 · 2017-12-13T17:50:34.583

2

You can specify type in PyQ as well:

>>> from pyq import K
>>> q('!', ["Name", "Ask", "Bid", "Time"], [K.symbol([]), K.float([]), K.float([]), K.timestampt([])]).flip.meta.show()
c   | t f a
----| -----
Name| s
Ask | f
Bid | f
Time| p

Or in your case, you can use something like this:

>>> q('!', ["Name", "Ask", "Bid", "Time"], [K.symbol(["LTC-USD","BTC-USD"]), 
... K.float([310.,16700.]), 
... K.float([310.01,16700.92]), 
... K.timestamp([datetime.datetime(2017,12,13,17,40,44), datetime.datetime(2017,12,13,17,40,45)])]).flip.show()
Name    Ask   Bid      Time
----------------------------------------------------
LTC-USD 310   310.01   2017.12.13D17:40:44.000000000
BTC-USD 16700 16700.92 2017.12.13D17:40:45.000000000

Example above can be simplified further, as PyQ knows how to convert Python types into q types.

>>> q('!', ["Name", "Ask", "Bid", "Time"], [["LTC-USD","BTC-USD"], [310.,16700.], [310.01,16700.92], 
... [datetime.datetime(2017,12,13,17,40,44), datetime.datetime(2017,12,13,17,40,45)]]).flip.meta.show()
c   | t f a
----| -----
Name| s
Ask | f
Bid | f
Time| p

You can read more in the Constructs and casts section of the PyQ's User guide.

edited Dec 13 '17 at 17:50

answered Dec 13 '17 at 17:37

sashk

3,946
2
33
41

Adding `q.set(':alpha', q('!', ["Name", "Ask", "Bid", "Time"], [K.symbol([]), K.float([]), K.float([]), K.timestamp([])]).flip)` now throws the following error for my upsert `q.upsert(':alpha', [json.dumps(market_name[5:]), json.dumps(ask), json.dumps(bid), json.dumps(timeStamp)])` `_k.error: type` It works fine if I don't define char type... Any ideas on how to fix this? – marrowgari Dec 13 '17 at 19:03
Also, if I set all char type as symbols it will work... `q.set(':alpha', q('!', ["Name", "Ask", "Bid", "Time"], [K.symbol([]), K.symbol([]), K.symbol([]), K.symbol([])]).flip)`... but leaves me with the same problem as before. – marrowgari Dec 13 '17 at 19:23
I believe you need to cast into correct types before `upsert`. Check data types you're getting from `json.dumps`. Without seeing your code, it will be hard to debug. – sashk Dec 13 '17 at 20:49
The commenting section isn't the best. I'll ask a new question and post the code. Thank you. – marrowgari Dec 14 '17 at 01:26

marrowgari · Accepted Answer · 2017-12-19T22:29:14.457

I was passing a datetime char type to a timestamp column. In q I would define these as...

q)table:([]date:`datetime$(); name:`symbol$())
q)meta table
c   | t f a
----| -----
date| z
name| s

q)table:([]date:`timestamp$(); name:`symbol$())
q)meta table
c   | t f a
----| -----
date| p
name| s

I was able to work around this by parsing the datetime to a timestamp and casting to K.timestamp([])...

Code below...

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.load(':alpha')

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(60.0)

Now returns the correct char type in my table...

q)meta alpha
c   | t f a
----| -----
Name| s
Ask | f
Bid | f
Last| f
Vol | f
Time| p

PyQ - casting datatypes to empty tables

2 Answers2