Connecting

Connecting#

Connection strings are SQLAlchemy URL standard.

Some example connection strings:

mysql+pymysql://scott:tiger@localhost/foo
oracle://scott:tiger@127.0.0.1:1521/sidname
sqlite://
sqlite:///foo.db
mssql+pyodbc://username:password@host/database?driver=SQL+Server+Native+Client+11.0

Note that mysql and mysql+pymysql connections (and perhaps others) don’t read your client character set information from .my.cnf. You need to specify it in the connection string::

mysql+pymysql://scott:tiger@localhost/foo?charset=utf8

Note that an impala connection with impyla for HiveServer2 requires disabling autocommit::

%config SqlMagic.autocommit=False
%sql impala://hserverhost:port/default?kerberos_service_name=hive&auth_mechanism=GSSAPI

Connection arguments not whitelisted by SQLALchemy can be provided as a flag with (-a|–connection_arguments)the connection string as a JSON string. See SQLAlchemy Args

%sql --connection_arguments {"timeout":10,"mode":"ro"} sqlite:// SELECT * FROM work;
%sql -a '{"timeout":10, "mode":"ro"}' sqlite:// SELECT * from work;

Connecting to…#

Check out our guide for connecting to a database:

PostgreSQL

Connecting securely#

It is highly recommended that you do not pass plain credentials.

%load_ext sql

Building connection string#

One option is to use getpass, type your password, build your connection string and pass it to %sql:

from getpass import getpass

password = getpass()
connection_string = f"postgresql://user:{password}@localhost/database"
%sql $connection_string

Using `DATABASE_URL`#

You may also set the DATABASE_URL environment variable, and %sql will automatically load it from there. You can do it either by setting the environment variable from your terminal or in your notebook:

from getpass import getpass
from os import environ

password = getpass()
environ["DATABASE_URL"] = f"postgresql://user:{password}@localhost/database"

# without any args, %sql reads from DATABASE_URL
%sql

DSN connections#

Alternately, you can store connection info in a configuration file, under a section name chosen to refer to your database.

For example, if dsn.ini contains:

[DB_CONFIG_1] 
drivername=postgres 
host=my.remote.host 
port=5433 
database=mydatabase 
username=myuser 
password=1234

then you can:

%config SqlMagic.dsn_filename='./dsn.ini'
%sql --section DB_CONFIG_1 

Using an existing `sqlalchemy.engine.Engine`#

New in version 0.5.1.

Use an existing Engine by passing the variable name to %sql.

import pandas as pd
from sqlalchemy.engine import create_engine

engine = create_engine("sqlite://")

df = pd.DataFrame({"x": range(5)})
df.to_sql("numbers", engine)

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%sql engine

%%sql
SELECT * FROM numbers

*  sqlite://
Done.

index	x
0	0
1	1
2	2
3	3
4	4