Python SQL Examples

Example #1

File: test_sql_compute.py Project: vitan/blaze

def test_outer_join():
    L = Symbol('L', 'var * {id: int, name: string, amount: real}')
    R = Symbol('R', 'var * {city: string, id: int}')

    from blaze.sql import SQL
    engine = sa.create_engine('sqlite:///:memory:')

    _left = [(1, 'Alice', 100), (2, 'Bob', 200), (4, 'Dennis', 400)]
    left = SQL(engine, 'left', schema=L.schema)
    left.extend(_left)

    _right = [('NYC', 1), ('Boston', 1), ('LA', 3), ('Moscow', 4)]
    right = SQL(engine, 'right', schema=R.schema)
    right.extend(_right)

    conn = engine.connect()

    query = compute(join(L, R, how='inner'), {L: left.table, R: right.table})
    result = list(map(tuple, conn.execute(query).fetchall()))

    assert set(result) == set([(1, 'Alice', 100, 'NYC'),
                               (1, 'Alice', 100, 'Boston'),
                               (4, 'Dennis', 400, 'Moscow')])

    query = compute(join(L, R, how='left'), {L: left.table, R: right.table})
    result = list(map(tuple, conn.execute(query).fetchall()))

    assert set(result) == set([(1, 'Alice', 100, 'NYC'),
                               (1, 'Alice', 100, 'Boston'),
                               (2, 'Bob', 200, None),
                               (4, 'Dennis', 400, 'Moscow')])

    query = compute(join(L, R, how='right'), {L: left.table, R: right.table})
    print(query)
    result = list(map(tuple, conn.execute(query).fetchall()))
    print(result)

    assert set(result) == set([(1, 'Alice', 100, 'NYC'),
                               (1, 'Alice', 100, 'Boston'),
                               (3, None, None, 'LA'),
                               (4, 'Dennis', 400, 'Moscow')])

    # SQLAlchemy doesn't support full outer join
    """
    query = compute(join(L, R, how='outer'), {L: left.table, R: right.table})
    result = list(map(tuple, conn.execute(query).fetchall()))

    assert set(result) == set(
            [(1, 'Alice', 100, 'NYC'),
             (1, 'Alice', 100, 'Boston'),
             (2, 'Bob', 200, None),
             (3, None, None, 'LA'),
             (4, 'Dennis', 400, 'Moscow')])
    """

    conn.close()

Example #2

File: test_into_exhaustive.py Project: pgnepal/blaze

                    ('name', 'U7'), ('timestamp', 'M8[us]')])

schema = '{amount: int64, id: int64, name: string, timestamp: datetime}'
sql_schema = '{amount: int64, id: int64, name: string, timestamp: datetime[tz="UTC"]}'

arr = nd.array(L, dtype=schema)

bc = bcolz.ctable([np.array([100, 200, 300], dtype=np.int64),
                   np.array([1, 2, 3], dtype=np.int64),
                   np.array(['Alice', 'Bob', 'Charlie'], dtype='U7'),
                   np.array([datetime(2000, 12, 25, 0, 0, 1),
                             datetime(2001, 12, 25, 0, 0, 1),
                             datetime(2002, 12, 25, 0, 0, 1)], dtype='M8[us]')],
                  names=['amount', 'id', 'name', 'timestamp'])

sql = SQL('sqlite:///:memory:', 'accounts', schema=schema)
sql.extend(L)

data = {list: L,
        Table: Table(L, '{amount: int64, id: int64, name: string[7], timestamp: datetime}'),
        DataFrame: df,
        np.ndarray: x,
        nd.array: arr,
        bcolz.ctable: bc,
        CSV: csv,
        SQL: sql}

schema_no_date = '{amount: int64, id: int64, name: string[7]}'
sql_no_date = SQL('sqlite:///:memory:', 'accounts_no_date', schema=schema_no_date)

L_no_date = list(pluck([0, 1, 2], L))

Example #3

File: test_sql_compute.py Project: holdenk/blaze

def test_outer_join():
    L = TableSymbol('L', '{id: int, name: string, amount: real}')
    R = TableSymbol('R', '{city: string, id: int}')

    from blaze.sql import SQL
    engine = sa.create_engine('sqlite:///:memory:')

    _left = [(1, 'Alice', 100),
            (2, 'Bob', 200),
            (4, 'Dennis', 400)]
    left = SQL(engine, 'left', schema=L.schema)
    left.extend(_left)

    _right = [('NYC', 1),
             ('Boston', 1),
             ('LA', 3),
             ('Moscow', 4)]
    right = SQL(engine, 'right', schema=R.schema)
    right.extend(_right)

    conn = engine.connect()


    query = compute(join(L, R, how='inner'), {L: left.table, R: right.table})
    result = list(map(tuple, conn.execute(query).fetchall()))

    assert set(result) == set(
            [(1, 'Alice', 100, 'NYC'),
             (1, 'Alice', 100, 'Boston'),
             (4, 'Dennis', 400, 'Moscow')])

    query = compute(join(L, R, how='left'), {L: left.table, R: right.table})
    result = list(map(tuple, conn.execute(query).fetchall()))

    assert set(result) == set(
            [(1, 'Alice', 100, 'NYC'),
             (1, 'Alice', 100, 'Boston'),
             (2, 'Bob', 200, None),
             (4, 'Dennis', 400, 'Moscow')])

    query = compute(join(L, R, how='right'), {L: left.table, R: right.table})
    print(query)
    result = list(map(tuple, conn.execute(query).fetchall()))
    print(result)

    assert set(result) == set(
            [(1, 'Alice', 100, 'NYC'),
             (1, 'Alice', 100, 'Boston'),
             (3, None, None, 'LA'),
             (4, 'Dennis', 400, 'Moscow')])

    # SQLAlchemy doesn't support full outer join
    """
    query = compute(join(L, R, how='outer'), {L: left.table, R: right.table})
    result = list(map(tuple, conn.execute(query).fetchall()))

    assert set(result) == set(
            [(1, 'Alice', 100, 'NYC'),
             (1, 'Alice', 100, 'Boston'),
             (2, 'Bob', 200, None),
             (3, None, None, 'LA'),
             (4, 'Dennis', 400, 'Moscow')])
    """

    conn.close()

Example #4

File: test_comprehensive.py Project: ChrisBeaumont/blaze

t = TableSymbol('t', '{amount: int64, id: int64, name: string}')

L = [[100, 1, 'Alice'],
     [200, 2, 'Bob'],
     [300, 3, 'Charlie'],
     [400, 4, 'Dan'],
     [500, 5, 'Edith']]

df = DataFrame(L, columns=['amount', 'id', 'name'])

x = into(np.ndarray, df)

bc = into(bcolz.ctable, df)

sql = SQL('sqlite:///:memory:', 'accounts', schema=t.schema)
sql.extend(L)

sources = [df, x, bc, sql]

try:
    import pymongo
except ImportError:
    pymongo = mongo = None
if pymongo:
    from blaze.mongo import *
    try:
        db = pymongo.MongoClient().db
        db._test_comprehensive.drop()
        mongo = into(db._test_comprehensive, df)
        sources.append(mongo)

Example #5

File: test_into_exhaustive.py Project: vitan/blaze

def test_into_empty_sql():
    """ Test all sources into empty SQL database """
    sources = [v for k, v in data if k not in [list]]
    for a in sources:
        sql_empty = SQL('sqlite:///:memory:', 'accounts', schema=sql_schema)
        assert normalize(into(sql_empty, a)) == normalize(sql)

Example #6

File: test_into_exhaustive.py Project: vitan/blaze

arr = nd.array(L, dtype=schema)

bc = bcolz.ctable([
    np.array([100, 200, 300], dtype=np.int64),
    np.array([1, 2, 3], dtype=np.int64),
    np.array(['Alice', 'Bob', 'Charlie'], dtype='U7'),
    np.array([
        datetime(2000, 12, 25, 0, 0, 1),
        datetime(2001, 12, 25, 0, 0, 1),
        datetime(2002, 12, 25, 0, 0, 1)
    ],
             dtype='M8[us]')
],
                  names=['amount', 'id', 'name', 'timestamp'])

sql = SQL('sqlite:///:memory:', 'accounts', schema=schema)
sql.extend(L)

data = [
    (list, L),
    (Data,
     Data(
         L,
         'var * {amount: int64, id: int64, name: string[7], timestamp: datetime}'
     )), (DataFrame, df), (np.ndarray, x), (nd.array, arr), (bcolz.ctable, bc),
    (CSV, csv), (SQL, sql)
]

schema_no_date = '{amount: int64, id: int64, name: string[7]}'
sql_no_date = SQL('sqlite:///:memory:',
                  'accounts_no_date',

Example #7

File: test_comprehensive.py Project: vitan/blaze

from blaze.sql import SQL

sources = []

t = Symbol('t', 'var * {amount: int64, id: int64, name: string}')

L = [[100, 1, 'Alice'], [200, 2, 'Bob'], [300, 3, 'Charlie'], [400, 4, 'Dan'],
     [500, 5, 'Edith']]

df = DataFrame(L, columns=['amount', 'id', 'name'])

x = into(np.ndarray, df)

bc = into(bcolz.ctable, df)

sql = SQL('sqlite:///:memory:', 'accounts', schema=t.schema)
sql.extend(L)

sources = [df, x, bc, sql]

try:
    import pymongo
except ImportError:
    pymongo = mongo = None
if pymongo:
    from blaze.mongo import *
    try:
        db = pymongo.MongoClient().db
        db._test_comprehensive.drop()
        mongo = into(db._test_comprehensive, df)
        sources.append(mongo)