1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
40
41
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
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
75
78
79
80
81
84
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
106 self._query = query
107
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
114 self._has_output = self._is_select()
115
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
121
123
124
125 self._execute_query(self._query)
126
127
128
129
131 self._execute_query(self._bind(object))
132
133
134
135
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
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
174
175 - def connect(self, db, host, user, password):
177
180
183