Connect with SQLAlchemy
You can connect to your SingleStoreDB Cloud workspaces using SQLAlchemy.
See SQLAlchemy Documentation for related information.
Remarks
Use the MySQL dialect (
mysql://
) in thecreate_engine()
statement.Use one of the supported Python libraries.
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.