Sign inTry Free

Connect with SQLAlchemy

On this page

You can connect to your SingleStore Helios workspaces using SQLAlchemy.

See SQLAlchemy Documentation for related information.

Remarks

Example

The following example connects to a SingleStore workspace using SQLAlchemy, and returns all the rows in a table, say strg.

This example uses the following connection parameters:

  • Dialect: mysql

  • User: admin

  • Password: passkey

  • Host: svc-3nb226d8-ee13-47f0-8ca4-2dc820773442-dml.aws-oregon-2.svc.singlestore.com

  • Port: 3306

  • Database: dbTest

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 |
+----+------------+

Create a .py file (say pyTest.py), and add the following code to the file:

import sqlalchemy
from sqlalchemy import create_engine


engine = create_engine('mysql://admin:passkey@svc-3nb226d8-ee13-47f0-8ca4-2dc820773442-dml.aws-oregon-2.svc.singlestore.com:3306/dbTest')
conn = engine.connect()
result = conn.execute(text("SELECT * FROM strg"))
for row in result:
        print(row)

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

import sqlalchemy
from sqlalchemy import create_engine




engine = create_engine('mysql://admin:passkey@svc-3nb226d8-ee13-47f0-8ca4-2dc820773442-dml.aws-oregon-2.svc.singlestore.com: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):

import sqlalchemy
from sqlalchemy import create_engine




engine = create_engine('mysql://admin:passkey@svc-3nb226d8-ee13-47f0-8ca4-2dc820773442-dml.aws-oregon-2.svc.singlestore.com: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):

import sqlalchemy
from sqlalchemy import create_engine




engine = create_engine('mysql://admin:passkey@svc-3nb226d8-ee13-47f0-8ca4-2dc820773442-dml.aws-oregon-2.svc.singlestore.com: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):

import sqlalchemy
from sqlalchemy import create_engine




engine = create_engine('mysql://admin:passkey@svc-3nb226d8-ee13-47f0-8ca4-2dc820773442-dml.aws-oregon-2.svc.singlestore.com: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)

See SQLAlchemy SQL Expression Language Tutorial for more examples.

Troubleshooting

Error: ModuleNotFoundError: No module named 'MySQLdb'

Solution: Install a supported Python library, for example mysqlclient.

Last modified: August 13, 2024

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK