Re: Formatting Embedded SQL

Yeah, the spaces are a problem (to the best of my knowledge).  I
personally do something like

self.cur.execute("CREATE TABLE name (ID integer primary key,"+
                                                            "col2 integer,"+
                                                            "col3 text)")

Just a thought, but I bet there's a programmatic way to do it better.

On 12/5/06, Rich Shepard <[email protected]> wrote:
>    It seems to me that when a large CREATE TABLE, SELECT, or similar command
> is embedded in a python statement with line breaks for readability, that
> either pysqlite or squite3 becomes disoriented by the extra spaces. It then
> complains and refuses to play nice. Is this a correct assumption?
>
>    For example, I have a statement that is written thusly;
>
>   """ Rule tokens after parsing and before compiling """
>      self.cur.execute("CREATE TABLE rule_token (rule_num INTEGER, token_num INTEGER, \
>                                                  token_name TEXT, \
>                                                  token_len INTEGER, \
>                                                  token_type TEXT, \
>                                                  token_sym_value TEXT, \
>                                                  primary key (rule_num,
>                                                 token_num));")
>
>    Looks pretty and is very readable. However, when I ask sqlite3 for the
> schema, this is what it shows me:
>
> sqlite> .schema rule_token
> CREATE TABLE rule_token (rule_num INTEGER, token_num INTEGER,
> token_name TEXT,                                                 token_len
> INTEGER,                                                 token_type TEXT,
> token_sym_value TEXT,
> primary key (rule_num, token_num));
> CREATE TRIGGER fki_rule_token                       BEFORE INSERT ON
> rule_token                       FOR EACH ROW                       BEGIN
> SELECT CASE                         WHEN NEW.token_num IS NOT NULL AND
> (SELECT rule_num FROM rules WHERE                               token_nuk =
> NEW.rule_num) IS NULL                         THEN RAISE (ROLLBACK, 'insert
> on table rules                               violates foreign key constraint
> fk_rule_tok')                         END;                       END;
> CREATE TRIGGER fku_rule_token                       BEFORE UPDATE ON
> rule_token                       FOR EACH ROW                         BEGIN
> SELECT CASE                           WHEN NEW.token_num IS NOT NULL AND
> (SELECT rule_num FROM rules WHERE                             token_num =
> NEW.rule_num) IS NULL                           THEN RAISE(ROLLBACK, 'update
> on table rule_token                           violates foreign key
> constraint fk_rule_tok')                         END;
> END;
>
>    I've not yet tried working with this table, but I wonder if the additional
> spaces are an issue.
>
> Thanks,
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.               |    The Environmental Permitting
> Applied Ecosystem Services, Inc.(TM)    |            Accelerator
> <http://www.appl-ecosys.com>     Voice: 503-667-4517      Fax: 503-667-8863
> _______________________________________________
> pysqlite mailing list
> pysqlite-IAPFreCvJWPBWskQ1e/[email protected]
> http://lists.initd.org/mailman/listinfo/pysqlite
>