Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100755 213 lines (172 sloc) 6.767 kb
7b87dde Initial commit
Maxim Zhukov authored
1 #!/usr/bin/python
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
2 # -*- coding: utf-8 -*-
7b87dde Initial commit
Maxim Zhukov authored
3
72f49bb @crmaxx Add 'logger' in module
authored
4 import sys
c367aba @crmaxx Operation with the configuration files is added
authored
5 import ConfigParser
72f49bb @crmaxx Add 'logger' in module
authored
6 import logging
7
8 LEVELS = {'debug': logging.DEBUG,
9 'info': logging.INFO,
10 'warning': logging.WARNING,
11 'error': logging.ERROR,
12 'critical': logging.CRITICAL}
c367aba @crmaxx Operation with the configuration files is added
authored
13
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
14 """Инициализируем модуль для работы с конфигами"""
c367aba @crmaxx Operation with the configuration files is added
authored
15 config = ConfigParser.ConfigParser()
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
16 """Подгружаем конфиг"""
c367aba @crmaxx Operation with the configuration files is added
authored
17 config.read('loader.cfg')
18
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
19 """Получаем основные переменные"""
c367aba @crmaxx Operation with the configuration files is added
authored
20 user = config.get("Oracle", "user")
21 password = config.get("Oracle", "password")
22 server = config.get("Oracle", "server")
23 port = config.get("Oracle", "port")
24 instance = config.get("Oracle", "instance")
25
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
26 """Инициализируем систему логирования"""
72f49bb @crmaxx Add 'logger' in module
authored
27 level_name = config.get("Logs", "level")
28 level = LEVELS.get(level_name, logging.NOTSET)
29 logging.basicConfig(level = level,
56beb8c @crmaxx Add loggers in source code
authored
30 format = config.get("Logs", "format", 1),
31 filename = config.get("Logs", "filename"),
72f49bb @crmaxx Add 'logger' in module
authored
32 filemode = 'w')
33
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
34 """Создаём логгеры"""
35 main_logger = logging.getLogger('Main')
36 orcl_logger = logging.getLogger('Oracle')
37 file_logger = logging.getLogger('Files')
56beb8c @crmaxx Add loggers in source code
authored
38
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
39 main_logger.info("Starting mp3loader")
40
41 """Пробуем подключить модуль cx_Oracle"""
42 main_logger.debug("Try import cx_Oracle")
7b87dde Initial commit
Maxim Zhukov authored
43 try:
44 import cx_Oracle
6956af8 @crmaxx Add more informative outputs. Change TNS string.
authored
45 except ImportError, info:
fc882f2 @crmaxx Fix connection string
authored
46 print "Import Error: ", info
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
47 main_logger.critical("Import Error: %s", info)
7b87dde Initial commit
Maxim Zhukov authored
48 sys.exit()
49
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
50 """Проверяем версию модуля"""
51 main_logger.debug("Check cx_Oracle version")
7b87dde Initial commit
Maxim Zhukov authored
52 if cx_Oracle.version < '3.0':
fc882f2 @crmaxx Fix connection string
authored
53 print "Very old version of cx_Oracle: ", cx_Oracle.version
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
54 main_logger.critical("Very old version of cx_Oracle: %s", cx_Oracle.version)
7b87dde Initial commit
Maxim Zhukov authored
55 sys.exit()
56
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
57 """Пробуем подключиться к базе данных"""
7b87dde Initial commit
Maxim Zhukov authored
58 try:
6956af8 @crmaxx Add more informative outputs. Change TNS string.
authored
59 print "Connecting to Mobisky.."
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
60 orcl_logger.info("Connecting to Mobisky.")
c367aba @crmaxx Operation with the configuration files is added
authored
61 my_connection = cx_Oracle.connect(user + '/' + password + '@//' + server + ':' + port + '/' +instance)
7b87dde Initial commit
Maxim Zhukov authored
62 except cx_Oracle.DatabaseError, info:
6956af8 @crmaxx Add more informative outputs. Change TNS string.
authored
63 print "Logon Error:", info
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
64 orcl_logger.error("Logon Error: %s", info)
7b87dde Initial commit
Maxim Zhukov authored
65 exit(0)
66
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
67 """Открываем курсор"""
68 orcl_logger.debug("Opening cursor.")
7b87dde Initial commit
Maxim Zhukov authored
69 my_cursor = my_connection.cursor()
70
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
71 """Проверяем, есть ли таблица"""
72 SQL = """SELECT COUNT(*)
73 INTO :p_Value
74 FROM USER_OBJECTS
75 WHERE OBJECT_NAME = 'MSY_CONTENT_MP3_TMP'
76 AND OBJECT_TYPE = 'TABLE';"""
77
78 var = my_cursor.var(cx_Oracle.NUMBER)
79
80 try:
81 orcl_logger.debug("Check MSY_CONTENT_MP3_TMP is exist.")
82 my_cursor.execute(SQL, p_Value = var)
83 except cx_Oracle.DatabaseError, info:
84 print "SQL Error:", info
85 orcl_logger.error("SQL Error: %s", info)
86 exit(0)
87
88 COUNT = var.getvalue()
89
90 if COUNT:
91 """Удаляем временную таблицу"""
92 SQL = "TRUNCATE MSY_CONTENT_MP3_TMP;"
93
94 try:
95 orcl_logger.debug("Delete temporaly table")
96 my_cursor.execute(SQL)
97 except cx_Oracle.DatabaseError, info:
98 print "SQL Error:", info
99 orcl_logger.error("SQL Error: %s", info)
100 exit(0)
101
102 """Создаём временную таблицу"""
103 SQL = """CREATE TABLE MSY_CONTENT_MP3_TMP
104 (
105 CODE VARCHAR2(4000 BYTE)
106 );"""
107
108 try:
109 orcl_logger.debug("Create temporaly table")
110 my_cursor.execute(SQL)
111 except cx_Oracle.DatabaseError, info:
112 print "SQL Error:", info
113 orcl_logger.error("SQL Error: %s", info)
114 exit(0)
115
116 """Копируем из внешней таблицы во временную"""
117 SQL = """INSERT INTO MSY_CONTENT_MP3_TMP (CODE)
118 SELECT CODE FROM MSY_CONTENT_MP3_EXT;"""
119
120 try:
121 orcl_logger.debug("Copy data from external table into temporaly")
122 my_cursor.execute(SQL)
123 except cx_Oracle.DatabaseError, info:
124 print "SQL Error:", info
125 orcl_logger.error("SQL Error: %s", info)
126 exit(0)
127
128
129 """Проверяем коды в существуюущих"""
130 SQL = """SELECT CONTENT_ID FROM CONTENT WHERE CODE IN (
131 SELECT CODE FROM MSY_CONTENT_MP3_TMP);"""
132
133 try:
134 orcl_logger.debug("Check CODE in CONTENT")
135 my_cursor.execute(SQL)
136 except cx_Oracle.DatabaseError, info:
137 print "SQL Error:", info
138 orcl_logger.error("SQL Error: %s", info)
139 exit(0)
140
141 for record in my_cursor.fetchall():
142 print record
143
144 """Выбираем варианты контента у дублирующихся"""
145 SQL = "SELECT * FROM CONTENT_VARIANTS WHERE CONTENT_ID = :ID;"
146
147 """Копируем из временной таблицы в MSY_CONTENT_MP3"""
148 SQL = """INSERT INTO MSY_CONTENT_MP3 (CODE)
149 SELECT CODE FROM MSY_CONTENT_MP3_TMP
150 WHERE CODE NOT IN ('402884','402652','402669','402682');"""
151
152 """Добавляем в контент"""
153 SQL = """DECLARE
154 N_CONTENT_ID CONTENT.ID%TYPE;
155 BEGIN
156
157 FOR REC IN(
158 SELECT CODE FROM MSY_CONTENT_TMP
159 WHERE CODE NOT IN ('402884','402652','402669','402682');
160 )
161 LOOP
162
163 INSERT INTO CONTENT(CODE, TARIFF_CLASS_ID, CONTENT_GROUP_ID, NAME, IS_ENABLED,
164 IS_VISIBLE, IS_NEW, CREATE_DATE, UPDATE_DATE, WAP_FILE_NAME)
165 VALUES (REC.CODE, 0, 64054, REC.CODE, 1,
166 1, 1, SYSDATE, SYSDATE, REC.CODE||'.mp3')
167 RETURNING ID INTO N_CONTENT_ID;
168
169 INSERT INTO CONTENT_VARIANTS(CONTENT_ID, CONTENT_SUBTYPE_ID,FILE_NAME, REMOTE_URL)
170 VALUES(N_CONTENT_ID, 11, REC.CODE||'.mp3','http://127.0.0.1/downloads/'||REC.CODE||'.mp3');
171 END LOOP;
172 END;"""
173
174 """Пробуем выполнить запрос"""
7b87dde Initial commit
Maxim Zhukov authored
175 try:
176 my_cursor.execute("""
177 SELECT OWNER,
178 SEGMENT_TYPE,
179 TABLESPACE_NAME,
180 SUM(BLOCKS)SIZE_BLOCKS,
181 COUNT(*) SIZE_EXTENTS
182 FROM DBA_EXTENTS
183 WHERE OWNER LIKE :S
184 GROUP BY OWNER, SEGMENT_TYPE, TABLESPACE_NAME
6956af8 @crmaxx Add more informative outputs. Change TNS string.
authored
185 """, S = 'SYS%')
7b87dde Initial commit
Maxim Zhukov authored
186 except cx_Oracle.DatabaseError, info:
187 print "SQL Error:", info
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
188 orcl_logger.error("SQL Error: %s", info)
7b87dde Initial commit
Maxim Zhukov authored
189 exit(0)
190
f2aa1f6 @crmaxx Add more loggers. Begin coding logic
authored
191 orcl_logger.info('Database: %s', my_connection.tnsentry)
56beb8c @crmaxx Add loggers in source code
authored
192
7b87dde Initial commit
Maxim Zhukov authored
193 print 'Database:', my_connection.tnsentry
194 print
195 print "Used space by owner, object type, tablespace "
196 print "-----------------------------------------------------------"
197
fc882f2 @crmaxx Fix connection string
authored
198 title_mask = ('%-16s', '%-16s', '%-16s', '%-8s', '%-8s')
7b87dde Initial commit
Maxim Zhukov authored
199
200 i = 0
201
202 for column_description in my_cursor.description:
6956af8 @crmaxx Add more informative outputs. Change TNS string.
authored
203 print title_mask[i] % column_description[0],
7b87dde Initial commit
Maxim Zhukov authored
204 i = 1 + i
205
206 print ''
207 print "------------------------------------------------------------"
208
6956af8 @crmaxx Add more informative outputs. Change TNS string.
authored
209 row_mask = '%-16s %-16s %-16s %8.0f %8.0f '
7b87dde Initial commit
Maxim Zhukov authored
210
211 for record in my_cursor.fetchall():
6956af8 @crmaxx Add more informative outputs. Change TNS string.
authored
212 print row_mask % record
Something went wrong with that request. Please try again.