Passing tuples of tuples to sqlite

[lolmc]

On 28 Sep, 17:49, Dennis Lee Bieber <wlfr... at ix.netcom.com> wrote:
> On Mon, 28 Sep 2009 03:35:44 -0700 (PDT), xera121
> <lolmcbr... at googlemail.com> declaimed the following in
> gmane.comp.python.general:
>
> > Hi
> > I have tuples in the format shown below:
>
> > (u('2','3','4'),u('5','6','7')........u('20','21','22'))
>
>         What are they? The syntax shown is that of a function named
>                         u
> taking three string arguments, not a tuple.
>
> > but they are not being accepted into sqlite - due I think to the
> > excessive quote marks which are confusing the issue when converting to
> > a string to send it to sqlite.
> > Can someone advise me on how I should format the strings so the whole
> > tuple of tuples will be accepted in the sqlite insert query?
>
>         It would help to know the SQL statement you are trying to use to
> insert the data.
>
>         Note that in proper relational databases, repeated groups are not
> permitted, so your tuple should be split into three discrete "columns"
> (call them t0, t1, t2 <G>).
>
> insert into <table> (..., t0, t1, t2, ...)
>         values (..., ?, ?, ?, ...)
>
> and invoked as
>
>         cur.execute(theSQL, (..., tuple[0], tuple[1], tuple[2], ...) )
>
> letting the DB-API adapter handle the proper quoting/conversion.
>
>         On retrieval, you'd then have to recreate the tuple from the
> components.
> --
>         Wulfraed         Dennis Lee Bieber               KD6MOG
>         wlfr... at ix.netcom.com      HTTP://wlfraed.home.netcom.com/

The SQL is not very mysterious:

The sqlite database table is created as follows:

CREATE TABLE my_table (mytuples TEXT,myInteger INTEGER);

INSERT INTO table VALUES(mytuples,anInteger)

What I'm after as an end point is for each row in the db to have a
unique entry of tuples and it just occurred to me after looking at the
sqlite3 module documentation in python is that I can do an MD5 sum of
the tuples and have that as a INTEGER PRIMARY KEY field. I could then
split my list of tuples up into individual fields for each tuple which
would make it bulky to look at on screen but totally workable in the
retrieval/testing for uniqueness that I want to my with my data.

Also as an aside the u is not a call to a function it stands for
unicode and is put there by Python itself to denote the string is in
unicode format.

I'm going to do trials with the MD5 thing to prove it out.
Thanks for your input,

Lol Mc