Question regarding reading SQLite recorded data

[Tarmen]

Hello,

I have some burst recorded in a sqlite database, with the block of sqlite that you provided. The thing is, I'd like to send this data to another user and ideally, he would store it and represent it at the same time that I am getting more data into this db. So I was doing a python script that would be able to read this database and send the data recorded (latitude, longitude...). But the field data is in BLOB type and I am not able to read it. Is there any way to do it?
Maybe this solution is a bit complicated, but I didnt find a way to send this data with gnuradio blocks, is there any?

Thanks for your time!

[mhostetter]

First, I'm assuming you installed my other OOT module gr-sqlite, correct?

Are you trying to "replay" the recorded ADS-B packets in another GNU Radio flowgraph like described here?

Regarding reading the BLOB type, I describe here how the SQLite Sink stores data. The "PDU vector" (pmt::car) is stored in binary in a separate database column (default name is data). You can parse that in python like shown below. Also, refer to my python webserver for how to read and parse the binary PDU vector. Also, you can refer to this GNU Radio tutorial on working with PMT types in python.

import pmt

# pdu_bin is the binary BLOB, read from the database
pdu = pmt.deserialize_str(pdu_bin)  # Convert from PDU binary string to PDU python object
packet_bin = pmt.to_python(pmt.car(pdu))  # Take the CAR portion (PDU vector) and convert to python bytearray (?, I think)

[Tarmen]

Yes, you're right, I meant your OOT module of SQLite.
I tried to deserialize the BLOB type as you commented, and sneaked peak your webserver python code as well, but I can't still read the binary data. I think that the problem might be that I am reading it directly from the database... it throws an error like: "in method 'deserialize_str', argument 1 of type 'std::string' "

def run_query(query=''):

    conn = sqlite3.connect('adsb.db')
    c = conn.cursor()
    c.execute(query)

    if query.upper().startswith('SELECT'):
       data1 = c.fetchall()
       data2py = pmt.deserialize_str(data1)
       data = pmt.to_python(pmt.car(data2py))

    else:
        conn.commit()
        data= None

    c.close()
    conn.close()

   return data

query = "SELECT data FROM Info" 
result = run_query(query)
print(result)

Any ideas how to deal with this?

[mhostetter]

Ok, I know what's going wrong. The issue is you're reading all the rows at the same time with fetchall().

You could just read the first row, like this:

def run_query(query=''):

    conn = sqlite3.connect('adsb.db')
    c = conn.cursor()
    c.execute(query)

    if query.upper().startswith('SELECT'):
       data1 = c.fetchone()
       print(type(data1), data1)  # This is either a sqlite3.Row object or a byte string, see what it is
       data2py = pmt.deserialize_str(data1)
       # data2py = pmt.deserialize_str(data1[0])  # You may need to index the "data" column from the data1 row object
       data = pmt.to_python(pmt.car(data2py))

    else:
        conn.commit()
        data= None

    c.close()
    conn.close()

   return data

query = "SELECT data FROM Info" 
result = run_query(query)
print(result)

Alternately, you can iterate through all the rows and populate a list or 2D array or something.

def run_query(query=''):

    conn = sqlite3.connect('adsb.db')
    c = conn.cursor()
    c.execute(query)

    if query.upper().startswith('SELECT'):
       data = []
       for data1 in c.fetchall():
           data2py = pmt.deserialize_str(data1)
           # data2py = pmt.deserialize_str(data1[0])  # You may need to index the "data" column from the data1 row object
           data.append(pmt.to_python(pmt.car(data2py)))

    else:
        conn.commit()
        data= None

    c.close()
    conn.close()

   return data

query = "SELECT data FROM Info" 
result = run_query(query)
print(result)

If this doesn't work for you, print out some of the variable types and values and we can debug it.

[Tarmen]

Hi again, first, thank for all your help.
Well, so trying what you said;

def run_query(query=''):

    conn = sqlite3.connect('adsb.db')
    c = conn.cursor()
    c.execute(query)

    if query.upper().startswith('SELECT'):
       data1 = c.fetchone()
       print(type(data1), data1)  #"(<type 'tuple'>, (<read-write buffer ptr 0x7f347ec395b0, size 120 at 0x7f347ec39570>,))
       data2py = pmt.deserialize_str(data1)
       # data2py = pmt.deserialize_str(data1[0])  #Tried both options
       data = pmt.to_python(pmt.car(data2py))

    else:
        conn.commit()
        data= None

    c.close()
    conn.close()

   return data

query = "SELECT data FROM Info" 
result = run_query(query)
print(result)

Trying this one out, the first print says: "(<type 'tuple'>, (<read-write buffer ptr 0x7f347ec395b0, size 120 at 0x7f347ec39570>,))
and finally return the same error: "method 'deserialize_str', argument 1 of type 'std::string' "
With the second way that you told me to try (iterating throw the rows) return the error above of "method 'deserialize_str', argument 1 of type 'std::string' "
Also, I tried to do the query just to print out just one value of data, but still get the same errors.

Any thoughts?

[mhostetter]

What version of python is this? Python 2.7?

Try changing the "text factory" of the database connection to string, like below. I'm guessing this is the difference between my code and yours.

conn = sqlite3.connect('adsb.db')
conn.text_factory = str
c = conn.cursor()

Also, it may be helpful to reference my python SQLite source blocks from gr-sqlite. This is how I read back in python the PDUs I wrote into the database; it might give some insight.

If this doesn't work, please provide the outputs to these print statements.

data1 = c.fetchone()
print(type(data1))
print(len(data1))
print(type(data1[0]))
print(data1[0])

[Tarmen]

Yes, it's Python 2.7, I thought it was better to avoid compatibility problems. So the outputs to the prints that you suggested are the ones in the yellowish color (in the very same order):

I didn't have the time to check the SQLite source blocks but I'll surely do it later!

[mhostetter]

Ok, try this. I'm pretty sure this will work (although I've thought that a few times). We're zeroing in on the issue, though. This converts the buffer object of data1[0] to a bytestring, which I think is what deserialize_str() needs.

data1 = c.fetchone()
print(type(data1))
print(len(data1))
print(type(data1[0]))
print(data1[0])

data2py = pmt.deserialize_str(str(data1[0]))
print(type(data2py))
print(data2py)

data = pmt.to_python(pmt.car(data2py))
print(type(data))
print(data)

[Tarmen]

This it's working! thank you so much for taking your time.