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?