• Martin Thoma
  • Home
  • Categories
  • Tags
  • Archives
  • Support me

SQL Connection Strings

Contents

  • SQLite
  • MySQL and MariaDB
  • Others

When you want to connect to a database in SQLAlchemy, you need a connection string. It usually has the form

dialect[+driver]://user:password@host/dbname[?key=value..]

Quite often, the user is root and the host is localhost.

Once you have the valid connection string, you can test if it works via this script:

import sqlalchemy

engine = sqlalchemy.create_engine(SQLALCHEMY_DATABASE_URI, echo=True)
print(engine.table_names())

SQLite

requirements.txt: None

Connection:

SQLALCHEMY_DATABASE_URI = "sqlite:///absolute_filepath"

# Example:
SQLALCHEMY_DATABASE_URI = "sqlite:////tmp/test.db"

The first two slashes come from the seperator of dialect and driver, the third one from the separation between credentials+host and dbname, the fourth one is the path which is kind of the name of the database.

If you want an in-memory SQLite DB, just specify an empty URL (source):

SQLALCHEMY_DATABASE_URI = "sqlite://"

MySQL and MariaDB

requirements.txt:

PyMySQL

Connection:

SQLALCHEMY_DATABASE_URI = "mysql+pymysql://user:password@host/dbname"

There are a lot of other MySQL drivers:

  • mysqldb: C extension; does not work with Python 3 (reasons for pymysql)
  • mysqlconnector: Officially provided by MySQL; worst performance and not downloadable via PyPI (source)

Others

I haven't tried it, but sqlalchemy lists more like Oracle, Microsoft SQL Server and Sybase.

Published

Jun 2, 2019
by Martin Thoma

Category

Code

Tags

  • Database 6
  • MySQL 4
  • SQLAlchemy 2

Contact

  • Martin Thoma - A blog about Code, the Web and Cyberculture
  • E-mail subscription
  • RSS-Feed
  • Privacy/Datenschutzerklärung
  • Impressum
  • Powered by Pelican. Theme: Elegant by Talha Mansoor