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()
('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))
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()
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)
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)
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',
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)