[sqlite] Primary Key uniqueness

[Joe Bennett]

Hi,

I have three sqlite3 datbases that currently have no primary key (actually
done with two and stuck on the third). I am converting them to 'new' tables
with a primary key like so:

create table if not exists new_table
 (Column_1,Column_2,Column_3..,Column_47, primary key(Column_1,
Column_27, Column_47))

Now, when I insert the data from old_table into new_table, I get the error
message 'columns Column_1,Column_27,Column_47 are not unique' using this
syntax:

insert into new_table (Column_1,Column_2,Column_3..,Column_47) select *
from old_table

That's OK, shame on me for not checking that prior to the insert... Now, I
am using this syntax to find the dupes and remove them:

select rowid, Column_1,Column_27,Column_47,count(*)
from old_table
group by Column_1,Column_27,Column_47
having count(*) > 1


No rows meet this criteria... So, I'm looking for a better way to find the
non unique data in the old_table so I can clear this error and insert it
into the new table... Any ideas?




-Joe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create Temp Table from Query

[Joe Bennett]

Yes, that is what I'm looking for.

This type of query crashes my browsers so I'm assuming that the question is
based on the concept and not the actual syntax?
On Mar 2, 2012 10:08 AM, "Marc L. Allen" <mlal...@outsitenetworks.com>
wrote:

> CREATE TEMPORARY TABLE XYZ AS
>SELECT ...
>
> Is that what you're looking for?
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Joe Bennett
> > Sent: Friday, March 02, 2012 10:44 AM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] Create Temp Table from Query
> >
> > Hi,
> >
> > I am looking for some info on how to take an sqlite query result and
> > move that into a temp table. My hope is to focus the subsequent queries
> > down to a smaller dataset... I've been searching Google a bit but have
> > not been able to find what I am loking for... I'm not sure if that
> > means this is not the optimum way to acheve what I'm looking for...??
> >
> >
> >
> >
> > -Joe
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Create Temp Table from Query

[Joe Bennett]

Hi,

I am looking for some info on how to take an sqlite query result and move
that into a temp table. My hope is to focus the subsequent queries down to
a smaller dataset... I've been searching Google a bit but have not been
able to find what I am loking for... I'm not sure if that means this is not
the optimum way to acheve what I'm looking for...??




-Joe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot table from multiple columns

[Joe Bennett]

Cool, thank you! This works perfect... Now I have to disect it and figure
this syntax out more... Thanks to all for the education!



-Joe

On Wed, Feb 16, 2011 at 5:15 PM, Simon Davies
<simon.james.dav...@gmail.com>wrote:

> On 16 February 2011 23:00, Joe Bennett <jammer10...@gmail.com> wrote:
> > OK, this looks like it concatenated Column_1 and Column_2 and returns the
> > count of the new unique concatenated pair? What I am looking for (and I
> > apologize for not being clear) is a list of the unique values (Column
> > 1 and 2 appended) and their count... I'll try to demonstrate the expected
> > example from the table example I gave below:
> >
> > *Result**Count(result)*
> >
> > Value A   2
> > Value B   1
> > Value C   2
> > Value D   2
> > Value E   1
> >
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> create table tst( c1 integer, c2 integer );
> sqlite> insert into tst values( 1, 3 );
> sqlite> insert into tst values( 2, 1 );
> sqlite> insert into tst values( 3, 4 );
> sqlite> insert into tst values( 4, 5 );
> sqlite>
> sqlite> select val, count( val ) from  ( select c1 as val from tst
> union all select c2 from tst ) group by val;
> 1|2
> 2|1
> 3|2
> 4|2
> 5|1
> sqlite>
>
> Regards,
> Simon
>  ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot table from multiple columns

[Joe Bennett]

OK, this looks like it concatenated Column_1 and Column_2 and returns the
count of the new unique concatenated pair? What I am looking for (and I
apologize for not being clear) is a list of the unique values (Column
1 and 2 appended) and their count... I'll try to demonstrate the expected
example from the table example I gave below:

*Result**Count(result)*

Value A   2
Value B   1
Value C   2
Value D   2
Value E   1



-Joe
On Wed, Feb 16, 2011 at 3:40 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:

> On Wed, Feb 16, 2011 at 03:24:51PM -0600, Joe Bennett scratched on the
> wall:
>  > Hi all,
> >
> > I am trying to locate any examples (if possible) that can clue me in on
> > taking column a and appending column b then do a pivot/group by to get
> the
> > total for each unique values... I'll try to demonstrate the base data:
> >
> > *Column_1* *Column_2*
> > Value AValue C
> > Value BValue A
> > Value CValue D
> > Value DValue E
> >
> > Now, I can pivot/group on one or the other column via [select Column_1,
> > count(Column_1) from Table group by Column_1] but and looking for a way
> to
> > create something like a 'virtural' column by appending Column_1 and
> > Column_2... Not sure if there is an easier way than creating a temp table
> > with the data and pivoting that way...
>
>
> SELECT Column_1||Column_2, count(*) FROM Table GROUP BY 1;
>
>  "GROUP BY" and "ORDER BY" will both accept numbers.  If given a
>  number, it is used as a result column index.  This query computes
>  the append you want, and then groups by that result column.
>
>
>
>  In this specific case, you could also just group over both columns,
>  since the unique value you want depends on the values of those two
>  columns.  For example:
>
> SELECT Column_1||Column_2, count(*) FROM Table GROUP BY Column_1, Column2;
>
>  Many databases will not allow this, but SQLite does.
>
>   -j
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pivot table from multiple columns

[Joe Bennett]

Hi all,

I am trying to locate any examples (if possible) that can clue me in on
taking column a and appending column b then do a pivot/group by to get the
total for each unique values... I'll try to demonstrate the base data:

*Column_1* *Column_2*
Value AValue C
Value BValue A
Value CValue D
Value DValue E

Now, I can pivot/group on one or the other column via [select Column_1,
count(Column_1) from Table group by Column_1] but and looking for a way to
create something like a 'virtural' column by appending Column_1 and
Column_2... Not sure if there is an easier way than creating a temp table
with the data and pivoting that way...




-Joe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Update not completely commit(ing)

[Joe Bennett]

Hi all,

Have a question regarding a particular issue I am dealing with... I
have a database in which I create a pivot table to get a no dupe list
using a 'select distinct'. I then take that list into Python and then
iterate over it to look up data in another table to add in the
latitude and longitude for each entry. Each entry could have over a
thousand 'dupes' that I am trying to update with the lat/lon...
Anyway, what I have noticed is that when I run the following Python
'SQLite command' sometimes all the 'dupes' get updates and sometimes
some do and some don't as well as sometimes none get updated... I am
able to replicate this with the same SQLite command in SQLite Manager:

update_data = 'update matrix set %s = %f, %s = %f where %s = "%s"' %
(A_B + '_Lat', Lat_Site, A_B + '_Lon',Lon_Site, A_B, Site[0])


I know that the chosen method to add in the variables is not
recommended, but I have not figured out how to do it with the ?. So
this is what I have right now... Functionality trumps security in this
case as I am the only one even opening the file... Any ideas on what
I'm doing wrong to get this sporadic result for the updates?



-Joe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing two tables?

[Joe Bennett]

I'm not sure I'm far enough along with sqlite to maybe get what ya'll
are telling me... I'll try explaining it this way...


Have two tables that have the same columns, but different number of
rows (rows do not match but columns do. 86 matching columns in each
table):

TableA:

Column1Column 2Column3 Column4
Row1Data  Row1Data   Row1DataRow1Data
Row2Data  Row2Data   Row2DataRow2Data
Row3Data  Row3Data   Row3DataRow3Data
Row4Data  Row4Data   Row4DataRow4Data


TableB:
Column1Column 2Column3 Column4
Row1Data  Row1Data   Row1DataRow1Data
Row2Data  Row2Data   Row2DataRow2Data



Now, I'm looking to do this:

Find the first row of data in TableB, take Column1 and Column2's data
from row one and see if that data exists in TableA. Something like
this: SELECT * FROM TableA WHERE Column1=Row1Data AND
Column2=Row1Data.

When the corresponding data is returned from TableA, I then want to
check each column in the returned row from TableA matches its
cooresponding column/ row in TableB... If there is not a match, let me
know... Then move on to the next row in TableB and do it all over
again

I'm attempting to do this in Python and am wondering if it would be
easier to do this with a query or bring each row in as a dictionary
and compare...???


-Joe

On Tue, Sep 29, 2009 at 2:39 PM, Petite Abeille
 wrote:
>
> On Sep 29, 2009, at 6:50 PM, Cory Nelson wrote:
>
>> i believe he means except, not minus.
>
> Correct. Got my SQL dialects intermingled :)
>
>> If all you need is differing
>> rows, this will work like a charm.  Otherwise if you need a more
>> fine-grained delta like only returning columns that changed, you will
>> need a more complex (but still pretty simple) join.
>>
>> SELECT * FROM t_foo EXCEPT SELECT * FROM t_bar;
>
> And for the "fancy" join, something like:
>
> select     *
> from       bar
> left join  foo
> on         foo.id = bar.id
> where      foo.id is null
> or         foo.baz != bar.baz
>
> etc...
>
> As always, details might vary.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing two tables?

[Joe Bennett]

Figuratively I'm looking to take a row in table A, find it in table B
and compare the values in each column... If there is a delta, let me
know What I am trying to do is take an old table and compare it to
the new one and show the changes...



-Joe

On Tue, Sep 29, 2009 at 12:37 PM,   wrote:
> What is the delta?
>
> RBS
>
>
>> Hi,
>>
>>
>> Have two tables structured exactly the same. Want to compare both of
>> them and get the delta. Been Googling for about an hour now and I see
>> tools that do this (maybe a freeware one I haven't found?) and was
>> looking for a solution that more meets the budget I was given for this
>> project, zero... Any words of wisdom from the group at large on where
>> to find how to do what I'm looking for or any examples?
>>
>>
>>
>> -Joe
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Comparing two tables?

[Joe Bennett]

Hi,


Have two tables structured exactly the same. Want to compare both of
them and get the delta. Been Googling for about an hour now and I see
tools that do this (maybe a freeware one I haven't found?) and was
looking for a solution that more meets the budget I was given for this
project, zero... Any words of wisdom from the group at large on where
to find how to do what I'm looking for or any examples?



-Joe
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Joe Bennett]

Hi,

Please send me a copy if you would. Been looking at doing this exact same thing!



-Joe


On Thu, Sep 24, 2009 at 7:42 PM, C. Mundi  wrote:
> On Thu, Sep 24, 2009 at 12:37 PM, C. Mundi  wrote:
>
>>
>> Hi.  I have scanned the list archives and Googled.  I may have missed
>> something, but what I found was not sufficiently general or robust for my
>> needs.
>>
>> Happily, sqlite has a very nice .output mode for CSV.  It correctly
>> double-quotes fields when they contain a space or a comma, and it correctly
>> 'escapes' double-quote literals as "".  Great!  I need to go the other way.
>>
>> I don't see an inverse .import mode in sqlite to handle the completely
>> general CSV syntax.  As anyone who has ever tried knows, parsing compliant
>> CSV is a lot harder than writing compliant CSV.
>>
>> I'm hoping someone will (please) tell me I missed something in the sqlite
>> docs.  Otherwise, I guess I'll be using python's csv module to turn my CSV
>> file into SQL insert statements.  This is likely to be an infequent task,
>> but it has to be done perfectly.  So if someone knows of a command-line tool
>> to turn CSV into SQL inserts, I would appreciate that too.
>>
>> Thanks,
>> Carlos
>>
>
> Following up my own post:
>
> I just bit the bullet and did it.  Python has an excellent csv module,
> capable of handling just about any dialect you're likely to encounter.  I am
> so grateful I did not have to write a parser for CSV.  In just a few lines I
> can read the csv right into sqlite.  If anyone wants the code I will post it
> here if deemed appropriate.
>
> Carlos
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users