Running simple SQL commands on the command line

08 January 2005   2 comments   Python

https://www.peterbe.com/plog/rundotsql/rundotsql.py

Mind That Age!

This blog post is 12 years old! Most likely, its content is outdated. Especially if it's technical.

Powered by Fusion×

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.

Follow @peterbe on Twitter

Comments

Tony
mysql already does this:
mysql -u uname dbname < file.sql
Peter Bengtsson
What if the file has DTML in it? With conditionals and parameters?
I think it's the same syntax for postgres but again it does have the same amount of magic :)
Thank you for posting a comment

Your email will never ever be published


Related posts

Previous:
www.issuetrackerproduct.com is out! 04 January 2005
Next:
My favorite CSS Zen Garden design 10 January 2005
Related by Keyword:
How do log ALL PostgreSQL SQL happening 20 July 2015
Fastest database for Tornado 09 October 2013
Postgres collation errors on CITEXT fields when upgrading to 9.1 21 May 2012
Connecting with psycopg2 without a username and password 24 February 2011
Optimization of getting random rows out of a PostgreSQL in Django 23 February 2011
Related by Text:
How to use premailer as a command line script 13 July 2012
CommandLineApp by Doug Hellmann 22 February 2008
gorun.py - Using (py)inotify to run commands when files change 20 July 2009
Date formatting in python or in PostgreSQL 20 July 2004
type - Writing shell scripts 28 April 2006