Skip to main content

Connect with SQLAlchemy

You can connect to your SingleStoreDB Cloud workspaces using SQLAlchemy.

See SQLAlchemy Documentation for related information.

Remarks

Example

The following example connects to a SingleStoreDB 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 SingleStoreDB 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("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("CREATE TABLE stock (ID INT, Code VARCHAR(4), Qty INT)")
conn.execute("INSERT INTO stock VALUES (1,'xvfg',23)")
conn.execute("INSERT INTO stock VALUES (2,'dtsi',12)")
conn.execute("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("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("UPDATE stock SET Qty = 10 WHERE Code = 'rtky'")
result = conn.execute("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("DELETE FROM stock WHERE code = 'rtky'")
result = conn.execute("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.

In this section: