I would like to create zip archives within a python batch script. I would like to compress individual files or entire directories of files. You can use the built-in zipfile module, and create a ZipFile as you would a normal File object, e.g., >>> >>> foo = zipfile.ZipFile(‘foo.zip’, mode=’w’) >>> foo.write(‘foo.txt’) >>> Unfortunately, by default […]
Author: timwarnock
chaining ssh tunnels
Imagine you’re working within a private home network and need to connect to an Oracle database within a corporate network accessible only through a bastion host hidden within the corporate network. Odd as that sounds, it’s a typical network configuration, as follows: The layout is very simple, when you’re within the corporate network you must […]
timeout command in python
I would like to add a timeout to any shell command such that if it does not complete within a specified number of seconds the command will exit. This would be useful for a any long-running command where I’d like it to die on its own rather than manually killing the long-running process. There are […]
python slice and sql every Nth row
I would like to retrieve every Nth row of a SQL table, and I would like this accessed via a python slice function. A python slice allows access to a list (or any object that implements a __getitem__ method) by a start, stop, and step — for example, >>> foo = range(100) >>> foo[5] 5 […]
sqlplus, utility scripts
I would like to use sqlplus in development projects, and I would like it to be easy to use. First off, I want command-history and tab-completion. The easiest approach is to use rlwrap which uses the GNU readline library as a wrapper to command-line programs such as sqlplus. If you install rlwrap, you can then […]
screen and screenrc
I would like to use GNU screen as a window manager. By default screen seems like little more than a persistent shell (that you can resume even after logging out). By itself, this is incredibly useful if you wish to access the exact same terminal session from different locations. For example, on the command-line you […]
nvl, ifnull, nullif, isnull, coalesce
I would like to programmatically handle NULL values in a database. If I add or multiply a known value to a NULL, the result is still NULL. This is an appropriate result as a NULL value in a database schema means “unknown” or “nonexistent”, so mathematic functions against known values and NULL should produce NULL. […]
oracle, limit results and pagination
I would like to limit query results in oracle to support pagination. In postgres, mysql, and sqlite you can use the LIMIT and OFFSET modifier, e.g., mysql> SELECT login FROM users ORDER BY login LIMIT 2 OFFSET 70001; +————-+ | login | +————-+ | acrobat11 | | acrobat12 | +————-+ 2 rows in set (0.03 […]
sqlplus, edit stored procedures
Previously, we discussed viewing stored procedures in sqlplus. It came to my attention in an Ask Tom article of a very handy way to edit a package in sqlplus. Set your $SQLPATH environment variable to wherever you wish to store useful scripts, including a login.sql — make sure to set an editor in login.sql, e.g., […]
sqlplus, view stored procedures
I would like to view stored procedures and packages in sqlplus. All stored procedures, functions, and packages can be found in the user_source, dba_source, or all_source (depending on your access). Let’s examine user_source. foo_owner@FOO> desc user_source Name Null? Type —————————————– ——– —————————- NAME VARCHAR2(30) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000) foo_owner@FOO> Let’s examine user_source.type to […]
sqlplus explain plan
I would like to view explain plan output in sqlplus. One easy approach is to set autotrace. You can set autotrace on to show query statistics as well as an exection plan. You can also view only the execution plan, for example, foo_owner@FOO> set autotrace on explain foo_owner@FOO> SELECT COUNT(*) FROM tax_facts WHERE create_date EXPLAIN […]
sqlplus, exploring schemas and data
I would like to get information about different schemas and tables in sqlplus. I would like this to be as easy as mysql, it’s less intuitive but almost as easy. To get a list of schemas (similar to mysql “show databases”), you can run the following, foo_owner@FOO> SELECT username FROM all_users ORDER BY username; USERNAME […]
sqlplus pagesize and linesize
I would like sqlplus output to be more readable. Fortunately, you can adjust the pagesize and linesize variables on the fly, e.g., foo_owner@FOO> set pagesize 50000 foo_owner@FOO> set linesize 120 You can set pagesize to 0, which is very useful for scripted output, as it will not print any column headers and only print the […]
sqlplus command prompt (sqlprompt)
I would like to change the sqlplus command-prompt to something more useful than SQL> You can modify the sqlprompt variable as follows, SQL> SQL> set sqlprompt “_USER’@’_CONNECT_IDENTIFIER> ” FOO_OWNER@FOO> You can also use variables such as _PRIVILEGE and _DATE, although all I really want is the user and schema. Unfortunately, with the above technique, if […]
reverse ssh tunnel
I would like ssh access to a protected host that is not directly accessible on the Internet but does have outbound access. This is a common scenario in corporate networks that often require a vpn for remote access; but in situations where vpn access is not available (e.g., I forgot my keyfob, or I don’t […]