SQLAlchemy makes use of gadget of dialects to speak with diverse kinds of databases. Each database has a corresponding DBAPI wrapper. All dialects require that the right DBAPI motive force is set up.
Following dialects are covered in SQLAlchemy API −
- Firebird
- Microsoft SQL Server
- MySQL
- Oracle
- PostgreSQL
- SQL
- Sybase
An Engine item primarily based on a URL is produced with the aid of create_engine() characteristic. These URLs can encompass username, password, hostname, and database call. There can be non-obligatory key-word arguments for added configuration. In a few cases, a record direction is ordinary, and in others, a “facts supply name” replaces the “host” and “database” portions. The usual shape of a database URL is as follows −
dialect+driver://username:password@host:port/database
PostgreSQL
The PostgreSQL dialect makes use of psycopg2 because the default DBAPI. Pg8000 is also available as a pure-Python substitute as proven below:
# default
engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
# psycopg2
engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
# pg8000
engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
MySQL
The MySQL dialect makes use of mysql-python as the default DBAPI. There are many MySQL DBAPIs available, together with MySQL-connector-python as follows −
# default
engine = create_engine('mysql://scott:tiger@localhost/foo')
# mysql-python
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
# MySQL-connector-python
engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
Oracle
The Oracle dialect uses cx_oracle as the default DBAPI as follows −
engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')
Microsoft SQL Server
The SQL Server dialect makes use of pyodbc because the default DBAPI. Pymssql is also available.
# pyodbc
engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')
# pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
SQLite
SQLite connects to file-based totally databases, using the Python built-in module sqlite3 with the aid of default. As SQLite connects to local files, the URL format is barely one of a kind. The “record” portion of the URL is the filename of the database. For a relative record course, this requires 3 slashes as proven under −
engine = create_engine('sqlite:///foo.db')
And for an absolute file path, the three slashes are followed with the aid of absolutely the path as given under −
engine = create_engine('sqlite:///C:\\path\\to\\foo.db')
To use a SQLite:reminiscence:database, specify an empty URL as given below −
engine = create_engine('sqlite://')
Conclusion
In the primary part of this academic, we've got learnt how to use the Expression Language to execute SQL statements. Expression language embeds SQL constructs in Python code. In the second part, we've mentioned item relation mapping capability of SQLAlchemy. The ORM API maps the SQL tables with Python classes.