def test_print_named_queries(named_query):
PGSpecial().execute(None, "\\ns test_name select * from bar")
assert "test_name" in NamedQueries.instance.list()
result = PGSpecial().execute(None, "\\np test_n.*")
assert result == [("", [("test_name", "select * from bar")],
["Name", "Query"], "")]
result = PGSpecial().execute(None, "\\np")
assert result[0][:3] == (
None,
None,
None,
)
def run(conn, sql, config, user_namespace):
if sql.strip():
for statement in sqlparse.split(sql):
first_word = sql.strip().split()[0].lower()
if first_word == 'begin':
raise Exception("ipython_sql does not support transactions")
if first_word.startswith('\\') and 'postgres' in str(conn.dialect):
pgspecial = PGSpecial()
_, cur, headers, _ = pgspecial.execute(
conn.session.connection.cursor(), statement)[0]
result = FakeResultProxy(cur, headers)
else:
txt = sqlalchemy.sql.text(statement)
result = conn.session.execute(txt, user_namespace)
try:
# mssql has autocommit
if 'mssql' not in str(conn.dialect):
conn.session.execute('commit')
except sqlalchemy.exc.OperationalError:
pass # not all engines can commit
if result and config.feedback:
print(interpret_rowcount(result.rowcount))
resultset = ResultSet(result, statement, config)
if config.autopandas:
return resultset.DataFrame()
else:
return resultset
#returning only last result, intentionally
else:
return 'Connected: %s' % conn.name
def run(conn, sql, config, user_namespace):
if sql.strip():
for statement in sqlparse.split(sql):
first_word = sql.strip().split()[0].lower()
if first_word == 'begin':
raise Exception("ipython_sql does not support transactions")
if first_word.startswith('\\') and 'postgres' in str(conn.dialect):
if not PGSpecial:
raise ImportError('pgspecial not installed')
pgspecial = PGSpecial()
_, cur, headers, _ = pgspecial.execute(
conn.session.connection.cursor(), statement)[0]
result = FakeResultProxy(cur, headers)
else:
txt = sqlalchemy.sql.text(statement)
result = conn.session.execute(txt, user_namespace)
with conn.cursor() as cur:
cur.execute(txt).fetchall()
_commit(conn=conn, config=config)
if result and config.feedback:
print(interpret_rowcount(result.rowcount))
resultset = ResultSet(result, statement, config)
if config.autopandas:
return resultset.DataFrame()
else:
return resultset
#returning only last result, intentionally
else:
return 'Connected: %s' % conn.name
def __init__(self, force_passwd_prompt=False, never_passwd_prompt=False,
pgexecute=None, pgclirc_file=None, row_limit=None,
single_connection=False):
self.force_passwd_prompt = force_passwd_prompt
self.never_passwd_prompt = never_passwd_prompt
self.pgexecute = pgexecute
# Load config.
c = self.config = get_config(pgclirc_file)
self.logger = logging.getLogger(__name__)
self.initialize_logging()
self.set_default_pager(c)
self.output_file = None
self.pgspecial = PGSpecial()
self.multi_line = c['main'].as_bool('multi_line')
self.multiline_mode = c['main'].get('multi_line_mode', 'psql')
self.vi_mode = c['main'].as_bool('vi')
self.pgspecial.timing_enabled = c['main'].as_bool('timing')
if row_limit is not None:
self.row_limit = row_limit
else:
self.row_limit = c['main'].as_int('row_limit')
self.table_format = c['main']['table_format']
self.syntax_style = c['main']['syntax_style']
self.cli_style = c['colors']
self.wider_completion_menu = c['main'].as_bool('wider_completion_menu')
self.less_chatty = c['main'].as_bool('less_chatty')
self.null_string = c['main'].get('null_string', '<null>')
self.prompt_format = c['main'].get('prompt', self.default_prompt)
self.on_error = c['main']['on_error'].upper()
self.completion_refresher = CompletionRefresher()
self.query_history = []
# Initialize completer
smart_completion = c['main'].as_bool('smart_completion')
keyword_casing = c['main']['keyword_casing']
self.settings = {
'casing_file': get_casing_file(c),
'generate_casing_file': c['main'].as_bool('generate_casing_file'),
'generate_aliases': c['main'].as_bool('generate_aliases'),
'asterisk_column_order': c['main']['asterisk_column_order'],
'single_connection': single_connection,
'keyword_casing': keyword_casing,
}
completer = PGCompleter(smart_completion, pgspecial=self.pgspecial,
settings=self.settings)
self.completer = completer
self._completer_lock = threading.Lock()
self.register_special_commands()
self.eventloop = create_eventloop()
self.cli = None
def __init__(self,
force_passwd_prompt=False,
never_passwd_prompt=False,
pgexecute=None,
pgclirc_file=None):
self.force_passwd_prompt = force_passwd_prompt
self.never_passwd_prompt = never_passwd_prompt
self.pgexecute = pgexecute
from pgcli import __file__ as package_root
package_root = os.path.dirname(package_root)
default_config = os.path.join(package_root, 'pgclirc')
write_default_config(default_config, pgclirc_file)
# Load config.
c = self.config = load_config(pgclirc_file, default_config)
self.logger = logging.getLogger(__name__)
self.initialize_logging()
self.set_default_pager(c)
self.output_file = None
self.pgspecial = PGSpecial()
self.multi_line = c['main'].as_bool('multi_line')
self.vi_mode = c['main'].as_bool('vi')
self.pgspecial.timing_enabled = c['main'].as_bool('timing')
self.table_format = c['main']['table_format']
self.syntax_style = c['main']['syntax_style']
self.cli_style = c['colors']
self.wider_completion_menu = c['main'].as_bool('wider_completion_menu')
self.on_error = c['main']['on_error'].upper()
self.completion_refresher = CompletionRefresher()
self.query_history = []
# Initialize completer
smart_completion = c['main'].as_bool('smart_completion')
completer = PGCompleter(smart_completion, pgspecial=self.pgspecial)
self.completer = completer
self._completer_lock = threading.Lock()
self.register_special_commands()
self.cli = None
def executor(connection):
cur = connection.cursor()
pgspecial = PGSpecial()
def query_runner(sql):
results = []
for title, rows, headers, status in pgspecial.execute(cur=cur,
sql=sql):
if rows:
results.extend((title, list(rows), headers, status))
else:
results.extend((title, None, headers, status))
return results
return query_runner
def test_exit_without_active_connection(executor):
quit_handler = MagicMock()
pgspecial = PGSpecial()
pgspecial.register(quit_handler,
'\\q',
'\\q',
'Quit pgcli.',
arg_type=NO_QUERY,
case_sensitive=True,
aliases=(':q', ))
with patch.object(executor, "conn", BrokenConnection()):
# we should be able to quit the app, even without active connection
run(executor, "\\q", pgspecial=pgspecial)
quit_handler.assert_called_once()
# an exception should be raised when running a query without active connection
with pytest.raises(psycopg2.InterfaceError):
run(executor, "select 1", pgspecial=pgspecial)
def __init__(self, force_passwd_prompt=False, never_passwd_prompt=False,
pgexecute=None, pgclirc_file=None, row_limit=None,
single_connection=False, less_chatty=None, prompt=None, prompt_dsn=None,
auto_vertical_output=False, warn=None):
self.force_passwd_prompt = force_passwd_prompt
self.never_passwd_prompt = never_passwd_prompt
self.pgexecute = pgexecute
self.dsn_alias = None
self.watch_command = None
# Load config.
c = self.config = get_config(pgclirc_file)
NamedQueries.instance = NamedQueries.from_config(self.config)
self.logger = logging.getLogger(__name__)
self.initialize_logging()
self.set_default_pager(c)
self.output_file = None
self.pgspecial = PGSpecial()
self.multi_line = c['main'].as_bool('multi_line')
self.multiline_mode = c['main'].get('multi_line_mode', 'psql')
self.vi_mode = c['main'].as_bool('vi')
self.auto_expand = auto_vertical_output or c['main'].as_bool(
'auto_expand')
self.expanded_output = c['main'].as_bool('expand')
self.pgspecial.timing_enabled = c['main'].as_bool('timing')
if row_limit is not None:
self.row_limit = row_limit
else:
self.row_limit = c['main'].as_int('row_limit')
self.min_num_menu_lines = c['main'].as_int('min_num_menu_lines')
self.multiline_continuation_char = c['main']['multiline_continuation_char']
self.table_format = c['main']['table_format']
self.syntax_style = c['main']['syntax_style']
self.cli_style = c['colors']
self.wider_completion_menu = c['main'].as_bool('wider_completion_menu')
c_dest_warning = c['main'].as_bool('destructive_warning')
self.destructive_warning = c_dest_warning if warn is None else warn
self.less_chatty = bool(less_chatty) or c['main'].as_bool('less_chatty')
self.null_string = c['main'].get('null_string', '<null>')
self.prompt_format = prompt if prompt is not None else c['main'].get('prompt', self.default_prompt)
self.prompt_dsn_format = prompt_dsn
self.on_error = c['main']['on_error'].upper()
self.decimal_format = c['data_formats']['decimal']
self.float_format = c['data_formats']['float']
self.keyring_enabled = c["main"].as_bool("keyring")
self.pgspecial.pset_pager(self.config['main'].as_bool(
'enable_pager') and "on" or "off")
self.style_output = style_factory_output(
self.syntax_style, c['colors'])
self.now = dt.datetime.today()
self.completion_refresher = CompletionRefresher()
self.query_history = []
# Initialize completer
smart_completion = c['main'].as_bool('smart_completion')
keyword_casing = c['main']['keyword_casing']
self.settings = {
'casing_file': get_casing_file(c),
'generate_casing_file': c['main'].as_bool('generate_casing_file'),
'generate_aliases': c['main'].as_bool('generate_aliases'),
'asterisk_column_order': c['main']['asterisk_column_order'],
'qualify_columns': c['main']['qualify_columns'],
'case_column_headers': c['main'].as_bool('case_column_headers'),
'search_path_filter': c['main'].as_bool('search_path_filter'),
'single_connection': single_connection,
'less_chatty': less_chatty,
'keyword_casing': keyword_casing,
}
completer = PGCompleter(smart_completion, pgspecial=self.pgspecial,
settings=self.settings)
self.completer = completer
self._completer_lock = threading.Lock()
self.register_special_commands()
self.prompt_app = None
static_folder="../dist/static",
template_folder="../dist")
# Define origins of api requests to enable Cross Origin Request Sharing for session object across api requests
cors = CORS(app,
resources={r"/api/*": {
'origins': ['http://localhost:8080']
}},
headers=['Content-Type'],
expose_headers=['Access-Control-Allow-Origin'],
supports_credentials=True)
# Load app and database settings from config file
config(app)
pgspecial = PGSpecial()
#####################
# Utility functions #
#####################
# Get a database connection for a given username and password
def fetch_connection(username, password):
try:
connection = pg.connect(user=username,
password=password,
host=app.config['host'],
port=app.config['port'],
database=app.config['dbname'])
def __init__(
self,
force_passwd_prompt=False,
never_passwd_prompt=False,
pgexecute=None,
pgclirc_file=None,
row_limit=None,
single_connection=False,
less_chatty=None,
prompt=None,
prompt_dsn=None,
auto_vertical_output=False,
warn=None,
):
self.force_passwd_prompt = force_passwd_prompt
self.never_passwd_prompt = never_passwd_prompt
self.pgexecute = pgexecute
self.dsn_alias = None
self.watch_command = None
# Load config.
c = self.config = get_config(pgclirc_file)
NamedQueries.instance = NamedQueries.from_config(self.config)
self.logger = logging.getLogger(__name__)
self.initialize_logging()
self.set_default_pager(c)
self.output_file = None
self.pgspecial = PGSpecial()
self.multi_line = c["main"].as_bool("multi_line")
self.multiline_mode = c["main"].get("multi_line_mode", "psql")
self.vi_mode = c["main"].as_bool("vi")
self.auto_expand = auto_vertical_output or c["main"].as_bool("auto_expand")
self.expanded_output = c["main"].as_bool("expand")
self.pgspecial.timing_enabled = c["main"].as_bool("timing")
if row_limit is not None:
self.row_limit = row_limit
else:
self.row_limit = c["main"].as_int("row_limit")
self.min_num_menu_lines = c["main"].as_int("min_num_menu_lines")
self.multiline_continuation_char = c["main"]["multiline_continuation_char"]
self.table_format = c["main"]["table_format"]
self.syntax_style = c["main"]["syntax_style"]
self.cli_style = c["colors"]
self.wider_completion_menu = c["main"].as_bool("wider_completion_menu")
c_dest_warning = c["main"].as_bool("destructive_warning")
self.destructive_warning = c_dest_warning if warn is None else warn
self.less_chatty = bool(less_chatty) or c["main"].as_bool("less_chatty")
self.null_string = c["main"].get("null_string", "<null>")
self.prompt_format = (
prompt
if prompt is not None
else c["main"].get("prompt", self.default_prompt)
)
self.prompt_dsn_format = prompt_dsn
self.on_error = c["main"]["on_error"].upper()
self.decimal_format = c["data_formats"]["decimal"]
self.float_format = c["data_formats"]["float"]
self.initialize_keyring()
self.pgspecial.pset_pager(
self.config["main"].as_bool("enable_pager") and "on" or "off"
)
self.style_output = style_factory_output(self.syntax_style, c["colors"])
self.now = dt.datetime.today()
self.completion_refresher = CompletionRefresher()
self.query_history = []
# Initialize completer
smart_completion = c["main"].as_bool("smart_completion")
keyword_casing = c["main"]["keyword_casing"]
self.settings = {
"casing_file": get_casing_file(c),
"generate_casing_file": c["main"].as_bool("generate_casing_file"),
"generate_aliases": c["main"].as_bool("generate_aliases"),
"asterisk_column_order": c["main"]["asterisk_column_order"],
"qualify_columns": c["main"]["qualify_columns"],
"case_column_headers": c["main"].as_bool("case_column_headers"),
"search_path_filter": c["main"].as_bool("search_path_filter"),
"single_connection": single_connection,
"less_chatty": less_chatty,
"keyword_casing": keyword_casing,
}
completer = PGCompleter(
smart_completion, pgspecial=self.pgspecial, settings=self.settings
)
self.completer = completer
self._completer_lock = threading.Lock()
self.register_special_commands()
self.prompt_app = None
def pgspecial():
return PGSpecial()
def test_delete_named_queries(named_query):
PGSpecial().execute(None, "\\ns test_foo select * from foo")
assert "test_foo" in NamedQueries.instance.list()
PGSpecial().execute(None, "\\nd test_foo")
assert "test_foo" not in NamedQueries.instance.list()
def test_save_named_queries(named_query):
PGSpecial().execute(None, "\\ns test select * from foo")
expected = {"test": "select * from foo"}
assert NamedQueries.instance.list() == expected
