Mercurial > sqlpython
changeset 518:69997c324eb9 1.7.2
1.7.2
| author | Catherine Devlin <catherine.devlin@gmail.com> |
|---|---|
| date | Tue, 09 Nov 2010 05:41:08 -0500 |
| parents | c36e0aa695a4 |
| children | e28767423d4b |
| files | docs/source/intro.rst setup.py sqlpython/connections.py sqlpython/mysqlpy.py sqlpython/sakila-test.txt sqlpython/sqlpyPlus.py sqlpython/sqlpython.py |
| diffstat | 7 files changed, 53 insertions(+), 41 deletions(-) [+] |
line wrap: on
line diff
--- a/docs/source/intro.rst Wed Nov 03 07:47:17 2010 -0400 +++ b/docs/source/intro.rst Tue Nov 09 05:41:08 2010 -0500 @@ -10,7 +10,7 @@ SQLPython is a command-line interface to relational databases. It was created as an alternative to Oracle's ``SQL\*Plus``, and can likewise be used instead of postgres' ``psql`` or mysql's ``mysql`` text clients. For the most part, it can be used as any other text-based SQL interface would; this document focuses on -the extra capabilities +the extra capabilities. License ------- @@ -92,7 +92,7 @@ Non-Oracle RDBMS ---------------- -Version 1.7.0 of sqlpython works against PostgreSQL and MySQL, though the testing against +Version 1.7.2 of sqlpython works against PostgreSQL and MySQL, though the testing against those databases has been very slight thus far. Help in testing and improving sqlpython's functions against those databases is welcome. Support for Microsoft SQL Server and sqlite will be available as soon as those databases are added to the Gerald project, and volunteers
--- a/setup.py Wed Nov 03 07:47:17 2010 -0400 +++ b/setup.py Tue Nov 09 05:41:08 2010 -0500 @@ -14,7 +14,7 @@ Operating System :: OS Independent""".splitlines() setup(name="sqlpython", - version="1.7.1", + version="1.7.2", description="Command-line interface to Oracle", long_description="Customizable alternative to Oracle's SQL*PLUS command-line interface", author="Luca Canali", @@ -22,7 +22,7 @@ url="http://packages.python.org/sqlpython", packages=find_packages(), include_package_data=True, - install_requires=['pyparsing','cmd2>=0.6.1','gerald>=0.4.1.1', + install_requires=['pyparsing','cmd2>=0.6.2','gerald>=0.4.1.1', 'genshi>=0.5'], extras_require = { 'oracle': ['cx_Oracle>=5.0.2'],
--- a/sqlpython/connections.py Wed Nov 03 07:47:17 2010 -0400 +++ b/sqlpython/connections.py Tue Nov 09 05:41:08 2010 -0500 @@ -30,8 +30,8 @@ except ImportError: pass -#if not gerald_classes: -# raise ImportError, 'No Python database adapters installed!' +if not gerald_classes: + raise ImportError, 'No Python database adapters installed!' class ObjectDescriptor(object): def __init__(self, name, dbobj): @@ -247,8 +247,13 @@ curs = self.connection.cursor() dbapiext.execute_f(curs, self.column_qry, **identifier) return curs + def tables_and_views(self, target): + identifier = {'table_name': target + '%'} + curs = self.connection.cursor() + dbapiext.execute_f(curs, self.tables_and_views_qry, **identifier) + return curs def _source(self, target, opts): - identifier = {'text': '%%%s%%' % target.upper()} + identifier = {'text': '%%%s%%' % target.lower()} if opts.all: identifier['owner'] = '%' else: @@ -265,10 +270,11 @@ for (line_number, line) in enumerate(code.splitlines()): if target.search(line): yield (row[0], row[1], row[2], line_number, line) - gerald_types = {'TABLE': gerald.oracle_schema.Table, - 'VIEW': gerald.oracle_schema.View} def object_metadata(self, owner, object_type, name): return self.gerald_types[object_type](name, self.connection.cursor(), owner) + tables_and_views_qry = """SELECT table_name + FROM information_schema.tables + WHERE table_name LIKE LOWER(%(table_name)S)""" parser = optparse.OptionParser() @@ -312,22 +318,24 @@ FROM information_schema.columns c JOIN information_schema.tables t ON (c.table_schema = t.table_schema AND c.table_name = t.table_name) - WHERE ( (c.table_schema %(owner_op)s %(owner)S) OR (c.table_schema = 'public')) - AND c.table_name %(table_name_op)s %(table_name)S - AND c.column_name %(column_name_op)s %(column_name)S""" + WHERE ( (c.table_schema %(owner_op)s LOWER(%(owner)S)) OR (c.table_schema = 'public')) + AND c.table_name %(table_name_op)s LOWER(%(table_name)S) + AND LOWER(c.column_name) %(column_name_op)s LOWER(%(column_name)S)""" source_qry = """SELECT r.routine_schema, r.routine_type, r.routine_name, 0 AS line, r.routine_definition FROM information_schema.routines r - WHERE r.routine_schema %(owner_op)s %(owner)S - AND UPPER(r.routine_definition) LIKE %(text)S""" + WHERE r.routine_schema %(owner_op)s LOWER(%(owner)S) + AND LOWER(r.routine_definition) LIKE %(text)S""" all_object_qry = """SELECT table_schema, table_type, table_name FROM information_schema.tables - WHERE table_schema %(owner_op)s %(owner)S - AND table_type %(type_op)s %(type)S - AND table_name %(name_op)s %(name)S + WHERE table_schema %(owner_op)s LOWER(%(owner)S) + AND table_type %(type_op)s UPPER(%(type)S) + AND table_name %(name_op)s LOWER(%(name)S) ORDER BY table_schema, table_type, table_name %(sort_direction)s""" gerald_types = {'TABLE': gerald.mysql_schema.Table, 'VIEW': gerald.mysql_schema.View, - 'BASE TABLE': gerald.mysql_schema.Table} + 'BASE TABLE': gerald.mysql_schema.Table, + # 'SYSTEM VIEW': gerald.mysql_schema.Table - system views throw errors + } class PostgresInstance(DatabaseInstance): @@ -350,21 +358,21 @@ return dict((b[0], b[1].lower()) for b in binds) all_object_qry = """SELECT table_schema, table_type, table_name FROM information_schema.tables - WHERE ( (table_schema %(owner_op)s %(owner)S) OR (table_schema = 'public') ) - AND table_type %(type_op)s %(type)S - AND table_name %(name_op)s %(name)S + WHERE ( (table_schema %(owner_op)s LOWER(%(owner)S)) OR (table_schema = 'public') ) + AND table_type %(type_op)s UPPER(%(type)S) + AND table_name %(name_op)s LOWER(%(name)S) ORDER BY table_schema, table_type, table_name %(sort_direction)s""" column_qry = """SELECT c.table_schema, t.table_type, c.table_name, c.column_name FROM information_schema.columns c JOIN information_schema.tables t ON (c.table_schema = t.table_schema AND c.table_name = t.table_name) - WHERE ( (c.table_schema %(owner_op)s %(owner)S) OR (c.table_schema = 'public')) - AND c.table_name %(table_name_op)s %(table_name)S - AND c.column_name %(column_name_op)s %(column_name)S""" + WHERE ( (c.table_schema %(owner_op)s LOWER(%(owner)S)) OR (c.table_schema = 'public')) + AND c.table_name %(table_name_op)s LOWER(%(table_name)S) + AND c.column_name %(column_name_op)s LOWER(%(column_name)S)""" source_qry = """SELECT r.routine_schema, r.routine_type, r.routine_name, 0 AS line, r.routine_definition FROM information_schema.routines r - WHERE ( (r.routine_schema %(owner_op)s %(owner)S) OR (r.routine_schema = 'public') ) - AND UPPER(r.routine_definition) LIKE %(text)S""" + WHERE ( (r.routine_schema %(owner_op)s LOWER(%(owner)S)) OR (r.routine_schema = 'public') ) + AND LOWER(r.routine_definition) LIKE %(text)S""" gerald_types = {'BASE TABLE': gerald.postgres_schema.Table, 'VIEW': gerald.postgres_schema.View} @@ -373,7 +381,6 @@ default_port = 1521 connection_parser = re.compile('(?P<username>[^/\s@]*)(/(?P<password>[^/\s@]*))?(@((?P<hostname>[^/\s:]*)(:(?P<port>\d{1,4}))?/)?(?P<database>[^/\s:]*))?(\s+as\s+(?P<mode>sys(dba|oper)))?', re.IGNORECASE) - case = str.upper def object_name_case(self, name): return name.upper() def uri(self): @@ -408,20 +415,23 @@ dsn = self.dsn, mode = self.mode) all_object_qry = """SELECT owner, object_type, object_name FROM all_objects - WHERE owner %(owner_op)s %(owner)S - AND object_type %(type_op)s %(type)S - AND object_name %(name_op)s %(name)S + WHERE owner %(owner_op)s UPPER(%(owner)S) + AND object_type %(type_op)s UPPER(%(type)S) + AND object_name %(name_op)s UPPER(%(name)S) ORDER BY owner, object_type, object_name %(sort_direction)s""" column_qry = """SELECT atc.owner, ao.object_type, atc.table_name, atc.column_name FROM all_tab_columns atc JOIN all_objects ao ON (atc.table_name = ao.object_name AND atc.owner = ao.owner) - WHERE atc.owner %(owner_op)s %(owner)S - AND atc.table_name %(table_name_op)s %(table_name)S - AND atc.column_name %(column_name_op)s %(column_name)S """ + WHERE atc.owner %(owner_op)s UPPER(%(owner)S) + AND atc.table_name %(table_name_op)s UPPER(%(table_name)S) + AND atc.column_name %(column_name_op)s UPPER(%(column_name)S) """ source_qry = """SELECT owner, type, name, line, text FROM all_source WHERE owner %(owner_op)s %(owner)S - AND UPPER(text) LIKE %(text)S""" + AND lower(text) LIKE %(text)S""" + tables_and_views_qry = """SELECT table_name + FROM all_tables + WHERE table_name LIKE UPPER(%(table_name)S)""" def source(self, target, opts): return self._source(target, opts) def bindSyntax(self, varname):
--- a/sqlpython/mysqlpy.py Wed Nov 03 07:47:17 2010 -0400 +++ b/sqlpython/mysqlpy.py Tue Nov 09 05:41:08 2010 -0500 @@ -13,9 +13,9 @@ class mysqlpy(sqlpyPlus): ''' -MySqlPy V1.7.0 - 'sqlplus in python' +MySqlPy V1.7.2 - 'sqlplus in python' Author: Luca.Canali@cern.ch -Rev: 1.7.0, 06-Feb-10 +Rev: 1.7.2, 06-Feb-10 Companion of SqlPython, a python module that reproduces Oracle's command line within python and sqlpyPlus. Major contributions by Catherine Devlin, http://catherinedevlin.blogspot.com
--- a/sqlpython/sakila-test.txt Wed Nov 03 07:47:17 2010 -0400 +++ b/sqlpython/sakila-test.txt Tue Nov 09 05:41:08 2010 -0500 @@ -136,5 +136,4 @@ Constraints on BASE TABLE sakila.film_actor Foreign key "fk_film_actor_actor": columns (actor_id) in table "actor" Foreign key "fk_film_actor_film": columns (film_id) in table "film" -Primary key "PRIMARY": (actor_id,film_id) -0:sakila@sakila> \ No newline at end of file +Primary key "PRIMARY": (actor_id,film_id) \ No newline at end of file
--- a/sqlpython/sqlpyPlus.py Wed Nov 03 07:47:17 2010 -0400 +++ b/sqlpython/sqlpyPlus.py Tue Nov 09 05:41:08 2010 -0500 @@ -164,7 +164,7 @@ %s""" % (self.purpose, __file__, "\n".join([s[0] for s in self.softwareList]))) class Dummy_Options(object): - all = None + all = True dummy_options = Dummy_Options() softwareLists = { @@ -596,7 +596,8 @@ if segment in ('select', 'where', 'having', 'set', 'order by', 'group by'): completions = [c[-1] for c in self.current_instance.columns(text + '%', '%', dummy_options)] elif segment in ('from', 'update', 'insert into'): - completions = [t for t in schemas[username].table_names if t.startswith(text)] + #print self.current_instance.tables_and_views(text) + completions = [t[0] for t in self.current_instance.tables_and_views(text)] elif segment == 'beginning': completions = [n for n in self.get_names() if n.startswith('do_')] + [ 'insert', 'update', 'delete', 'drop', 'alter', 'begin', 'declare', 'create']
--- a/sqlpython/sqlpython.py Wed Nov 03 07:47:17 2010 -0400 +++ b/sqlpython/sqlpython.py Tue Nov 09 05:41:08 2010 -0500 @@ -10,7 +10,7 @@ import cmd2,getpass,binascii,re,os,platform import pyparsing, connections -__version__ = '1.7.0' +__version__ = '1.7.2' try: import cx_Oracle except ImportError: @@ -120,6 +120,7 @@ help='close connection {N} (or current)'), cmd2.make_option('-C', '--closeall', action='store_true', help='close all connections'), + cmd2.make_option('--postgresql', action='store_true', help='Connect to postgreSQL: `connect --postgresql [DBNAME [USERNAME]]`'), cmd2.make_option('--postgres', action='store_true', help='Connect to postgreSQL: `connect --postgres [DBNAME [USERNAME]]`'), cmd2.make_option('--oracle', action='store_true', help='Connect to an Oracle database'), cmd2.make_option('--mysql', action='store_true', help='Connect to a MySQL database'), @@ -142,6 +143,7 @@ connect user/password@SID (Oracle is the default RDBMS target) connect --postgres --hostname=hostname dbname username connect --mysql dbname username''' + opts.postgres = opts.postgres or opts.postgresql if opts.closeall: self.closeall() return