SQLAlchemy

History

SQLAlchemy was first released in February 2006. It has evolved to include a wide range of features for database interaction and has gained popularity among Python developers. Notable versions include:

• Version 0.1 (2006): Initial release.
• Version 1.0 (2015): Major enhancements in ORM and SQL expression language.
• Version 1.4 (2021): Introduction of a new ORM API.

Example

The following example represents an n-to-1 relationship between movies and their directors. It is shown how user-defined Python classes create corresponding database tables, how instances with relationships are created from either side of the relationship, and finally how the data can be queried — illustrating automatically generated SQL queries for both lazy and eager loading.

Schema definition

Creating two Python classes and corresponding database tables in the DBMS:

<span id="mwRQ"></span><span class="kn" id="mwRg">from</span><span class="w" id="mwRw"> </span><span class="nn" id="mwSA">sqlalchemy</span><span class="w" id="mwSQ"> </span><span class="kn" id="mwSg">import</span> <span class="o" id="mwSw">*</span> <span class="kn" id="mwTA">from</span><span class="w" id="mwTQ"> </span><span class="nn" id="mwTg">sqlalchemy.ext.declarative</span><span class="w" id="mwTw"> </span><span class="kn" id="mwUA">import</span> <span class="n" id="mwUQ">declarative_base</span> <span class="kn" id="mwUg">from</span><span class="w" id="mwUw"> </span><span class="nn" id="mwVA">sqlalchemy.orm</span><span class="w" id="mwVQ"> </span><span class="kn" id="mwVg">import</span> <span class="n" id="mwVw">relation</span><span class="p" id="mwWA">,</span> <span class="n" id="mwWQ">sessionmaker</span> <span class="n" id="mwWg">Base</span> <span class="o" id="mwWw">=</span> <span class="n" id="mwXA">declarative_base</span><span class="p" id="mwXQ">()</span> <span class="k" id="mwXg">class</span><span class="w" id="mwXw"> </span><span class="nc" id="mwYA">Movie</span><span class="p" id="mwYQ">(</span><span class="n" id="mwYg">Base</span><span class="p" id="mwYw">):</span> <span class="n" id="mwZA">__tablename__</span> <span class="o" id="mwZQ">=</span> <span class="s2" id="mwZg">"movies"</span> <span class="nb" id="mwZw">id</span> <span class="o" id="mwaA">=</span> <span class="n" id="mwaQ">Column</span><span class="p" id="mwag">(</span><span class="n" id="mwaw">Integer</span><span class="p" id="mwbA">,</span> <span class="n" id="mwbQ">primary_key</span><span class="o" id="mwbg">=</span><span class="kc" id="mwbw">True</span><span class="p" id="mwcA">)</span> <span class="n" id="mwcQ">title</span> <span class="o" id="mwcg">=</span> <span class="n" id="mwcw">Column</span><span class="p" id="mwdA">(</span><span class="n" id="mwdQ">String</span><span class="p" id="mwdg">(</span><span class="mi" id="mwdw">255</span><span class="p" id="mweA">),</span> <span class="n" id="mweQ">nullable</span><span class="o" id="mweg">=</span><span class="kc" id="mwew">False</span><span class="p" id="mwfA">)</span> <span class="n" id="mwfQ">year</span> <span class="o" id="mwfg">=</span> <span class="n" id="mwfw">Column</span><span class="p" id="mwgA">(</span><span class="n" id="mwgQ">Integer</span><span class="p" id="mwgg">)</span> <span class="n" id="mwgw">directed_by</span> <span class="o" id="mwhA">=</span> <span class="n" id="mwhQ">Column</span><span class="p" id="mwhg">(</span><span class="n" id="mwhw">Integer</span><span class="p" id="mwiA">,</span> <span class="n" id="mwiQ">ForeignKey</span><span class="p" id="mwig">(</span><span class="s2" id="mwiw">"directors.id"</span><span class="p" id="mwjA">))</span> <span class="n" id="mwjQ">director</span> <span class="o" id="mwjg">=</span> <span class="n" id="mwjw">relation</span><span class="p" id="mwkA">(</span><span class="s2" id="mwkQ">"Director"</span><span class="p" id="mwkg">,</span> <span class="n" id="mwkw">backref</span><span class="o" id="mwlA">=</span><span class="s2" id="mwlQ">"movies"</span><span class="p" id="mwlg">,</span> <span class="n" id="mwlw">lazy</span><span class="o" id="mwmA">=</span><span class="kc" id="mwmQ">False</span><span class="p" id="mwmg">)</span> <span class="k" id="mwmw">def</span><span class="w" id="mwnA"> </span><span class="fm" id="mwnQ">__init__</span><span class="p" id="mwng">(</span><span class="bp" id="mwnw">self</span><span class="p" id="mwoA">,</span> <span class="n" id="mwoQ">title</span><span class="o" id="mwog">=</span><span class="kc" id="mwow">None</span><span class="p" id="mwpA">,</span> <span class="n" id="mwpQ">year</span><span class="o" id="mwpg">=</span><span class="kc" id="mwpw">None</span><span class="p" id="mwqA">):</span> <span class="bp" id="mwqQ">self</span><span class="o" id="mwqg">.</span><span class="n" id="mwqw">title</span> <span class="o" id="mwrA">=</span> <span class="n" id="mwrQ">title</span> <span class="bp" id="mwrg">self</span><span class="o" id="mwrw">.</span><span class="n" id="mwsA">year</span> <span class="o" id="mwsQ">=</span> <span class="n" id="mwsg">year</span> <span class="k" id="mwsw">def</span><span class="w" id="mwtA"> </span><span class="fm" id="mwtQ">__repr__</span><span class="p" id="mwtg">(</span><span class="bp" id="mwtw">self</span><span class="p" id="mwuA">):</span> <span class="k" id="mwuQ">return</span> <span class="sa" id="mwug">f</span><span class="s2" id="mwuw">"Movie(</span><span class="si" id="mwvA">{</span><span class="bp" id="mwvQ">self</span><span class="o" id="mwvg">.</span><span class="n" id="mwvw">title</span><span class="si" id="mwwA">}</span><span class="s2" id="mwwQ">, </span><span class="si" id="mwwg">{</span><span class="bp" id="mwww">self</span><span class="o" id="mwxA">.</span><span class="n" id="mwxQ">year</span><span class="si" id="mwxg">}</span><span class="s2" id="mwxw">, </span><span class="si" id="mwyA">{</span><span class="bp" id="mwyQ">self</span><span class="o" id="mwyg">.</span><span class="n" id="mwyw">director</span><span class="si" id="mwzA">}</span><span class="s2" id="mwzQ">)"</span> <span class="k" id="mwzg">class</span><span class="w" id="mwzw"> </span><span class="nc" id="mw0A">Director</span><span class="p" id="mw0Q">(</span><span class="n" id="mw0g">Base</span><span class="p" id="mw0w">):</span> <span class="n" id="mw1A">__tablename__</span> <span class="o" id="mw1Q">=</span> <span class="s2" id="mw1g">"directors"</span> <span class="nb" id="mw1w">id</span> <span class="o" id="mw2A">=</span> <span class="n" id="mw2Q">Column</span><span class="p" id="mw2g">(</span><span class="n" id="mw2w">Integer</span><span class="p" id="mw3A">,</span> <span class="n" id="mw3Q">primary_key</span><span class="o" id="mw3g">=</span><span class="kc" id="mw3w">True</span><span class="p" id="mw4A">)</span> <span class="n" id="mw4Q">name</span> <span class="o" id="mw4g">=</span> <span class="n" id="mw4w">Column</span><span class="p" id="mw5A">(</span><span class="n" id="mw5Q">String</span><span class="p" id="mw5g">(</span><span class="mi" id="mw5w">50</span><span class="p" id="mw6A">),</span> <span class="n" id="mw6Q">nullable</span><span class="o" id="mw6g">=</span><span class="kc" id="mw6w">False</span><span class="p" id="mw7A">,</span> <span class="n" id="mw7Q">unique</span><span class="o" id="mw7g">=</span><span class="kc" id="mw7w">True</span><span class="p" id="mw8A">)</span> <span class="k" id="mw8Q">def</span><span class="w" id="mw8g"> </span><span class="fm" id="mw8w">__init__</span><span class="p" id="mw9A">(</span><span class="bp" id="mw9Q">self</span><span class="p" id="mw9g">,</span> <span class="n" id="mw9w">name</span><span class="o" id="mw-A">=</span><span class="kc" id="mw-Q">None</span><span class="p" id="mw-g">):</span> <span class="bp" id="mw-w">self</span><span class="o" id="mw_A">.</span><span class="n" id="mw_Q">name</span> <span class="o" id="mw_g">=</span> <span class="n" id="mw_w">name</span> <span class="k" id="mwAQA">def</span><span class="w" id="mwAQE"> </span><span class="fm" id="mwAQI">__repr__</span><span class="p" id="mwAQM">(</span><span class="bp" id="mwAQQ">self</span><span class="p" id="mwAQU">):</span> <span class="k" id="mwAQY">return</span> <span class="sa" id="mwAQc">f</span><span class="s2" id="mwAQg">"Director(</span><span class="si" id="mwAQk">{</span><span class="bp" id="mwAQo">self</span><span class="o" id="mwAQs">.</span><span class="n" id="mwAQw">name</span><span class="si" id="mwAQ0">}</span><span class="s2" id="mwAQ4">)"</span> <span class="n" id="mwAQ8">engine</span> <span class="o" id="mwARA">=</span> <span class="n" id="mwARE">create_engine</span><span class="p" id="mwARI">(</span><span class="s2" id="mwARM">"dbms://user:pwd@host/dbname"</span><span class="p" id="mwARQ">)</span> <span class="n" id="mwARU">Base</span><span class="o" id="mwARY">.</span><span class="n" id="mwARc">metadata</span><span class="o" id="mwARg">.</span><span class="n" id="mwARk">create_all</span><span class="p" id="mwARo">(</span><span class="n" id="mwARs">engine</span><span class="p" id="mwARw">)</span>

Data insertion

One can insert a director-movie relationship via either entity:

<span id="mwASE"></span><span class="n" id="mwASI">Session</span> <span class="o" id="mwASM">=</span> <span class="n" id="mwASQ">sessionmaker</span><span class="p" id="mwASU">(</span><span class="n" id="mwASY">bind</span><span class="o" id="mwASc">=</span><span class="n" id="mwASg">engine</span><span class="p" id="mwASk">)</span> <span class="n" id="mwASo">session</span> <span class="o" id="mwASs">=</span> <span class="n" id="mwASw">Session</span><span class="p" id="mwAS0">()</span> <span class="n" id="mwAS4">m1</span> <span class="o" id="mwAS8">=</span> <span class="n" id="mwATA">Movie</span><span class="p" id="mwATE">(</span><span class="s2" id="mwATI">"Robocop"</span><span class="p" id="mwATM">,</span> <span class="mi" id="mwATQ">1987</span><span class="p" id="mwATU">)</span> <span class="n" id="mwATY">m1</span><span class="o" id="mwATc">.</span><span class="n" id="mwATg">director</span> <span class="o" id="mwATk">=</span> <span class="n" id="mwATo">Director</span><span class="p" id="mwATs">(</span><span class="s2" id="mwATw">"Paul Verhoeven"</span><span class="p" id="mwAT0">)</span> <span class="n" id="mwAT4">d2</span> <span class="o" id="mwAT8">=</span> <span class="n" id="mwAUA">Director</span><span class="p" id="mwAUE">(</span><span class="s2" id="mwAUI">"George Lucas"</span><span class="p" id="mwAUM">)</span> <span class="n" id="mwAUQ">d2</span><span class="o" id="mwAUU">.</span><span class="n" id="mwAUY">movies</span> <span class="o" id="mwAUc">=</span> <span class="p" id="mwAUg">[</span><span class="n" id="mwAUk">Movie</span><span class="p" id="mwAUo">(</span><span class="s2" id="mwAUs">"Star Wars"</span><span class="p" id="mwAUw">,</span> <span class="mi" id="mwAU0">1977</span><span class="p" id="mwAU4">),</span> <span class="n" id="mwAU8">Movie</span><span class="p" id="mwAVA">(</span><span class="s2" id="mwAVE">"THX 1138"</span><span class="p" id="mwAVI">,</span> <span class="mi" id="mwAVM">1971</span><span class="p" id="mwAVQ">)]</span> <span class="k" id="mwAVU">try</span><span class="p" id="mwAVY">:</span> <span class="n" id="mwAVc">session</span><span class="o" id="mwAVg">.</span><span class="n" id="mwAVk">add</span><span class="p" id="mwAVo">(</span><span class="n" id="mwAVs">m1</span><span class="p" id="mwAVw">)</span> <span class="n" id="mwAV0">session</span><span class="o" id="mwAV4">.</span><span class="n" id="mwAV8">add</span><span class="p" id="mwAWA">(</span><span class="n" id="mwAWE">d2</span><span class="p" id="mwAWI">)</span> <span class="n" id="mwAWM">session</span><span class="o" id="mwAWQ">.</span><span class="n" id="mwAWU">commit</span><span class="p" id="mwAWY">()</span> <span class="k" id="mwAWc">except</span><span class="p" id="mwAWg">:</span> <span class="n" id="mwAWk">session</span><span class="o" id="mwAWo">.</span><span class="n" id="mwAWs">rollback</span><span class="p" id="mwAWw">()</span>

Querying

<span id="mwAXA"></span><span class="n" id="mwAXE">alldata</span> <span class="o" id="mwAXI">=</span> <span class="n" id="mwAXM">session</span><span class="o" id="mwAXQ">.</span><span class="n" id="mwAXU">query</span><span class="p" id="mwAXY">(</span><span class="n" id="mwAXc">Movie</span><span class="p" id="mwAXg">)</span><span class="o" id="mwAXk">.</span><span class="n" id="mwAXo">all</span><span class="p" id="mwAXs">()</span> <span class="k" id="mwAXw">for</span> <span class="n" id="mwAX0">somedata</span> <span class="ow" id="mwAX4">in</span> <span class="n" id="mwAX8">alldata</span><span class="p" id="mwAYA">:</span> <span class="nb" id="mwAYE">print</span><span class="p" id="mwAYI">(</span><span class="n" id="mwAYM">somedata</span><span class="p" id="mwAYQ">)</span>

SQLAlchemy issues the following query to the DBMS (omitting aliases):

<span id="mwAYg"></span><span class="k" id="mwAYk">SELECT</span><span class="w" id="mwAYo"> </span><span class="n" id="mwAYs">movies</span><span class="p" id="mwAYw">.</span><span class="n" id="mwAY0">id</span><span class="p" id="mwAY4">,</span><span class="w" id="mwAY8"> </span><span class="n" id="mwAZA">movies</span><span class="p" id="mwAZE">.</span><span class="n" id="mwAZI">title</span><span class="p" id="mwAZM">,</span><span class="w" id="mwAZQ"> </span><span class="n" id="mwAZU">movies</span><span class="p" id="mwAZY">.</span><span class="k" id="mwAZc">year</span><span class="p" id="mwAZg">,</span><span class="w" id="mwAZk"> </span><span class="n" id="mwAZo">movies</span><span class="p" id="mwAZs">.</span><span class="n" id="mwAZw">directed_by</span><span class="p" id="mwAZ0">,</span><span class="w" id="mwAZ4"> </span><span class="n" id="mwAZ8">directors</span><span class="p" id="mwAaA">.</span><span class="n" id="mwAaE">id</span><span class="p" id="mwAaI">,</span><span class="w" id="mwAaM"> </span><span class="n" id="mwAaQ">directors</span><span class="p" id="mwAaU">.</span><span class="n" id="mwAaY">name</span> <span class="k" id="mwAac">FROM</span><span class="w" id="mwAag"> </span><span class="n" id="mwAak">movies</span><span class="w" id="mwAao"> </span><span class="k" id="mwAas">LEFT</span><span class="w" id="mwAaw"> </span><span class="k" id="mwAa0">OUTER</span><span class="w" id="mwAa4"> </span><span class="k" id="mwAa8">JOIN</span><span class="w" id="mwAbA"> </span><span class="n" id="mwAbE">directors</span><span class="w" id="mwAbI"> </span><span class="k" id="mwAbM">ON</span><span class="w" id="mwAbQ"> </span><span class="n" id="mwAbU">directors</span><span class="p" id="mwAbY">.</span><span class="n" id="mwAbc">id</span><span class="w" id="mwAbg"> </span><span class="o" id="mwAbk">=</span><span class="w" id="mwAbo"> </span><span class="n" id="mwAbs">movies</span><span class="p" id="mwAbw">.</span><span class="n" id="mwAb0">directed_by</span>

The output:

<span id="mwAcE"></span><span class="n" id="mwAcI">Movie</span><span class="p" id="mwAcM">(</span><span class="s1" id="mwAcQ">'Robocop'</span><span class="p" id="mwAcU">,</span> <span class="mi" id="mwAcY">1987</span><span class="n" id="mwAcc">L</span><span class="p" id="mwAcg">,</span> <span class="n" id="mwAck">Director</span><span class="p" id="mwAco">(</span><span class="s1" id="mwAcs">'Paul Verhoeven'</span><span class="p" id="mwAcw">))</span> <span class="n" id="mwAc0">Movie</span><span class="p" id="mwAc4">(</span><span class="s1" id="mwAc8">'Star Wars'</span><span class="p" id="mwAdA">,</span> <span class="mi" id="mwAdE">1977</span><span class="n" id="mwAdI">L</span><span class="p" id="mwAdM">,</span> <span class="n" id="mwAdQ">Director</span><span class="p" id="mwAdU">(</span><span class="s1" id="mwAdY">'George Lucas'</span><span class="p" id="mwAdc">))</span> <span class="n" id="mwAdg">Movie</span><span class="p" id="mwAdk">(</span><span class="s1" id="mwAdo">'THX 1138'</span><span class="p" id="mwAds">,</span> <span class="mi" id="mwAdw">1971</span><span class="n" id="mwAd0">L</span><span class="p" id="mwAd4">,</span> <span class="n" id="mwAd8">Director</span><span class="p" id="mwAeA">(</span><span class="s1" id="mwAeE">'George Lucas'</span><span class="p" id="mwAeI">))</span>

Setting lazy=True (default) instead, SQLAlchemy would first issue a query to get the list of movies and only when needed (lazy) for each director a query to get the name of the corresponding director:

<span id="mwAec"></span><span class="k" id="mwAeg">SELECT</span><span class="w" id="mwAek"> </span><span class="n" id="mwAeo">movies</span><span class="p" id="mwAes">.</span><span class="n" id="mwAew">id</span><span class="p" id="mwAe0">,</span><span class="w" id="mwAe4"> </span><span class="n" id="mwAe8">movies</span><span class="p" id="mwAfA">.</span><span class="n" id="mwAfE">title</span><span class="p" id="mwAfI">,</span><span class="w" id="mwAfM"> </span><span class="n" id="mwAfQ">movies</span><span class="p" id="mwAfU">.</span><span class="k" id="mwAfY">year</span><span class="p" id="mwAfc">,</span><span class="w" id="mwAfg"> </span><span class="n" id="mwAfk">movies</span><span class="p" id="mwAfo">.</span><span class="n" id="mwAfs">directed_by</span> <span class="k" id="mwAfw">FROM</span><span class="w" id="mwAf0"> </span><span class="n" id="mwAf4">movies</span> <span class="k" id="mwAf8">SELECT</span><span class="w" id="mwAgA"> </span><span class="n" id="mwAgE">directors</span><span class="p" id="mwAgI">.</span><span class="n" id="mwAgM">id</span><span class="p" id="mwAgQ">,</span><span class="w" id="mwAgU"> </span><span class="n" id="mwAgY">directors</span><span class="p" id="mwAgc">.</span><span class="n" id="mwAgg">name</span> <span class="k" id="mwAgk">FROM</span><span class="w" id="mwAgo"> </span><span class="n" id="mwAgs">directors</span> <span class="k" id="mwAgw">WHERE</span><span class="w" id="mwAg0"> </span><span class="n" id="mwAg4">directors</span><span class="p" id="mwAg8">.</span><span class="n" id="mwAhA">id</span><span class="w" id="mwAhE"> </span><span class="o" id="mwAhI">=</span><span class="w" id="mwAhM"> </span><span class="o" id="mwAhQ">%</span><span class="n" id="mwAhU">s</span>