def test_script_or_execute_to_df(secrets):
c = Channel(**secrets["sql"])
stmt = STATEMENTS.get(c.distro.name)
if not stmt:
pytest.skip()
scripted_r = Script(stmt).read()
executed_r = execute(stmt, fetch="df")
assert scripted_r.equals(executed_r)
scripted_t = list(Script(stmt).read().itertuples(name=None, index=False))
executed_t = list(tuple(x) for x in execute(stmt, fetch="tuples"))
assert scripted_t == executed_t
def test_errors_being_swallowed(test_channel):
raw = """
use IRISpii;
select * from linkage.sodifjsd;
if object_id('IRISpii.linkage.setup_for_reparsing') is not null drop table IRISpii.linkage.setup_for_reparsing;
CREATE TABLE [linkage].[setup_for_reparsing](
[display_id] [int] NULL,
[new_emp_number] [varchar](200) NULL,
[last_name] [varchar](50) NULL,
[first_name] [varchar](50) NULL,
[middle_name] [varchar](50) NULL,
[full_name] [varchar](200) NOT NULL
) ON [PRIMARY];
with fulled as (
select
*,
concat(rtrim(replace(emp_last_name, ',', '')), ', ', rtrim(emp_first_name), ' '+rtrim(middle_name)) as full_name
from linkage.sdaoifjs
)
insert into linkage.setup_for_reparsing (display_id, new_emp_number, last_name, first_name, middle_name, full_name)
select * from fulled;
go """
with pytest.raises(Exception):
Script(raw, channel=test_channel).execute()
def test_do_not_add_foolish_semicolon(make_channel):
c = make_channel("mssql")
Script(
"""
SELECT 1 FROM SYS.TABLES;
GO
SELECT 1 FROM SYS.TABLES
GO""",
channel=c,
).execute()
def t_real_query_with_bad_columns(self, test_channel, test_distro, caplog):
stmt = {
"mssql": "select top 5 1 from information_schema.tables;",
"postgresql":
"select 1, 2 from information_schema.tables limit 5;",
"mysql": "select 1, 2 from information_schema.tables limit 5;",
"sqlite": "select 1, 2 from sqlite_master;",
}[test_distro]
df = Script(stmt, channel=test_channel).to_table()
# Builtin SQL drivers return bad column names (blank or '?column?')
assert "WARNING" in caplog.text
assert len(df.columns) > 0
# and there should be no bad columns in the final set
assert not set(Identifier.BLACKLIST).intersection(df.columns)
def t_large_query(self, test_channel):
query = """
select spam from /*vikings.breakfast;
-- eat all the spam before the vikings
GO
create plate as select * from the larder
wait what*/
breakfast
where deliciousness > 9;
go ;
and on;
go;
and on;
"""
cleaned_expected = [
"select spam from breakfast where deliciousness > 9;",
"and on;",
"and on;",
]
q = Script(query, channel=test_channel)
assert cleanup_whitespace(q.parsed) == cleaned_expected
def t_create_objects_via_shared_channel(self, test_channel):
s = Script("select * from spam;", channel=test_channel)
t = Table("spam", channel=test_channel)
assert s.channel == t.channel == test_channel
def t_parsing_nested_multilines(self, inputs, outputs, test_channel):
s = Script(inputs, channel=test_channel)
assert s.parsed[0] == outputs
def t_parsing_ignore_non_double_dash(self, inputs, outputs, test_channel):
s = Script(inputs, channel=test_channel)
assert s.parsed == outputs
def t_comments_proper_double_dash(self, inputs, outputs, test_channel):
s = Script(inputs, channel=test_channel)
assert s.parsed == outputs
def t_query_parsing_ignoring_comments(self, inputs, cleaned_expected,
test_channel):
s = Script(inputs, channel=test_channel)
assert cleanup_whitespace(s.parsed) == cleaned_expected
def t_q_parsing(self, test_channel):
q = Script("select * from hi.there;", channel=test_channel)
assert q.parsed == ["select * from hi.there;"]
def t_do_not_get_too_angry_at_go(self, s):
assert "go" in Script._normalize_batch_end(s)
def t_strip_semis(self, semi, gogo):
s = "select 1 from sys.tables" + semi + gogo
assert Script._normalize_batch_end(s) == "select 1 from sys.tables;"