x404.co.uk
http://www.x404.co.uk/forum/

Databases..
http://www.x404.co.uk/forum/viewtopic.php?f=4&t=10052		 Page 1 of 2
Author:  Angelic [ Wed Aug 18, 2010 9:52 am ]
Post subject:  Databases..
Hey guys,

Wondering if you can help me. My project basically need a way to get a load of data from a massive database and then run a query in SQL to get more data. It's a little complicated so i'll explain:

I've got an SQL query to get a list of host keys. There's 38,000 (students at leeds university!) so doing it manually is a bit of a no-no. I then need to run another query on all 38,000 of those and save each output as a .csv file. Might take an age so that's fine.

I was thinking, would it be possible to use another language (say, Python cos its easy) to open up a list of all the host keys, run an SQL query on each of them and save them? Or even better would it be possible in SQL?
Author:  EddArmitage [ Wed Aug 18, 2010 10:07 am ]
Post subject:  Re: Databases..
Why do you have to query the database twice? Surely you query it once, getting all the relevant records, and then you deal with them how you see fit (in this case it seems to be outputting them to a csv file). And yes, for just outputting to a simple text file I'd use something like Python.
Author:  Angelic [ Wed Aug 18, 2010 10:23 am ]
Post subject:  Re: Databases..
Well the first query would be to get the host keys, the second query would be to use said host keys to bring up that person's timetable. But if there's another way to do it i'm very open to suggestions - i'm an absolute novice at all this! Could there be a way to run one query which would take every host key and put it through the timetable fetching query?
Author:  EddArmitage [ Wed Aug 18, 2010 10:49 am ]
Post subject:  Re: Databases..
Angelic wrote:
Well the first query would be to get the host keys, the second query would be to use said host keys to bring up that person's timetable. But if there's another way to do it i'm very open to suggestions - i'm an absolute novice at all this! Could there be a way to run one query which would take every host key and put it through the timetable fetching query?

I'd imagine so, depending on the database structure, but you'd have a query a bit like:

Code:
SELECT [ALL THE FIELDS YOU WANT TO STORE IN THE CSV]
FROM [ALL THE TABLES YOU HAVE TO GRAB STUFF FROM]
WHERE [HOST KEY IS ONE YOU'RE INTERESTED IN] AND [CONDITIONAL STUFF TO GET AT TIMETABLE]


I'd have a looksy at w3schools.com for more ideas of what can be done with SQL.
Author:  finlay666 [ Wed Aug 18, 2010 12:54 pm ]
Post subject:  Re: Databases..
Is it MS SQL server?

If so do it in .Net, then you gain file writing and direct DB interaction and it's a cleaner way to use it than manually running multiple actions by just running one
Author:  EddArmitage [ Wed Aug 18, 2010 1:16 pm ]
Post subject:  Re: Databases..
finlay666 wrote:
If so do it in .Net, then you gain file writing and direct DB interaction and it's a cleaner way to use it than manually running multiple actions by just running one

Good point, although by the sounds of it this won't take 50 lines of Python, and is only going to be used once or twice, so I'd just clobber it together and be done with it!
Author:  finlay666 [ Wed Aug 18, 2010 4:40 pm ]
Post subject:  Re: Databases..
with a link to the dbml....

foreach(person s in persons)
{
// pull the host key (if you need to)
..

// create the csv
s.CreateCSV(string.format("Someplace/{0}.csv", s.id);
}

then have a method to create the CSV from the object

bish bash bosh, jobs a good'un
Author:  Angelic [ Wed Aug 18, 2010 9:54 pm ]
Post subject:  Re: Databases..
Hm, I shall definitely be looking into this .net thinger... How hard is it to do something like this from no experience in about a day?
Author:  big_D [ Thu Aug 19, 2010 4:12 am ]
Post subject:  Re: Databases..
Angelic wrote:
Well the first query would be to get the host keys, the second query would be to use said host keys to bring up that person's timetable. But if there's another way to do it i'm very open to suggestions - i'm an absolute novice at all this! Could there be a way to run one query which would take every host key and put it through the timetable fetching query?

Using a sub-select as the WHERE clause should work.

Code:
SELECT fields FROM table WHERE table.field IN (SELECT MastKey FROM MasterTable WHERE CriteriaForStudents);
Author:  EddArmitage [ Thu Aug 19, 2010 8:28 am ]
Post subject:  Re: Databases..
Angelic wrote:
Hm, I shall definitely be looking into this .net thinger... How hard is it to do something like this from no experience in about a day?

I still reckon it's overkill for something this trivial, and locks you into Windows! Sorry Finlay! (I do want to pick up .Net dev again, it's just kind of pointless for uni coursework as everything has to compile on a unix box) (8-p)
Author:  Angelic [ Thu Aug 19, 2010 10:05 am ]
Post subject:  Re: Databases..
Okay after looking into .Net...

I think I know LESS about it now than I did when I started.

Python it is!
Author:  EddArmitage [ Thu Aug 19, 2010 10:17 am ]
Post subject:  Re: Databases..
[quote="Angelic"]Okay after looking into .Net...quote]
It's definitely worth looking into at some point (assuming you want to do MS development), I just wouldn't bother for this.
Author:  Angelic [ Thu Aug 19, 2010 10:22 am ]
Post subject:  Re: Databases..
I might at some point recreationally, but it probably won't be to do professionally - i'm just not good enough!

Right this is the query that brings up my own timetable:

GET_TIMETABLE
Code:
Select name,scheduled_periods,duration
from sdowner.SDVC_ACTIVITY
where Id IN
(Select Id from sdowner.SDVR_ALLC_SET
where Student_Set = (select Id from sdowner.SDO_STUDENT_SET
where HOST_KEY ='597050' ))

And this is the query that gets all the host keys:

GET_HOST_KEYS
Code:
select host_key
from sdowner.SDO_STUDENT_SET

To be safe, just to test it works, I can add:

GET_SPECIFIC_HOST_KEYS
Code:
where department = ‘32642DAC5B87808BCCF150497CA14982’

and that will only bring up 200 results (the Computing students).

Is there any way to combine these? It's highly confusing me... Or will this be a case of running the GET_HOST_KEYS query, saving it as a CSV and from within python running a loop to run the GET_TIMETABLE query for each row in the CSV?
Author:  big_D [ Thu Aug 19, 2010 11:42 am ]
Post subject:  Re: Databases..
Just replace the specific host key with the department where clause... At least for the second 2.

What is the relation between SDVR_ALLC_SET and SDO_STUDENT_SET?
Author:  Angelic [ Thu Aug 19, 2010 11:59 am ]
Post subject:  Re: Databases..
I don't quite understand? Replace what with whonow? Sorry to be so dense!

I'm not sure what the relationship is entirely, but I believe it's something like SDO_STUDENT_SET is where my key is found, and SDVR_ALLC_SET is where the SDO_STUDENT_SET is as they're separated into staff and students...

I could be entirely wrong though. The lovely lady that guided me through it spoke quite a lot of jargon and keeping up with her was a challenge to say the least.
Page 1 of 2 All times are UTC
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/