Latest posts on Из transbase в mysql. jython topichttp://python.su/forum/topic/24573/2014-07-22T14:33:49+03:00Альтернативные платформы :: Python для Java :: Из transbase в mysql. jython
2014-07-22T14:33:49+03:00helm2004130979Сам написал, сам отвечу (как всегда :) ):<br/><br/><div class="code"><pre> <span class="n">i</span> <span class="o">=</span> <span class="mi">1</span>
<span class="n">querry</span> <span class="o">=</span> <span class="s">""</span>
<span class="k">for</span> <span class="n">result</span> <span class="ow">in</span> <span class="n">cursorTD</span><span class="o">.</span><span class="n">fetchall</span><span class="p">():</span>
<span class="k">if</span> <span class="n">i</span> <span class="o">==</span> <span class="mi">1</span><span class="p">:</span>
<span class="n">querry</span> <span class="o">=</span> <span class="s">"INSERT ignore INTO </span><span class="si">%s</span><span class="s"> ( `id`, `KORID`,`LANGCODE`, `TEXTSTR`) VALUES (</span><span class="si">%s</span><span class="s">, </span><span class="si">%s</span><span class="s">, '</span><span class="si">%s</span><span class="s">', '</span><span class="si">%s</span><span class="s">' )"</span> <span class="o">%</span> <span class="p">(</span><span class="n">mysqlTable</span><span class="p">,</span> <span class="n">i</span><span class="p">,</span> <span class="n">result</span><span class="p">[</span><span class="mi">0</span><span class="p">],</span> <span class="n">result</span><span class="p">[</span><span class="mi">1</span><span class="p">],</span> <span class="n">result</span><span class="p">[</span><span class="mi">2</span><span class="p">])</span>
<span class="c">#пустые строки заменяем на мускульные</span>
<span class="k">if</span> <span class="n">result</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">==</span> <span class="bp">None</span><span class="p">:</span>
<span class="n">result</span> <span class="o">=</span> <span class="nb">list</span><span class="p">(</span> <span class="n">result</span> <span class="p">)</span>
<span class="n">result</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">=</span> <span class="s">"Null"</span>
<span class="n">result</span> <span class="o">=</span> <span class="nb">tuple</span><span class="p">(</span> <span class="n">result</span> <span class="p">)</span>
<span class="k">if</span> <span class="n">result</span><span class="p">[</span><span class="mi">2</span><span class="p">]</span> <span class="o">==</span> <span class="bp">None</span><span class="p">:</span>
<span class="n">result</span> <span class="o">=</span> <span class="nb">list</span><span class="p">(</span> <span class="n">result</span> <span class="p">)</span>
<span class="n">result</span><span class="p">[</span><span class="mi">2</span><span class="p">]</span> <span class="o">=</span> <span class="s">"Null"</span>
<span class="n">result</span> <span class="o">=</span> <span class="nb">tuple</span><span class="p">(</span> <span class="n">result</span> <span class="p">)</span>
<span class="n">querry</span> <span class="o">+=</span> <span class="s">",(</span><span class="si">%s</span><span class="s">, </span><span class="si">%s</span><span class="s">, '</span><span class="si">%s</span><span class="s">', '</span><span class="si">%s</span><span class="s">' )"</span> <span class="o">%</span> <span class="p">(</span><span class="n">i</span><span class="p">,</span> <span class="n">result</span><span class="p">[</span><span class="mi">0</span><span class="p">],</span> <span class="n">result</span><span class="p">[</span><span class="mi">1</span><span class="p">],</span> <span class="n">result</span><span class="p">[</span><span class="mi">2</span><span class="p">])</span>
<span class="n">i</span> <span class="o">+=</span> <span class="mi">1</span>
<span class="k">print</span> <span class="nb">len</span><span class="p">(</span><span class="n">cursorTD</span><span class="o">.</span><span class="n">fetchall</span><span class="p">())</span>
<span class="k">print</span> <span class="n">querry</span>
<span class="n">cursorMySQL</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">querry</span><span class="p">)</span>
<span class="n">mysqlConn</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
</pre></div><br/>Производительность скорости записи в мускульувеличилась в на порядок, а то и на два.
Альтернативные платформы :: Python для Java :: Из transbase в mysql. jython
2014-07-04T10:06:26+03:00helm2004130195Доброго времени суток!<br/>Задача передо мной стоит такая - нужно из БД transbase (БД написаная только под винду) перегнать в Мускуль. БД большая (список всех больниц и лечебных пунктов во всем мире) - таблицы в несколько десятков гигабайт.<br/><br/><div class="code"><pre><span class="kn">from</span> <span class="nn">com.ziclix.python.sql</span> <span class="kn">import</span> <span class="n">zxJDBC</span>
<span class="kn">from</span> <span class="nn">settings</span> <span class="kn">import</span> <span class="o">*</span>
<span class="kn">from</span> <span class="nn">org.python.core</span> <span class="kn">import</span> <span class="n">codecs</span>
<span class="n">codecs</span><span class="o">.</span><span class="n">setDefaultEncoding</span><span class="p">(</span><span class="s">'utf-8'</span><span class="p">)</span>
<span class="n">tableName</span> <span class="o">=</span> <span class="s">"TB_IMAGES"</span>
<span class="n">mysqlTable</span> <span class="o">=</span> <span class="s">"TB_IMAGES"</span>
<span class="k">try</span><span class="p">:</span>
<span class="n">connTD</span> <span class="o">=</span> <span class="n">zxJDBC</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="n">dbUrl</span><span class="p">,</span> <span class="n">dbUser</span><span class="p">,</span> <span class="n">dbPassword</span><span class="p">,</span> <span class="n">dbDriver</span><span class="p">)</span>
<span class="n">cursorTD</span> <span class="o">=</span> <span class="n">connTD</span><span class="o">.</span><span class="n">cursor</span><span class="p">(</span><span class="bp">True</span><span class="p">)</span>
<span class="c">#connect to MySQL</span>
<span class="n">mysqlConn</span> <span class="o">=</span> <span class="n">zxJDBC</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="n">url</span><span class="p">,</span> <span class="n">user</span><span class="p">,</span> <span class="n">password</span><span class="p">,</span> <span class="n">driver</span><span class="p">,</span> <span class="n">autoReconnect</span><span class="o">=</span><span class="s">"true"</span><span class="p">)</span>
<span class="n">cursorMySQL</span> <span class="o">=</span> <span class="n">mysqlConn</span><span class="o">.</span><span class="n">cursor</span><span class="p">(</span><span class="bp">True</span><span class="p">)</span>
<span class="n">cursorTD</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"SELECT * FROM </span><span class="si">%s</span><span class="s">"</span> <span class="o">%</span> <span class="n">tableName</span><span class="p">)</span>
<span class="n">cursorMySQL</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"DROP TABLE IF EXISTS </span><span class="si">%s</span><span class="s">"</span> <span class="o">%</span> <span class="n">mysqlTable</span><span class="p">)</span>
<span class="n">cursorMySQL</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"CREATE TABLE IF NOT EXISTS </span><span class="si">%s</span><span class="s"> (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `IMAGENAME` text , `IMAGEDATA` blob)"</span> <span class="o">%</span><span class="p">(</span><span class="n">mysqlTable</span><span class="p">))</span>
<span class="n">mysqlConn</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="k">for</span> <span class="n">result</span> <span class="ow">in</span> <span class="n">cursorTD</span><span class="o">.</span><span class="n">fetchall</span><span class="p">():</span>
<span class="k">if</span> <span class="n">result</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">==</span> <span class="bp">None</span><span class="p">:</span>
<span class="n">result</span> <span class="o">=</span> <span class="nb">list</span><span class="p">(</span> <span class="n">result</span> <span class="p">)</span>
<span class="n">result</span><span class="p">[</span><span class="mi">1</span><span class="p">]</span> <span class="o">=</span> <span class="s">"Null"</span>
<span class="n">result</span> <span class="o">=</span> <span class="nb">tuple</span><span class="p">(</span> <span class="n">result</span> <span class="p">)</span>
<span class="c">#print "INSERT INTO %s ( `ID`,`TYPEID`, `COUNTRYID`, `LTFINISHED`) VALUES ( %s, %s, %s )" % (mysqlTable, result[0], result[1], result[2])</span>
<span class="n">cursorMySQL</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s">"INSERT INTO </span><span class="si">%s</span><span class="s"> ( `IMAGENAME`, `IMAGEDATA`) VALUES ( '</span><span class="si">%s</span><span class="s">', </span><span class="si">%s</span><span class="s">)"</span> <span class="o">%</span> <span class="p">(</span><span class="n">mysqlTable</span><span class="p">,</span> <span class="n">result</span><span class="p">[</span><span class="mi">0</span><span class="p">],</span> <span class="n">result</span><span class="p">[</span><span class="mi">1</span><span class="p">]))</span>
<span class="n">mysqlConn</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span>
<span class="c">#mysqlConn.commit()</span>
<span class="k">except</span> <span class="ne">Exception</span><span class="p">,</span> <span class="n">e</span><span class="p">:</span>
<span class="k">print</span> <span class="n">e</span>
</pre></div><br/>Так вот у меня в цикле mysqlConn.commit() от чего оно жутко тормозит. Когда вывожу из цикла, то появляется ошибка. Может есть другой путь? Менее затратный к аппаратным ресурсам? Спасибо.