DOCSEARCHbyAlgolia
Submit Docs

PyWRDS

repository·main·Indexed 41 days ago

https://github.com/wharton/wrds

A Python package to interface with Wharton Research Data Services (WRDS), allowing users to query datasets via PostgreSQL and extract them directly into Pandas DataFrames. It provides the wrds.Connection class for managing database connections, executing raw SQL queries with raw_sql(), and exploring metadata through methods like list_libraries(), list_tables(), and describe_table(). Supports Python 3.8 through 3.12.

Tokens
2.8K
Snippets
11
Records
13
Agent score
50%

What's inside PyWRDS

  1. Set up development environment

    main

    To contribute to PyWRDS, clone the repository and install the package in editable mode with development dependencies:

    pip install -e .[dev]

    Run the test suite using pytest.

    pip install -e .[dev]
pytest

  2. Install PyWRDS via virtual environment

    main

    PyWRDS supports Python 3.8 through 3.12. It is recommended to install the package within a virtual environment (venv) to manage dependencies. A WRDS account is required to use the package.

    Linux or MacOS

    $ python -m venv --copies --prompt pywrds pywrds
$ source pywrds/bin/activate
(pywrds) $ python -m pip install -U pip wheel wrds

    To reactivate the environment later:

    $ source pywrds/bin/activate

    Windows

    C:\Users\username> python -m venv --copies --prompt pywrds pywrds
C:\Users\username> pywrds\Scripts\activate
(pywrds) C:\Users\username> python -m pip install -U pip wheel wrds

    To reactivate the environment later:

    C:\Users\username> pywrds\Scripts\activate
    # Linux or MacOS
$ python -m venv --copies --prompt pywrds pywrds
$ source pywrds/bin/activate
(pywrds) $ python -m pip install -U pip wheel wrds

# Windows
C:\Users\username> python -m venv --copies --prompt pywrds pywrds
C:\Users\username> pywrds\Scripts\activate
(pywrds) C:\Users\username> python -m pip install -U pip wheel wrds

  3. Use wrds.Connection to query WRDS data

    main

    The primary way to interact with WRDS is through the wrds.Connection object. You can use it as a standard object or as a context manager to ensure the connection is closed automatically.

    Basic Workflow

    1. Initialize Connection: Call wrds.Connection(). You will be prompted for your WRDS username and password.
    2. List Libraries: Use list_libraries() to see available datasets.
    3. List Tables: Use list_tables(library="name") to see tables within a specific library.
    4. Describe Table: Use describe_table(library="name", table="name") to see schema information (rows, column names, types, etc.).
    5. Get Data: Use get_table(library="name", table="name", rows=N) to extract data directly into a Pandas DataFrame.
    6. Close Connection: Call .close() or use a with statement.

    Example Usage

    import wrds

# Using as a standard object
db = wrds.Connection()
db.list_libraries()
db.list_tables(library="crsp")
db.describe_table(library="crsp", table="stocknames")
stocknames = db.get_table(library="crsp", table="stocknames", rows=10)
db.close()

# Using as a context manager (recommended)
with wrds.Connection() as db:
    stocknames = db.get_table(library='crsp', table='stocknames', rows=10)
    print(stocknames.head())
    import wrds

# Using as a standard object
db = wrds.Connection()
db.list_libraries()
db.list_tables(library="crsp")
db.describe_table(library="crsp", table="stocknames")
stocknames = db.get_table(library="crsp", table="stocknames", rows=10)
db.close()

# Using as a context manager (recommended)
with wrds.Connection() as db:
    stocknames = db.get_table(library='crsp', table='stocknames', rows=10)
    print(stocknames.head())

  4. Manage WRDS credentials with `.pgpass`

    main

    To avoid entering your username and password every time, you can use a .pgpass file. The Connection class provides a method to automate this setup.

    Usage:

    1. Connect to WRDS using Connection().
    2. Call db.create_pgpass_file().
    3. Follow the interactive prompt to save your credentials.

    On Unix-like systems, the file is created at ~/.pgpass with 400 permissions. On Windows, it is created as pgpass.conf in the %APPDATA%\postgresql directory.

    db = wrds.Connection()
db.create_pgpass_file()

  5. wrds.Connection API reference

    main

    The wrds.Connection class provides methods to explore and extract data from the WRDS platform.

    MethodDescription
    list_libraries()Returns a list of available libraries (e.g., ['audit', 'bank', 'crsp', ...])
    list_tables(library="name")Returns a list of tables within the specified library.
    describe_table(library="name", table="name")Returns metadata about the table, including row count and column details (name, nullable, type).
    get_table(library="name", table="name", rows=N)Extracts data from the specified table into a Pandas DataFrame. Use rows to limit the number of rows returned.
    close()Closes the connection to the database.

  6. Retrieve an entire table with `get_table()`

    main

    The get_table() method is a convenience function to pull an entire table (or a subset of it) into a pandas.DataFrame without writing manual SQL.

    # Get the first 1000 rows of a specific table with specific columns
data = db.get_table('wrdssec_all', 'dforms', rows=1000, columns=['cik', 'fdate', 'coname'])

  7. Explore WRDS metadata using list_libraries and list_tables

    main

    Once a wrds.Connection is established, you can discover available datasets using the following methods:

    • list_libraries(): Returns a list of available WRDS libraries (e.g., ['aha', 'crsp', ...]).
    • list_tables(library='...'): Returns a list of tables available within the specified library.
    import wrds
db = wrds.Connection()

# Get all libraries
libraries = db.list_libraries()

# Get all tables in the 'crsp' library
tables = db.list_tables(library='crsp')

  8. Execute SQL queries with raw_sql()

    main

    Use the raw_sql method to execute SQL statements against the WRDS databases. The method returns the results as a Pandas DataFrame.

    Parameters:

    • sql: The SQL query string to execute.
    • index_col (optional): The name of the column to be used as the index in the resulting DataFrame.
    import wrds
db = wrds.Connection()

# Querying a table and setting 'permno' as the index
data = db.raw_sql('SELECT * FROM crsp.stocknames', index_col='permno')

  9. Explore WRDS schemas and tables

    main

    Once connected, you can inspect the available data structures using the following methods:

    • list_libraries(): Returns a list of all Postgres schemata (libraries) the user has permission to access.
    • list_tables(library): Returns a list of all views, tables, and foreign tables within a specific schema.
    • describe_table(library, table): Returns a pandas.DataFrame containing column names, types, nullability, and comments for a specific table.
    • get_row_count(library, table): Returns an approximate row count for the specified table.
    # List available libraries
libs = db.list_libraries()

# List tables in a specific library
tables = db.list_tables('wrdssec')

# Get column metadata for a table
metadata = db.describe_table('wrdssec_all', 'dforms')

  10. Query the database using raw SQL with `raw_sql()`

    main

    The raw_sql() method allows you to execute arbitrary SQL strings and returns the results as a pandas.DataFrame. It supports parameterization to prevent SQL injection and provides several utility options for data processing.

    Key Arguments:

    • sql: The SQL query string.
    • params: A dictionary of parameters for a parameterized query (e.g., {'id': 1}).
    • date_cols: A list of column names to parse as dates, or a dictionary specifying formats.
    • index_col: Column(s) to set as the index.
    • chunksize: If set, returns an iterator of DataFrames or processes in chunks to save memory.
    • coerce_float: (bool) Attempts to convert non-string/non-numeric objects to floats.
    # Basic usage
data = db.raw_sql('SELECT cik, fdate, coname FROM wrdssec_all.dforms;', date_cols=['fdate'], index_col='cik')

# Parameterized query
params = {'syms': ('A', 'AAPL'), 'min_size': 50000}
query = 'SELECT * FROM taqmsec.ctm_20030910 WHERE sym_root IN %(syms)s AND size > %(min_size)s'
data = db.raw_sql(query, params=params)

  11. Initialize a WRDS connection with wrds.Connection()

    main

    To access WRDS data, import the wrds module and instantiate a Connection object. This object serves as the primary interface for exploring libraries, tables, and executing SQL queries to retrieve data directly into Pandas DataFrames.

    Note: When calling raw_sql, you can specify an index_col to set the index of the resulting DataFrame.

    import wrds

# Establish connection
db = wrds.Connection()

# List available libraries
print(db.list_libraries())

# List tables within a specific library
print(db.list_tables(library='crsp'))

# Execute SQL and return a Pandas DataFrame
data = db.raw_sql('SELECT * FROM crsp.stocknames', index_col='permno')
print(data.head())

  12. Initialize a WRDS Connection

    main

    Use the Connection class to establish a connection to the WRDS database. By default, it attempts to connect immediately. You can provide connection parameters via keyword arguments to override defaults.

    Connection Parameters:

    • wrds_hostname: WRDS database hostname (default: wrds-pgdata.wharton.upenn.edu)
    • wrds_port: database connection port number (default: 9737)
    • wrds_dbname: WRDS database name (default: wrds)
    • wrds_username: WRDS username
    • wrds_password: WRDS password
    • autoconnect: If False, the connection is not established until connect() is called.
    • wrds_connect_args: Dictionary of additional connection arguments.

    The constructor also attempts to use a .pgpass file or PostgreSQL environment variables (like PGHOST) if credentials are not explicitly provided.

    import wrds

# Basic usage (will prompt for credentials if not in .pgpass)
db = wrds.Connection()

# Usage with explicit credentials
db = wrds.Connection(
    wrds_username='my_user',
    wrds_password='my_password'
)