Mercurial > sqlpython
changeset 497:128aa57c4b8d
first try at switching postgres query to dbapiext form
| author | Catherine Devlin <catherine.devlin@gmail.com> |
|---|---|
| date | Wed, 27 Oct 2010 10:04:31 -0400 |
| parents | af846fd968cd |
| children | fc0a6c58b7a7 |
| files | setup.py sqlpython/connections.py sqlpython/pagila_test.txt |
| diffstat | 3 files changed, 172 insertions(+), 14 deletions(-) [+] |
line wrap: on
line diff
--- a/setup.py Wed Sep 15 17:28:25 2010 -0400 +++ b/setup.py Wed Oct 27 10:04:31 2010 -0400 @@ -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.0', + install_requires=['pyparsing','cmd2>=0.6.1','gerald>=0.4.1', 'genshi>=0.5'], extras_require = { 'oracle': ['cx_Oracle>=5.0.2'],
--- a/sqlpython/connections.py Wed Sep 15 17:28:25 2010 -0400 +++ b/sqlpython/connections.py Wed Oct 27 10:04:31 2010 -0400 @@ -2,10 +2,13 @@ import os import getpass import gerald +import gerald.utilities +import gerald.utilities.dburi import time import optparse import doctest import pyparsing +import dbapiext gerald_classes = {} @@ -217,18 +220,17 @@ if (identifier['owner'] == '%') and (not opts.all): identifier['owner'] = self.username for col in ('owner', 'type', 'name'): - operator = self.comparison_operator(identifier[col]) - clause = '%s %s' % (operator, self.bindSyntax(col)) - clauses.append(clause) + if '_' in identifier[col] or '%' in identifier[col]: + identifier[col + '_op'] = 'LIKE' + else: + identifier[col + '_op'] = '=' if hasattr(opts, 'reverse') and opts.reverse: - sort_direction = 'DESC' + identifier['sort_direction'] = 'DESC' else: - sort_direction = 'ASC' - clauses.append(sort_direction) - qry = self.all_object_qry % tuple(clauses) - binds = (('owner', identifier['owner']), ('type', identifier['type']), ('name', identifier['name'])) + identifier['sort_direction'] = 'ASC' curs = self.connection.cursor() - curs.execute(qry, self.bindVariables(binds)) + dbapiext.execute_f(curs, self.all_object_qry, **identifier) + #curs.execute(qry, self.bindVariables(binds)) return curs def columns(self, target, opts): target = self.sql_format_wildcards(target) @@ -306,6 +308,12 @@ FROM all_source WHERE owner %s %s AND UPPER(text) LIKE %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 + ORDER BY table_schema, table_type, table_name %(sort_direction)s""" class PostgresInstance(DatabaseInstance): rdbms = 'postgres' @@ -327,10 +335,10 @@ 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 %s) OR (table_schema = 'public') ) - AND table_type %s - AND table_name %s - ORDER BY table_schema, table_type, table_name %s""" + 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 + 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
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/sqlpython/pagila_test.txt Wed Oct 27 10:04:31 2010 -0400 @@ -0,0 +1,150 @@ +""" +Run these against a ``pagila`` database (user paglia, password paglia) +created from http://pgfoundry.org/projects/dbsamples/ +""" +SQL.No_Connection> connect postgres://pagila:pagila@localhost/pagila +0:pagila@pagila> set color off +colors - was: True +now: False +0:pagila@pagila> desc actor +BASE TABLE public.actor +Name Nullable Type +----------- -------- --------------------------- +actor_id NOT NULL integer +first_name NOT NULL varchar(45) +last_name NOT NULL varchar(45) +last_update NOT NULL timestamp without time zone + +0:pagila@pagila> desc actor\g +BASE TABLE public.actor + + +**** Row: 1 +Name: actor_id +Nullable: NOT NULL +Type: integer + +**** Row: 2 +Name: first_name +Nullable: NOT NULL +Type: varchar(45) + +**** Row: 3 +Name: last_name +Nullable: NOT NULL +Type: varchar(45) + +**** Row: 4 +Name: last_update +Nullable: NOT NULL +Type: timestamp without time zone + +0:pagila@pagila> \d -l actor\g +BASE TABLE public.actor + + +**** Row: 1 +N: 1 +Name: actor_id +Nullable: NOT NULL +Type: integer +Key: P +Default: nextval('actor_actor_id_seq'::regclass) +Comments: + +**** Row: 2 +N: 2 +Name: first_name +Nullable: NOT NULL +Type: varchar(45) +Key: +Default: +Comments: + +**** Row: 3 +N: 3 +Name: last_name +Nullable: NOT NULL +Type: varchar(45) +Key: +Default: +Comments: + +**** Row: 4 +N: 4 +Name: last_update +Nullable: NOT NULL +Type: timestamp without time zone +Key: +Default: now() +Comments: + + +0:pagila@pagila> show constraints on actor +Constraints on BASE TABLE public.actor +Primary key "actor_pkey": (actor_id) + +0:pagila@pagila> show indexes on actor +Indexes on BASE TABLE public.actor +idx_actor_last_name (last_name) btree + +0:pagila@pagila> select actor_id, first_name, last_name from actor;3 + +actor_id first_name last_name +-------- ---------- --------- +1 PENELOPE GUINESS +2 NICK WAHLBERG +3 ED CHASE + +3 rows selected. + +0:pagila@pagila> select actor_id, first_name, last_name from actor\j3 + + +{"actor": [ + {"actor_id": 1, "first_name": "PENELOPE", "last_name": "GUINESS"}, + {"actor_id": 2, "first_name": "NICK", "last_name": "WAHLBERG"}, + {"actor_id": 3, "first_name": "ED", "last_name": "CHASE"} + ] +} + +3 rows selected. + +0:pagila@pagila> set wild on +wildsql - was: False +now: True +0:pagila@pagila> select #1, #2, #3 from actor;3 + +actor_id first_name last_name +-------- ---------- --------- +1 PENELOPE GUINESS +2 NICK WAHLBERG +3 ED CHASE + +3 rows selected. + +0:pagila@pagila> select !#4 from actor;3 + +actor_id first_name last_name +-------- ---------- --------- +1 PENELOPE GUINESS +2 NICK WAHLBERG +3 ED CHASE + +3 rows selected. + +0:pagila@pagila> select *name from actor;3 + +first_name last_name +---------- --------- +PENELOPE GUINESS +NICK WAHLBERG +ED CHASE + +3 rows selected. + +0:pagila@pagila> show constraint on film_actor; +Constraints on BASE TABLE public.film_actor +Foreign key "film_actor_actor_id_fkey": columns (actor_id) in table "actor" +Foreign key "film_actor_film_id_fkey": columns (film_id) in table "film" +Primary key "film_actor_pkey": (film_id,actor_id)