Sign inTry Free

Connect with SQLAlchemy

On this page

You can connect to your SingleStore clusters using SQLAlchemy.

See SQLAlchemy Documentation for related information.

Remarks

  • Use the MySQL dialect (mysql://) in the create_engine() statement.

  • Use one of the supported Python libraries.

  • Use 127.0.0.1 instead of localhost in the create_engine() statement.

Example

The following example connects to a SingleStore cluster using SQLAlchemy, and returns all the rows in a table, say strg.

This example uses the following connection parameters:

  • Dialect: mysql

  • User: root

  • Password: passkey

  • Host: 127.0.0.1

  • 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://root:passkey@127.0.0.1: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://root:passkey@127.0.0.1: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://root:passkey@127.0.0.1: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://root:passkey@127.0.0.1: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://root:passkey@127.0.0.1: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.

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/<SinglestoreDB_node_id>/data/memsql.sock

    Replace <SinglestoreDB_node_id> with the MemSQL ID of the aggregator node. Run the sdb-admin list-nodes command to list the SingleStore nodes.

Last modified: August 13, 2024

Was this article helpful?

On this page

Was this article helpful?