# Database access

    if hasattr(cur, 'query'): return cur.query

    elif hasattr(cur, '_last_executed'): return cur._last_executed

    else: return None

    def __init__(self, cols, cause=None):

        DbException.__init__(self, 'columns: ' + ', '.join(cols), cause)

        self.cols = cols

class DbWarning(UserWarning): pass

def col_names(cur): return (col[0] for col in cur.description)

def rows(cur): return iter(lambda: cur.fetchone(), None)

def consume_rows(cur):

    '''Used to fetch all rows so result will be cached'''

    iters.consume_iter(rows(cur))

def next_row(cur): return rows(cur).next()

def row(cur):

    row_ = next_row(cur)

    consume_rows(cur)

    return row_

def next_value(cur): return next_row(cur)[0]

def value(cur): return row(cur)[0]

def values(cur): return iters.func_iter(lambda: next_value(cur))

def value_or_none(cur):

    try: return value(cur)

    except StopIteration: return None

    'MySQL': ('MySQLdb', {'password': 'passwd', 'database': 'db'}),

    'PostgreSQL': ('psycopg2', {}),

}

DatabaseErrors_set = set([DbException])

DatabaseErrors = tuple(DatabaseErrors_set)

def _add_module(module):

    DatabaseErrors_set.add(module.DatabaseError)

    global DatabaseErrors

    DatabaseErrors = tuple(DatabaseErrors_set)

def db_config_str(db_config):

    return db_config['engine']+' database '+db_config['database']

        self.serializable = serializable

        self.__db = None

    def __getattr__(self, name):

        if name == '__dict__': raise Exception('getting __dict__')

        if name == 'db': return self._db()

        else: raise AttributeError()

    def __getstate__(self):

        state = copy.copy(self.__dict__) # shallow copy

        return state

    def _db(self):

        if self.__db == None:

            # Process db_config

            db_config = self.db_config.copy() # don't modify input!

            module_name, mappings = db_engines[db_config.pop('engine')]

            module = __import__(module_name)

            _add_module(module)

            for orig, new in mappings.iteritems():

                try: util.rename_key(db_config, orig, new)

                except KeyError: pass

            # Connect

            self.__db = module.connect(**db_config)

            # Configure connection

            if self.serializable: run_raw_query(self,

                'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE')

        return self.__db

            except Exception, e:

                self.result = e # cache the exception as the result

                self._cache_result()

                raise

            finally: self.query = get_cur_query(self.inner)

            if self.rowcount == 0 and not self._is_insert: consume_rows(self)

            row = self.inner.fetchone()

            # otherwise, fetched all rows

            return row

        def _cache_result(self):

            # idempotent, but an invalid insert will always be invalid

                    util.dict_subset(dicts.AttrsDictView(self),

                    ['query', 'result', 'rowcount', 'description']))

            def __init__(self, cached_result): self.__dict__ = cached_result

                # otherwise, result is a rows list

                self.iter = iter(self.result)

            def fetchone(self):

                try: return self.iter.next()

                except StopIteration: return None

        try:

            if cacheable:

                query_lookup = _query_lookup(query, params)

                try:

                    cur = self.query_results[query_lookup]

                    used_cache = True

                except KeyError: cur = self.DbCursor(self)

            else: cur = self.db.cursor()

            # Run query

            try: cur.execute(query, params)

            except Exception, e:

                _add_cursor_info(e, cur)

                raise

            if self.log_debug != log_debug_none: # only compute msg if needed

                if used_cache: cache_status = 'Cache hit'

                elif cacheable: cache_status = 'Cache miss'

                else: cache_status = 'Non-cacheable'

                    +strings.one_line(get_cur_query(cur)))

        return cur

    def is_cached(self, query, params=None):

        return _query_lookup(query, params) in self.query_results

    if re.search(r'\W', name) != None: raise NameException('Name "'+name

        +'" may contain only alphanumeric characters and _')

            # Don't enclose in quotes because this disables case-insensitivity

            return name

    else: raise NotImplementedError("Can't escape name for "+module+' database')

    return quote + name.replace(quote, '') + quote

def esc_name_by_engine(engine, name, **kw_args):

    return esc_name_by_module(db_engines[engine][0], name, **kw_args)

def esc_name(db, name, **kw_args):

    return esc_name_by_module(util.root_module(db.db), name, **kw_args)

    if schema != None: return esc_name_(schema)+'.'+table

    else: return table

    '''For args, see DbConn.run_query()'''

    return db.run_query(*args, **kw_args)

    module = util.root_module(db.db)

    if module == 'psycopg2': return db.db.cursor().mogrify(query, params)

    else: raise NotImplementedError("Can't mogrify query for "+module+

        ' database')

    except:

    else:

        return with_savepoint(db, run)

    else: return run() # don't need savepoint if cached

    table_is_esc=False):

    @param fields Use None to select all fields in the table

    @param table_is_esc Whether the table name has already been escaped

    def parse_col(field):

        '''Parses fields'''

        if isinstance(field, tuple): # field is literal values

            value, col = field

            sql_ = '%s'

            params.append(value)

        else: sql_ = esc_name_(field) # field is col name

        else: cond_ += '='

        cond_ += ' %s'

        return cond_

    if fields == None: query += '*'

    missing = True

        query += ' WHERE '+' AND '.join(map(cond, conds.iteritems()))

    if limit != None: query += ' LIMIT '+str(limit); missing = False

    if start != None:

        if start != 0: query += ' OFFSET '+str(start)

        missing = False

    if missing: warnings.warn(DbWarning(

        'SELECT statement missing a WHERE, LIMIT, or OFFSET clause: '+query))

    '''For params, see mk_select() and run_query()'''

    recover = kw_args.pop('recover', None)

    cacheable = kw_args.pop('cacheable', True)

    query, params = mk_select(db, *args, **kw_args)

    return run_query(db, query, params, recover, cacheable)

    @param returning str|None An inserted column (such as pkey) to return

        query will be fully cached, not just if it raises an exception.

    '''

    if cols == []: cols = None # no cols (all defaults) = unknown col names

    # Build query

    query = 'INSERT INTO '+table

    if cols != None:

        map(check_name, cols)

        query += ' ('+', '.join(cols)+')'

    query += ' '+select_query

    if returning != None:

        check_name(returning)

        query += ' RETURNING '+returning

        # Create function

            ['insert', table] + cols)))

        function_query = '''\

    AS $$'''+mogrify(db, query, params)+''';$$;

'''

        run_query(db, function_query, cacheable=True)

        # Return query that uses function

def insert_select(db, *args, **kw_args):

    @param into Name of temp table to place RETURNING values in

    '''

    into = kw_args.pop('into', None)

    if into != None: kw_args['embeddable'] = True

    cacheable = kw_args.pop('cacheable', True)

    query, params = mk_insert_select(db, *args, **kw_args)

        return run_query(db, query, params, recover, cacheable)

    else: # place RETURNING values in temp table

            cacheable)

            recover, cacheable) # CREATE TABLE sets rowcount to # inserts

    '''For args, see insert_select()'''

    else:

        cols = row.keys()

        row = row.values()

    row = list(row) # ensure that "!= []" works

    labels = []

    values = []

    for value in row:

        if value == default: labels.append('DEFAULT')

        else:

            labels.append('%s')

            values.append(value)

    # Build query

    else: query = None

    elif module == 'MySQLdb': return db.insert_id()

    else: return None

    return run_query(db, 'TRUNCATE '+qual_name(db, schema, table)+' CASCADE')

##### Database structure queries

    check_name(table)

    '''Can also use this for UNIQUE constraints, because a UNIQUE index is

    automatically created. When you don't know whether something is a UNIQUE

    constraint or a UNIQUE index, use this function.'''

    check_name(table)

    check_name(index)

    if module == 'psycopg2':

        return list(values(run_query(db, '''\

(

        SELECT attnum, attname

        FROM pg_index

        JOIN pg_class index ON index.oid = indexrelid

        JOIN pg_class table_ ON table_.oid = indrelid

        JOIN pg_attribute ON attrelid = indrelid AND attnum = ANY (indkey)

        WHERE

            table_.relname = %(table)s

            AND index.relname = %(index)s

    UNION

        SELECT attnum, attname

        FROM

        (

            SELECT

                indrelid

                , (regexp_matches(indexprs, E':varattno (\\\\d+)', 'g'))[1]::int

                    AS indkey

            FROM pg_index

            JOIN pg_class index ON index.oid = indexrelid

            JOIN pg_class table_ ON table_.oid = indrelid

            WHERE

                table_.relname = %(table)s

                AND index.relname = %(index)s

        ) s

        JOIN pg_attribute ON attrelid = indrelid AND attnum = indkey

) s

''',

    else: raise NotImplementedError("Can't list index columns for "+module+

        ' database')

    check_name(table)

    check_name(constraint)

        return list(values(run_query(db, '''\

SELECT attname

FROM pg_constraint

JOIN pg_class ON pg_class.oid = conrelid

JOIN pg_attribute ON attrelid = conrelid AND attnum = ANY (conkey)

WHERE

    relname = %(table)s

    AND conname = %(constraint)s

ORDER BY attnum

''',

            {'table': table, 'constraint': constraint})))

    else: raise NotImplementedError("Can't list constraint columns for "+module+

        ' database')

SELECT tablename

FROM pg_tables

WHERE

    schemaname = %(schema)s

    AND tablename LIKE %(table_like)s

ORDER BY tablename

''',

            params, cacheable=True))

    elif module == 'MySQLdb':

        return values(run_query(db, 'SHOW TABLES LIKE %(table_like)s', params,

            cacheable=True))

    '''For kw_args, see tables()'''

    for table in tables(db, schema, **kw_args): truncate(db, table, schema)

    '''Translates known DB errors to typed exceptions'''

    try: return func()

        msg = str(e)

            r'"(([^\W_]+)_[^"]+)"', msg)

        if match:

            constraint, table = match.groups()

            'constraint', msg)

    '''Recovers from errors'''

    return with_parsed_errors(db, lambda: insert(db, table, row, returning,

        recover=True))

    '''

        if row_ct_ref != None and cur.rowcount >= 0:

            row_ct_ref[0] += cur.rowcount

        return value(cur)

            util.dict_subset_right_join(row, e.cols), recover=True))

    try: return value(select(db, table, [pkey], row, 1, recover=True))

def put_table(db, out_table, out_cols, in_table, in_cols, pkey, row_ct_ref=None,

    table_is_esc=False):

    '''Recovers from errors.

    Only works under PostgreSQL (uses INSERT RETURNING).

    '''

    pkeys_table = clean_name(out_table)+'_pkeys'

    def insert_():

        return insert_select(db, out_table, out_cols,

            *mk_select(db, in_table, in_cols, table_is_esc=table_is_esc),

            returning=pkey, into=pkeys_table, recover=True,

            table_is_esc=table_is_esc)

    try:

        cur = with_parsed_errors(db, insert_)

        if row_ct_ref != None and cur.rowcount >= 0:

            row_ct_ref[0] += cur.rowcount