Reading Time: 7 minutes
sqlalchemy logo

The SQLAlchemy Toolkit and Object Relational Mapper is an extensive set of utilities for working with Python and databases. This toolkit provides a package full of popular persistence patterns, designed for economical and robust database accessibility. SQLAlchemy allows a developer to use simple SQL statements (unlike other Object Relational Mapping tools) which provide a helpful method to connect database tables with user-defined Python classes. The SQLAlchemy Object Relational Mapping tool is primarily centered on using the SQL Expression language.

SQLAlchemy Interactions

SQLAlchemy has several ways of dealing directly with the data within a database.

  • Raw SQL - Allows for custom, explicit, SQL statements that can be developed for a specific purpose or database
  • SQL Expression Language - A language that accurately reflects relational database structures and expressions using Python constructs
  • ORM - Object-relational mapping is a method of coding that translates data between different system types using an object-oriented programming language
sqlalchemy arch small

SQLAlchemy Components

Engine: Engine is the entryway for an SQLAlchemy application and functions as an abstraction layer of the API and databases. It interacts with the Dialect components and the connection pool to deliver SQL statements from SQLAlchemy over to the database.

Dialect: Dialect is the framework SQLAlchemy uses to interact with the various types of DBAPI database implementations. All dialects need a suitable DBAPI driver to be installed. SQLAlchemy maintains dialects for multiple database services including:

  • MySQL
  • Microsoft SQL Server
  • Oracle
  • PostgreSQL
  • SQLite
  • Firebird 
  • Informix
  • Sybase

MetaData: MetaData consists of multiple Python assets that depict the tables and other schema-level items. The metadata for the database can be declared by explicitly naming the different components as well as their properties, using constructs like: 

  • Table 
  • Column
  • ForeignKey 

MetaData can also be generated easily by SQLAlchemy by using a procedure termed reflection.

Pre-flight Check

  • These instructions are mainly being performed as the root user on a Liquid Web Self-Managed Ubuntu 18.04.3 LTS dedicated server.
  • These instructions assume you already have a working installation of Python 2 and/or Python 3 (versions 3.3 or >), with pip, python3-pip, venv, virtualenv and setuptools installed on your server. If you’re looking for instructions on how to get Python 3 installed on your CentOS 7 box, please check out this tutorial!

How To Install SQLAlchemy

Python Virtual Environment

First, let's look at installing the software within a Python virtual environment. It's best that we create a virtualenv (or venv depending on your version of Python) before we install SQLAlchemy. So, let's go ahead and do that.

[root@host ~]# virtualenv sqlalchemy
New python executable in sqlalchemy/bin/python
Installing distribute.....................done.
Installing pip................done
[root@host ~]# cd sqlalchemy
[root@host ~]# cd sqlalchemy/ 
[root@host ~]# source bin/activate
(sqlalchemy) root@host:/home/temp/sqlalchemy#

Next, the quickest method to install SQLAlchemy is to use the Python package manager pip.

[root@host ~]# pip3 install sqlalchemy
Downloading unpacking sqlalchemy
  Downloading SQLAlchemy-0.8.1.tar.gz (3.8Mb) : 3.8Mb downloaded
  Running setup.py egg_info for package sqlalchemy
  .......
    no previously included directories found matching 'doc/build/output'
Successfully installed sqlalchemy
Cleaning up....
[root@host ~]# 

This will limit the install to that virtual environment,

Next, we will demonstrate how to install SQLAlchemy and other necessary packages onto a CentOS and Ubuntu Linux server. 

CentOS

The easiest way to install SQLAlchemy systemwide is via the Python package manager pip.

[root@host ~]# which python3
/usr/bin/python3
[root@host ~]# python3 -m pip install --upgrade pip
(sqlalchemy) [root@host sqlalchemy]# pip3 install sqlalchemy
Collecting sqlalchemy
 Downloading https://files.pythonhosted.org/packages/17/7f/35879c73859368ad19a952b69ee780aa97fc30350dabd45fb948d6a4e3ea/SQLAlchemy-1.3.12.tar.gz (6.0MB)
  100% |████████████████████████████████| 6.0MB 290kB/s 
Installing collected packages: sqlalchemy
 Running setup.py install for sqlalchemy ... done
Successfully installed sqlalchemy-1.3.12
(sqlalchemy) [root@host sqlalchemy]#

Ubuntu

Again, the easiest way to install SQLAlchemy is via pip.

root@host:~# apt-get install python-pip
Reading package lists... Done
Building dependency tree   
Reading state information... Done
Do you want to continue? [Y/n] y
...
...
...
root@host:~# 

Next, we can install SQLAlchemy via pip.

root@host:~# pip3 install SQLAlchemy
Collecting SQLAlchemy
 Downloading https://files.pythonhosted.org/packages/17/7f/35879c73859368ad19a952b69ee780aa97fc30350dabd45fb948d6a4e3ea/SQLAlchemy-1.3.12.tar.gz (6.0MB)
  100% |████████████████████████████████| 6.0MB 102kB/s 
Building wheels for collected packages: SQLAlchemy
 Running setup.py bdist_wheel for SQLAlchemy ... done
 Stored in directory: /root/.cache/pip/wheels/ee/33/44/0788a6e806866ae2e246d5cd841d07498a46bcb3f3c42ea5a4
Successfully built SQLAlchemy
Installing collected packages: SQLAlchemy
Successfully installed SQLAlchemy-1.3.12
root@host:~#

Then, we can install the various DBAPI drivers for PostgreSQL and MySQL (python-psycopg2, python-mysqldb). SQLAlchemy requires these modules if working with PostgreSQL and MySQL. 

root@host:~# apt-get install python-psycopg2
Reading package lists... Done
Building dependency tree   
Reading state information... Done
The following additional packages will be installed:
 libpq5 python-egenix-mxdatetime python-egenix-mxtools
Suggested packages:
 python-egenix-mxdatetime-dbg python-egenix-mxdatetime-doc python-egenix-mxtools-dbg python-egenix-mxtools-doc
 python-psycopg2-doc
The following NEW packages will be installed:
 libpq5 python-egenix-mxdatetime python-egenix-mxtools python-psycopg2
0 upgraded, 4 newly installed, 0 to remove and 25 not upgraded.
Need to get 406 kB of archives.
After this operation, 1918 kB of additional disk space will be used.
Do you want to continue? [Y/n] Y
...
...
...
root@host:~# 
root@host:~# apt-get install python-mysqldb
Reading package lists... Done
Building dependency tree   
Reading state information... Done
The following additional packages will be installed:
 libmysqlclient20
Suggested packages:
 python-mysqldb-dbg
The following NEW packages will be installed:
 libmysqlclient20 python-mysqldb
0 upgraded, 2 newly installed, 0 to remove and 25 not upgraded.
Need to get 738 kB of archives.
After this operation, 4187 kB of additional disk space will be used.
Do you want to continue? [Y/n] Y
...
...
...
root@host:~#

Now, we will install MySQL-server and PostgreSQL.

root@host:~# apt-get install mysql-server
Reading package lists... Done
Building dependency tree   
Reading state information... Done
The following NEW packages will be installed:
 mysql-server
0 upgraded, 1 newly installed, 0 to remove and 25 not upgraded.
Need to get 0 B/9940 B of archives.
After this operation, 110 kB of additional disk space will be used.
Selecting previously unselected package mysql-server.
(Reading database ... 108778 files and directories currently installed.)
Preparing to unpack .../mysql-server_5.7.28-0ubuntu0.18.04.4_all.deb ...
Unpacking mysql-server (5.7.28-0ubuntu0.18.04.4) ...
Setting up mysql-server (5.7.28-0ubuntu0.18.04.4) ...
root@host:~#
root@host:~# apt-get install postgresql
Reading package lists... Done
Building dependency tree   
Reading state information... Done
The following additional packages will be installed:
 libsensors4 postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common sysstat
Suggested packages:
 lm-sensors postgresql-doc locales-all postgresql-doc-10 libjson-perl isag
The following NEW packages will be installed:
 libsensors4 postgresql postgresql-10 postgresql-client-10 postgresql-client-common postgresql-common sysstat
0 upgraded, 7 newly installed, 0 to remove and 25 not upgraded.
Need to get 5209 kB of archives.
After this operation, 20.6 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
...
...
...
Success. You can now start the database server using:

  /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner Data directory Log file
10 main 5432 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (10+190ubuntu0.1) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for ureadahead (0.100.0-21) ...
Processing triggers for libc-bin (2.27-3ubuntu1) ...
Processing triggers for systemd (237-3ubuntu10.33) ...
root@host:~#

Now, we will install the sqlite3 command-line interface.

root@host:~# apt-get install sqlite3
Reading package lists... Done
Building dependency tree   
Reading state information... Done
Suggested packages:
 sqlite3-doc
The following NEW packages will be installed:
 sqlite3
0 upgraded, 1 newly installed, 0 to remove and 25 not upgraded.
Need to get 754 kB of archives.
After this operation, 2481 kB of additional disk space will be used.
Get:1 http://us.archive.ubuntu.com/ubuntu bionic-updates/main amd64 sqlite3 amd64 3.22.0-1ubuntu0.2 [754 kB]
Fetched 754 kB in 0s (2132 kB/s)
Selecting previously unselected package sqlite3.
(Reading database ... 109582 files and directories currently installed.)
Preparing to unpack .../sqlite3_3.22.0-1ubuntu0.2_amd64.deb ...
Unpacking sqlite3 (3.22.0-1ubuntu0.2) ...
Setting up sqlite3 (3.22.0-1ubuntu0.2) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
root@host:~# 

Next, we run the “apt-get install python3-dev” command to install the package, which contains Python.h. This is a header file that will allow us to install the C extension speedup for SQLAlchemy.

root@host:~# apt-get install python3-dev
Reading package lists... Done
Building dependency tree   
Reading state information... Done
The following additional packages will be installed:
 libpython3-dev libpython3.6-dev python3.6-dev
The following NEW packages will be installed:
 libpython3-dev libpython3.6-dev python3-dev python3.6-dev
0 upgraded, 4 newly installed, 0 to remove and 25 not upgraded.
Need to get 45.3 MB of archives.
After this operation, 76.7 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
...
...
...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
root@host:~# 

Now, run the installer for the “python3-setuptools”.

root@host:~/sqlalchemy# apt-get install -y python3-setuptools
Reading package lists... Done
Building dependency tree   
Reading state information... Done
Suggested packages:
 python-setuptools-doc
The following NEW packages will be installed:
 python3-setuptools
0 upgraded, 1 newly installed, 0 to remove and 25 not upgraded.
Need to get 248 kB of archives.
After this operation, 1319 kB of additional disk space will be used.
Get:1 http://us.archive.ubuntu.com/ubuntu bionic/main amd64 python3-setuptools all 39.0.1-2 [248 kB]
Fetched 248 kB in 0s (802 kB/s)     
Selecting previously unselected package python3-setuptools.
(Reading database ... 109588 files and directories currently installed.)
Preparing to unpack .../python3-setuptools_39.0.1-2_all.deb ...
Unpacking python3-setuptools (39.0.1-2) ...
Setting up python3-setuptools (39.0.1-2) ...
root@host:~/sqlalchemy#

Next, let’s cd into the sqlalchemy directory and run setup.py

root@host:~/sqlalchemy# python3 setup.py install
running install
running bdist_egg
running egg_info
...
...
...
Installed /usr/local/lib/python3.6/dist-packages/SQLAlchemy-1.4.0b1.dev0-py3.6-linux-x86_64.egg
Processing dependencies for SQLAlchemy==1.4.0b1.dev0
Finished processing dependencies for SQLAlchemy==1.4.0b1.dev0
root@host:~/sqlalchemy# 

Verify the Version of SQLAlchemy

Finally, we can verify the installation.

[root@host ~]# python3
Python 3.6.8 (default, Aug 7 2019, 17:28:10) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.3.12'
>>> exit()
[root@host ~]#

Install using Git

The last method of installation we will cover is using git. 

root@host:~# git clone https://github.com/sqlalchemy/sqlalchemy.git
Cloning into 'sqlalchemy'...
remote: Enumerating objects: 98, done.
remote: Counting objects: 100% (98/98), done.
remote: Compressing objects: 100% (65/65), done.
remote: Total 154689 (delta 37), reused 81 (delta 20), pack-reused 154591
Receiving objects: 100% (154689/154689), 41.14 MiB | 32.56 MiB/s, done.
Resolving deltas: 100% (116631/116631), done.
root@host:~# cd sqlalchemy/
root@host:~/sqlalchemy# python setup.py install
running install
running bdist_egg
running egg_info
...
...
...
Installed /usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.4.0b1.dev0-py2.7-linux-x86_64.egg
Processing dependencies for SQLAlchemy==1.4.0b1.dev0
Finished processing dependencies for SQLAlchemy==1.4.0b1.dev0
root@host:~/sqlalchemy#
root@host:~/sqlalchemy# python
Python 2.7.17 (default, Nov  7 2019, 10:07:09) 
[GCC 7.4.0] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.4.0b1

To verify the install and check our version of SQLAlchemy we will run the following commands.

root@host:~/sqlalchemy# python
Python 2.7.17 (default, Nov  7 2019, 10:07:09) 
[GCC 7.4.0] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.4.0b1
root@host:~/sqlalchemy# 

And that’s it! SQLAlchemy is installed. 

Additional Tools

SQLAlchemy also includes a plethora of additional tools and accessories to expand your library and resources as seen here.

Order Now While There’s Still Time!

To procure a stable, durable and cutting edge platform that is favorable to SQLAlchemy, contact us right away to see the full line of dedicated servers we have available. As a bonus, you can purchase a secure server at a reduced rate!

Give us a call at 800.580.4985, or open a chat or ticket with us to speak with one of our knowledgeable Solutions or Experienced Hosting advisors to learn how you can take advantage of this technology today!

Avatar for David Singer

About the Author: David Singer

I am a g33k, Linux blogger, developer, student, and former Tech Writer for Liquidweb.com. My passion for all things tech drives my hunt for all the coolz. I often need a vacation after I get back from vacation....

Latest Articles

Blocking IP or whitelisting IP addresses with UFW

Read Article

CentOS Linux 7 end of life migrations

Read Article

Use ChatGPT to diagnose and resolve server issues

Read Article

What is SDDC VMware?

Read Article

Best authentication practices for email senders

Read Article