• 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:[email protected]/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:[email protected]/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 4
  • MySQL 4
  • SQLAlchemy 1

Contact

  • Martin Thoma - A blog about Code, the Web and Cyberculture
  • Datenschutzerklärung
  • Impressum
  • Powered by Pelican. Theme: Elegant by Talha Mansoor