LSSTApplications  10.0-2-g4f67435,11.0.rc2+1,11.0.rc2+12,11.0.rc2+3,11.0.rc2+4,11.0.rc2+5,11.0.rc2+6,11.0.rc2+7,11.0.rc2+8
LSSTDataManagementBasePackage
mysqlExecutor.py
Go to the documentation of this file.
1 #
2 # LSST Data Management System
3 # Copyright 2008, 2009, 2010 LSST Corporation.
4 #
5 # This product includes software developed by the
6 # LSST Project (http://www.lsst.org/).
7 #
8 # This program is free software: you can redistribute it and/or modify
9 # it under the terms of the GNU General Public License as published by
10 # the Free Software Foundation, either version 3 of the License, or
11 # (at your option) any later version.
12 #
13 # This program is distributed in the hope that it will be useful,
14 # but WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU General Public License for more details.
17 #
18 # You should have received a copy of the LSST License Statement and
19 # the GNU General Public License along with this program. If not,
20 # see <http://www.lsstcorp.org/LegalNotices/>.
21 #
22 
23 from __future__ import with_statement
24 from contextlib import closing
25 import getpass
26 import MySQLdb as sql
27 import argparse
28 import os
29 import subprocess
30 import sys
31 from lsst.daf.persistence import DbAuth
32 
33 
34 class MysqlExecutor(object):
35  def __init__(self, host, database, user, port=3306, password=None):
36  self.host = host
37  self.port = port
38  self.user = user
39  self.database = database
40  if password == None:
41  if self.host is not None and self.port is not None and \
42  DbAuth.available(self.host, str(self.port)):
43  self.user = DbAuth.username(self.host, str(self.port))
44  password = DbAuth.password(self.host, str(self.port))
45  elif not os.path.exists(os.path.join(os.environ['HOME'], ".my.cnf")):
46  password = getpass.getpass("%s's MySQL password: " % user)
47  self.password = password
48  self.mysqlCmd = ['mysql']
49  if host is not None:
50  self.mysqlCmd += ['-h', self.host]
51  if port is not None:
52  self.mysqlCmd += ['-P', str(self.port)]
53  if user is not None:
54  self.mysqlCmd += ['-u', self.user]
55  if password is not None:
56  self.mysqlCmd += ['-p' + self.password]
57 
58  def createDb(self, database, options=['-vvv']):
59  if not isinstance(database, basestring):
60  raise TypeError('database name is not a string')
61  cmd = list(self.mysqlCmd)
62  cmd += options
63  cmd += ['-e', 'CREATE DATABASE %s;' % database]
64  subprocess.check_call(cmd, stdout=sys.stdout, stderr=sys.stderr)
65  sys.stdout.flush()
66  sys.stderr.flush()
67 
68  def execStmt(self, stmt, stdout=sys.stdout, options=['-vvv']):
69  if not isinstance(stmt, basestring):
70  raise TypeError('SQL statement is not a string')
71  cmd = list(self.mysqlCmd)
72  if self.database is not None:
73  cmd += ['-D', self.database]
74  cmd += options
75  cmd += ['-e', stmt]
76  subprocess.check_call(cmd, stdout=stdout, stderr=sys.stderr)
77  stdout.flush()
78  sys.stderr.flush()
79 
80  def execScript(self, script, options=['-vvv']):
81  if not isinstance(script, basestring):
82  raise TypeError('Script file name is not a string')
83  if not os.path.isfile(script):
84  raise RuntimeError(
85  'Script %s does not exist or is not a file' % script)
86  with open(script, 'rb') as f:
87  cmd = list(self.mysqlCmd)
88  if self.database is not None:
89  cmd += ['-D', self.database]
90  cmd += options
91  subprocess.check_call(cmd, stdin=f,
92  stdout=sys.stdout, stderr=sys.stderr)
93  sys.stdout.flush()
94  sys.stderr.flush()
95 
96  def runQuery(self, query):
97  if not isinstance(query, basestring):
98  raise TypeError('Query is not a string')
99  with closing(self.getConn()) as conn:
100  with closing(conn.cursor()) as cursor:
101  print query
102  sys.stdout.flush()
103  cursor.execute(query)
104  return cursor.fetchall()
105 
106  def isView(self, table):
107  with closing(self.getConn()) as conn:
108  with closing(conn.cursor()) as cursor:
109  cursor.execute('SELECT COUNT(*) FROM information_schema.tables '
110  'WHERE table_schema=%s AND table_name=%s AND '
111  'table_type=\'VIEW\'', (self.database, table))
112  return cursor.fetchone()[0] == 1
113 
114  def exists(self, table):
115  with closing(self.getConn()) as conn:
116  with closing(conn.cursor()) as cursor:
117  cursor.execute('SELECT COUNT(*) FROM information_schema.tables '
118  'WHERE table_schema=%s AND table_name=%s',
119  (self.database, table))
120  return cursor.fetchone()[0] == 1
121 
122  def getConn(self):
123  kw = dict()
124  if self.host is not None:
125  kw['host'] = self.host
126  if self.port is not None:
127  kw['port'] = self.port
128  if self.user is not None:
129  kw['user'] = self.user
130  if self.database is not None:
131  kw['db'] = self.database
132  if self.password is not None:
133  kw['passwd'] = self.password
134  return sql.connect(**kw)
135 
136 
137 def addDbOptions(parser):
138  if not isinstance(parser, argparse.ArgumentParser):
139  raise TypeError('Expecting an argparse.ArgumentParser')
140  defUser = (os.environ.has_key('USER') and os.environ['USER']) or None
141  parser.add_argument(
142  "--user", default=defUser, dest="user",
143  help="MySQL database user name (%(default)s).")
144  parser.add_argument(
145  "--host", default="lsst10.ncsa.uiuc.edu", dest="host",
146  help="MySQL database server hostname (%(default)s).")
147  parser.add_argument(
148  "--port", default=3306, type=int, dest="port",
149  help="MySQL database server port (%(default)d).")
150