ArtProject/GenerateSQLScriptForArtist.py at master · AnnKats93/ArtProject · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
import sys, os;

## Create and fill Artist table

fileScript = open('SQLArtDB.txt', 'w')

tableArtist = 'Artist'
tablePainting = 'Painting'

# delete Tables
deleteStr = 'DROP TABLE %s;\n' % (tablePainting)
fileScript.write(deleteStr)
deleteStr = 'DROP TABLE %s;\n' % (tableArtist)
fileScript.write(deleteStr)


columnNames = ['Id', 'Name', 'Description']

createStr = 'CREATE TABLE %s (\n%s int not null auto_increment primary key, \n%s varchar(300) not null, \n%s varchar(300));\n\n' % (
    tableArtist, columnNames[0], columnNames[1], columnNames[2])
fileScript.write(createStr)

dirName = r'/var/www/Art/WikiArt/'
relativeDirName = r'Art/WikiArt/'

artistIdMap = {}
counter = 1

for fileName in os.listdir(dirName):

    painterName = fileName
    artistIdMap[painterName] = counter
    counter += 1

    insertStr = 'INSERT INTO %s(%s) VALUES(\'%s\');\n' % (
        tableArtist, columnNames[1], painterName)
    fileScript.write(insertStr)


## Create and fill Painting table


columnNames = ['Id', 'IdPainter', 'Title', 'Address']

createStr = 'CREATE TABLE %s (\n%s int not null auto_increment primary key, \n%s int not null, \n%s varchar(300), \n%s varchar(300) not null, foreign key (%s) references Artist(Id));\n\n' % (
    tablePainting, columnNames[0], columnNames[1], columnNames[2],
    columnNames[3], columnNames[1])
fileScript.write(createStr)

for artistDir in os.listdir(dirName):

    counter = 1;
    for painting in os.listdir(dirName + artistDir):

        if painting.find('\'') >= 0:
            continue

        paintingAddress = relativeDirName + artistDir + r'/' + painting

        insertStr = 'INSERT INTO %s(%s, %s, %s) VALUES(%d, \'%s\', \'%s\');\n' % (
            tablePainting, columnNames[1], columnNames[2], columnNames[3],
            artistIdMap[artistDir], painting, paintingAddress)
        fileScript.write(insertStr)

        counter += 1
        if counter > 30:
            break


fileScript.close()