Connect with SQLAlchemy

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Remarks

Use the SingleStore dialect (singlestoredb://) in the create_engine() statement.
Use one of the supported Python libraries. SingleStore recommends using the SingleStore Python Client.
Use 127.0.0.1 instead of localhost in the create_engine() statement.

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
Port: 3306
Database name: dbTest

Run the following command in the SingleStore command line:

SQL

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:

Python

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)

Run the following command:

Shell

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

Python

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:

Shell

python3 ct.py

Read Operation Example

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

Python

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:

Shell

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

Python

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:

Shell

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

Python

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:

Shell

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.

Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Solution: To resolve this issue:

Specify 127.0.0.1 as the host instead of localhost.
Modify the socket value in the /etc/mysql/my.cnf file to your SingleStore socket. For example,
```
[client]
port = 3306
socket = /var/lib/memsql/<Singlestore_node_id>/data/memsql.sock
```
Replace <Singlestore_node_id> with the MemSQL ID of the aggregator node. Run the sdb-admin list-nodes command to list the SingleStore nodes.

Connect with SQLAlchemy

On this page

Remarks

Example

Create Operation Example

Read Operation Example

Update Operation Example

Delete Operation Example

Troubleshooting

Error: ModuleNotFoundError: No module named 'MySQLdb'

Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

References

Was this article helpful?

On this page

Was this article helpful?