similar artists - LabROSA - Columbia University

bawltherapistSoftware and s/w Development

Dec 13, 2013 (3 years and 7 months ago)

91 views

/home/thierry/Columbia/MSongsDB/tutorials/tutorial5/tutorial5.py January 25,2011 1
26"""
27 T h i e r r y B e r t i n - M a h i e u x ( 2 0 1 1 ) C o l u m b i a U n i v e r s i t y
28 t b 2 3 3 2 @ c o l u m b i a.e d u
29
30 T h i s c o d e d e m o t h e u s e o f t h e a r t i s t _ s i m i l a r i t y.d b
31 T h i s i s a l m o s t t h e s a m e c o d e a s d e m o _ a r t i s t _ s i m i l a r i t y.p y
32 i n t h e g i t h u b r e p o s i t o r y.
33
34 T h i s i s p a r t o f t h e M i l l i o n S o n g D a t a s e t p r o j e c t f r o m
35 L a b R O S A ( C o l u m b i a U n i v e r s i t y ) a n d T h e E c h o N e s t.
36
37 C o p y r i g h t 2 0 1 1,T h i e r r y B e r t i n - M a h i e u x
38
39 T h i s p r o g r a m i s f r e e s o f t w a r e:y o u c a n r e d i s t r i b u t e i t a n d/o r m o d i f y
40 i t u n d e r t h e t e r m s o f t h e G N U G e n e r a l P u b l i c L i c e n s e a s p u b l i s h e d b y
41 t h e F r e e S o f t w a r e F o u n d a t i o n,e i t h e r v e r s i o n 3 o f t h e L i c e n s e,o r
42 ( a t y o u r o p t i o n ) a n y l a t e r v e r s i o n.
43
44 T h i s p r o g r a m i s d i s t r i b u t e d i n t h e h o p e t h a t i t w i l l b e u s e f u l,
45 b u t W I T H O U T A N Y W A R R A N T Y;w i t h o u t e v e n t h e i m p l i e d w a r r a n t y o f
46 M E R C H A N T A B I L I T Y o r F I T N E S S F O R A P A R T I C U L A R P U R P O S E.S e e t h e
47 G N U G e n e r a l P u b l i c L i c e n s e f o r m o r e d e t a i l s.
48
49 Y o u s h o u l d h a v e r e c e i v e d a c o p y o f t h e G N U G e n e r a l P u b l i c L i c e n s e
50 a l o n g w i t h t h i s p r o g r a m.I f n o t,s e e < h t t p://w w w.g n u.o r g/l i c e n s e s/>.
51"""
52
53 i m p o r t o s
54 i m p o r t s y s
55 i m p o r t g l o b
56 i m p o r t t i m e
57 i m p o r t d a t e t i m e
58 i m p o r t n u m p y a s n p
59 t r y:
60 i m p o r t s q l i t e 3
61 e x c e p t I m p o r t E r r o r:
62 p r i n t ’ y o u n e e d s q l i t e 3 i n s t a l l e d t o u s e t h i s p r o g r a m ’
63 s y s.e x i t ( 0 )
64
65
66 d e f e n c o d e _ s t r i n g ( s ):
67"""
68 S i m p l e u t i l i t y f u n c t i o n t o m a k e s u r e a s t r i n g i s p r o p e r
69 t o b e u s e d i n a S Q L i t e q u e r y
70 ( d i f f e r e n t t h a n p o s g t r e s q l,n o N t o s p e c i f y u n i c o d e )
71 E X A M P L E:
72 T h a t ’ s m y b o y!- > ’ T h a t ’ ’ s m y b o y!’
73"""
74 r e t u r n"’"+ s.r e p l a c e ("’","’ ’") +"’"
75
76#P A T H T O a r t i s t _ s i m i l a r i t y.d b
77#C H A N G E T H I S T O Y O U R L O C A L C O N F I G U R A T I O N
78#I T S H O U L D B E I N T H E A D D I T I O N A L F I L E S
79#( y o u c a n u s e ’ s u b s e t _ a r t i s t _ s i m i l a r i t y.d b ’ )
80 d b f i l e = ’/h o m e/t h i e r r y/C o l u m b i a/MS o n g s DB/T a s k s _ D e m o s/S Q L i t e/
a r t i s t _ s i m i l a r i t y.d b ’
81
82#c o n n e c t t o t h e S Q L i t e d a t a b a s e
83 c o n n = s q l i t e 3.c o n n e c t ( d b f i l e )
/home/thierry/Columbia/MSongsDB/tutorials/tutorial5/tutorial5.py January 25,2011 2
84
85#f r o m t h a t c o n n e c t i o n,g e t a c u r s o r t o d o q u e r i e s
86#N O T E:w e c o u l d q u e r y d i r e c t l y f r o m t h e c o n n e c t i o n o b j e c t
87 c = c o n n.c u r s o r ( )
88
89 p r i n t ’ *************** GENERAL SQLITE DEMO *************************** ’
* * * * * * * * * * * * * * * GENERAL S Q L I T E DEMO * * * * * * * * * * * * * * * * * * * * * * * * * * *
66
67#l i s t a l l t a b l e s i n t h a t d a t a s e t
68#n o t e t h a t s q l i t e d o e s t h e a c t u a l j o b w h e n w e c a l l f e t c h a l l ( ) o r f e t c h o n e ( )
69 q ="SELECT n a m e FROM s q l i t e _ m a s t e r WHERE t y p e = ’ t a b l e ’ ORDER BY n a m e"
70 r e s = c.e x e c u t e ( q )
71 p r i n t"* t a b l e s c o n t a i n e d i n t h a t S Q L i t e f i l e/d a t a b a s e ( t h e r e s h o u l d b e 3 ):"
* t a b l e s c o n t a i n e d i n t h a t S Q L i t e f i l e/d a t a b a s e ( t h e r e s h o u l d b e 3 ):
72 p r i n t r e s.f e t c h a l l ( )
[ ( u ’ a r t i s t s ’,),( u ’ s i m i l a r i t y ’,) ]
73
74#l i s t a l l i n d i c e s
75 q ="SELECT n a m e FROM s q l i t e _ m a s t e r WHERE t y p e = ’ i n d e x ’ ORDER BY n a m e"
76 r e s = c.e x e c u t e ( q )
77 p r i n t ’ * i n d i c e s i n t h e d a t a b a s e t o m a k e r e a d s f a s t e r:’
* i n d i c e s i n t h e d a t a b a s e t o m a k e r e a d s f a s t e r:
78 p r i n t r e s.f e t c h a l l ( )
[ ( u ’ i d x _ s i m _ t a r g e t ’,),( u ’ i d x _ t a r g e t _ s i m ’,),( u ’ s q l i t e _ a u t o i n d e x _ a r t i s t s _ 1
’,) ]
80
81 p r i n t ’ *************** ARTISTS TABLE DEMO **************************** ’
* * * * * * * * * * * * * * * A R T I S T S TABLE DEMO * * * * * * * * * * * * * * * * * * * * * * * * * * * *
81
82#l i s t a l l a r t i s t I D
83 q ="SELECT a r t i s t _ i d FROM a r t i s t s"
84 r e s = c.e x e c u t e ( q )
85 p r i n t"* n u m b e r o f a r t i s t E c h o N e s t I D i n ’ a r t i s t s ’ t a b l e:"
* n u m b e r o f a r t i s t E c h o N e s t I D i n ’ a r t i s t s ’ t a b l e:
86 p r i n t l e n ( r e s.f e t c h a l l ( ) )
4 4 7 4 5
88
89 p r i n t ’ *************** ARTIST SI MI LARI TY DEMO ************************ ’
* * * * * * * * * * * * * * * A R T I S T S I M I L A R I T Y DEMO * * * * * * * * * * * * * * * * * * * * * * * *
89
90#g e t a r a n d o m s i m i l a r i t y r e l a t i o n s h i p
91 q ="SELECT t a r g e t,s i m i l a r FROM s i m i l a r i t y L I M I T 1"
92 r e s = c.e x e c u t e ( q )
93 a,s = r e s.f e t c h o n e ( )
94 p r i n t ’ * o n e r a n d o m s i m i l a r i t y r e l a t i o n s h i p ( A −>B m e a n s B s i m i l a r t o A ):’
* o n e r a n d o m s i m i l a r i t y r e l a t i o n s h i p ( A - > B m e a n s B s i m i l a r t o A ):
95 p r i n t a,’ −> ’,s
A R 0 0 2 U A 1 1 8 7 B 9 A 6 3 7 D - > A R Q D O R 8 1 1 8 7 F B 3 B 0 6 C
/home/thierry/Columbia/MSongsDB/tutorials/tutorial5/tutorial5.py January 25,2011 3
96
97#c o u n t n u m b e r o f s i m i l a r a r t i s t t o a i n p r e v i o u s c a l l
98 q ="SELECT C o u n t ( s i m i l a r ) FROM s i m i l a r i t y WHERE t a r g e t ="+ e n c o d e _ s t r i n g ( a )
99 r e s = c.e x e c u t e ( q )
100 p r i n t ’ * a r t i s t ’,a,’ h a s t h a t ma n y s i m i l a r a r t i s t s i n t h e d a t a s e t:’
* a r t i s t A R 0 0 2 U A 1 1 8 7 B 9 A 6 3 7 D h a s t h a t m a n y s i m i l a r a r t i s t s i n t h e d a t a s e t:
101 p r i n t r e s.f e t c h o n e ( ) [ 0 ]
4 7
102
103#c o u n t n u m b e r o f a r t i s t s ( c q u e r i e s u p ) i s s i m i l a r t o
104 q ="SELECT C o u n t ( t a r g e t ) FROM s i m i l a r i t y WHERE s i m i l a r ="+ e n c o d e _ s t r i n g ( s )
105 r e s = c.e x e c u t e ( q )
106 p r i n t ’ * a r t i s t ’,s,’ i s s i m i l a r t o t h a t ma n y a r t i s t s i n t h e d a t a s e t:’
* a r t i s t A R Q D O R 8 1 1 8 7 F B 3 B 0 6 C i s s i m i l a r t o t h a t m a n y a r t i s t s i n t h e d a t a s e t
:
107 p r i n t r e s.f e t c h o n e ( ) [ 0 ]
1 5 4
108
109#D O N E
110#c l o s e c u r s o r a n d c o n n e c t i o n
111#( i f f o r s o m e r e a s o n y o u a d d e d s t u f f t o t h e d b o r a l t e r
112#a t a b l e,y o u n e e d t o a l s o d o a c o n n.c o m m i t ( ) )
113 c.c l o s e ( )
114 c o n n.c l o s e ( )