Passing tuples of tuples to sqlite

[xera121]

Hi
I have tuples in the format shown below:

(u('2','3','4'),u('5','6','7')........u('20','21','22'))

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?

Thanks

Lol McBride

 

[Tim Chase]

I have tuples in the format shown below:

(u('2','3','4'),u('5','6','7')........u('20','21','22'))

Um, this is not valid Python which might explain the errors
you're getting. Are you sure you don't mean to write:

((u'2', u'3', u'4'), (u'5', u'6', u'7')...)

because "u" prefixing a tuple is invalid.

-tkc

 

[lolmc]

Hi
I have tuples in the format shown below:

(u('2','3','4'),u('5','6','7')........u('20','21','22'))

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?

Thanks

Lol McBride

After receiving an email I realised that the way I have presented the
tuple unicode formatting is incorrect. I will post proper code when I
get access to my machine later to day.

Lol Mc

 

[lolmc]

After receiving an email I realised that the way I have presented the
tuple unicode formatting is incorrect. I will post proper code when I
get access to my machine later to day.

Lol Mc

Did some SSh and vnc majic and I've got the proper formatting:

[(u'(7, 28, 36)', u'(35, 47, 49)', u'(25, 34, 46)', u'(2, 5, 40)',
u'(7, 24, 35)', u'(8, 17, 20)', u'(24, 33, 37)', u'(5, 8, 19)')]

So what i want to do is put the above into a primary key field so that
I know when duplicates have occurred and I only add unique data into
the field.When I have copied the string of the query into sqlitemanger
and tried to execute it ther it says ther is an error in the SQL query
at ',' - which is not too helpful as I am not sure if it is the
first , or 3rd or last , which is causing the problem!
I have a feeling though that it is the quote mark after the unicode
delimiter which is the issue but I'm not 100% sure - can anyone shed
light on this and offer a solution?

Thanks

Lol Mc

 

[Dennis Lee Bieber]

[(u'(7, 28, 36)', u'(35, 47, 49)', u'(25, 34, 46)', u'(2, 5, 40)',
u'(7, 24, 35)', u'(8, 17, 20)', u'(24, 33, 37)', u'(5, 8, 19)')]

Okay, now we have a LIST containing ONE TUPLE. That tuple contains
EIGHT unicode STRINGS. As strings, they will need to be exact matches...

"(7,28,36)" is not equal to "(7, 28, 36)" -- but numeric tuples of
(7,28,36) IS equal to (7, 28, 36)

So what i want to do is put the above into a primary key field so that
I know when duplicates have occurred and I only add unique data into
the field.When I have copied the string of the query into sqlitemanger
and tried to execute it ther it says ther is an error in the SQL query
at ',' - which is not too helpful as I am not sure if it is the
first , or 3rd or last , which is causing the problem!
I have a feeling though that it is the quote mark after the unicode
delimiter which is the issue but I'm not 100% sure - can anyone shed
light on this and offer a solution?

You still haven't shown us the SQL!

 

[Dennis Lee Bieber]

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.

 

[lolmc]

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.

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

 

[Dennis Lee Bieber]

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.

u'...' is a unicode marker (or u"..." )
u(...) is a function invocation

The latter is the syntax I to which I was responding.

And in none of the variations have I seen a "tuple of tuples"; the
second looked more like a "list of strings"

If you really need to store tuples /as/ tuples, in a form that can
be retrieved, you should look into the defining type converter/adapters
that will do the db storage format to Python data translation for you...

 

[lolmc]

I have had success using the hashlib library to do a hexhash of the
tuples and then put each tuple in it's own field.The hash is
successfully being used as a test for uniqueness.

Thanks

Lol Mc