snipt

Ctrl+h for KB shortcuts

Python

Map DB rows into a recordtype in Python

# a sort of lightweight read-only ORM

# the recordtype fields must match exactly the DB fields (see below how to obtain them)!
Identif = recordtype("Identif", "Protein ID tech")

Identif.__str__ = lambda self: self.Protein

def read_db(db, limit=0):

    db = MySQLdb.connect(..., db=db, cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()
    if limit > 0:
        limit = " LIMIT %d" % limit
    else:
        limit = ""
    # end if
    cursor.execute("SELECT ID, Protein, tech FROM IDs" + limit)
    result = cursor.fetchall()

    res = {}
    for row in result:
        #print r.keys() for getting the field names to be specified in the recordtype
        res[row['ID']] = Identif(**row) # "**" transforms the parameters into a dictionary
    # end for
    db.close()

    return res

# end def read_db

# now use

prots = read_db("Proteins")
prots['id1'].Protein
https://snipt.net/embed/83bd3e386405381aa8e965d5911fa6b2/
/raw/83bd3e386405381aa8e965d5911fa6b2/
83bd3e386405381aa8e965d5911fa6b2
python
Python
35
2019-07-16T11:11:44
True
False
False
/api/public/snipt/67724/
map-db-rows-into-a-recordtype
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a> <a href="#L-26">26</a> <a href="#L-27">27</a> <a href="#L-28">28</a> <a href="#L-29">29</a> <a href="#L-30">30</a> <a href="#L-31">31</a> <a href="#L-32">32</a> <a href="#L-33">33</a> <a href="#L-34">34</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1"># a sort of lightweight read-only ORM</span> </span><span id="L-2"><a name="L-2"></a> </span><span id="L-3"><a name="L-3"></a><span class="c1"># the recordtype fields must match exactly the DB fields (see below how to obtain them)!</span> </span><span id="L-4"><a name="L-4"></a><span class="n">Identif</span> <span class="o">=</span> <span class="n">recordtype</span><span class="p">(</span><span class="s2">&quot;Identif&quot;</span><span class="p">,</span> <span class="s2">&quot;Protein ID tech&quot;</span><span class="p">)</span> </span><span id="L-5"><a name="L-5"></a> </span><span id="L-6"><a name="L-6"></a><span class="n">Identif</span><span class="o">.</span><span class="n">__str__</span> <span class="o">=</span> <span class="k">lambda</span> <span class="bp">self</span><span class="p">:</span> <span class="bp">self</span><span class="o">.</span><span class="n">Protein</span> </span><span id="L-7"><a name="L-7"></a> </span><span id="L-8"><a name="L-8"></a><span class="k">def</span> <span class="nf">read_db</span><span class="p">(</span><span class="n">db</span><span class="p">,</span> <span class="n">limit</span><span class="o">=</span><span class="mi">0</span><span class="p">):</span> </span><span id="L-9"><a name="L-9"></a> </span><span id="L-10"><a name="L-10"></a> <span class="n">db</span> <span class="o">=</span> <span class="n">MySQLdb</span><span class="o">.</span><span class="n">connect</span><span class="p">(</span><span class="o">...</span><span class="p">,</span> <span class="n">db</span><span class="o">=</span><span class="n">db</span><span class="p">,</span> <span class="n">cursorclass</span><span class="o">=</span><span class="n">MySQLdb</span><span class="o">.</span><span class="n">cursors</span><span class="o">.</span><span class="n">DictCursor</span><span class="p">)</span> </span><span id="L-11"><a name="L-11"></a> <span class="n">cursor</span> <span class="o">=</span> <span class="n">db</span><span class="o">.</span><span class="n">cursor</span><span class="p">()</span> </span><span id="L-12"><a name="L-12"></a> <span class="k">if</span> <span class="n">limit</span> <span class="o">&gt;</span> <span class="mi">0</span><span class="p">:</span> </span><span id="L-13"><a name="L-13"></a> <span class="n">limit</span> <span class="o">=</span> <span class="s2">&quot; LIMIT </span><span class="si">%d</span><span class="s2">&quot;</span> <span class="o">%</span> <span class="n">limit</span> </span><span id="L-14"><a name="L-14"></a> <span class="k">else</span><span class="p">:</span> </span><span id="L-15"><a name="L-15"></a> <span class="n">limit</span> <span class="o">=</span> <span class="s2">&quot;&quot;</span> </span><span id="L-16"><a name="L-16"></a> <span class="c1"># end if</span> </span><span id="L-17"><a name="L-17"></a> <span class="n">cursor</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">&quot;SELECT ID, Protein, tech FROM IDs&quot;</span> <span class="o">+</span> <span class="n">limit</span><span class="p">)</span> </span><span id="L-18"><a name="L-18"></a> <span class="n">result</span> <span class="o">=</span> <span class="n">cursor</span><span class="o">.</span><span class="n">fetchall</span><span class="p">()</span> </span><span id="L-19"><a name="L-19"></a> </span><span id="L-20"><a name="L-20"></a> <span class="n">res</span> <span class="o">=</span> <span class="p">{}</span> </span><span id="L-21"><a name="L-21"></a> <span class="k">for</span> <span class="n">row</span> <span class="ow">in</span> <span class="n">result</span><span class="p">:</span> </span><span id="L-22"><a name="L-22"></a> <span class="c1">#print r.keys() for getting the field names to be specified in the recordtype</span> </span><span id="L-23"><a name="L-23"></a> <span class="n">res</span><span class="p">[</span><span class="n">row</span><span class="p">[</span><span class="s1">&#39;ID&#39;</span><span class="p">]]</span> <span class="o">=</span> <span class="n">Identif</span><span class="p">(</span><span class="o">**</span><span class="n">row</span><span class="p">)</span> <span class="c1"># &quot;**&quot; transforms the parameters into a dictionary</span> </span><span id="L-24"><a name="L-24"></a> <span class="c1"># end for</span> </span><span id="L-25"><a name="L-25"></a> <span class="n">db</span><span class="o">.</span><span class="n">close</span><span class="p">()</span> </span><span id="L-26"><a name="L-26"></a> </span><span id="L-27"><a name="L-27"></a> <span class="k">return</span> <span class="n">res</span> </span><span id="L-28"><a name="L-28"></a> </span><span id="L-29"><a name="L-29"></a><span class="c1"># end def read_db</span> </span><span id="L-30"><a name="L-30"></a> </span><span id="L-31"><a name="L-31"></a><span class="c1"># now use</span> </span><span id="L-32"><a name="L-32"></a> </span><span id="L-33"><a name="L-33"></a><span class="n">prots</span> <span class="o">=</span> <span class="n">read_db</span><span class="p">(</span><span class="s2">&quot;Proteins&quot;</span><span class="p">)</span> </span><span id="L-34"><a name="L-34"></a><span class="n">prots</span><span class="p">[</span><span class="s1">&#39;id1&#39;</span><span class="p">]</span><span class="o">.</span><span class="n">Protein</span> </span></pre></div> </td></tr></table>
DB, Python