# Connect with SQLAlchemy

You can connect to your SingleStore deployments from SQLAlchemy using the [SingleStore SQLAlchemy dialect](https://github.com/singlestore-labs/sqlalchemy-singlestoredb) (`singlestoredb://`).

## Remarks

* Use the SingleStore dialect (`singlestoredb://`) in the `create_engine()` statement.
* Use one of the [supported Python libraries](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-python/supported-python-libraries.md). SingleStore recommends using the [SingleStore Python Client](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-python/connect-using-the-singlestore-python-client.md).
* 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`

1. 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;

   ```
   ```output

   +----+------------+
   | 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
   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
   python3 pyTest.py

   ```
   ```output

   (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

```

```output

(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

```

```output

(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

```

```output

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

```

Refer to [SQLAlchemy SQL Expression Language Tutorial](https://docs.sqlalchemy.org/en/14/core/tutorial.html) for more examples.

## Troubleshooting

## Error: ModuleNotFoundError: No module named 'MySQLdb'

**Solution**: Install a [supported Python library](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-python/supported-python-libraries.md), for example the [SingleStore Python Client](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-python/connect-using-the-singlestore-python-client.md).

## 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,
  ```text
  [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.

## References

* [SingleStore SQLAlchemy Dialect GitHub repository](https://github.com/singlestore-labs/sqlalchemy-singlestoredb)
* [SingleStore SQLAlchemy Dialect](https://sqlalchemy-singlestoredb.labs.singlestore.com/)
* [SQLAlchemy Documentation](https://docs.sqlalchemy.org/)

***

Modified at: September 26, 2025

Source: [/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-python/connect-with-sqlalchemy/](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-python/connect-with-sqlalchemy/)

(An index of the documentation is available at /llms.txt)