Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

Connect with SQLAlchemy

You can connect to your SingleStore Helios deployments from SQLAlchemy using the SingleStore SQLAlchemy dialect (singlestoredb://).

Remarks

Example

The following example connects to a SingleStore deployment using SQLAlchemy and returns all the rows in a table named strg.

This example uses the following connection parameters:

  • Dialect: singlestoredb://

  • SingleStore database username: s2user

  • Password of the SingleStore database user: passkey

  • Hostname of the SingleStore deployment: svchost

    Refer to SingleStore Helios Endpoints to determine the endpoint (host:port) of your cluster.

  • Port: 3306

  • Database name: dbTest

  1. Run the following command in the SingleStore command line:

    CREATE TABLE strg (ID INT, Name VARCHAR(20));
    INSERT INTO strg VALUES (1, "sam_duke");
    INSERT INTO strg VALUES (2, "adam_west");
    INSERT INTO strg VALUES (3, "anna_moore");
    SELECT * FROM strg;
    +----+------------+
    | ID | Name       |
    +----+------------+
    |  1 | sam_duke   |
    |  2 | adam_west  |
    |  3 | anna_moore |
    +----+------------+
  2. Create a .py file (say pyTest.py), and add the following code to the file:

    from sqlalchemy import create_engine, text
    engine = create_engine('singlestoredb://s2user:passkey@svchost/dbTest')
    conn = engine.connect()
    result = conn.execute(text("SELECT * FROM strg"))
    for row in result:
    print(row)
  3. Run the following command:

    python3 pyTest.py
    (3, 'anna_moore')
    (2, 'adam_west')
    (1, 'sam_duke')

The following examples perform CRUD operations using the same connection parameters.

Create Operation Example

Add the following code to a .py file (say ct.py):

from sqlalchemy import create_engine, text
engine = create_engine('singlestoredb://s2user:passkey@svchost:3306/dbTest')
conn = engine.connect()
conn.execute(text("CREATE TABLE stock (ID INT, Code VARCHAR(4), Qty INT)"))
conn.execute(text("INSERT INTO stock VALUES (1,'xvfg',23)"))
conn.execute(text("INSERT INTO stock VALUES (2,'dtsi',12)"))
conn.execute(text("INSERT INTO stock VALUES (3,'rtky',8)"))
conn.close()

Run the following command:

python3 ct.py

Read Operation Example

Add the following code to a .py file (say rt.py):

from sqlalchemy import create_engine, text
engine = create_engine('singlestoredb://s2user:passkey@svchost:3306/dbTest')
conn = engine.connect()
result = conn.execute(text("SELECT * FROM stock"))
for row in result:
print(row)
conn.close()

Run the following command:

python3 rt.py
(1, 'xvfg', 23)
(2, 'dtsi', 12)
(3, 'rtky', 8)

Update Operation Example

Add the following code to a .py file (say ut.py):

from sqlalchemy import create_engine, text
engine = create_engine('singlestoredb://s2user:passkey@svchost:3306/dbTest')
conn = engine.connect()
conn.execute(text("UPDATE stock SET Qty = 10 WHERE Code = 'rtky'"))
result = conn.execute(text("SELECT * FROM stock"))
for row in result:
print(row)
conn.close()

Run the following command:

python3 ut.py
(3, 'rtky', 10)
(1, 'xvfg', 23)
(2, 'dtsi', 12)

Delete Operation Example

Add the following code to a .py file (say dt.py):

from sqlalchemy import create_engine, text
engine = create_engine('singlestoredb://s2user:passkey@svchost:3306/dbTest')
conn = engine.connect()
conn.execute(text("DELETE FROM stock WHERE code = 'rtky'"))
result = conn.execute(text("SELECT * FROM stock"))
for row in result:
print(row)
conn.close()

Run the following command:

python3 dt.py
(1, 'xvfg', 23)
(2, 'dtsi', 12)

Refer to SQLAlchemy SQL Expression Language Tutorial for more examples.

Troubleshooting

Error: ModuleNotFoundError: No module named 'MySQLdb'

Solution: Install a supported Python library, for example the SingleStore Python Client.

References

Last modified:

Was this article helpful?