Package osh :: Package command :: Module sql
[frames] | no frames]

Source Code for Module osh.command.sql

  1  # osh 
  2  # Copyright (C) 2005 Jack Orenstein <jao@geophile.com> 
  3  # 
  4  # This program is free software; you can redistribute it and/or modify 
  5  # it under the terms of the GNU General Public License as published by 
  6  # the Free Software Foundation; either version 2 of the License, or 
  7  # (at your option) any later version. 
  8  # 
  9  # This program is distributed in the hope that it will be useful, 
 10  # but WITHOUT ANY WARRANTY; without even the implied warranty of 
 11  # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
 12  # GNU General Public License for more details. 
 13  # 
 14  # You should have received a copy of the GNU General Public License 
 15  # along with this program; if not, write to the Free Software 
 16  # Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. 
 17   
 18  """C{sql [DB] QUERY} 
 19   
 20  Executes a sql query on a specified database.  Occurrences of 
 21  formatting directives (e.g. C{%s}) will be replaced by input values. 
 22   
 23  The database is selected by C{DB}. If C{DB} matches an C{osh.sql} 
 24  profile in C{.oshrc}, then the database is specified by that 
 25  profile. If C{DB} is omitted, then the default profile is used. 
 26   
 27  If C{QUERY} is a I{select} statement, then the query is executed and 
 28  output rows, represented by tuples, are written to output.  If C{QUERY} 
 29  is any other type of SQL statement, then no output is written. 
 30  """ 
 31   
 32  import os 
 33   
 34  import osh.loader 
 35  import osh.core 
 36   
 37  # CLI 
38 -def _sql():
39 return _Sql()
40 41 # CLI
42 -def sql(query, db = None):
43 """Executes a sql query on a specified database. Occurrences of 44 formatting directives (e.g. C{%s}) will be replaced by input values. 45 The database is selected by C{db}. If C{db} matches an C{osh.sql} 46 profile in C{.oshrc}, then the database is specified by that 47 profile. If C{db} is C{None}, then the default profile is used. 48 If C{QUERY} is a I{select} statement, then the query is executed and 49 output rows, represented by tuples, are written to output. If C{QUERY} 50 is any other type of SQL statement, then no output is written. 51 """ 52 args = [] 53 if db: 54 args.append(db) 55 args.append(query) 56 return _Sql().process_args(*args)
57
58 -def _tuple_like(object):
59 return isinstance(object, list) or isinstance(object, tuple)
60
61 -class _Sql(osh.core.Generator):
62 63 _db_type = None 64 _host = None 65 _db = None 66 _header = None 67 _user = None 68 _password = None 69 _connection = None 70 _query = None 71 _has_output = None 72 73 74 # object interface 75
76 - def __init__(self):
77 osh.core.Generator.__init__(self, '', (1, 2))
78 79 80 # OshCommand interface 81
82 - def doc(self):
83 return __doc__
84
85 - def setup(self):
86 args = self.args() 87 db_profile = None 88 if args.has_next(): 89 query = args.next_string() 90 if args.has_next(): 91 db_profile = query 92 query = args.next_string() 93 if args.has_next(): 94 self.usage() 95 else: 96 self.usage() 97 if not db_profile: 98 db_profile_from_env = osh.core.default_db_profile 99 if db_profile_from_env: 100 db_profile = db_profile_from_env 101 else: 102 db_profile = osh.core.config_value('sql') 103 if not db_profile: 104 raise Exception('No db profile selected') 105 # query 106 self._query = query 107 # connection info 108 dbtype = osh.core.config_value('sql', db_profile, 'dbtype') 109 host = osh.core.config_value('sql', db_profile, 'host') 110 db = osh.core.config_value('sql', db_profile, 'db') 111 user = osh.core.config_value('sql', db_profile, 'user') 112 password = osh.core.config_value('sql', db_profile, 'password') 113 # query type 114 self._has_output = self._is_select() 115 # Load dbtype module and connect 116 self._db_type = osh.loader.load_and_create('sql' + dbtype) 117 self._connection = self._db_type.connect(db, host, user, password)
118 119 120 # Generator interface 121
122 - def execute(self):
123 # If this command is the first in a pipeline, then generate will be 124 # called. 125 self._execute_query(self._query)
126 127 128 # Receiver interface 129
130 - def receive(self, object):
131 self._execute_query(self._bind(object))
132 133 134 # For use by this class 135
136 - def _execute_query(self, query):
137 try: 138 if self._has_output: 139 for row in self._db_type.run_query(self._connection, query): 140 self.send(row) 141 else: 142 self.send(self._db_type.run_update(self._connection, query)) 143 finally: 144 self.send_complete()
145
146 - def _bind(self, object):
147 query = self._query 148 if _tuple_like(object): 149 tuple = object 150 else: 151 tuple = (object,) 152 for value in tuple: 153 query = query.replace('%s', str(value), 1) 154 return query
155
156 - def _is_select(self):
157 query = self._query.lower() 158 select_position = self._find(query, 'select') 159 insert_position = self._find(query, 'insert') 160 update_position = self._find(query, 'update') 161 delete_position = self._find(query, 'delete') 162 return (select_position < len(query) and 163 select_position < insert_position and 164 select_position < update_position and 165 select_position < delete_position)
166
167 - def _find(self, string, substring):
168 position = string.find(substring) 169 if position == -1: 170 position = len(string) 171 return position
172
173 -class _DBType(object):
174
175 - def connect(self, db, host, user, password):
176 assert False
177
178 - def run_query(self, connection, query):
179 assert False
180
181 - def run_update(self, connection, query):
182 assert False
183