Running simple SQL commands on the command line

command line, commandline, zsql, ~/bin, postgresql, oracle, mysql, psql, psql -f, rundotsql, rundotsql.py, dtml

7^th of January 2005

In my Zope usage I use a lot of SQL stored on the filesystem. These files contain DTML syntax and parameters like ZSQL Method objects inside the ZODB. Sometimes, to test the SQL code I have in these files I want to run it on the command line. To be able to do that I've written this little Python script which is placed in my ~/bin directory in Linux. It only works with PostgreSQL at the moment but people who like the idea and prefer Oracle or MySQL could probably find where to make their changes. (The psql -f command is the ticket)

Example foo.sql:

 <params>id=1
 name</params>
 SELECT *
 FROM sometable
 WHERE id = <dtml-sqlvar id type="int">
 AND   name = <dtml-sqlvar name type="string">

Which is run like this:

 peterbe@trillian:~ $ rundotsql.py -U peterbe testdb foo.sql

If you run the command:

 peterbe@trillian:~ $ rundotsql.py -h

then you'll hopefully understand how it works. If you have specified the database connection string once you won't have to do it again unless you're changing database. If you want to test your SQL but without committing anything for real to the database you can run it like this:

 peterbe@trillian:~ $ rundotsql.py --test delete_everything.sql

It will wrap the SQL in a BEGIN and ROLLBACK command.

Have a play with it if you might find it useful. Please let me know of any problems or ideas you have. The DTML "rendering" is dead-stupid. It's NOT using the real Zope DTML rendering engine; rather, it's using some simple regular expressions.

Comment

	Name: Email: hide my email address. Your email address will be encoded to prevent email-extraction spiders from reading it so you won't get spammed if you decide to show your email address.
	What number is this?

Dear all people entering comments,
Due to an increase in comment spam I've had to make it mandatory to recognize the imagenumbers. If you're unable to post comments, please contact me as soon as possible.