/search.css" rel="stylesheet" type="text/css"/> /search.js">
| Classes | Job Modules | Data Objects | Services | Algorithms | Tools | Packages | Directories | Tracs |

In This Package:

dbcmd.py
Go to the documentation of this file.
00001 #!/usr/bin/env python
00002 """
00003 """
00004 import os, logging, re
00005 from dbconf import DBConf
00006 log = logging.getLogger(__name__)
00007 
00008 class CommandLine(dict):
00009     """
00010     Base class for composing and invoking command lines in a separate process 
00011     """
00012     def _exepath(self):
00013         for cmd in self._exenames:
00014             which = os.popen("which %s" % cmd).read().rstrip("\n")
00015             if os.path.exists(which):
00016                 return which
00017         return None
00018     exepath = property( _exepath )
00019     cmd      = property( lambda self:self._cmd % self )
00020     cmd_nopw = property( lambda self:self._cmd % dict(self, password="***") )
00021 
00022     def __init__(self, *args, **kwa ):
00023         dict.__init__(self, *args, **kwa )
00024         exe = self.exepath
00025         assert exe, "cannot find executable %r check your PATH " % self._exenames
00026         self['exepath']=exe
00027         self['path']="/dev/null"
00028         self['argline']=""
00029 
00030     def __str__(self):
00031         return "%s %s " % (self.__class__.__name__, self.cmd_nopw )
00032 
00033     def __call__(self, **kwa):
00034         verbose = kwa.pop('verbose', False)
00035         self.update(kwa)
00036         if verbose:
00037             log.info(self)
00038         else:
00039             log.debug(self)
00040         return os.popen(self.cmd).read()        
00041 
00042 class MySQLCommand(CommandLine):
00043     """
00044     Direct usage of mysql commandline rather than going via mysql-python, useful when the 
00045     tabulated mysql output is needed.
00046 
00047     Usage::
00048 
00049         from DybPython import DB
00050         db = DB("tmp_offline_db")
00051         print db.mysql("select * from DemoVld where SEQNO in (1,10)", opts="-t" )
00052 
00053    For example::
00054 
00055         In [6]: p db.mysql("select * from DemoVld where SEQNO in (1,16)", opts="-t")
00056         +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00057         | SEQNO | TIMESTART           | TIMEEND             | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE         | INSERTDATE          |
00058         +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00059         |     1 | 2010-01-01 01:00:00 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 2010-01-01 01:00:00 | 2011-08-22 12:14:21 | 
00060         |    16 | 2010-01-01 04:00:00 | 2038-01-19 03:14:07 |      127 |       1 |       0 |    0 |          -1 | 2010-01-01 04:05:00 | 2011-08-22 12:14:36 | 
00061         +-------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+
00062          
00063     Uses regexp from http://stackoverflow.com/questions/967443/python-module-to-shellquote-unshellquote
00064     in order to protect the input SQL from the shell.  
00065     """
00066     _exenames = ['mysql','mysql5']
00067     _cmd = "echo %(sql)s | %(exepath)s --no-defaults %(opts)s --host=%(host)s --user=%(user)s --password=%(password)s %(database)s  "
00068     _quote = re.compile('(?=[^-0-9a-zA-Z_./\n])') 
00069 
00070     def __call__(self, sql, opts=""  ):
00071         verbose = False
00072         qsql =  self._quote.sub('\\\\', sql).replace('\n',"'\n'") 
00073         self.update( sql=qsql, opts=opts )
00074         return CommandLine.__call__(self, verbose=verbose) 
00075  
00076 
00077 class MySQLLoad(CommandLine):
00078     """
00079     Wrapper around sql loading commandline that hooks up to the 
00080     config parameters from the :file:`~/.my.cnf` 
00081     """
00082     _exenames = ['mysql','mysql5']
00083     _cmd = "cat %(path)s | %(exepath)s --no-defaults --host=%(host)s --user=%(user)s --password=%(password)s %(database)s  "
00084  
00085     def __call__(self, path, verbose=True ):
00086         """Arguments are used to update the tables list and dumppath """
00087         assert os.path.exists(path),  "MySQLLoad ERROR no mysqldump file at %s " % path
00088         assert self['database'].startswith('tmp_'), "MySQLLoad error invalid database name %r " % self 
00089         self['path']=path 
00090         return CommandLine.__call__(self, verbose=verbose) 
00091 
00092 
00093 class MySQLDumpT(CommandLine):
00094     """
00095     Try the mysqldump -T/tmp  option 
00096         the specified directory must exist and be writable 
00097 
00098         surprised to find that this worked ...with G connected to C
00099         and writing into /tmp on G 
00100 
00101     """
00102     _exenames = ['mysqldump','mysqldump5']
00103     _cmd = "time %(exepath)s --no-defaults --skip-opt --host=%(host)s --user=%(user)s --password=%(password)s  -T%(tdir)s %(database)s "
00104     
00105     def __call__(self, tdir, verbose=True ):
00106         """Arguments are used to update the tables list and dumppath """
00107         self['tdir']=tdir 
00108         return CommandLine.__call__(self, verbose=verbose)
00109  
00110 
00111 class MySQLImport(CommandLine):
00112     """
00113     Options ``--replace/--ignore`` has an important caveat regarding ``--local``
00114     that is confusingly not mentioned in some places in the documentation:
00115  
00116     * http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html
00117     * http://dev.mysql.com/doc/refman/5.0/en/load-data.html
00118 
00119     * `--local` : reads input files locally from the client host, otherwise attempts to read from as path on the server
00120     * `--no-defaults` : must be the first option
00121 
00122     `replace` and `ignore` options control handling of input rows that duplicate existing rows on unique key values. 
00123 
00124     * `--replace` : new rows replace existing rows that have the same unique key value. 
00125     * `--ignore`  : input rows that duplicate an existing row on a unique key value are skipped.
00126 
00127     If you do not specify either option, the **behavior depends on whether the LOCAL keyword is specified**. 
00128     * Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. 
00129     * With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.
00130 
00131     DBI updates only add new entries to Vld and Payload tables, so the default (when using `--local` as we must)
00132     of `--ignore` is OK.  
00133 
00134     But LOCALSEQNO/GLOBALSEQNO table which is keyed on TABLENAME and contains the LASTUSEDSEQNO for each DBI pair,
00135     must be imported with '--replace' in order for updates to be propagated in.,
00136 
00137     Experiment with dupe key behaviour::
00138 
00139            vi ~/dybaux/catalog/tmp_offline_db/LOCALSEQNO/LOCALSEQNO.csv
00140            python dbcmd.py
00141            echo select \* from LOCALSEQNO | mysql tmp_offline_db      ## on the DB server
00142 
00143 
00144     Bad imports leads to NULLified rows::
00145 
00146         0       0000-00-00 00:00:00     0000-00-00 00:00:00     NULL    NULL    NULL    NULL    NULL    0000-00-00 00:00:00     0000-00-00 00:00:00
00147 
00148     """
00149     _exenames = ['mysqlimport','mysqlimport5']
00150     _cmd = 'time %(exepath)s --no-defaults --local --verbose %(replace_ignore)s --host=%(host)s --user=%(user)s --password=%(password)s  --fields-optionally-enclosed-by="\\"" --fields-terminated-by=, --ignore-lines=1  %(xopts)s %(database)s %(paths)s '
00151     def __call__(self, csvpaths=[] , verbose=True, replace_ignore="--ignore" , xopts=""):
00152         self['paths']=' '.join(csvpaths)
00153         self['replace_ignore'] = replace_ignore
00154         self['xopts']=xopts   
00155         return CommandLine.__call__(self, verbose=verbose)
00156 
00157 
00158 
00159 
00160 
00161 
00162 class MySQLDump(CommandLine):
00163     """
00164     Wrapper around a mysqldump command line that integrates 
00165     config parameters from the :file:`~/.my.cnf` with table list and
00166     dumppath call arguments.
00167   
00168     For background on mysqldump see http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
00169 
00170     Options used with  the mysqldump command  includes `--no-defaults` which   
00171     ensures the command is fully controlled by this script and does not depend on 
00172     users defaults. Also the `--skip-opt` is used to avoid an error when used by 
00173     with mysql identities without LOCK TABLES privilege::
00174   
00175         mysqldump: Got error: 1044: Access denied for user 'dayabay'@'%' to database 'offline_db' when doing LOCK TABLES
00176 
00177     """
00178     _exenames = ['mysqldump','mysqldump5']
00179     _cmd = "time %(exepath)s --no-defaults --skip-opt --extended-insert --host=%(host)s --user=%(user)s --password=%(password)s %(database)s %(argline)s > %(path)s ; du -hs %(path)s "
00180     
00181     def __call__(self, tables, dumppath , verbose=True ):
00182         """Arguments are used to update the tables list and dumppath """
00183         self['argline']=" ".join(tables)
00184         self['path']=dumppath
00185         return CommandLine.__call__(self, verbose=verbose)
00186 
00187 
00188 class MySQLDumpDBI(CommandLine):
00189     """
00190     Does the dump in 2 steps, with output concatenated to a single dumpfile:
00191 
00192     #. payload and validity tables
00193     #. LOCALSEQNO table with a where clause, to only pluck entries relevant to the selected tables
00194 
00195     """
00196     _exenames = ['mysqldump','mysqldump5']
00197     _cmd = ";".join([
00198             "time %(exepath)s --no-defaults --skip-opt --extended-insert --host=%(host)s --user=%(user)s --password=%(password)s %(database)s %(argline)s > %(path)s ",
00199             "time %(exepath)s --no-defaults --skip-opt --extended-insert --host=%(host)s --user=%(user)s --password=%(password)s %(database)s LOCALSEQNO --where \"%(where)s\" >> %(path)s ",
00200             " du -hs %(path)s",
00201              ])
00202     
00203     def __call__(self, tables, dumppath , verbose=True ):
00204         """
00205         Arguments control the tables list and dumppath 
00206 
00207         :param tables: list of all tables to be dumped, including payload and validity tables 
00208         :param dumppath: path of dump file
00209 
00210         """
00211         dbi_tables=filter(lambda t:t != 'LOCALSEQNO',tables)
00212         pay_tables=filter(lambda t:t[-3:] != 'Vld',dbi_tables)
00213         self['argline']=" ".join(dbi_tables)                  ## excludes LOCALSEQNO from initial dump
00214         self['path']=dumppath
00215         self['where'] = "TABLENAME in %s " % str(tuple( ['*'] + pay_tables ))
00216         return CommandLine.__call__(self, verbose=verbose)
00217 
00218 class MySQLDumpWhere(CommandLine):
00219     """
00220     Unusual options:
00221 
00222     --quick, -q
00223 
00224           This option is useful for dumping large tables. 
00225           It forces mysqldump to retrieve rows for a table from the server a row at a 
00226           time rather than retrieving the entire row set and
00227           buffering it in memory before writing it out.
00228 
00229     """
00230     _exenames = ['mysqldump','mysqldump5']
00231     _cmd = "time %(exepath)s --no-defaults --skip-opt --extended-insert --quick %(no_data)s --host=%(host)s --user=%(user)s --password=%(password)s %(database)s %(tables)s --where \"%(where)s\" %(direct)s %(path)s "
00232     
00233     def __call__(self, tables, dumppath , verbose=True ):
00234         """Arguments are used to update the tables list and dumppath """
00235         self['tables']=" ".join(tables)
00236         self['path']=dumppath
00237         return CommandLine.__call__(self, verbose=verbose)
00238 
00239 
00240 class MySQLDescribe(CommandLine):
00241     """
00242     Call method Returns the text description of a DB table, usage::
00243     
00244         cnf = DBConf("tmp_offline_db")
00245         dsc = MySQLDescribe(cnf)
00246         print dsc(table="LOCALSEQNO")
00247         print dsc(table="CalibPmtSpec")
00248  
00249     """
00250     _exenames = ['mysql','mysql5']
00251     _cmd = "echo describe %(table)s | %(exepath)s --no-defaults -t --host=%(host)s --user=%(user)s --password=%(password)s %(database)s "
00252 
00253 
00254 class MySQLCmd(CommandLine):
00255     """
00256     Return command without doing it
00257     """
00258     _exenames = ['mysql','mysql5']
00259     _cmd = "%(exepath)s --no-defaults -t --host=%(host)s --user=%(user)s --password=%(password)s %(database)s "
00260 
00261     def __call__(self):
00262         return self.cmd 
00263 
00264 
00265 if __name__ == '__main__':
00266     cnf = DBConf("tmp_offline_db")
00267     #imp = MySQLImport(cnf)
00268     #path = os.path.expanduser("~/dybaux/catalog/tmp_offline_db/LOCALSEQNO/LOCALSEQNO.csv")
00269     #print imp( tabfile=path , verbose=True, replace_ignore="--replace" )
00270     #dmp = MySQLDumpDBI(cnf)
00271     #print dmp( ["FeeCableMap","FeeCableMapVld"], "/tmp/dumpdbi.sql" )
00272 
00273     #dsc = MySQLDescribe(cnf)
00274     #print dsc(table="LOCALSEQNO")
00275     #print dsc(table="CalibPmtSpec")
00276     #s=dsc(table="CalibPmtSpecx")
00277     #print s
00278     #print "starting ... %s " % s[0:10]
00279 
00280     cmd = MySQLCmd(cnf)
00281     print cmd()
00282 
00283 
00284 
00285 
00286  
| Classes | Job Modules | Data Objects | Services | Algorithms | Tools | Packages | Directories | Tracs |

Generated on Fri May 16 2014 09:55:40 for DybPython by doxygen 1.7.4