<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="In [13]: pd.MultiIndex.from_arrays([[], []])
Out[13]: 
MultiIndex(levels=[[], []],
           labels=[[], []])

In [14]: pd.MultiIndex.from_arrays([[], []]).unique()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
&lt;ipython-input-14-d5be3b23b8c3&gt; in &lt;module&gt;()
----&gt; 1 pd.MultiIndex.from_arrays([[], []]).unique()

~/pandas/pandas/core/indexes/multi.py in unique(self, level)
   1071 
   1072         if level is None:
-&gt; 1073             return super(MultiIndex, self).unique()
   1074         else:
   1075             level = self._get_level_number(level)

~/pandas/pandas/core/indexes/base.py in unique(self, level)
   4366             self._validate_index_level(level)
   4367         result = super(Index, self).unique()
-&gt; 4368         return self._shallow_copy(result)
   4369 
   4370     def drop_duplicates(self, keep='first'):

~/pandas/pandas/core/indexes/multi.py in _shallow_copy(self, values, **kwargs)
    558             # discards freq
    559             kwargs.pop('freq', None)
--&gt; 560             return MultiIndex.from_tuples(values, **kwargs)
    561         return self.view()
    562 

~/pandas/pandas/core/indexes/multi.py in from_tuples(cls, tuples, sortorder, names)
   1315             if names is None:
   1316                 msg = 'Cannot infer number of levels from empty list'
-&gt; 1317                 raise TypeError(msg)
   1318             arrays = [[]] * len(names)
   1319         elif isinstance(tuples, (np.ndarray, Index)):

TypeError: Cannot infer number of levels from empty list
"><pre class="notranslate"><code class="notranslate">In [13]: pd.MultiIndex.from_arrays([[], []])
Out[13]: 
MultiIndex(levels=[[], []],
           labels=[[], []])

In [14]: pd.MultiIndex.from_arrays([[], []]).unique()
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
&lt;ipython-input-14-d5be3b23b8c3&gt; in &lt;module&gt;()
----&gt; 1 pd.MultiIndex.from_arrays([[], []]).unique()

~/pandas/pandas/core/indexes/multi.py in unique(self, level)
   1071 
   1072         if level is None:
-&gt; 1073             return super(MultiIndex, self).unique()
   1074         else:
   1075             level = self._get_level_number(level)

~/pandas/pandas/core/indexes/base.py in unique(self, level)
   4366             self._validate_index_level(level)
   4367         result = super(Index, self).unique()
-&gt; 4368         return self._shallow_copy(result)
   4369 
   4370     def drop_duplicates(self, keep='first'):

~/pandas/pandas/core/indexes/multi.py in _shallow_copy(self, values, **kwargs)
    558             # discards freq
    559             kwargs.pop('freq', None)
--&gt; 560             return MultiIndex.from_tuples(values, **kwargs)
    561         return self.view()
    562 

~/pandas/pandas/core/indexes/multi.py in from_tuples(cls, tuples, sortorder, names)
   1315             if names is None:
   1316                 msg = 'Cannot infer number of levels from empty list'
-&gt; 1317                 raise TypeError(msg)
   1318             arrays = [[]] * len(names)
   1319         elif isinstance(tuples, (np.ndarray, Index)):

TypeError: Cannot infer number of levels from empty list

</code></pre></div>
<p dir="auto">I have been trying to insert ~30k rows into a mysql database using pandas-0.15.1, oursql-0.9.3.1 and sqlalchemy-0.9.4. Because the machine is as across the atlantic from me, calling <code class="notranslate">data.to_sql</code> was taking &gt;1 hr to insert the data. On inspecting with wireshark, the issue is that it is sending an insert for every row, then waiting for the ACK before sending the next, and, long story short, the ping times are killing me.</p>
<p dir="auto">However, following the instructions from <a href="http://docs.sqlalchemy.org/en/latest/core/dml.html#sqlalchemy.sql.expression.Insert.values" rel="nofollow">SQLAlchemy</a>, I changed</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="def _execute_insert(self, conn, keys, data_iter):
    data = [dict((k, v) for k, v in zip(keys, row)) for row in data_iter]
    conn.execute(self.insert_statement(), data)"><pre class="notranslate"><span class="pl-k">def</span> <span class="pl-en">_execute_insert</span>(<span class="pl-s1">self</span>, <span class="pl-s1">conn</span>, <span class="pl-s1">keys</span>, <span class="pl-s1">data_iter</span>):
    <span class="pl-s1">data</span> <span class="pl-c1">=</span> [<span class="pl-en">dict</span>((<span class="pl-s1">k</span>, <span class="pl-s1">v</span>) <span class="pl-k">for</span> <span class="pl-s1">k</span>, <span class="pl-s1">v</span> <span class="pl-c1">in</span> <span class="pl-en">zip</span>(<span class="pl-s1">keys</span>, <span class="pl-s1">row</span>)) <span class="pl-k">for</span> <span class="pl-s1">row</span> <span class="pl-c1">in</span> <span class="pl-s1">data_iter</span>]
    <span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">self</span>.<span class="pl-en">insert_statement</span>(), <span class="pl-s1">data</span>)</pre></div>
<p dir="auto">to</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="def _execute_insert(self, conn, keys, data_iter):
    data = [dict((k, v) for k, v in zip(keys, row)) for row in data_iter]
    conn.execute(self.insert_statement().values(data))"><pre class="notranslate"><span class="pl-k">def</span> <span class="pl-en">_execute_insert</span>(<span class="pl-s1">self</span>, <span class="pl-s1">conn</span>, <span class="pl-s1">keys</span>, <span class="pl-s1">data_iter</span>):
    <span class="pl-s1">data</span> <span class="pl-c1">=</span> [<span class="pl-en">dict</span>((<span class="pl-s1">k</span>, <span class="pl-s1">v</span>) <span class="pl-k">for</span> <span class="pl-s1">k</span>, <span class="pl-s1">v</span> <span class="pl-c1">in</span> <span class="pl-en">zip</span>(<span class="pl-s1">keys</span>, <span class="pl-s1">row</span>)) <span class="pl-k">for</span> <span class="pl-s1">row</span> <span class="pl-c1">in</span> <span class="pl-s1">data_iter</span>]
    <span class="pl-s1">conn</span>.<span class="pl-en">execute</span>(<span class="pl-s1">self</span>.<span class="pl-en">insert_statement</span>().<span class="pl-en">values</span>(<span class="pl-s1">data</span>))</pre></div>
<p dir="auto">and the entire operation completes in less than a minute. (To save you a click, the difference is between multiple calls to <code class="notranslate">insert into foo (columns) values (rowX)</code> and one massive <code class="notranslate">insert into foo (columns) VALUES (row1), (row2), row3)</code>). Given how often people are likely to use pandas to insert large volumes of data, this feels like a huge win that would be great to be included more widely.</p>
<p dir="auto">Some challenges:</p>
<ul dir="auto">
<li>Not every database supports multirow inserts (SQLite and SQLServer didn't in the past, though they do now). I don't know how to check for this via SQLAlchemy</li>
<li>The MySQL server I was using didn't allow me to insert the data all in one go, I had to set the chunksize (5k worked fine, but I guess the full 30k was too much). If we made this the default insert, most people would have to add a chunk size (which might be hard to calculate, as it might be determined by the maximum packet size of the server).</li>
</ul>
<p dir="auto">The easiest way to do this, would be to add a <code class="notranslate">multirow=</code> boolean parameter (default <code class="notranslate">False</code>) to the <code class="notranslate">to_sql</code> function, and then leave the user responsible for setting the chunksize, but perhaps there's a better way?</p>
<p dir="auto">Thoughts?</p>