It's a fine start but depending on how large the database is likely to
grow (how many songs, how many plays of the same song), you might not
want to duplicate the song info every time it's played. Look up
normalisation (or if you're American you might want to spell that with
a z ;). http://en.wikipedia.org/wiki/Database_normalisation
Typically this means you would have two or three tables - one (or two)
for artist/song and another for play information which just stores a
timestamp and a reference to the song ID.
You'd then have to query with a JOIN statement, e.g.
select s.*, count(p.*) from plays p join songs s on p.song_id =
p.rowid where s.title = 'We will rock you' and p.date = ...
Good question. Good luck.
Cheers,
Larry
On 6 March 2012 13:30, John Salerno <johnj...@gmail.com> wrote:
> Hi all. I'm very new to all of this, so I'm not necessarily looking for
> over-the-top advice, although anything you tell me will be appreciated. I
> just want to create a simple script (in Python, using sqlite3) that reads a
> "|" delineated text file and extracts the following bits of information
> from each line in the file: a date, a time, a song artist, and a song title.
>
> The point is that I want to create a database storing these four pieces of
> information and then later be able to retrieve the information in multiple
> ways, such as selecting a date and song and seeing how many times it was
> played that day, or selecting a date and person, or just a song, or just a
> person, etc.
>
> So I'm wondering what the best way to create such a database would be.
> Should each of those four pieces of information be an entry in the table?
>
> In case anyone knows Python (although I'm sure you can probably all read
> this anyway), here's what I came up with so far, just to see if it works,
> which it does:
>
>
> import sqlite3
>
> conn = sqlite3.connect('song_db')
> c = conn.cursor()
>
> c.execute('create table songs (date text, time text, artist text, title
> text)')
>
> with open('song_list.txt') as song_file:
> for line in song_file:
> entries = tuple(line.strip().split('|'))
> c.execute('insert into songs values (?, ?, ?, ?)', entries)
>
> conn.commit
> c.close()
>
>
> This creates a table with as many entries as there are lines in the file.
> Is there a more efficient way?
>
> Thanks!
>
> _______________________________________________
> 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
