BradRuderman / pyhs2

MIT License
208 stars 107 forks source link

description field is missing from Cursor object #31

Open serednya opened 9 years ago

serednya commented 9 years ago

Hello,

I'm in need of the field cursor.description. I would do it myself but I don't know how to.

This is a field that exists in many (most?) database modules and is defined in PEP249. [https://www.python.org/dev/peps/pep-0249/#cursor-attributes]

I have made a ""work around"" at my company but its terrible.

The idea was that since I know most queries will be in the form of: "select blah,blahblah,etc from my_awesome_tbl where 1=1" I can do the below to fill the first 2 fields:

Invoke:

description = ["name", "type", "display_size", "internal_size", "precision", "scale", "null_ok"]
description[0], description[1] = hive_description(conn, sql_query)

Terrible implementation:

def hive_description(conn, sql):
    """
    This is my terrible fix.

    Only the first 2 fields of the description field are populated
    Modules such as cx_Oracle populate all 7.

    :param conn: ConnectionInfo object
    :param sql: str object
    :return: 2 lists: column name list and column type list
    """
    assert(sql.strip().lower().startswith("select"))

    # yes, this could have been done with regex, but i was lazy
    table = sql[sql.index("from ")+len("from "):]  # remove before tbl name
    table = table[:table.index(" ")]  # remove after table name

    cursor = conn.cursor()
    cursor.execute("describe " + table)
    res = cursor.fetchall()   # its tiny so get the whole thing
    col_names = [i[0] for i in res]
    col_types = [i[1] for i in res]

    return col_names, col_types

Another fun fact:

cur = conn.cursor() cur.execute('select * from test_me_tbl') print cur.fetchall() [['2014-09-18', 'goodbye', 'world'], ['2014-09-17', 'hello', 'world']] cur.getSchema() Traceback (most recent call last): File "", line 1, in File ".../pyhs2/cursor.py", line 199, in getSchema col['type'] = get_type(c.typeDesc) File ".../pyhs2/cursor.py", line 12, in get_type return TTypeId._VALUES_TO_NAMES[ttype.primitiveEntry.type] KeyError: 18 cur = conn.cursor() print cur.getDatabases() [['default', '']] print cur.getSchema() [{'comment': 'Schema name.', 'columnName': 'TABLE_SCHEM', 'type': 'STRING_TYPE'}, {'comment': 'Catalog name.', 'columnName': 'TABLE_CATALOG', 'type': 'STRING_TYPE'}]

BradRuderman commented 9 years ago

I need to look if HS2 provides this natively, or else this would be wrapped around cur.getSchema() .

serednya commented 9 years ago

So I managed to ""create"" the description field by just using "getSchema()" after a given sql execution (which still cannot reuse the same cursor: bad code?)

description = tuple((col['columnName'], col['type'], None, None, None, None, None) for col in res)

PEP 249

The first two items ( name and type_code ) are mandatory, the other five are optional and are set to None if no meaningful values can be provided.

pilipolio commented 9 years ago

I have just opened https://github.com/BradRuderman/pyhs2/pull/43 to add the description field.