Connect with SQLAlchemy

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.

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

  Refer to SingleStore Helios Endpoints to determine the endpoint (host:port) of your deployment.

• Port: 3306

• Database name: dbTest

1. Run the following command in the SingleStore command line:

   SQL

   Copy

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

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

   Python

   Copy

   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)

3. Run the following command:

   Shell

   Copy

   python3 pyTest.py

   (3, 'anna_moore')
   (2, 'adam_west')
   (1, 'sam_duke')

The following examples perform CRUD operations using the same connection parameters.

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

python3 ct.py

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

python3 rt.py

(1, 'xvfg', 23)
(2, 'dtsi', 12)
(3, 'rtky', 8)

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

python3 ut.py

(3, 'rtky', 10)
(1, 'xvfg', 23)
(2, 'dtsi', 12)

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

python3 dt.py

(1, 'xvfg', 23)
(2, 'dtsi', 12)

Troubleshooting

References

• SingleStore SQLAlchemy Dialect GitHub repository

• SingleStore SQLAlchemy Dialect

• SQLAlchemy Documentation