# Shopify Orders Rest API Wrapper and Data Export
pyShopify is a python wrapper for Shopify's REST API that pulls and inserts store data into a relational database and exports to a CSV file. This library is compatible with Microsoft SQL Server, MariaDB, and MySQL.
This has the flexibility of exporting the data to MS SQL Server or MySQL (and MariaDB), exporting to csv and yielding or returning a dictionary of dataframes.
The Structure of the Database is described in depth in the [Docs](docs) folder. The database can be built through the `shopify_db` CLI command or programmatically.
This requires the creation of a private app in the Shopify Admin center -> Setting -> Apps and Sales Channels -> Develop Apps.
## Table of Contents
- [Shopify Orders Rest API Wrapper and Data Export](#shopify-orders-rest-api-wrapper-and-data-export)
- [Table of Contents](#table-of-contents)
- [Data Returned](#data-returned)
- [Orders Data](#orders-data)
- [customers Data](#customers-data)
- [Product Data](#product-data)
- [Configuration](#configuration)
- [Default Configuration](#default-configuration)
- [Environment Variables](#environment-variables)
- [Example `config.ini` Configuration](#example-configini-configuration)
- [`config.ini` Sections](#configini-sections)
- [Microsoft SQL Server `config.ini` Section](#microsoft-sql-server-configini-section)
- [Configuration Dictionary](#configuration-dictionary)
- [Installation](#installation)
- [SQL Driver Requirements](#sql-driver-requirements)
- [Installing MySQLdb (MySQLClient) Driver](#installing-mysqldb-mysqlclient-driver)
- [Installing pyMySQL Driver](#installing-pymysql-driver)
- [Installing MS SQL ODBC Driver](#installing-ms-sql-odbc-driver)
- [Installing Python and Virtual Environment](#installing-python-and-virtual-environment)
- [Creating the Database and Tables](#creating-the-database-and-tables)
- [CLI Command](#cli-command)
- [Programatically via Python](#programatically-via-python)
- [Run pyShopify with in Python Script](#run-pyshopify-with-in-python-script)
- [Instantiating the `ShopifyApp` Class](#instantiating-the-shopifyapp-class)
- [Updating Configuration](#updating-configuration)
- [Product and Inventory Data](#product-and-inventory-data)
- [Methods that Return products and Inventory Data](#methods-that-return-products-and-inventory-data)
- [Get All Product, Variant and Product Option DataFrames](#get-all-product-variant-and-product-option-dataframes)
- [Get Inventory Levels and Locations Details](#get-inventory-levels-and-locations-details)
- [Methods that Write Inventory and Product Data](#methods-that-write-inventory-and-product-data)
- [Orders and customers Data](#orders-and-customers-data)
- [Returning/Yielding customers \& Orders Data](#returningyielding-customers--orders-data)
- [Writing Orders and Customers Data](#writing-orders-and-customers-data)
- [Writing Data to Desired Output](#writing-data-to-desired-output)
- [Writing All Data to DB or CSV](#writing-all-data-to-db-or-csv)
- [Running as Command Line Application](#running-as-command-line-application)
- [Data Structure](#data-structure)
- [Docker Container](#docker-container)
- [Docker Compose](#docker-compose)
- [MS SQL Docker Container](#ms-sql-docker-container)
- [MySQL Docker Container](#mysql-docker-container)
- [Creating a Container](#creating-a-container)
- [Running pyshopify in the container](#running-pyshopify-in-the-container)
## Data Returned
The following is a brief summary of the data being returned. The keys of the return dictionary match the table names of the database structure. The DataFrame columns match the columns of each table. For a detailed view of the structure of each DataFrame, see the database documentation.
### Orders Data
| Key/Table | Description |
|-----------------------------------------------------|-------------------------------------------------------------------------------------------|
| [orders](docs/Tables/orders.md) | Order ID's, date, pricing details, fulfilment status, financial status |
| [order_attr](docs/Tables/order_attr.md) | Attribution details of each order - landing page, source url, referral url, campaign |
| [order_prices](docs/Tables/order_prices.md) | Prices, discounts and charges for each order. |
| [line_items](docs/Tables/line_items.md) | Order line items with order ID, date, quantity, product/variant ID's, sku, title, pricing |
| [adjustments](docs/Tables/adjustments.md) | Order Refund adjustments |
| [discount_apps](docs/Tables/discount_apps.md) | Discount Applications for Each Order |
| [discount_codes](docs/Tables/discount_codes.md) | Discount Codes In Use for Each Order |
| [refund_line_item](docs/Tables/refund_line_item.md) | Refunded Units |
| [refunds](docs/Tables/refunds.md) | Order refunds |
| [ship_lines](docs/Tables/ship_lines.md) | Order ID, shipping pricing, carrier, code, source, title |
### customers Data
| Key/Table | Description |
|-----------------------------------------------------|--------------------------------------------------------------------|
| [customers](docs/Tables/customers.md) | Customer purchase totals, numbers of orders, last order, geography |
### Product Data
| Name | Description |
|----------------------------------------------------|-------------------------------------------------|
| [products](docs/Tables/products.md) | Product & Listing Data |
| [variants](docs/Tables/variants.md) | Product Variant details & Inventory |
| [product_options](docs/Tables/product_options.md) | Product & variant options configurations |
|[inventory_levels](docs/Tables/inventory_levels.md)|Inventory Levels for items at each inventory location|
|[inventory_locations](docs/Tables/inventory_locations.md)|Inventory locations |
## Configuration
The application can be configured three ways, starting with the lowest priority:
1. Default configuration - loaded automatically
2. Reading Environment Variables. When using the included docker container, these environment variables are automatically set from docker compose.
3. File named `config.ini` in current working directory or with relative or absolute file path passed in as an argument `app = pyshopify.ShopifyApp(config_dir='/home/user/app/config.ini')`.
4. Configuration dictionary with 'shopify', 'sql' and/or 'csv' keys as the ini file is structured.
### Default Configuration
**Note** Schema is based on the MS SQL Server construct, not MySQL. MySQL does not have schemas in the same sense as MSSQL, therefore it will have no impact if defined for a MySQL database.
This is the default configuration automatically passed to `ShopifyApp` instance.
```python
default_configuration = {
'shopify': {
'items_per_page': 250,
'days': 7,
'admin_ep': '/admin/api/',
'customers_ep': 'customers.json',
'orders_ep': 'orders.json',
'version': '2022-07',
'time_zone': 'America/New_York',
},
'sql': {
'connector': 'mssql+pyodbc',
'azure': False,
'encrypt': False,
'windows_auth': False,
'db': 'shop_rest'
},
'csv': {
'filepath': 'csv_export',
},
}
```
### Environment Variables
```bash
# Shopify Environment Variables
$ export SHOPIFY_ACCESS_TOKEN=<your access token>
$ export SHOPIFY_STORE_NAME=<your store name>
$ export SHOPIFY_API_VERSION=<Shopify api version> # ex. 2022-07
# SQL Server Environment Variables
$ export SHOPIFY_DB_USER=<your shopify db user>
$ export SHOPIFY_DB_PASSWORD=<your shopify db password>
$ export SHOPIFY_DB_NAME=<your shopify db name>
$ export SHOPIFY_DB_SERVER=<your db server>
$ export SHOPIFY_DB_SCHEMA=<your schema name>
```
### Example `config.ini` Configuration
The python script is configured through an ini based file using `configparser`.
#### `config.ini` Sections
This is the structure of the `config.ini` file. The sections are `shopify`, `sql` and `csv`. The `shopify` and `sql` sections are required, the and `csv` section is optional.
The date range to pull the data can be set to the last number of days using `days` or a specific date range using `start` and `end`. If both are set, `start_end` and `end_date` will take precedence.
```ini
[shopify]
# Optional Configuration
orders_ep = orders.json
customers_ep = customers.json
api_version = 2022-07
api_path = /admin/api/
items_per_page = 250
# Required Configuration
store_name = ***STORE-NAME-IN-ADMIN-URL***
# Shopify Access Token
access_token = ***ACCESS TOKEN FROM ADMIN***
# Uncomment to get data between two dates
# Overrides past days of history - Can be in 'YYYYMMDD' or 'MM-DD-YYYY' format
# start = 20210610
# end = 20210617
# Get past days of history - 7 days is default
days = 30
[csv]
# Relative directory for CSV exports
filepath = csv_export
[sql]
# Configuration for Database - See sections below
```
#### Microsoft SQL Server `config.ini` Section
The following is an example of `config.ini` section for Microsoft SQL Server. The connector is `mssql+pyodbc`. To use windows authentication, set `windows_auth` to True, it defaults to False. The `db` is the database of tables. If no schema is set, `dbo` will be used.
`port` is optional, if not set, the default port for the connector will be used.
If using Azure MySQL or SQL Server, set `azure = true` and `encrypt = true`. If using a self-signed certificate, set `ssl_cert` to the file path or url of the certificate. The `ssl_cert` value is ignored for MSSQL, the certificate must be installed in the system store. `TrustedServerCertificate` is required to be `True` with the `encrypt` option, unless you have a valid certificate matching your domain from a CA.
**Please note** If running this from a local computer, the firewall rule must be set on Azure to allow access to your current IP address.
```ini
[shopify]
...
...
[sql]
# Set true if using Azure server
azure = false
# DB dialect & driver - first part of connection string -eg mssql-pyodbc, mysql-pymysql
connector = mssql+pyodbc
# Set to True to enable ssl encryption (usually required for Azure)
encrypt = false
# Option valid only for MSSQL - Do not verify server certificate
TrustedServerCertificate = yes
# Set MSSQL Driver Name - Optional, driver is automatically detected
; driver = ODBC Driver 17 for SQL Server
# Connection string query for other connection parameters
# use comma separated list, i.e. param1:value1, param2:value2
# DO NOT include any of the configuration options set above
; connetion_query =
# Database server & port - localhost for docker container
server = ***SERVER_ADDRESS***
port = 1433
database = shop_rest
# Schema is optional, based on MSSQL construct (no impact for MySQL)
schema = dbo
# Database user & password
db_user = sa
db_pass = ***DATABASE_PW***
```
**MySQL Configuration**
Both `mysql+pymysql` and `mysql+mysqldb` are supported. This is the new version of mysqldb - [MySQLClient](https://github.com/PyMySQL/mysqlclient). Other drivers have not been tested.
If connecting to azure, set `azure = true` and `encrypt = true` if required to use SSL/TLS. The current `DigiCert` certificate will be automatically downloaded from Microsoft unless `ssl_cert` is set.
```ini
[shopify]
...
...
[csv]
...
[sql]
# Set true if using Azure server
azure = false
# DB dialect & driver - first part of connection string -eg mssql-pyodbc, mysql-pymysql
connector = mysql+pymysql
# Set to True to enable ssl encryption (usually required for Azure)
encrypt = false
# Database server & port - localhost for docker container
server = ***SERVER_ADDRESS***
# The default SSL certificate is automatically loaded for Azure MySQL
# if using another replace this value with file path or url
# Has no impact on MSSQL - CA cert must be in system store
; ssl_cert = https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem
port = 1433
database = shop_rest
# Database user & password
db_user = ***DB_USER***
db_pass = ***DATABASE_PW***
# Connection string query for other connection parameters
# use comma separated list, i.e. param1:value1, param2:value2
# DO NOT include any of the configuration options set above
; connetion_query =
```
### Configuration Dictionary
The configuration dictionary overrides the `config.ini` file. It follows the same structure of the `config.ini` file.
```python
from pyshopify.runner import ShopifyApp
configuration = {
"shopify": {
"access_token": "<your api key>",
"store_name": "<your store name>",
"api_version": "2022-07",
"days": 7,
},
"sql": {
"connector": "mssql+pyodbc",
"azure": False,
"encrypt": False,
"TrustedServerCertificate": True,
"db": "**DB_NAME**",
"server": "**SERVER_ADDRESS**",
"port": 1433,
"db_user": "**DB_USER**",
"db_pass": "**DB_PASS**",
"schema": "dbo",
},
"csv": {
"filepath": "csv_export",
}
}
app = ShopifyApp(config_dict=configuration)
```
___
## Installation
### SQL Driver Requirements
Before pyShopify can be installed, the appropriate requirements for the SQL driver must be installed. The following drivers are supported:
- [pyodbc](https://github.com/mkleehammer/pyodbc/wiki/Install) - SQL Server Driver, requires additional installation steps.
- [pymysql](https://github.com/PyMySQL/PyMySQL) - Pure python MySQL driver, the easiest to install, no prerequisites needed.
- [mysqldb](https://github.com/PyMySQL/mysqlclient) - New fork of C-based MySQLdb driver. Faster than pymysql but requires additional installation steps.
#### Installing MySQLdb (MySQLClient) Driver
The MySQLdb driver (MySQLClient) requires MySQL C libraries if building from source. On Windows, the [MariaDB C Connecter](https://mariadb.com/downloads/connectors/) may be required.
On Ubuntu, the following packages should be first installed:
```bash
apt install -y build-essential libssl-dev python3-dev libmysqlclient-dev
```
Then install the python package:
```bash
python3 -m pip install mysqlclient
```
The python package can also be installed with the pyshopify package: `pip install pyshopify[mysqldb]`
#### Installing pyMySQL Driver
It's pure python, so it's as simple as:
```bash
pip install pymysql
```
Note: you can also use the pip extras to install the python package `pip install pyshopify[pymysql]`
### Installing MS SQL ODBC Driver
On linux, msodbc18 needs to be installed first, then unixodbc headers. This is an example on Ubuntu (only compatible with 18.04, 20.04 & 21.04):
```shell script
$ sudo su
# Install curl and gnupg2 if needed
$ apt update && apt install curl gnupg2 lsb_release
# Add Microsoft's Repository
$ curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
$ curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list
# Install ODBC Driver and unixodbc-dev headers
$ apt update && apt install -y msodbcsql18 unixodbc-dev
```
The Windows ODBC driver can be found here [Microsoft SQL OBC Driver](https://docs.microsoft.com/en-us/sql/connect/odbc/windows/system-requirements-installation-and-driver-files?view=sql-server-ver15#installing-microsoft-odbc-driver-for-sql-server)
### Installing Python and Virtual Environment
On Ubuntu or WSL:
```shell script
# Install python & venv if needed
$ apt install python3.9 python3.9-dev python3-pip python3-venv git
$ exit # exit sudo
$ cd ~
$ python3.9 -m venv ~/venv
$ git clone https://github.com/webdjoe/pyshopify.git
$ source ~/venv/bin/activate && pip install -r ./pyshopify/requirements.txt
```
___
## Creating the Database and Tables
The database and table structure can be created one of two ways - CLI command or via Python script. All have the ability to create a new database or use an existing one. A new user can also be created with exclusive access to that database. It is recommended to create a new user instead of using the admin user when running the script. A new schema can also be created in MSSQL or the default `dbo` schema can be used.
### CLI Command
Use the `shopify_db` command to create the database, user and tables.
```bash
shopify_db --help
Usage: shopify_db [OPTIONS]
pyshopify database builder.
Build on existing database with --tables-only
New user can be created with --db-user and --db-pass
For MSSQL, --schema will be created if it does not exist
specifying date_start will build a date dimension table starting at date_start.
Use flag --azure and --encrypt to connect to Azure SQL/MySQL server
Options:
--db-tables / --tables-only Specify whether to build database or tables
only
-c, connector TEXT Database connector - mysql+mysqldb, mysql+pymysql, mssql+pyodbc
-s, --server TEXT SQL Server name or IP address
--azure Enable Azure SQL Server connection
--encrypt / --no-encrypt Enable SSL encryption [default: False]
-p, --port INTEGER SQL Server port
-U, --sa-user TEXT SQL Server SA user
-W, --sa-pass TEXT SQL Server SA password
-d, --db-name TEXT DB to create or existing db
--schema TEXT Schema to create or existing schema
--db-user TEXT DB user to create
--db-pass TEXT Created DB user password
--driver TEXT SQL Server ODBC driver
--date-start TEXT Create DateDim table from starting date YYYY-
MM-DD
--help Show this message and exit.
```
Build a new MSSQL database, user and schema with the following command:
```shell
shopify_db -c mssql+pyodbc -s localhost -p 1433 \
-U sa -W TheStrongestpassword1! -d test_shop \
--db-user test_shop_user --db-pass TestShopPass1! \
--schema NewSchema --date-start 2015-01-01
```
Build tables into existing MSSQL database, no new user or schema:
```shell
shopify_db --tables-only -c mssql+pyodbc -s localhost -p 1433 \
-U sa -W TheStrongestpassword1! -d test_shop \
--date-start 2015-01-01
```
Build a new MySQL database and user with the following command:
```shell
shopify_db -c mysql+pymysql -s localhost -p 3306 \
-U sa -W TheStrongestpassword1! -d test_shop \
--db-user test_shop_user --db-pass TestShopPass1! \
```
Create user and tables in existing Azure SQL Database
```shell
shopify_db --tables-only -c mssql+pyodbc -s azure.database.windows.net \
--azure --encrypt -p 3306 -U sa -W TheStrongestpassword1! \
-d test_shop --db-user test_shop_user --db-pass TestShopPass1! \
```
### Programatically via Python
The `DBFactory` class in the `pyshopify.sql` module can be used to create the database and tables, as well as user and/or schema.
```python
from pyshopify.sql import DBFactory
# port, schema, driver and connect_query are optional for MSSQL
# Schema and driver have no impact on MySQL
db_builder = DBFactory(connector='mssql+pyodbc',
server=localhost,
port=1433,
db_name='shop_rest',
sa_user='sa',
sa_pass='password',
schema='Shopify',
driver='ODBC Driver 17 for SQL Server', azure=False,
encrypt=False,
windows_auth=False)
# Create database
db_builder.create_db()
# Optional but recommended, create a new login and user with appropriate permissions
db_builder.create_user('shopify_user', 'password')
# Build tables and the new schema if specified
db_builder.create_tables()
# Optional, Build DateDimension table
db_builder.create_date_dimension('2015-01-01')
```
___
## Run pyShopify with in Python Script
The primary class is `pyshopify.runner.ShopifyApp()` which contains the necessary execution methods.
### Instantiating the `ShopifyApp` Class
Instantiate `ShopifyApp` from the `pyshopify.runner` module.
```python
from pyshopify.runner import ShopifyApp
# Instantiate ShopifyApp() with a configuration dictionary or absolute or relative location of config.ini file
shop_instance = ShopifyApp(config_dir='rel/dir/to/config.ini')
# Use configuration dictionary above to instantiate ShopifyApp()
shop_instance = ShopifyApp(config_dict=configuration)
```
### Updating Configuration
The configuration can be updated at any point with the `update_config()` method. This can update the dates or time period of the export. Any date periods specified will override the current configuration, regardless of precedence.
```python
# A full or partial configuration dictionary can be passed to update_config()
updated_config = {
'shopify': {
'start': '2020-01-01',
'end': '2020-01-31'
}
}
shop_instance.update_config(updated_config)
```
### Product and Inventory Data
Product and variant data can be pulled into a dictionary of DataFrames or written to SQL Server or a CSV file.
#### Methods that Return products and Inventory Data
These methods retrieve inventory locations, inventory levels and product details into a dictionary of DataFrames. The dictionary keys are the respective table names with the same columns.
**Get Inventory Levels at all locations**
The `inventory_table()` method returns a single dataframe with the product and variant id and title at all inventory locations. The method accepts lists or comma separated strings of product ids, variant ids and location_id. If no ids are specified, all products and variants will be returned.
```python
from pyshopify.runner import ShopifyApp()
app = ShopifyApp()
inventory_levels = app.inventory_table(product_ids=[],
variant_ids=[],
location_ids=[])
# Returns DataFrame of inventory items with product & variant id & title
# Returns:
# DataFrame(columns=[
# 'product_id', 'variant_id', 'sku', 'product_title', 'variant_title',
# 'available', 'location_id'])
```
**Get All Products and Inventory Detail DataFrames**
The `get_products_inventory()` method returns a dictionary of DataFrames with product, variant, product option, inventory locations & inventory levels. The dictionary keys are the respective table names with values containing a dataframe of the associated table structure.
```python
product_details = app.get_products_inventory()
product_details.items()
# Returns:
# [(products DataFrame)
# (variants, DataFrame),
# (product_options, DataFrame),
# (inventory_locations, DataFrame),
# (inventory_levels, DataFrame)]
```
##### Get All Product, Variant and Product Option DataFrames
The `get_products()` method returns a dictionary of DataFrames containing the product, variant and product option data. The dictionary keys are the respective table names with values containing a dataframe of the associated table structure.
The variants table contains the quantity of product that is available accross all locations.
```python
product_details = app.get_products()
product_details.items()
# Returns:
# [(products DataFrame)
# (variants, DataFrame),
# (product_options, DataFrame)]
```
##### Get Inventory Levels and Locations Details
The `get_inventory_levels()` method returns a dictionary of DataFrames containing the inventory levels and inventory locations data. The dictionary keys are the respective table names with values containing a dataframe of the associated table structure.
A list or comma separated string of location ids can be passed to filter the inventory levels to the specified locations.
```python
# Get inventory levels at specific or all locations
# Locations can be passed as a list or comma separated string of location ID's
# Get location ID's from the get_inventory_locations() method
inventory_levels = app.get_inventory_levels(locations=[])
inventory_levels.items()
# Returns:
# [(inventory_levels, DataFrame)]
```
The `get_inventory_locations()` method returns a dictionary containing a DataFrame of inventory locations.
```python
# Get Dictionary with DataFrame of locations inventory is being held
inv_locations = app.get_inventory_locations()
inv_locations
# Returns:
# {"inventory_locations": DataFrame(
# columns=['id', 'name', 'address1', 'address2',
# 'city', 'province', 'country', 'zip',
# 'phone', 'active', 'updated_at'])}
```
#### Methods that Write Inventory and Product Data
The following functions output the inventory and products data to a CSV file or SQL Server. The CSV file will be written to the directory specified in the configuration.
A configuration dictionary can be passed to each method to override the current configuration. The configuration dictionary can be a full or partial configuration dictionary.
```python
# Write All products and All Inventory Data to CSV or SQL Server
app.products_inventory_writer(write_sql=True, write_csv=Tru, config_dict=None)
# Write products, variants & options to CSV or SQL Server
app.products_writer(config_dict=None)
# Write inventory levels to CSV or SQL
app.inventory_levels_writer(write_csv=True, write_sql=True, config_dict=None)
# Write inventory locations to CSV or SQL
app.inventory_locations_writer(write_csv=True, write_sql=True, config_dict=None)
```
### Orders and customers Data
The following methods return the order details, pricing information and customers data. The methods return or yield a dictionary of DataFrames. The dictionary keys are the respective table names with the same columns. See the database documention for full column types and descriptions.
#### Returning/Yielding customers & Orders Data
The customers and orders data can be returned or yielded, in addition to written to the database or CSV file. A full or partial configuration dictionary can be passed to these methods to override the current configuration.
All of these methods can accept a configuration dictionary with just the shopify section.
**Shopify Configuration Dictionary**
The shopify configuration dictionary is the sql section of `config.ini`
```python
shop_config = {
'start': '2020-06-01',
'end': '2020-06-30'
}
```
**Get Full Customers and Orders DataFrames**
This method will return a full dataset of customers and orders DataFrames for the specified time period. The method returns a dictionary of DataFrames with the dictionary keys being the respective table names.
These methods are not recommended for large time periods due to memory constraints.
```python
from pyshopify.runner import ShopifyApp
app = ShopifyApp()
shopify_config = {
"access_token": "<your api key>",
"store_name": "<your store name>",
"api_version": "2022-07",
"days": 7
}
# Return full customers and orders dataset
orders = app.get_orders_customers_df(shopify_config=shopify_config)
orders.items()
# Returns:
# [('customers', <pandas.DataFrame>),
# ('Orders', <pandas.DataFrame>),
# ('refunds', <pandas.DataFrame>),
# ('line_items', <pandas.DataFrame>),
# ('refund_line_item', <pandas.DataFrame>),
# ('adjustments', <pandas.DataFrame>),
# ('discount_apps', <pandas.DataFrame>),
# ('discount_codes', <pandas.DataFrame>),
# ('ship_lines', <pandas.DataFrame>),
# ('order_prices', <pandas.DataFrame>),
# ('order_attr', <pandas.DataFrame>)]
# Return full customers dataset
customers = app.get_full_customers_df()
customers.items()
# Returns:
# [('customers', <pandas.DataFrame>)]
# Return full orders and customers dataset
all_data = app.get_full_orders_df(shopify_config=shopify_config)
all_data.items()
# Returns:
# [('Orders', <pandas.DataFrame>),
# ('refunds', <pandas.DataFrame>),
# ('line_items', <pandas.DataFrame>),
# ('refund_line_item', <pandas.DataFrame>),
# ('adjustments', <pandas.DataFrame>),
# ('discount_apps', <pandas.DataFrame>),
# ('discount_codes', <pandas.DataFrame>),
# ('ship_lines', <pandas.DataFrame>),
# ('order_prices', <pandas.DataFrame>),
# ('order_attr', <pandas.DataFrame>)]
```
**Iterating through Orders and customers Data**
For larger datasets, it is recommended to use the generator methods to iterate through the paginated data. The methods yield the same structure as the `get_full_orders_df()` and `get_full_customers_df()` methods.
A [shopify configuration dictionary](#returningyielding-customers--orders-data) can be passed to these methods as well.
```python
from pyshopify.runner import ShopifyApp
app = ShopifyApp()
# A shopify configuration dictionary can be passed to all the generator methods
for order_dict in app.orders_iterator(shopify_config=shopify_config):
order_dict.items()
# Returns:
# [('Orders', <pandas.DataFrame>),
# ('refunds', <pandas.DataFrame>),
# ('line_items', <pandas.DataFrame>),
# ('refund_line_item', <pandas.DataFrame>),
# ('adjustments', <pandas.DataFrame>),
# ('discount_apps', <pandas.DataFrame>),
# ('discount_codes', <pandas.DataFrame>),
# ('ship_lines', <pandas.DataFrame>),
# ('order_attr', <pandas.DataFrame>)]
# ('order_prices', <pandas.DataFrame>)]
# Iterate through customers data
for customers_dict in app.customers_iterator():
customers_dict.items()
# Returns:
# [('customers', <pandas.DataFrame>)]
```
#### Writing Orders and Customers Data
There are several convenience methods that can be used to write the orders and customers data to CSV and SQL Server. The sql configuration can be passed directly to these methods, as the `engine` is only created during the first SQL method and stored as an instance variable `app.engine`
#### Writing Data to Desired Output
```python
from pyshopify.runner import ShopifyApp
# No arguments defaults to attempt to read to a config.ini file in CWD
app = ShopifyApp()
# Main method that can write order & customer data to csv/sql and set configuration. A full configuration dictionary can be passed to this method.
app.orders_customers_writer(write_csv=True, write_sql=True, config=full_config)
# Write only orders data to CSV and/or SQL
app.orders_writer(write_sql=True, write_csv=True, config=full_config)
# Write only customers data to CsV and/or SQL
app.customers_writer(write_sql=True, write_csv=True, config=full_config)
```
### Writing All Data to DB or CSV
A convenience method can be used to write all data to the database or CSV. This accepts a full configuration dictionary.
```python
from pyshopify.runner import ShopifyApp
app = ShopifyApp()
# Write product, inventory, customers, and orders data to db
app.write_all_to_sql(config=full_config)
# Write product, inventory, customers, and orders data to csv
app.write_all_to_csv(config=full_config)
```
## Running as Command Line Application
The command line application `shopify_cli` can output the data to CSV or SQL Server. The command line application can be run with the following command:
From command line:
```bash
# There are different command options
$ shopify_cli --help
Usage: shopify_cli [OPTIONS]
Run Shopify App CLI.
Use -d or --days to get days of history, default 7 Use -b or --between to
get data between 2 dates - yyyy-MM-dd yyyy-MM-dd,
--config is the relative or absolute location of config.ini
Options:
--all Get all product, order and customer data
--orders Get orders data
--customers Get customers data
--products Get products data
-d, --days INTEGER get days of history, default 7
-b, --between TEXT... get between 2 dates - yyyy-MM-dd,ex -b 2020-01-01
2020-01-02
--sql-out / --no-sql write to database - Default False
--csv-out / --no-csv Write results to csv files - Default true
--csv-location TEXT Relative folder of csv export folderdefaults to
csv_export/
--config TEXT Relative location of config.ini - defaultsto
config.ini in currect directory
# Write last 30 days of all data to DB
$ shopify_cli --all -d 30 --sql-out --config config.ini
# Write 30 days of only orders data to CSV
$ shopify_cli --orders -d 30 --csv-out --config config.ini
# Get customers created between 2 dates
$ shopify_cli --customers -b 2020-01-01 2020-01-02 --sql-out
```
## Data Structure
The structure of the custom return dictionary reflects the SQL database structure that it will update. See the [database docs](docs/tables.md) for full details on each table/DataFrame.
```python
from pyshopify import ShopifyApp
# Enable custom in config.ini
shop_app = ShopifyApp()
orders_dict = shop_app.orders_full_df()
orders_dict = {
'Orders': DataFrame,
'refunds': DataFrame,
'line_items': DataFrame,
'refund_line_item': DataFrame,
'adjustments': DataFrame,
'discount_apps': DataFrame,
'discount_codes': DataFrame,
'ship_lines': DataFrame,
'order_attr': DataFrame,
'order_prices': DataFrame
}
customers_dict = shop_app.customers_full_df()
customers_dict = {
"customers": DataFrame
}
```
## Docker Container
There are two docker containers that can be used to run the application - an MS SQL based container and a MySQL based container. They are located in the respective docker folders, [docker-mysql](docker-mysql) and [docker-mssql](docker-mssql). Set the environment variables in the docker-compose.yml file before running.
Both containers have the same structure and can be used interchangeably. The only difference is the database engine used.
Set environment variables and SA password to configure the database in `docker-compose.yml`. Configure a volume for CSV export if needed
The `config.ini` should be mapped to the `/root` user directory inside the container. A csv folder can also be mapped to a local folder. This should match the relative csv filepath in the configuration file. The working directory is the root user's home directory.
### Docker Compose
The docker compose file configures the db administrator and the shopify database user. The date dimension table is built based on the `STARTING_DATE` environment variable.
The default location of the `config.ini` file is in the root user dir, the container's working directory. The data directory volume is the db data folder and the csv_export folder can also be mapped to a local directory.
#### MS SQL Docker Container
The Microsoft SQL Server container is based on the Microsoft image with Ubuntu 20.04 and the pyshopify library installed alongside python 3.9. The container is configured to run the `shopify_cli` command line application.
On the initial run, the database structure is automatically built based on the environment variables in docker compose. The `config.ini` file is automatically populated with the database details based on the environment variables.
```yaml
services:
sqlserver:
build:
context: ..
dockerfile: docker-mssql/Dockerfile
container_name: shop_sql
hostname: shop_sql
ports:
- "1433:1433"
environment:
ACCEPT_EULA: "Y"
SA_PASSWORD: "TheStrongestpassword1!"
MSSQL_SA_PASSWORD: "TheStrongestpassword1!"
SHOPIFY_DB_USER: "shop_user"
SHOPIFY_DB_PASSWORD: "Strongerpassword1!"
SHOPIFY_DB_NAME: "shop_rest"
SHOPIFY_DB_SCHEMA: "dbo"
STARTING_DATE: "2019-01-01"
CONFIG_FILE: "/root/config.ini"
volumes:
- "../csv_export:/root/csv_export"
- "../data:/var/opt/mssql"
- "../config.ini:/root/config.ini"
```
#### MySQL Docker Container
The MySQL container is based on the MySQL image with Debian-8 and the pyshopify library installed alongside python 3.9. The container is configured to run the `shopify_cli` command line application.
On the initial run, the database structure is automatically built based on the environment variables in docker compose. The `config.ini` file is automatically populated with the database details based on the environment variables.
```yml
services:
mysql-db:
build:
context: ..
dockerfile: docker-mysql/Dockerfile
container_name: shop_mysql
hostname: shop_mysql
ports:
- "3306:3306"
environment:
MYSQL_TCP_PORT: 3306
MYSQL_ROOT_PASSWORD: StrongestPassword1
DB_NAME: shop_sql
DB_USER: shop_user
DB_PASS: StrongerPassword1
STARTING_DATE: "2019-01-01"
CONFIG_FILE: "/root/config.ini"
volumes:
- "../csv_export:/root/csv_export"
- "../data:/var/lib/mysql"
- "../config.ini:/root/config.ini"
```
### Creating a Container
To create a container:
```shell script
git clone https://github.com/webdjoe/pyshopify
# Switch to the appropriate docker container folder
cd pyshopify/docker-mssql
cd pyshopify/docker-mysql
# edit the docker compose environment variables with vim or nano
vim docker-compose.yml
# Build and run container detached
sudo docker compose up --build -d
# edit config.ini file
vim config.ini
```
Once started test if SQL server is running. A `0` return value indicates the server has started up and is ready for connections.
```shell script
sudo docker exec -it shop_sql bash -c '/opt/mssql-tools/bin/sqlcmd -h -1 -t 1 -U sa \
-P "$MSSQL_SA_PASSWORD" -Q "SET NOCOUNT ON; Select SUM(state) from sys.databases"'
```
### Running pyshopify in the container
`shopify_cli` can be called in container to pull data and send to database or csv files. Check out the [`shopify_cli`](#running-as-command-line-application) section for more details.
```shell script
# Get last 30 days of data and import into SQL Server running in container
sudo docker exec -it shop_sql shopify_cli --all -d 30 --sql-out
# Get orders data between dates and import into SQL Server
sudo docker exec -it shop_sql shopify_cli --orders -b 2020-01-01 2020-12-31 --sql-out
# Pull 30 days of data into csv files
sudo docker exec -it shop_sql shopify_cli --csv-out --csv-location csv_export
```
Raw data
{
"_id": null,
"home_page": "https://github.com/pyshopify",
"name": "pyshopify",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.9",
"maintainer_email": "",
"keywords": "shopify,sql server,mssql,python,docker,python3,pyodbc",
"author": "Joseph Trabulsy",
"author_email": "webdjoe@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/d5/28/4d267d0ca96405c9c7c38bdd93b7496b73ed9ab8ed8f987c97d5c266028b/pyshopify-0.9.11.tar.gz",
"platform": null,
"description": "# Shopify Orders Rest API Wrapper and Data Export\r\n\r\npyShopify is a python wrapper for Shopify's REST API that pulls and inserts store data into a relational database and exports to a CSV file. This library is compatible with Microsoft SQL Server, MariaDB, and MySQL.\r\n\r\nThis has the flexibility of exporting the data to MS SQL Server or MySQL (and MariaDB), exporting to csv and yielding or returning a dictionary of dataframes.\r\n\r\nThe Structure of the Database is described in depth in the [Docs](docs) folder. The database can be built through the `shopify_db` CLI command or programmatically.\r\n\r\nThis requires the creation of a private app in the Shopify Admin center -> Setting -> Apps and Sales Channels -> Develop Apps.\r\n\r\n## Table of Contents\r\n\r\n- [Shopify Orders Rest API Wrapper and Data Export](#shopify-orders-rest-api-wrapper-and-data-export)\r\n - [Table of Contents](#table-of-contents)\r\n - [Data Returned](#data-returned)\r\n - [Orders Data](#orders-data)\r\n - [customers Data](#customers-data)\r\n - [Product Data](#product-data)\r\n - [Configuration](#configuration)\r\n - [Default Configuration](#default-configuration)\r\n - [Environment Variables](#environment-variables)\r\n - [Example `config.ini` Configuration](#example-configini-configuration)\r\n - [`config.ini` Sections](#configini-sections)\r\n - [Microsoft SQL Server `config.ini` Section](#microsoft-sql-server-configini-section)\r\n - [Configuration Dictionary](#configuration-dictionary)\r\n - [Installation](#installation)\r\n - [SQL Driver Requirements](#sql-driver-requirements)\r\n - [Installing MySQLdb (MySQLClient) Driver](#installing-mysqldb-mysqlclient-driver)\r\n - [Installing pyMySQL Driver](#installing-pymysql-driver)\r\n - [Installing MS SQL ODBC Driver](#installing-ms-sql-odbc-driver)\r\n - [Installing Python and Virtual Environment](#installing-python-and-virtual-environment)\r\n - [Creating the Database and Tables](#creating-the-database-and-tables)\r\n - [CLI Command](#cli-command)\r\n - [Programatically via Python](#programatically-via-python)\r\n - [Run pyShopify with in Python Script](#run-pyshopify-with-in-python-script)\r\n - [Instantiating the `ShopifyApp` Class](#instantiating-the-shopifyapp-class)\r\n - [Updating Configuration](#updating-configuration)\r\n - [Product and Inventory Data](#product-and-inventory-data)\r\n - [Methods that Return products and Inventory Data](#methods-that-return-products-and-inventory-data)\r\n - [Get All Product, Variant and Product Option DataFrames](#get-all-product-variant-and-product-option-dataframes)\r\n - [Get Inventory Levels and Locations Details](#get-inventory-levels-and-locations-details)\r\n - [Methods that Write Inventory and Product Data](#methods-that-write-inventory-and-product-data)\r\n - [Orders and customers Data](#orders-and-customers-data)\r\n - [Returning/Yielding customers \\& Orders Data](#returningyielding-customers--orders-data)\r\n - [Writing Orders and Customers Data](#writing-orders-and-customers-data)\r\n - [Writing Data to Desired Output](#writing-data-to-desired-output)\r\n - [Writing All Data to DB or CSV](#writing-all-data-to-db-or-csv)\r\n - [Running as Command Line Application](#running-as-command-line-application)\r\n - [Data Structure](#data-structure)\r\n - [Docker Container](#docker-container)\r\n - [Docker Compose](#docker-compose)\r\n - [MS SQL Docker Container](#ms-sql-docker-container)\r\n - [MySQL Docker Container](#mysql-docker-container)\r\n - [Creating a Container](#creating-a-container)\r\n - [Running pyshopify in the container](#running-pyshopify-in-the-container)\r\n\r\n## Data Returned\r\n\r\nThe following is a brief summary of the data being returned. The keys of the return dictionary match the table names of the database structure. The DataFrame columns match the columns of each table. For a detailed view of the structure of each DataFrame, see the database documentation.\r\n\r\n### Orders Data\r\n\r\n| Key/Table | Description |\r\n|-----------------------------------------------------|-------------------------------------------------------------------------------------------|\r\n| [orders](docs/Tables/orders.md) | Order ID's, date, pricing details, fulfilment status, financial status |\r\n| [order_attr](docs/Tables/order_attr.md) | Attribution details of each order - landing page, source url, referral url, campaign |\r\n| [order_prices](docs/Tables/order_prices.md) | Prices, discounts and charges for each order. |\r\n| [line_items](docs/Tables/line_items.md) | Order line items with order ID, date, quantity, product/variant ID's, sku, title, pricing |\r\n| [adjustments](docs/Tables/adjustments.md) | Order Refund adjustments |\r\n| [discount_apps](docs/Tables/discount_apps.md) | Discount Applications for Each Order |\r\n| [discount_codes](docs/Tables/discount_codes.md) | Discount Codes In Use for Each Order |\r\n| [refund_line_item](docs/Tables/refund_line_item.md) | Refunded Units |\r\n| [refunds](docs/Tables/refunds.md) | Order refunds |\r\n| [ship_lines](docs/Tables/ship_lines.md) | Order ID, shipping pricing, carrier, code, source, title |\r\n\r\n### customers Data\r\n\r\n| Key/Table | Description |\r\n|-----------------------------------------------------|--------------------------------------------------------------------|\r\n| [customers](docs/Tables/customers.md) | Customer purchase totals, numbers of orders, last order, geography |\r\n\r\n### Product Data\r\n\r\n| Name | Description |\r\n|----------------------------------------------------|-------------------------------------------------|\r\n| [products](docs/Tables/products.md) | Product & Listing Data |\r\n| [variants](docs/Tables/variants.md) | Product Variant details & Inventory |\r\n| [product_options](docs/Tables/product_options.md) | Product & variant options configurations |\r\n|[inventory_levels](docs/Tables/inventory_levels.md)|Inventory Levels for items at each inventory location|\r\n|[inventory_locations](docs/Tables/inventory_locations.md)|Inventory locations |\r\n\r\n## Configuration\r\n\r\nThe application can be configured three ways, starting with the lowest priority:\r\n\r\n1. Default configuration - loaded automatically\r\n2. Reading Environment Variables. When using the included docker container, these environment variables are automatically set from docker compose.\r\n3. File named `config.ini` in current working directory or with relative or absolute file path passed in as an argument `app = pyshopify.ShopifyApp(config_dir='/home/user/app/config.ini')`.\r\n4. Configuration dictionary with 'shopify', 'sql' and/or 'csv' keys as the ini file is structured.\r\n\r\n### Default Configuration\r\n\r\n**Note** Schema is based on the MS SQL Server construct, not MySQL. MySQL does not have schemas in the same sense as MSSQL, therefore it will have no impact if defined for a MySQL database.\r\n\r\nThis is the default configuration automatically passed to `ShopifyApp` instance.\r\n\r\n```python\r\ndefault_configuration = {\r\n 'shopify': {\r\n 'items_per_page': 250,\r\n 'days': 7,\r\n 'admin_ep': '/admin/api/',\r\n 'customers_ep': 'customers.json',\r\n 'orders_ep': 'orders.json',\r\n 'version': '2022-07',\r\n 'time_zone': 'America/New_York',\r\n },\r\n 'sql': {\r\n 'connector': 'mssql+pyodbc',\r\n 'azure': False,\r\n 'encrypt': False,\r\n 'windows_auth': False,\r\n 'db': 'shop_rest'\r\n },\r\n 'csv': {\r\n 'filepath': 'csv_export',\r\n },\r\n}\r\n```\r\n\r\n### Environment Variables\r\n\r\n```bash\r\n# Shopify Environment Variables\r\n$ export SHOPIFY_ACCESS_TOKEN=<your access token>\r\n$ export SHOPIFY_STORE_NAME=<your store name>\r\n$ export SHOPIFY_API_VERSION=<Shopify api version> # ex. 2022-07\r\n# SQL Server Environment Variables\r\n$ export SHOPIFY_DB_USER=<your shopify db user>\r\n$ export SHOPIFY_DB_PASSWORD=<your shopify db password>\r\n$ export SHOPIFY_DB_NAME=<your shopify db name>\r\n$ export SHOPIFY_DB_SERVER=<your db server>\r\n$ export SHOPIFY_DB_SCHEMA=<your schema name>\r\n```\r\n\r\n### Example `config.ini` Configuration\r\n\r\nThe python script is configured through an ini based file using `configparser`.\r\n\r\n#### `config.ini` Sections\r\n\r\nThis is the structure of the `config.ini` file. The sections are `shopify`, `sql` and `csv`. The `shopify` and `sql` sections are required, the and `csv` section is optional.\r\n\r\nThe date range to pull the data can be set to the last number of days using `days` or a specific date range using `start` and `end`. If both are set, `start_end` and `end_date` will take precedence.\r\n\r\n```ini\r\n[shopify]\r\n# Optional Configuration\r\norders_ep = orders.json\r\ncustomers_ep = customers.json\r\napi_version = 2022-07\r\napi_path = /admin/api/\r\nitems_per_page = 250\r\n\r\n# Required Configuration\r\nstore_name = ***STORE-NAME-IN-ADMIN-URL***\r\n# Shopify Access Token\r\naccess_token = ***ACCESS TOKEN FROM ADMIN***\r\n\r\n# Uncomment to get data between two dates\r\n# Overrides past days of history - Can be in 'YYYYMMDD' or 'MM-DD-YYYY' format\r\n# start = 20210610\r\n# end = 20210617\r\n\r\n# Get past days of history - 7 days is default\r\ndays = 30\r\n\r\n[csv]\r\n# Relative directory for CSV exports\r\nfilepath = csv_export\r\n\r\n[sql]\r\n# Configuration for Database - See sections below\r\n```\r\n\r\n#### Microsoft SQL Server `config.ini` Section\r\n\r\nThe following is an example of `config.ini` section for Microsoft SQL Server. The connector is `mssql+pyodbc`. To use windows authentication, set `windows_auth` to True, it defaults to False. The `db` is the database of tables. If no schema is set, `dbo` will be used.\r\n\r\n`port` is optional, if not set, the default port for the connector will be used.\r\n\r\nIf using Azure MySQL or SQL Server, set `azure = true` and `encrypt = true`. If using a self-signed certificate, set `ssl_cert` to the file path or url of the certificate. The `ssl_cert` value is ignored for MSSQL, the certificate must be installed in the system store. `TrustedServerCertificate` is required to be `True` with the `encrypt` option, unless you have a valid certificate matching your domain from a CA.\r\n\r\n**Please note** If running this from a local computer, the firewall rule must be set on Azure to allow access to your current IP address.\r\n\r\n```ini\r\n[shopify]\r\n...\r\n...\r\n\r\n[sql]\r\n# Set true if using Azure server\r\nazure = false\r\n\r\n# DB dialect & driver - first part of connection string -eg mssql-pyodbc, mysql-pymysql\r\nconnector = mssql+pyodbc\r\n\r\n# Set to True to enable ssl encryption (usually required for Azure)\r\nencrypt = false\r\n\r\n# Option valid only for MSSQL - Do not verify server certificate\r\nTrustedServerCertificate = yes\r\n\r\n# Set MSSQL Driver Name - Optional, driver is automatically detected\r\n; driver = ODBC Driver 17 for SQL Server\r\n\r\n# Connection string query for other connection parameters\r\n# use comma separated list, i.e. param1:value1, param2:value2\r\n# DO NOT include any of the configuration options set above\r\n; connetion_query =\r\n\r\n# Database server & port - localhost for docker container\r\nserver = ***SERVER_ADDRESS***\r\nport = 1433\r\ndatabase = shop_rest\r\n# Schema is optional, based on MSSQL construct (no impact for MySQL)\r\nschema = dbo\r\n# Database user & password\r\ndb_user = sa\r\ndb_pass = ***DATABASE_PW***\r\n```\r\n\r\n**MySQL Configuration**\r\nBoth `mysql+pymysql` and `mysql+mysqldb` are supported. This is the new version of mysqldb - [MySQLClient](https://github.com/PyMySQL/mysqlclient). Other drivers have not been tested.\r\n\r\nIf connecting to azure, set `azure = true` and `encrypt = true` if required to use SSL/TLS. The current `DigiCert` certificate will be automatically downloaded from Microsoft unless `ssl_cert` is set.\r\n\r\n```ini\r\n\r\n[shopify]\r\n...\r\n...\r\n\r\n[csv]\r\n...\r\n\r\n[sql]\r\n# Set true if using Azure server\r\nazure = false\r\n\r\n# DB dialect & driver - first part of connection string -eg mssql-pyodbc, mysql-pymysql\r\nconnector = mysql+pymysql\r\n\r\n# Set to True to enable ssl encryption (usually required for Azure)\r\nencrypt = false\r\n\r\n# Database server & port - localhost for docker container\r\nserver = ***SERVER_ADDRESS***\r\n# The default SSL certificate is automatically loaded for Azure MySQL\r\n# if using another replace this value with file path or url\r\n# Has no impact on MSSQL - CA cert must be in system store\r\n; ssl_cert = https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem\r\nport = 1433\r\ndatabase = shop_rest\r\n# Database user & password\r\ndb_user = ***DB_USER***\r\ndb_pass = ***DATABASE_PW***\r\n\r\n# Connection string query for other connection parameters\r\n# use comma separated list, i.e. param1:value1, param2:value2\r\n# DO NOT include any of the configuration options set above\r\n; connetion_query =\r\n\r\n```\r\n\r\n### Configuration Dictionary\r\n\r\nThe configuration dictionary overrides the `config.ini` file. It follows the same structure of the `config.ini` file.\r\n\r\n```python\r\nfrom pyshopify.runner import ShopifyApp\r\nconfiguration = {\r\n \"shopify\": {\r\n \"access_token\": \"<your api key>\",\r\n \"store_name\": \"<your store name>\",\r\n \"api_version\": \"2022-07\",\r\n \"days\": 7,\r\n },\r\n \"sql\": {\r\n \"connector\": \"mssql+pyodbc\",\r\n \"azure\": False,\r\n \"encrypt\": False,\r\n \"TrustedServerCertificate\": True,\r\n \"db\": \"**DB_NAME**\",\r\n \"server\": \"**SERVER_ADDRESS**\",\r\n \"port\": 1433,\r\n \"db_user\": \"**DB_USER**\",\r\n \"db_pass\": \"**DB_PASS**\",\r\n \"schema\": \"dbo\",\r\n },\r\n \"csv\": {\r\n \"filepath\": \"csv_export\",\r\n }\r\n}\r\n\r\napp = ShopifyApp(config_dict=configuration)\r\n\r\n```\r\n\r\n___\r\n\r\n## Installation\r\n\r\n### SQL Driver Requirements\r\n\r\nBefore pyShopify can be installed, the appropriate requirements for the SQL driver must be installed. The following drivers are supported:\r\n\r\n- [pyodbc](https://github.com/mkleehammer/pyodbc/wiki/Install) - SQL Server Driver, requires additional installation steps.\r\n- [pymysql](https://github.com/PyMySQL/PyMySQL) - Pure python MySQL driver, the easiest to install, no prerequisites needed.\r\n- [mysqldb](https://github.com/PyMySQL/mysqlclient) - New fork of C-based MySQLdb driver. Faster than pymysql but requires additional installation steps.\r\n\r\n#### Installing MySQLdb (MySQLClient) Driver\r\n\r\nThe MySQLdb driver (MySQLClient) requires MySQL C libraries if building from source. On Windows, the [MariaDB C Connecter](https://mariadb.com/downloads/connectors/) may be required.\r\n\r\nOn Ubuntu, the following packages should be first installed:\r\n\r\n```bash\r\napt install -y build-essential libssl-dev python3-dev libmysqlclient-dev\r\n```\r\n\r\nThen install the python package:\r\n\r\n```bash\r\npython3 -m pip install mysqlclient\r\n```\r\n\r\nThe python package can also be installed with the pyshopify package: `pip install pyshopify[mysqldb]`\r\n\r\n#### Installing pyMySQL Driver\r\n\r\nIt's pure python, so it's as simple as:\r\n\r\n```bash\r\npip install pymysql\r\n```\r\n\r\nNote: you can also use the pip extras to install the python package `pip install pyshopify[pymysql]`\r\n\r\n### Installing MS SQL ODBC Driver\r\n\r\nOn linux, msodbc18 needs to be installed first, then unixodbc headers. This is an example on Ubuntu (only compatible with 18.04, 20.04 & 21.04):\r\n\r\n```shell script\r\n$ sudo su\r\n# Install curl and gnupg2 if needed\r\n$ apt update && apt install curl gnupg2 lsb_release\r\n# Add Microsoft's Repository\r\n$ curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -\r\n$ curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list\r\n# Install ODBC Driver and unixodbc-dev headers\r\n$ apt update && apt install -y msodbcsql18 unixodbc-dev\r\n```\r\n\r\nThe Windows ODBC driver can be found here [Microsoft SQL OBC Driver](https://docs.microsoft.com/en-us/sql/connect/odbc/windows/system-requirements-installation-and-driver-files?view=sql-server-ver15#installing-microsoft-odbc-driver-for-sql-server)\r\n\r\n### Installing Python and Virtual Environment\r\n\r\nOn Ubuntu or WSL:\r\n\r\n```shell script\r\n# Install python & venv if needed\r\n$ apt install python3.9 python3.9-dev python3-pip python3-venv git\r\n$ exit # exit sudo\r\n$ cd ~\r\n$ python3.9 -m venv ~/venv\r\n$ git clone https://github.com/webdjoe/pyshopify.git\r\n$ source ~/venv/bin/activate && pip install -r ./pyshopify/requirements.txt\r\n```\r\n\r\n___\r\n\r\n## Creating the Database and Tables\r\n\r\nThe database and table structure can be created one of two ways - CLI command or via Python script. All have the ability to create a new database or use an existing one. A new user can also be created with exclusive access to that database. It is recommended to create a new user instead of using the admin user when running the script. A new schema can also be created in MSSQL or the default `dbo` schema can be used.\r\n\r\n### CLI Command\r\n\r\nUse the `shopify_db` command to create the database, user and tables.\r\n\r\n```bash\r\nshopify_db --help\r\n\r\nUsage: shopify_db [OPTIONS]\r\n\r\n pyshopify database builder.\r\n\r\n Build on existing database with --tables-only\r\n New user can be created with --db-user and --db-pass\r\n For MSSQL, --schema will be created if it does not exist\r\n\r\n specifying date_start will build a date dimension table starting at date_start.\r\n\r\n Use flag --azure and --encrypt to connect to Azure SQL/MySQL server\r\n\r\nOptions:\r\n --db-tables / --tables-only Specify whether to build database or tables\r\n only\r\n -c, connector TEXT Database connector - mysql+mysqldb, mysql+pymysql, mssql+pyodbc\r\n -s, --server TEXT SQL Server name or IP address\r\n --azure Enable Azure SQL Server connection\r\n --encrypt / --no-encrypt Enable SSL encryption [default: False]\r\n -p, --port INTEGER SQL Server port\r\n -U, --sa-user TEXT SQL Server SA user\r\n -W, --sa-pass TEXT SQL Server SA password\r\n -d, --db-name TEXT DB to create or existing db\r\n --schema TEXT Schema to create or existing schema\r\n --db-user TEXT DB user to create\r\n --db-pass TEXT Created DB user password\r\n --driver TEXT SQL Server ODBC driver\r\n --date-start TEXT Create DateDim table from starting date YYYY-\r\n MM-DD\r\n --help Show this message and exit.\r\n\r\n```\r\n\r\nBuild a new MSSQL database, user and schema with the following command:\r\n\r\n```shell\r\nshopify_db -c mssql+pyodbc -s localhost -p 1433 \\\r\n -U sa -W TheStrongestpassword1! -d test_shop \\\r\n --db-user test_shop_user --db-pass TestShopPass1! \\\r\n --schema NewSchema --date-start 2015-01-01\r\n```\r\n\r\nBuild tables into existing MSSQL database, no new user or schema:\r\n\r\n```shell\r\nshopify_db --tables-only -c mssql+pyodbc -s localhost -p 1433 \\\r\n -U sa -W TheStrongestpassword1! -d test_shop \\\r\n --date-start 2015-01-01\r\n```\r\n\r\nBuild a new MySQL database and user with the following command:\r\n\r\n```shell\r\nshopify_db -c mysql+pymysql -s localhost -p 3306 \\\r\n -U sa -W TheStrongestpassword1! -d test_shop \\\r\n --db-user test_shop_user --db-pass TestShopPass1! \\\r\n```\r\n\r\nCreate user and tables in existing Azure SQL Database\r\n\r\n```shell\r\nshopify_db --tables-only -c mssql+pyodbc -s azure.database.windows.net \\\r\n --azure --encrypt -p 3306 -U sa -W TheStrongestpassword1! \\\r\n -d test_shop --db-user test_shop_user --db-pass TestShopPass1! \\\r\n\r\n```\r\n\r\n### Programatically via Python\r\n\r\nThe `DBFactory` class in the `pyshopify.sql` module can be used to create the database and tables, as well as user and/or schema.\r\n\r\n```python\r\nfrom pyshopify.sql import DBFactory\r\n\r\n# port, schema, driver and connect_query are optional for MSSQL\r\n# Schema and driver have no impact on MySQL\r\ndb_builder = DBFactory(connector='mssql+pyodbc',\r\n server=localhost,\r\n port=1433,\r\n db_name='shop_rest',\r\n sa_user='sa',\r\n sa_pass='password',\r\n schema='Shopify',\r\n driver='ODBC Driver 17 for SQL Server', azure=False,\r\n encrypt=False,\r\n windows_auth=False)\r\n# Create database\r\ndb_builder.create_db()\r\n\r\n# Optional but recommended, create a new login and user with appropriate permissions\r\ndb_builder.create_user('shopify_user', 'password')\r\n\r\n# Build tables and the new schema if specified\r\ndb_builder.create_tables()\r\n\r\n# Optional, Build DateDimension table\r\ndb_builder.create_date_dimension('2015-01-01')\r\n```\r\n\r\n___\r\n\r\n## Run pyShopify with in Python Script\r\n\r\nThe primary class is `pyshopify.runner.ShopifyApp()` which contains the necessary execution methods.\r\n\r\n### Instantiating the `ShopifyApp` Class\r\n\r\nInstantiate `ShopifyApp` from the `pyshopify.runner` module.\r\n\r\n```python\r\nfrom pyshopify.runner import ShopifyApp\r\n\r\n# Instantiate ShopifyApp() with a configuration dictionary or absolute or relative location of config.ini file\r\nshop_instance = ShopifyApp(config_dir='rel/dir/to/config.ini')\r\n\r\n# Use configuration dictionary above to instantiate ShopifyApp()\r\nshop_instance = ShopifyApp(config_dict=configuration)\r\n```\r\n\r\n### Updating Configuration\r\n\r\nThe configuration can be updated at any point with the `update_config()` method. This can update the dates or time period of the export. Any date periods specified will override the current configuration, regardless of precedence.\r\n\r\n```python\r\n# A full or partial configuration dictionary can be passed to update_config()\r\nupdated_config = {\r\n 'shopify': {\r\n 'start': '2020-01-01',\r\n 'end': '2020-01-31'\r\n }\r\n}\r\n\r\nshop_instance.update_config(updated_config)\r\n```\r\n\r\n### Product and Inventory Data\r\n\r\nProduct and variant data can be pulled into a dictionary of DataFrames or written to SQL Server or a CSV file.\r\n\r\n#### Methods that Return products and Inventory Data\r\n\r\nThese methods retrieve inventory locations, inventory levels and product details into a dictionary of DataFrames. The dictionary keys are the respective table names with the same columns.\r\n\r\n**Get Inventory Levels at all locations**\r\nThe `inventory_table()` method returns a single dataframe with the product and variant id and title at all inventory locations. The method accepts lists or comma separated strings of product ids, variant ids and location_id. If no ids are specified, all products and variants will be returned.\r\n\r\n```python\r\nfrom pyshopify.runner import ShopifyApp()\r\napp = ShopifyApp()\r\n\r\ninventory_levels = app.inventory_table(product_ids=[],\r\n variant_ids=[],\r\n location_ids=[])\r\n# Returns DataFrame of inventory items with product & variant id & title\r\n# Returns:\r\n# DataFrame(columns=[\r\n# 'product_id', 'variant_id', 'sku', 'product_title', 'variant_title',\r\n# 'available', 'location_id'])\r\n```\r\n\r\n**Get All Products and Inventory Detail DataFrames**\r\nThe `get_products_inventory()` method returns a dictionary of DataFrames with product, variant, product option, inventory locations & inventory levels. The dictionary keys are the respective table names with values containing a dataframe of the associated table structure.\r\n\r\n```python\r\nproduct_details = app.get_products_inventory()\r\nproduct_details.items()\r\n# Returns:\r\n# [(products DataFrame)\r\n# (variants, DataFrame),\r\n# (product_options, DataFrame),\r\n# (inventory_locations, DataFrame),\r\n# (inventory_levels, DataFrame)]\r\n```\r\n\r\n##### Get All Product, Variant and Product Option DataFrames\r\n\r\nThe `get_products()` method returns a dictionary of DataFrames containing the product, variant and product option data. The dictionary keys are the respective table names with values containing a dataframe of the associated table structure.\r\n\r\nThe variants table contains the quantity of product that is available accross all locations.\r\n\r\n```python\r\nproduct_details = app.get_products()\r\nproduct_details.items()\r\n# Returns:\r\n# [(products DataFrame)\r\n# (variants, DataFrame),\r\n# (product_options, DataFrame)]\r\n```\r\n\r\n##### Get Inventory Levels and Locations Details\r\n\r\nThe `get_inventory_levels()` method returns a dictionary of DataFrames containing the inventory levels and inventory locations data. The dictionary keys are the respective table names with values containing a dataframe of the associated table structure.\r\n\r\nA list or comma separated string of location ids can be passed to filter the inventory levels to the specified locations.\r\n\r\n```python\r\n# Get inventory levels at specific or all locations\r\n# Locations can be passed as a list or comma separated string of location ID's\r\n# Get location ID's from the get_inventory_locations() method\r\ninventory_levels = app.get_inventory_levels(locations=[])\r\ninventory_levels.items()\r\n# Returns:\r\n# [(inventory_levels, DataFrame)]\r\n```\r\n\r\nThe `get_inventory_locations()` method returns a dictionary containing a DataFrame of inventory locations.\r\n\r\n```python\r\n# Get Dictionary with DataFrame of locations inventory is being held\r\ninv_locations = app.get_inventory_locations()\r\ninv_locations\r\n# Returns:\r\n# {\"inventory_locations\": DataFrame(\r\n# columns=['id', 'name', 'address1', 'address2',\r\n# 'city', 'province', 'country', 'zip',\r\n# 'phone', 'active', 'updated_at'])}\r\n```\r\n\r\n#### Methods that Write Inventory and Product Data\r\n\r\nThe following functions output the inventory and products data to a CSV file or SQL Server. The CSV file will be written to the directory specified in the configuration.\r\n\r\nA configuration dictionary can be passed to each method to override the current configuration. The configuration dictionary can be a full or partial configuration dictionary.\r\n\r\n```python\r\n# Write All products and All Inventory Data to CSV or SQL Server\r\napp.products_inventory_writer(write_sql=True, write_csv=Tru, config_dict=None)\r\n\r\n# Write products, variants & options to CSV or SQL Server\r\napp.products_writer(config_dict=None)\r\n\r\n# Write inventory levels to CSV or SQL\r\napp.inventory_levels_writer(write_csv=True, write_sql=True, config_dict=None)\r\n\r\n# Write inventory locations to CSV or SQL\r\napp.inventory_locations_writer(write_csv=True, write_sql=True, config_dict=None)\r\n```\r\n\r\n### Orders and customers Data\r\n\r\nThe following methods return the order details, pricing information and customers data. The methods return or yield a dictionary of DataFrames. The dictionary keys are the respective table names with the same columns. See the database documention for full column types and descriptions.\r\n\r\n#### Returning/Yielding customers & Orders Data\r\n\r\nThe customers and orders data can be returned or yielded, in addition to written to the database or CSV file. A full or partial configuration dictionary can be passed to these methods to override the current configuration.\r\n\r\nAll of these methods can accept a configuration dictionary with just the shopify section.\r\n\r\n**Shopify Configuration Dictionary**\r\nThe shopify configuration dictionary is the sql section of `config.ini`\r\n\r\n```python\r\nshop_config = {\r\n 'start': '2020-06-01',\r\n 'end': '2020-06-30'\r\n}\r\n```\r\n\r\n**Get Full Customers and Orders DataFrames**\r\nThis method will return a full dataset of customers and orders DataFrames for the specified time period. The method returns a dictionary of DataFrames with the dictionary keys being the respective table names.\r\n\r\nThese methods are not recommended for large time periods due to memory constraints.\r\n\r\n```python\r\nfrom pyshopify.runner import ShopifyApp\r\napp = ShopifyApp()\r\n\r\nshopify_config = {\r\n \"access_token\": \"<your api key>\",\r\n \"store_name\": \"<your store name>\",\r\n \"api_version\": \"2022-07\",\r\n \"days\": 7\r\n}\r\n\r\n# Return full customers and orders dataset\r\norders = app.get_orders_customers_df(shopify_config=shopify_config)\r\norders.items()\r\n# Returns:\r\n# [('customers', <pandas.DataFrame>),\r\n# ('Orders', <pandas.DataFrame>),\r\n# ('refunds', <pandas.DataFrame>),\r\n# ('line_items', <pandas.DataFrame>),\r\n# ('refund_line_item', <pandas.DataFrame>),\r\n# ('adjustments', <pandas.DataFrame>),\r\n# ('discount_apps', <pandas.DataFrame>),\r\n# ('discount_codes', <pandas.DataFrame>),\r\n# ('ship_lines', <pandas.DataFrame>),\r\n# ('order_prices', <pandas.DataFrame>),\r\n# ('order_attr', <pandas.DataFrame>)]\r\n\r\n# Return full customers dataset\r\ncustomers = app.get_full_customers_df()\r\ncustomers.items()\r\n# Returns:\r\n# [('customers', <pandas.DataFrame>)]\r\n\r\n# Return full orders and customers dataset\r\nall_data = app.get_full_orders_df(shopify_config=shopify_config)\r\nall_data.items()\r\n# Returns:\r\n# [('Orders', <pandas.DataFrame>),\r\n# ('refunds', <pandas.DataFrame>),\r\n# ('line_items', <pandas.DataFrame>),\r\n# ('refund_line_item', <pandas.DataFrame>),\r\n# ('adjustments', <pandas.DataFrame>),\r\n# ('discount_apps', <pandas.DataFrame>),\r\n# ('discount_codes', <pandas.DataFrame>),\r\n# ('ship_lines', <pandas.DataFrame>),\r\n# ('order_prices', <pandas.DataFrame>),\r\n# ('order_attr', <pandas.DataFrame>)]\r\n```\r\n\r\n**Iterating through Orders and customers Data**\r\nFor larger datasets, it is recommended to use the generator methods to iterate through the paginated data. The methods yield the same structure as the `get_full_orders_df()` and `get_full_customers_df()` methods.\r\n\r\nA [shopify configuration dictionary](#returningyielding-customers--orders-data) can be passed to these methods as well.\r\n\r\n```python\r\nfrom pyshopify.runner import ShopifyApp\r\napp = ShopifyApp()\r\n\r\n# A shopify configuration dictionary can be passed to all the generator methods\r\nfor order_dict in app.orders_iterator(shopify_config=shopify_config):\r\n order_dict.items()\r\n # Returns:\r\n # [('Orders', <pandas.DataFrame>),\r\n # ('refunds', <pandas.DataFrame>),\r\n # ('line_items', <pandas.DataFrame>),\r\n # ('refund_line_item', <pandas.DataFrame>),\r\n # ('adjustments', <pandas.DataFrame>),\r\n # ('discount_apps', <pandas.DataFrame>),\r\n # ('discount_codes', <pandas.DataFrame>),\r\n # ('ship_lines', <pandas.DataFrame>),\r\n # ('order_attr', <pandas.DataFrame>)]\r\n # ('order_prices', <pandas.DataFrame>)]\r\n\r\n# Iterate through customers data\r\nfor customers_dict in app.customers_iterator():\r\n customers_dict.items()\r\n # Returns:\r\n # [('customers', <pandas.DataFrame>)]\r\n```\r\n\r\n#### Writing Orders and Customers Data\r\n\r\nThere are several convenience methods that can be used to write the orders and customers data to CSV and SQL Server. The sql configuration can be passed directly to these methods, as the `engine` is only created during the first SQL method and stored as an instance variable `app.engine`\r\n\r\n#### Writing Data to Desired Output\r\n\r\n```python\r\nfrom pyshopify.runner import ShopifyApp\r\n\r\n# No arguments defaults to attempt to read to a config.ini file in CWD\r\napp = ShopifyApp()\r\n\r\n# Main method that can write order & customer data to csv/sql and set configuration. A full configuration dictionary can be passed to this method.\r\napp.orders_customers_writer(write_csv=True, write_sql=True, config=full_config)\r\n\r\n# Write only orders data to CSV and/or SQL\r\napp.orders_writer(write_sql=True, write_csv=True, config=full_config)\r\n\r\n# Write only customers data to CsV and/or SQL\r\napp.customers_writer(write_sql=True, write_csv=True, config=full_config)\r\n\r\n```\r\n\r\n### Writing All Data to DB or CSV\r\n\r\nA convenience method can be used to write all data to the database or CSV. This accepts a full configuration dictionary.\r\n\r\n```python\r\nfrom pyshopify.runner import ShopifyApp\r\n\r\napp = ShopifyApp()\r\n\r\n# Write product, inventory, customers, and orders data to db\r\napp.write_all_to_sql(config=full_config)\r\n\r\n# Write product, inventory, customers, and orders data to csv\r\napp.write_all_to_csv(config=full_config)\r\n```\r\n\r\n## Running as Command Line Application\r\n\r\nThe command line application `shopify_cli` can output the data to CSV or SQL Server. The command line application can be run with the following command:\r\n\r\nFrom command line:\r\n\r\n```bash\r\n# There are different command options\r\n$ shopify_cli --help\r\nUsage: shopify_cli [OPTIONS]\r\n\r\n Run Shopify App CLI.\r\n\r\n Use -d or --days to get days of history, default 7 Use -b or --between to\r\n get data between 2 dates - yyyy-MM-dd yyyy-MM-dd,\r\n\r\n --config is the relative or absolute location of config.ini\r\n\r\nOptions:\r\n --all Get all product, order and customer data\r\n --orders Get orders data\r\n --customers Get customers data\r\n --products Get products data\r\n -d, --days INTEGER get days of history, default 7\r\n -b, --between TEXT... get between 2 dates - yyyy-MM-dd,ex -b 2020-01-01\r\n 2020-01-02\r\n --sql-out / --no-sql write to database - Default False\r\n --csv-out / --no-csv Write results to csv files - Default true\r\n --csv-location TEXT Relative folder of csv export folderdefaults to\r\n csv_export/\r\n --config TEXT Relative location of config.ini - defaultsto\r\n config.ini in currect directory\r\n\r\n# Write last 30 days of all data to DB\r\n$ shopify_cli --all -d 30 --sql-out --config config.ini\r\n\r\n# Write 30 days of only orders data to CSV\r\n$ shopify_cli --orders -d 30 --csv-out --config config.ini\r\n\r\n# Get customers created between 2 dates\r\n$ shopify_cli --customers -b 2020-01-01 2020-01-02 --sql-out\r\n```\r\n\r\n## Data Structure\r\n\r\nThe structure of the custom return dictionary reflects the SQL database structure that it will update. See the [database docs](docs/tables.md) for full details on each table/DataFrame.\r\n\r\n```python\r\nfrom pyshopify import ShopifyApp\r\n\r\n# Enable custom in config.ini\r\nshop_app = ShopifyApp()\r\n\r\norders_dict = shop_app.orders_full_df()\r\n\r\norders_dict = {\r\n 'Orders': DataFrame,\r\n 'refunds': DataFrame,\r\n 'line_items': DataFrame,\r\n 'refund_line_item': DataFrame,\r\n 'adjustments': DataFrame,\r\n 'discount_apps': DataFrame,\r\n 'discount_codes': DataFrame,\r\n 'ship_lines': DataFrame,\r\n 'order_attr': DataFrame,\r\n 'order_prices': DataFrame\r\n}\r\n\r\ncustomers_dict = shop_app.customers_full_df()\r\n\r\ncustomers_dict = {\r\n \"customers\": DataFrame\r\n}\r\n\r\n```\r\n\r\n## Docker Container\r\n\r\nThere are two docker containers that can be used to run the application - an MS SQL based container and a MySQL based container. They are located in the respective docker folders, [docker-mysql](docker-mysql) and [docker-mssql](docker-mssql). Set the environment variables in the docker-compose.yml file before running.\r\n\r\nBoth containers have the same structure and can be used interchangeably. The only difference is the database engine used.\r\n\r\nSet environment variables and SA password to configure the database in `docker-compose.yml`. Configure a volume for CSV export if needed\r\n\r\nThe `config.ini` should be mapped to the `/root` user directory inside the container. A csv folder can also be mapped to a local folder. This should match the relative csv filepath in the configuration file. The working directory is the root user's home directory.\r\n\r\n### Docker Compose\r\n\r\nThe docker compose file configures the db administrator and the shopify database user. The date dimension table is built based on the `STARTING_DATE` environment variable.\r\n\r\nThe default location of the `config.ini` file is in the root user dir, the container's working directory. The data directory volume is the db data folder and the csv_export folder can also be mapped to a local directory.\r\n\r\n#### MS SQL Docker Container\r\n\r\nThe Microsoft SQL Server container is based on the Microsoft image with Ubuntu 20.04 and the pyshopify library installed alongside python 3.9. The container is configured to run the `shopify_cli` command line application.\r\n\r\nOn the initial run, the database structure is automatically built based on the environment variables in docker compose. The `config.ini` file is automatically populated with the database details based on the environment variables.\r\n\r\n```yaml\r\nservices:\r\n sqlserver:\r\n build:\r\n context: ..\r\n dockerfile: docker-mssql/Dockerfile\r\n container_name: shop_sql\r\n hostname: shop_sql\r\n ports:\r\n - \"1433:1433\"\r\n environment:\r\n ACCEPT_EULA: \"Y\"\r\n SA_PASSWORD: \"TheStrongestpassword1!\"\r\n MSSQL_SA_PASSWORD: \"TheStrongestpassword1!\"\r\n SHOPIFY_DB_USER: \"shop_user\"\r\n SHOPIFY_DB_PASSWORD: \"Strongerpassword1!\"\r\n SHOPIFY_DB_NAME: \"shop_rest\"\r\n SHOPIFY_DB_SCHEMA: \"dbo\"\r\n STARTING_DATE: \"2019-01-01\"\r\n CONFIG_FILE: \"/root/config.ini\"\r\n volumes:\r\n - \"../csv_export:/root/csv_export\"\r\n - \"../data:/var/opt/mssql\"\r\n - \"../config.ini:/root/config.ini\"\r\n```\r\n\r\n#### MySQL Docker Container\r\n\r\nThe MySQL container is based on the MySQL image with Debian-8 and the pyshopify library installed alongside python 3.9. The container is configured to run the `shopify_cli` command line application.\r\n\r\nOn the initial run, the database structure is automatically built based on the environment variables in docker compose. The `config.ini` file is automatically populated with the database details based on the environment variables.\r\n\r\n```yml\r\nservices:\r\n mysql-db:\r\n build:\r\n context: ..\r\n dockerfile: docker-mysql/Dockerfile\r\n container_name: shop_mysql\r\n hostname: shop_mysql\r\n ports:\r\n - \"3306:3306\"\r\n environment:\r\n MYSQL_TCP_PORT: 3306\r\n MYSQL_ROOT_PASSWORD: StrongestPassword1\r\n DB_NAME: shop_sql\r\n DB_USER: shop_user\r\n DB_PASS: StrongerPassword1\r\n STARTING_DATE: \"2019-01-01\"\r\n CONFIG_FILE: \"/root/config.ini\"\r\n volumes:\r\n - \"../csv_export:/root/csv_export\"\r\n - \"../data:/var/lib/mysql\"\r\n - \"../config.ini:/root/config.ini\"\r\n```\r\n\r\n### Creating a Container\r\n\r\nTo create a container:\r\n\r\n```shell script\r\ngit clone https://github.com/webdjoe/pyshopify\r\n\r\n# Switch to the appropriate docker container folder\r\ncd pyshopify/docker-mssql\r\ncd pyshopify/docker-mysql\r\n\r\n# edit the docker compose environment variables with vim or nano\r\nvim docker-compose.yml\r\n\r\n# Build and run container detached\r\nsudo docker compose up --build -d\r\n\r\n# edit config.ini file\r\nvim config.ini\r\n```\r\n\r\nOnce started test if SQL server is running. A `0` return value indicates the server has started up and is ready for connections.\r\n\r\n```shell script\r\n sudo docker exec -it shop_sql bash -c '/opt/mssql-tools/bin/sqlcmd -h -1 -t 1 -U sa \\\r\n -P \"$MSSQL_SA_PASSWORD\" -Q \"SET NOCOUNT ON; Select SUM(state) from sys.databases\"'\r\n```\r\n\r\n### Running pyshopify in the container\r\n\r\n`shopify_cli` can be called in container to pull data and send to database or csv files. Check out the [`shopify_cli`](#running-as-command-line-application) section for more details.\r\n\r\n```shell script\r\n# Get last 30 days of data and import into SQL Server running in container\r\nsudo docker exec -it shop_sql shopify_cli --all -d 30 --sql-out\r\n\r\n# Get orders data between dates and import into SQL Server\r\nsudo docker exec -it shop_sql shopify_cli --orders -b 2020-01-01 2020-12-31 --sql-out\r\n\r\n# Pull 30 days of data into csv files\r\nsudo docker exec -it shop_sql shopify_cli --csv-out --csv-location csv_export\r\n```\r\n",
"bugtrack_url": null,
"license": "",
"summary": "Shopify Orders & Customers API library with containerized MS SQL database",
"version": "0.9.11",
"split_keywords": [
"shopify",
"sql server",
"mssql",
"python",
"docker",
"python3",
"pyodbc"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "4017e9977fc8d01adabf0b23ffde6f6fcf7e905265db8fb2d2c1244b3fb54645",
"md5": "cfba5740b3c0526c24df706b7e87b20b",
"sha256": "5c59835cce6f7a9d74db88fb255fedd34dfdf6dc3b914dfe7111876cf2895194"
},
"downloads": -1,
"filename": "pyshopify-0.9.11-py3-none-any.whl",
"has_sig": false,
"md5_digest": "cfba5740b3c0526c24df706b7e87b20b",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.9",
"size": 40543,
"upload_time": "2023-02-11T02:15:26",
"upload_time_iso_8601": "2023-02-11T02:15:26.553590Z",
"url": "https://files.pythonhosted.org/packages/40/17/e9977fc8d01adabf0b23ffde6f6fcf7e905265db8fb2d2c1244b3fb54645/pyshopify-0.9.11-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "d5284d267d0ca96405c9c7c38bdd93b7496b73ed9ab8ed8f987c97d5c266028b",
"md5": "7c3bc49158d6ba6816d1dc3bac2c53de",
"sha256": "5a0818a8954b9b1a7cd39dd6c9d49cbfa2c2b58815f003c78a4b502d6b96f2cf"
},
"downloads": -1,
"filename": "pyshopify-0.9.11.tar.gz",
"has_sig": false,
"md5_digest": "7c3bc49158d6ba6816d1dc3bac2c53de",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.9",
"size": 58890,
"upload_time": "2023-02-11T02:15:28",
"upload_time_iso_8601": "2023-02-11T02:15:28.787809Z",
"url": "https://files.pythonhosted.org/packages/d5/28/4d267d0ca96405c9c7c38bdd93b7496b73ed9ab8ed8f987c97d5c266028b/pyshopify-0.9.11.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-02-11 02:15:28",
"github": false,
"gitlab": false,
"bitbucket": false,
"lcname": "pyshopify"
}
JSON