Using SQLAlchemy with YugabyteDB

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

Prerequisites

  • 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:

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

      import sqlalchemy
      
      sqlalchemy.version
      
  • 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 main.py and config.py files to it.

  • Add the following code to the config.py file:

    db_user = 'yugabyte'
    db_password = 'yugabyte'
    database = 'yugabyte'
    db_host = 'localhost'
    db_port = 5433
    
  • Add the following code to the main.py 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
    Base.metadata.create_all(engine)
    
    # 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])
    session.commit()
    
  • Execute the code using the following command:

    python3 main.py
    

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:

    ./bin/ysqlsh
    
  • Obtain the list of all the tables in the database by executing the following command:

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

    SELECT * FROM TEST;
    

    The output should be as follows:

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

Limitations

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.