This document describes how to use SQLAlchemy, a Python SQL tool and object-relational mapping (ORM) tool, with YugabetyDB.


  • YugabyteDB version 2.6 or later (see YugabyteDB Quick Start Guide).

  • Python version 2.7 or later.

  • The latest version of SQLAlchemy, which you can install using pip by executing the following command:

    pip3 install sqlalchemy

    Verify the installation as follows:

    • Open the Python prompt by executing the following command:

    • From the Python prompt, execute the following commands to check the SQLAlchemy version:

      import sqlalchemy
  • Psycopg2, the PostgreSQL database adapter for Python, which you can install using pip by executing the following command:

    pip3 install psycopg2

    Alternatively, you can install psycopg2-binary, a pre-compiled version of the module, by executing the following command:

    pip3 install psycopg2-binary

Use SQLAlchemy

Start using SQLAlchemy with YugabyteDB as follows:

  • Create a demo project and add a and files to it.

  • Add the following code to the file:

    db_user = 'yugabyte'
    db_password = 'yugabyte'
    database = 'yugabyte'
    db_host = 'localhost'
    db_port = 5433
  • Add the following code to the file:

    import config as cfg
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker, relationship
    from sqlalchemy import create_engine
    from sqlalchemy import MetaData
    from sqlalchemy import Table, Column, Integer, String, DateTime, ForeignKey
    Base = declarative_base()
    class Test(Base):
       __tablename__ = 'test'
       id = Column(Integer, primary_key=True)
       name = Column(String(255), unique=True, nullable=False)
    # create connection
    engine = create_engine('postgresql://{0}:{1}@{2}:{3}/{4}'.format(cfg.db_user, cfg.db_password, cfg.db_host, cfg.db_port, cfg.database))
    # create metadata
    # create session
    Session = sessionmaker(bind=engine)
    session = Session()
    # insert data
    tag_1 = Test(name='Bob')
    tag_2 = Test(name='John')
    tag_3 = Test(name='Ivy')
    session.add_all([tag_1, tag_2, tag_3])
  • Execute the code using the following command:


Test the code

Verify the code execution by looking for the changes inside the database as follows:

  • Navigate to your YugabyteDB installation directory by running the following command:

    cd /<path-to-yugabytedb>
  • Run the ysqlsh client by executing the following command:

  • Obtain the list of all the tables in the database by executing the following command:

  • Check if rows have been inserted into the table by executing the following:


    The output should be as follows:

    id | name
     1 | Bob
     2 | John
     3 | Ivy
    (3 rows)


Consider the following limitations:

  • Because of the distributed nature of YugabyteDB, rows returned by a query might not be in sequential or expected order. It is, therefore, recommended that you use the orderby() function to avoid the wrong data when executing functions such as first().
  • YugabyteDB does not support columns that contain a PRIMARY KEY of type user_defined_type.