Coverage for mindsdb / integrations / handlers / bigcommerce_handler / bigcommerce_tables.py: 0%
249 statements
« prev ^ index » next coverage.py v7.13.1, created at 2026-01-21 00:36 +0000
1from typing import List
2from decimal import Decimal
4import pandas as pd
6from mindsdb.integrations.handlers.bigcommerce_handler.bigcommerce_api_client import BigCommerceAPIClient
7from mindsdb.integrations.utilities.sql_utils import FilterCondition, FilterOperator, SortColumn
8from mindsdb.integrations.libs.api_handler import MetaAPIResource
9from mindsdb.utilities import log
12logger = log.getLogger(__name__)
15def _make_filter(conditions: list[FilterCondition] | None, op_map: dict) -> dict:
16 """Creates a filter dictionary, that can be used in the BigCommerce API.
18 Args:
19 conditions (list[FilterCondition]): The list of parsed filter conditions.
20 op_map (dict): The mapping of filter operators to API parameters.
22 Returns:
23 dict: The filter dictionary.
24 """
25 filter = {}
26 if conditions is None:
27 return filter
28 for condition in conditions:
29 simple_op = op_map.get((condition.column, condition.op))
30 if simple_op:
31 value = condition.value
32 if isinstance(value, list):
33 value = ",".join(map(str, value))
34 filter[simple_op] = value
35 condition.applied = True
36 return filter
39def _make_df(result: list[dict], table: MetaAPIResource):
40 """Converts a list of dictionaries to a pandas DataFrame.
41 If the list is empty, an empty DataFrame is returned with the columns from the table.
43 Args:
44 result (list[dict]): The list of dictionaries to convert.
45 table (MetaAPIResource): The table class.
47 Returns:
48 pd.DataFrame: The resulting DataFrame.
49 """
50 if len(result) == 0:
51 result = pd.DataFrame([], columns=table.get_columns())
52 else:
53 result = pd.DataFrame(result)
54 return result
57def _make_sort_condition_v3(sort: list[SortColumn], sortable_columns: list[str]):
58 """Creates a sort condition for the BigCommerce API v3.
60 Args:
61 sort (list[SortColumn]): The list of parsed sort columns.
62 sortable_columns (list[str]): The list of sortable columns.
64 Returns:
65 dict: The sort condition, that can be used in the BigCommerce API v3.
66 """
67 sort_condition = None
68 if isinstance(sort, list) and len(sort) > 1 and sort[0].column in sortable_columns:
69 sort_column = sort[0]
70 sort_condition = {
71 "sort": sort_column.column,
72 "direction": "asc" if sort_column.ascending else "desc",
73 }
74 return sort_condition
77def _make_sort_condition_v2(sort: list[SortColumn], sortable_columns: list[str]):
78 """Creates a sort condition for the BigCommerce API v2.
80 Args:
81 sort (list[SortColumn]): The list of parsed sort columns.
82 sortable_columns (list[str]): The list of sortable columns.
84 Returns:
85 dict: The sort condition, that can be used in the BigCommerce API v2.
86 """
87 sort_condition = None
88 if isinstance(sort, list) and len(sort) == 1 and sort[0].column in sortable_columns:
89 sort_column = sort[0]
90 sort_column.applied = True
91 sort_condition = f"{sort_column.column}:{'asc' if sort_column.ascending else 'desc'}"
92 return sort_condition
95class BigCommerceOrdersTable(MetaAPIResource):
96 """
97 The table abstraction for the 'orders' resource of the BigCommerce API.
98 """
100 name = "orders"
102 def list(
103 self,
104 conditions: list[FilterCondition] = None,
105 limit: int = None,
106 sort: list[SortColumn] = None,
107 **kwargs,
108 ):
109 """Executes a parsed SELECT SQL query on the 'orders' resource of the BigCommerce API.
111 Args:
112 conditions (list[FilterCondition]): The list of parsed filter conditions.
113 limit (int): The maximum number of records to return.
114 sort (list[SortColumn]): The list of parsed sort columns.
116 Returns:
117 pd.DataFrame: The resulting DataFrame.
118 """
119 client: BigCommerceAPIClient = self.handler.connect()
121 simple_op_map = {
122 ("id", FilterOperator.GREATER_THAN): "min_id",
123 ("id", FilterOperator.LESS_THAN): "max_id",
124 ("total_inc_tax", FilterOperator.GREATER_THAN): "min_total",
125 ("total_inc_tax", FilterOperator.LESS_THAN): "max_total",
126 ("customer_id", FilterOperator.EQUAL): "customer_id",
127 ("email", FilterOperator.EQUAL): "email",
128 ("status_id", FilterOperator.EQUAL): "status_id",
129 ("cart_id", FilterOperator.EQUAL): "cart_id",
130 ("payment_method", FilterOperator.EQUAL): "payment_method",
131 ("date_created", FilterOperator.GREATER_THAN): "min_date_created",
132 ("date_created", FilterOperator.LESS_THAN): "max_date_created",
133 ("date_modified", FilterOperator.GREATER_THAN): "min_date_modified",
134 ("date_modified", FilterOperator.LESS_THAN): "max_date_modified",
135 ("channel_id", FilterOperator.EQUAL): "channel_id",
136 ("external_order_id", FilterOperator.EQUAL): "external_order_id",
137 }
139 filter = _make_filter(conditions, simple_op_map)
141 for condition in conditions:
142 if condition.applied:
143 continue
144 # region special case for filter "id = x"
145 if condition.op == FilterOperator.EQUAL and condition.column == "id":
146 filter["min_id"] = condition.value
147 filter["max_id"] = condition.value
148 # endregion
150 sortable_columns = [
151 "id",
152 "customer_id",
153 "date_created",
154 "date_modified",
155 "status_id",
156 "channel_id",
157 "external_id",
158 ]
159 sort_condition = _make_sort_condition_v3(sort, sortable_columns)
161 result = client.get_orders(filter=filter, sort_condition=sort_condition, limit=limit)
162 result = _make_df(result, self)
164 decimal_columns = [meta["COLUMN_NAME"] for meta in self.meta_get_columns() if meta["DATA_TYPE"] == "DECIMAL"]
165 for column_name in decimal_columns:
166 if column_name in result:
167 result[column_name] = result[column_name].apply(Decimal)
169 return result
171 def get_columns(self) -> List[str]:
172 """
173 Retrieves the attributes (columns) of the 'orders' resource.
175 Returns:
176 list[str]: A list of attributes (columns) of the 'orders' resource.
177 """
178 columns = self.meta_get_columns()
179 return [column["COLUMN_NAME"] for column in columns]
181 def meta_get_tables(self, table_name: str) -> dict:
182 client: BigCommerceAPIClient = self.handler.connect()
183 orders_count = client.get_orders_count()
184 return {
185 "table_name": self.name,
186 "table_type": "BASE TABLE",
187 "table_description": "",
188 "row_count": orders_count,
189 }
191 def meta_get_columns(self, *args, **kwargs) -> List[str]:
192 return [
193 {
194 "TABLE_NAME": "orders",
195 "COLUMN_NAME": "id",
196 "DATA_TYPE": "INT",
197 "COLUMN_DESCRIPTION": "The ID of the order.",
198 },
199 {
200 "TABLE_NAME": "orders",
201 "COLUMN_NAME": "date_modified",
202 "DATA_TYPE": "DATETIME",
203 "COLUMN_DESCRIPTION": "Value representing the last modification of the order. RFC-2822. This date time is always in UTC in the api response.",
204 },
205 {
206 "TABLE_NAME": "orders",
207 "COLUMN_NAME": "date_shipped",
208 "DATA_TYPE": "VARCHAR",
209 "COLUMN_DESCRIPTION": "Value representing the date when the order is fully shipped. RFC-2822",
210 },
211 {
212 "TABLE_NAME": "orders",
213 "COLUMN_NAME": "cart_id",
214 "DATA_TYPE": "VARCHAR",
215 "COLUMN_DESCRIPTION": "The cart ID from which this order originated, if applicable.",
216 },
217 {
218 "TABLE_NAME": "orders",
219 "COLUMN_NAME": "status",
220 "DATA_TYPE": "VARCHAR",
221 "COLUMN_DESCRIPTION": "The status will include one of the values defined under Order Statuses.",
222 },
223 {"TABLE_NAME": "orders", "COLUMN_NAME": "subtotal_tax", "DATA_TYPE": "DECIMAL", "COLUMN_DESCRIPTION": ""},
224 {
225 "TABLE_NAME": "orders",
226 "COLUMN_NAME": "shipping_cost_tax",
227 "DATA_TYPE": "DECIMAL",
228 "COLUMN_DESCRIPTION": "",
229 },
230 {
231 "TABLE_NAME": "orders",
232 "COLUMN_NAME": "shipping_cost_tax_class_id",
233 "DATA_TYPE": "INT",
234 "COLUMN_DESCRIPTION": "",
235 },
236 {
237 "TABLE_NAME": "orders",
238 "COLUMN_NAME": "handling_cost_tax",
239 "DATA_TYPE": "DECIMAL",
240 "COLUMN_DESCRIPTION": "",
241 },
242 {
243 "TABLE_NAME": "orders",
244 "COLUMN_NAME": "handling_cost_tax_class_id",
245 "DATA_TYPE": "INT",
246 "COLUMN_DESCRIPTION": "",
247 },
248 {
249 "TABLE_NAME": "orders",
250 "COLUMN_NAME": "wrapping_cost_tax",
251 "DATA_TYPE": "DECIMAL",
252 "COLUMN_DESCRIPTION": "",
253 },
254 {
255 "TABLE_NAME": "orders",
256 "COLUMN_NAME": "wrapping_cost_tax_class_id",
257 "DATA_TYPE": "INT",
258 "COLUMN_DESCRIPTION": "",
259 },
260 {
261 "TABLE_NAME": "orders",
262 "COLUMN_NAME": "payment_status",
263 "DATA_TYPE": "VARCHAR",
264 "COLUMN_DESCRIPTION": "Payment status of the order. Allowed: authorized | captured | capture pending | declined | held for review | paid | partially refunded | pending | refunded | void | void pending",
265 },
266 {
267 "TABLE_NAME": "orders",
268 "COLUMN_NAME": "store_credit_amount",
269 "DATA_TYPE": "DECIMAL",
270 "COLUMN_DESCRIPTION": "Represents the store credit that the shopper has redeemed on this individual order.",
271 },
272 {
273 "TABLE_NAME": "orders",
274 "COLUMN_NAME": "gift_certificate_amount",
275 "DATA_TYPE": "DECIMAL",
276 "COLUMN_DESCRIPTION": "",
277 },
278 {
279 "TABLE_NAME": "orders",
280 "COLUMN_NAME": "currency_id",
281 "DATA_TYPE": "INT",
282 "COLUMN_DESCRIPTION": "The display currency ID. Depending on the currency selected, the value can be different from the transactional currency.",
283 },
284 {
285 "TABLE_NAME": "orders",
286 "COLUMN_NAME": "currency_code",
287 "DATA_TYPE": "VARCHAR",
288 "COLUMN_DESCRIPTION": "The currency code of the display currency used to present prices to the shopper on the storefront. Depending on the currency selected, the value can be different from the transactional currency.",
289 },
290 {
291 "TABLE_NAME": "orders",
292 "COLUMN_NAME": "currency_exchange_rate",
293 "DATA_TYPE": "DECIMAL",
294 "COLUMN_DESCRIPTION": "The exchange rate between the store's default currency and the display currency.",
295 },
296 {
297 "TABLE_NAME": "orders",
298 "COLUMN_NAME": "default_currency_id",
299 "DATA_TYPE": "INT",
300 "COLUMN_DESCRIPTION": "The transactional currency ID.",
301 },
302 {
303 "TABLE_NAME": "orders",
304 "COLUMN_NAME": "default_currency_code",
305 "DATA_TYPE": "VARCHAR",
306 "COLUMN_DESCRIPTION": "The currency code of the transactional currency the shopper pays in.",
307 },
308 {
309 "TABLE_NAME": "orders",
310 "COLUMN_NAME": "store_default_currency_code",
311 "DATA_TYPE": "VARCHAR",
312 "COLUMN_DESCRIPTION": "The currency code of the store's default currency.",
313 },
314 {
315 "TABLE_NAME": "orders",
316 "COLUMN_NAME": "store_default_to_transactional_exchange_rate",
317 "DATA_TYPE": "DECIMAL",
318 "COLUMN_DESCRIPTION": "The exchange rate between the store's default currency and the transactional currency used in the order.",
319 },
320 {
321 "TABLE_NAME": "orders",
322 "COLUMN_NAME": "coupon_discount",
323 "DATA_TYPE": "DECIMAL",
324 "COLUMN_DESCRIPTION": "",
325 },
326 {
327 "TABLE_NAME": "orders",
328 "COLUMN_NAME": "shipping_address_count",
329 "DATA_TYPE": "INT",
330 "COLUMN_DESCRIPTION": "The number of shipping addresses associated with this transaction.",
331 },
332 {
333 "TABLE_NAME": "orders",
334 "COLUMN_NAME": "is_deleted",
335 "DATA_TYPE": "BOOL",
336 "COLUMN_DESCRIPTION": "Indicates whether the order is deleted/archived.",
337 },
338 {
339 "TABLE_NAME": "orders",
340 "COLUMN_NAME": "total_tax",
341 "DATA_TYPE": "DECIMAL",
342 "COLUMN_DESCRIPTION": "Total tax amount for the order.",
343 },
344 {
345 "TABLE_NAME": "orders",
346 "COLUMN_NAME": "is_tax_inclusive_pricing",
347 "DATA_TYPE": "BOOL",
348 "COLUMN_DESCRIPTION": "Indicate whether the order's base prices include tax. If true, the base prices are inclusive of tax. If false, the base prices are exclusive of tax.",
349 },
350 {
351 "TABLE_NAME": "orders",
352 "COLUMN_NAME": "is_email_opt_in",
353 "DATA_TYPE": "BOOL",
354 "COLUMN_DESCRIPTION": "Indicates whether the shopper has selected an opt-in check box (on the checkout page) to receive emails.",
355 },
356 {
357 "TABLE_NAME": "orders",
358 "COLUMN_NAME": "order_source",
359 "DATA_TYPE": "VARCHAR",
360 "COLUMN_DESCRIPTION": "Reflects the origin of the order. It can affect the order's icon and source as defined in the control panel listing.",
361 },
362 {"TABLE_NAME": "orders", "COLUMN_NAME": "consignments", "DATA_TYPE": "JSON", "COLUMN_DESCRIPTION": ""},
363 {"TABLE_NAME": "orders", "COLUMN_NAME": "products", "DATA_TYPE": "JSON", "COLUMN_DESCRIPTION": ""},
364 {
365 "TABLE_NAME": "orders",
366 "COLUMN_NAME": "shipping_addresses",
367 "DATA_TYPE": "JSON",
368 "COLUMN_DESCRIPTION": "",
369 },
370 {"TABLE_NAME": "orders", "COLUMN_NAME": "coupons", "DATA_TYPE": "JSON", "COLUMN_DESCRIPTION": ""},
371 {"TABLE_NAME": "orders", "COLUMN_NAME": "billing_address", "DATA_TYPE": "JSON", "COLUMN_DESCRIPTION": ""},
372 {
373 "TABLE_NAME": "orders",
374 "COLUMN_NAME": "base_handling_cost",
375 "DATA_TYPE": "DECIMAL",
376 "COLUMN_DESCRIPTION": "The value of the base handling cost. The value can't be negative.",
377 },
378 {
379 "TABLE_NAME": "orders",
380 "COLUMN_NAME": "base_shipping_cost",
381 "DATA_TYPE": "DECIMAL",
382 "COLUMN_DESCRIPTION": "The value of the base shipping cost. The value can't be negative.",
383 },
384 {
385 "TABLE_NAME": "orders",
386 "COLUMN_NAME": "base_wrapping_cost",
387 "DATA_TYPE": "DECIMAL",
388 "COLUMN_DESCRIPTION": "The value of the base wrapping cost expressed as a floating point number to four decimal places in string format. The value can't be negative.",
389 },
390 {
391 "TABLE_NAME": "orders",
392 "COLUMN_NAME": "channel_id",
393 "DATA_TYPE": "INT",
394 "COLUMN_DESCRIPTION": "Shows where the order originated. The channel_id defaults to 1. The value must match the ID of a valid and enabled channel.",
395 },
396 {"TABLE_NAME": "orders", "COLUMN_NAME": "customer_id", "DATA_TYPE": "INT", "COLUMN_DESCRIPTION": ""},
397 {
398 "TABLE_NAME": "orders",
399 "COLUMN_NAME": "customer_message",
400 "DATA_TYPE": "VARCHAR",
401 "COLUMN_DESCRIPTION": "Message that the customer entered to the Order Comments box during checkout.",
402 },
403 {
404 "TABLE_NAME": "orders",
405 "COLUMN_NAME": "date_created",
406 "DATA_TYPE": "DATETIME",
407 "COLUMN_DESCRIPTION": "The date the order was created, formatted in the RFC-2822 standard. You set this attribute on Order creation (POST) to support the migration of historical orders. If you do not provide a value, then it will default to the current date/time. This date time is always in UTC in the api response.",
408 },
409 {
410 "TABLE_NAME": "orders",
411 "COLUMN_NAME": "discount_amount",
412 "DATA_TYPE": "DECIMAL",
413 "COLUMN_DESCRIPTION": "Amount of discount for this transaction. The value can't be negative.",
414 },
415 {
416 "TABLE_NAME": "orders",
417 "COLUMN_NAME": "ebay_order_id",
418 "DATA_TYPE": "TEXT",
419 "COLUMN_DESCRIPTION": "If the order was placed through eBay, the eBay order number will be included. Otherwise, the value will be 0.",
420 },
421 {
422 "TABLE_NAME": "orders",
423 "COLUMN_NAME": "external_id",
424 "DATA_TYPE": "INT",
425 "COLUMN_DESCRIPTION": "The order ID in another system, such as the Amazon order ID if this is an Amazon order.",
426 },
427 {
428 "TABLE_NAME": "orders",
429 "COLUMN_NAME": "external_merchant_id",
430 "DATA_TYPE": "INT",
431 "COLUMN_DESCRIPTION": "The merchant ID represents an upstream order from an external system. It is the source of truth for orders. After setting it, you cannot write to or update the external_merchant_id.",
432 },
433 {
434 "TABLE_NAME": "orders",
435 "COLUMN_NAME": "external_source",
436 "DATA_TYPE": "VARCHAR",
437 "COLUMN_DESCRIPTION": "This value identifies an external system that generated the order and submitted it to BigCommerce with the Orders API.",
438 },
439 {
440 "TABLE_NAME": "orders",
441 "COLUMN_NAME": "geoip_country",
442 "DATA_TYPE": "VARCHAR",
443 "COLUMN_DESCRIPTION": "The full name of the country where the customer made the purchase, based on the IP.",
444 },
445 {
446 "TABLE_NAME": "orders",
447 "COLUMN_NAME": "geoip_country_iso2",
448 "DATA_TYPE": "VARCHAR",
449 "COLUMN_DESCRIPTION": "The country where the customer made the purchase, in ISO2 format, based on the IP.",
450 },
451 {
452 "TABLE_NAME": "orders",
453 "COLUMN_NAME": "handling_cost_ex_tax",
454 "DATA_TYPE": "DECIMAL",
455 "COLUMN_DESCRIPTION": "The value of the handling cost, excluding tax. The value can't be negative.",
456 },
457 {
458 "TABLE_NAME": "orders",
459 "COLUMN_NAME": "handling_cost_inc_tax",
460 "DATA_TYPE": "DECIMAL",
461 "COLUMN_DESCRIPTION": "The value of the handling cost, including tax. The value can't be negative.",
462 },
463 {
464 "TABLE_NAME": "orders",
465 "COLUMN_NAME": "ip_address",
466 "DATA_TYPE": "VARCHAR",
467 "COLUMN_DESCRIPTION": "IPv4 Address of the customer, if known.",
468 },
469 {
470 "TABLE_NAME": "orders",
471 "COLUMN_NAME": "ip_address_v6",
472 "DATA_TYPE": "VARCHAR",
473 "COLUMN_DESCRIPTION": "IPv6 Address of the customer, if known.",
474 },
475 {
476 "TABLE_NAME": "orders",
477 "COLUMN_NAME": "items_shipped",
478 "DATA_TYPE": "INT",
479 "COLUMN_DESCRIPTION": "The number of items that have been shipped.",
480 },
481 {
482 "TABLE_NAME": "orders",
483 "COLUMN_NAME": "items_total",
484 "DATA_TYPE": "INT",
485 "COLUMN_DESCRIPTION": "The total number of items in the order.",
486 },
487 {
488 "TABLE_NAME": "orders",
489 "COLUMN_NAME": "order_is_digital",
490 "DATA_TYPE": "BOOL",
491 "COLUMN_DESCRIPTION": "Whether this is an order for digital products.",
492 },
493 {
494 "TABLE_NAME": "orders",
495 "COLUMN_NAME": "payment_method",
496 "DATA_TYPE": "VARCHAR",
497 "COLUMN_DESCRIPTION": "The payment method for this order.",
498 },
499 {
500 "TABLE_NAME": "orders",
501 "COLUMN_NAME": "payment_provider_id",
502 "DATA_TYPE": "INT",
503 "COLUMN_DESCRIPTION": "The external Transaction ID/Payment ID within this order's payment provider (if a payment provider was used).",
504 },
505 {
506 "TABLE_NAME": "orders",
507 "COLUMN_NAME": "refunded_amount",
508 "DATA_TYPE": "DECIMAL",
509 "COLUMN_DESCRIPTION": "The amount refunded from this transaction; always returns 0. The value can't be negative.",
510 },
511 {
512 "TABLE_NAME": "orders",
513 "COLUMN_NAME": "shipping_cost_ex_tax",
514 "DATA_TYPE": "DECIMAL",
515 "COLUMN_DESCRIPTION": "The value of shipping cost, excluding tax. The value can't be negative.",
516 },
517 {
518 "TABLE_NAME": "orders",
519 "COLUMN_NAME": "shipping_cost_inc_tax",
520 "DATA_TYPE": "DECIMAL",
521 "COLUMN_DESCRIPTION": "The value of shipping cost, including tax. The value can't be negative.",
522 },
523 {
524 "TABLE_NAME": "orders",
525 "COLUMN_NAME": "staff_notes",
526 "DATA_TYPE": "VARCHAR",
527 "COLUMN_DESCRIPTION": "Any additional notes for staff.",
528 },
529 {
530 "TABLE_NAME": "orders",
531 "COLUMN_NAME": "subtotal_ex_tax",
532 "DATA_TYPE": "DECIMAL",
533 "COLUMN_DESCRIPTION": "Override value for subtotal excluding tax. The value can't be negative. If specified, the field subtotal_inc_tax is also required.",
534 },
535 {
536 "TABLE_NAME": "orders",
537 "COLUMN_NAME": "subtotal_inc_tax",
538 "DATA_TYPE": "DECIMAL",
539 "COLUMN_DESCRIPTION": "Override value for subtotal including tax. The value can't be negative. If specified, the field subtotal_ex_tax is also required.",
540 },
541 {
542 "TABLE_NAME": "orders",
543 "COLUMN_NAME": "tax_provider_id",
544 "DATA_TYPE": "VARCHAR",
545 "COLUMN_DESCRIPTION": "BasicTaxProvider - Tax is set to manual and order is created in the store. AvaTaxProvider - Tax is set to automatic and order is created in the store. Empty string - The order is created with the API, or the tax provider is unknown.",
546 },
547 {
548 "TABLE_NAME": "orders",
549 "COLUMN_NAME": "customer_locale",
550 "DATA_TYPE": "VARCHAR",
551 "COLUMN_DESCRIPTION": "The customer's locale.",
552 },
553 {
554 "TABLE_NAME": "orders",
555 "COLUMN_NAME": "external_order_id",
556 "DATA_TYPE": "VARCHAR",
557 "COLUMN_DESCRIPTION": "The order ID in another system, such as the Amazon Order ID if this is an Amazon order. After setting it, you can update this field using a POST or PUT request.",
558 },
559 {
560 "TABLE_NAME": "orders",
561 "COLUMN_NAME": "total_ex_tax",
562 "DATA_TYPE": "DECIMAL",
563 "COLUMN_DESCRIPTION": "Override value for the total, excluding tax. If specified, the field total_inc_tax is also required. The value can't be negative.",
564 },
565 {
566 "TABLE_NAME": "orders",
567 "COLUMN_NAME": "total_inc_tax",
568 "DATA_TYPE": "DECIMAL",
569 "COLUMN_DESCRIPTION": "Override value for the total, including tax. If specified, the field total_ex_tax is also required. The value can't be negative.",
570 },
571 {
572 "TABLE_NAME": "orders",
573 "COLUMN_NAME": "wrapping_cost_ex_tax",
574 "DATA_TYPE": "DECIMAL",
575 "COLUMN_DESCRIPTION": "The value of the wrapping cost, excluding tax. The value can't be negative.",
576 },
577 {
578 "TABLE_NAME": "orders",
579 "COLUMN_NAME": "wrapping_cost_inc_tax",
580 "DATA_TYPE": "DECIMAL",
581 "COLUMN_DESCRIPTION": "The value of the wrapping cost, including tax. The value can't be negative.",
582 },
583 # These fields are not mentioned in the API documentation, but they are present in the actual response.
584 {"TABLE_NAME": "orders", "COLUMN_NAME": "status_id", "DATA_TYPE": "INT", "COLUMN_DESCRIPTION": ""},
585 {"TABLE_NAME": "orders", "COLUMN_NAME": "fees", "DATA_TYPE": "JSON", "COLUMN_DESCRIPTION": ""},
586 {
587 "TABLE_NAME": "orders",
588 "COLUMN_NAME": "credit_card_type",
589 "DATA_TYPE": "VARCHAR",
590 "COLUMN_DESCRIPTION": "",
591 },
592 {"TABLE_NAME": "orders", "COLUMN_NAME": "custom_status", "DATA_TYPE": "VARCHAR", "COLUMN_DESCRIPTION": ""},
593 ]
596class BigCommerceProductsTable(MetaAPIResource):
597 """
598 The table abstraction for the 'products' resource of the BigCommerce API.
599 """
601 name = "products"
603 def list(
604 self,
605 conditions: list[FilterCondition] = None,
606 limit: int = None,
607 sort: list[SortColumn] = None,
608 targets: list[str] = None,
609 **kwargs,
610 ):
611 """
612 Executes a parsed SELECT SQL query on the 'products' resource of the BigCommerce API.
614 Args:
615 conditions (list[FilterCondition]): The list of parsed filter conditions.
616 limit (int): The maximum number of records to return.
617 sort (list[SortColumn]): The list of parsed sort columns.
618 targets (list[str]): The list of target columns to return.
620 Returns:
621 pd.DataFrame: The resulting DataFrame.
622 """
623 client: BigCommerceAPIClient = self.handler.connect()
625 simple_op_map = {
626 ("id", FilterOperator.EQUAL): "id",
627 ("id", FilterOperator.IN): "id:in",
628 ("id", FilterOperator.NOT_IN): "id:not_in",
629 ("id", FilterOperator.GREATER_THAN): "id:greater",
630 ("id", FilterOperator.LESS_THAN): "id:less",
631 ("id", FilterOperator.GREATER_THAN_OR_EQUAL): "id:min",
632 ("id", FilterOperator.LESS_THAN_OR_EQUAL): "id:max",
633 ("channel_id", FilterOperator.IN): "channel_id:in",
634 ("categories", FilterOperator.EQUAL): "categories",
635 ("categories", FilterOperator.IN): "categories:in",
636 ("name", FilterOperator.EQUAL): "name",
637 ("mpn", FilterOperator.EQUAL): "mpn",
638 ("upc", FilterOperator.EQUAL): "upc",
639 ("price", FilterOperator.EQUAL): "price",
640 ("weight", FilterOperator.EQUAL): "weight",
641 ("condition", FilterOperator.EQUAL): "condition",
642 ("brand_id", FilterOperator.EQUAL): "brand_id",
643 ("date_modified", FilterOperator.EQUAL): "date_modified",
644 ("date_modified", FilterOperator.LESS_THAN_OR_EQUAL): "date_modified:max",
645 ("date_modified", FilterOperator.GREATER_THAN_OR_EQUAL): "date_modified:min",
646 ("date_last_imported", FilterOperator.EQUAL): "date_last_imported",
647 ("date_last_imported", FilterOperator.NOT_EQUAL): "date_last_imported:not",
648 ("date_last_imported", FilterOperator.LESS_THAN_OR_EQUAL): "date_last_imported:max",
649 ("date_last_imported", FilterOperator.GREATER_THAN_OR_EQUAL): "date_last_imported:min",
650 ("is_visible", FilterOperator.EQUAL): "is_visible",
651 ("is_featured", FilterOperator.EQUAL): "is_featured",
652 ("is_free_shipping", FilterOperator.EQUAL): "is_free_shipping",
653 ("inventory_level", FilterOperator.EQUAL): "inventory_level",
654 ("inventory_level", FilterOperator.IN): "inventory_level:in",
655 ("inventory_level", FilterOperator.NOT_IN): "inventory_level:not_in",
656 ("inventory_level", FilterOperator.GREATER_THAN_OR_EQUAL): "inventory_level:min",
657 ("inventory_level", FilterOperator.LESS_THAN_OR_EQUAL): "inventory_level:max",
658 ("inventory_level", FilterOperator.GREATER_THAN): "inventory_level:greater",
659 ("inventory_level", FilterOperator.LESS_THAN): "inventory_level:less",
660 ("inventory_low", FilterOperator.EQUAL): "inventory_low",
661 ("out_of_stock", FilterOperator.EQUAL): "out_of_stock",
662 ("total_sold", FilterOperator.EQUAL): "total_sold",
663 ("type", FilterOperator.EQUAL): "type",
664 ("keyword", FilterOperator.EQUAL): "keyword",
665 ("keyword_context", FilterOperator.EQUAL): "keyword_context",
666 ("availability", FilterOperator.EQUAL): "availability",
667 ("sku", FilterOperator.EQUAL): "sku",
668 ("sku", FilterOperator.IN): "sku:in",
669 }
671 filter = {}
672 for condition in conditions:
673 simple_op = simple_op_map.get((condition.column, condition.op))
674 if simple_op:
675 value = condition.value
676 if isinstance(value, list):
677 value = ",".join(map(str, value))
678 filter[simple_op] = value
679 condition.applied = True
681 filter = _make_filter(conditions, simple_op_map)
683 if targets:
684 available_columns = self.get_columns()
685 for column_name in targets:
686 if column_name not in available_columns:
687 raise ValueError(f"Field '{column_name}' does not exists")
688 filter["include_fields"] = ",".join(targets)
690 sortable_columns = [
691 "id",
692 "name",
693 "sku",
694 "price",
695 "date_modified",
696 "date_last_imported",
697 "inventory_level",
698 "is_visible",
699 "total_sold",
700 "calculated_price",
701 ]
702 sort_condition = _make_sort_condition_v3(sort, sortable_columns)
704 result = client.get_products(
705 filter=filter,
706 sort_condition=sort_condition,
707 limit=limit,
708 )
709 result = _make_df(result, self)
711 return result
713 def get_columns(self) -> List[str]:
714 """Retrieves the columns names of the 'products' resource.
716 Returns:
717 list[str]: A list of columns names of the 'products' resource.
718 """
719 columns = self.meta_get_columns()
720 return [column["COLUMN_NAME"] for column in columns]
722 def meta_get_tables(self, *args, **kwargs) -> dict:
723 client: BigCommerceAPIClient = self.handler.connect()
724 products_count = client.get_products_count()
725 return {
726 "table_name": self.name,
727 "table_type": "BASE TABLE",
728 "table_description": "",
729 "row_count": products_count,
730 }
732 def meta_get_columns(self, *args, **kwargs) -> List[str]:
733 return [
734 {"TABLE_NAME": "products", "COLUMN_NAME": "id", "DATA_TYPE": "INT"},
735 {"TABLE_NAME": "products", "COLUMN_NAME": "name", "DATA_TYPE": "TEXT"},
736 {"TABLE_NAME": "products", "COLUMN_NAME": "type", "DATA_TYPE": "TEXT"},
737 {"TABLE_NAME": "products", "COLUMN_NAME": "sku", "DATA_TYPE": "TEXT"},
738 {"TABLE_NAME": "products", "COLUMN_NAME": "description", "DATA_TYPE": "TEXT"},
739 {"TABLE_NAME": "products", "COLUMN_NAME": "weight", "DATA_TYPE": "INT"},
740 {"TABLE_NAME": "products", "COLUMN_NAME": "width", "DATA_TYPE": "INT"},
741 {"TABLE_NAME": "products", "COLUMN_NAME": "depth", "DATA_TYPE": "INT"},
742 {"TABLE_NAME": "products", "COLUMN_NAME": "height", "DATA_TYPE": "INT"},
743 {"TABLE_NAME": "products", "COLUMN_NAME": "price", "DATA_TYPE": "DECIMAL"},
744 {"TABLE_NAME": "products", "COLUMN_NAME": "cost_price", "DATA_TYPE": "DECIMAL"},
745 {"TABLE_NAME": "products", "COLUMN_NAME": "retail_price", "DATA_TYPE": "DECIMAL"},
746 {"TABLE_NAME": "products", "COLUMN_NAME": "sale_price", "DATA_TYPE": "DECIMAL"},
747 {"TABLE_NAME": "products", "COLUMN_NAME": "map_price", "DATA_TYPE": "DECIMAL"},
748 {"TABLE_NAME": "products", "COLUMN_NAME": "tax_class_id", "DATA_TYPE": "INT"},
749 {"TABLE_NAME": "products", "COLUMN_NAME": "product_tax_code", "DATA_TYPE": "TEXT"},
750 {"TABLE_NAME": "products", "COLUMN_NAME": "calculated_price", "DATA_TYPE": "DECIMAL"},
751 {"TABLE_NAME": "products", "COLUMN_NAME": "categories", "DATA_TYPE": "JSON"},
752 {"TABLE_NAME": "products", "COLUMN_NAME": "brand_id", "DATA_TYPE": "INT"},
753 {"TABLE_NAME": "products", "COLUMN_NAME": "option_set_id", "DATA_TYPE": "INT"},
754 {"TABLE_NAME": "products", "COLUMN_NAME": "option_set_display", "DATA_TYPE": "TEXT"},
755 {"TABLE_NAME": "products", "COLUMN_NAME": "inventory_level", "DATA_TYPE": "INT"},
756 {"TABLE_NAME": "products", "COLUMN_NAME": "inventory_warning_level", "DATA_TYPE": "INT"},
757 {"TABLE_NAME": "products", "COLUMN_NAME": "inventory_tracking", "DATA_TYPE": "TEXT"},
758 {"TABLE_NAME": "products", "COLUMN_NAME": "reviews_rating_sum", "DATA_TYPE": "DECIMAL"},
759 {"TABLE_NAME": "products", "COLUMN_NAME": "reviews_count", "DATA_TYPE": "INT"},
760 {"TABLE_NAME": "products", "COLUMN_NAME": "total_sold", "DATA_TYPE": "INT"},
761 {"TABLE_NAME": "products", "COLUMN_NAME": "fixed_cost_shipping_price", "DATA_TYPE": "DECIMAL"},
762 {"TABLE_NAME": "products", "COLUMN_NAME": "is_free_shipping", "DATA_TYPE": "BOOL"},
763 {"TABLE_NAME": "products", "COLUMN_NAME": "is_visible", "DATA_TYPE": "BOOL"},
764 {"TABLE_NAME": "products", "COLUMN_NAME": "is_featured", "DATA_TYPE": "BOOL"},
765 {"TABLE_NAME": "products", "COLUMN_NAME": "related_products", "DATA_TYPE": "JSON"},
766 {"TABLE_NAME": "products", "COLUMN_NAME": "warranty", "DATA_TYPE": "TEXT"},
767 {"TABLE_NAME": "products", "COLUMN_NAME": "bin_picking_number", "DATA_TYPE": "TEXT"},
768 {"TABLE_NAME": "products", "COLUMN_NAME": "layout_file", "DATA_TYPE": "TEXT"},
769 {"TABLE_NAME": "products", "COLUMN_NAME": "upc", "DATA_TYPE": "TEXT"},
770 {"TABLE_NAME": "products", "COLUMN_NAME": "mpn", "DATA_TYPE": "TEXT"},
771 {"TABLE_NAME": "products", "COLUMN_NAME": "gtin", "DATA_TYPE": "TEXT"},
772 {"TABLE_NAME": "products", "COLUMN_NAME": "date_last_imported", "DATA_TYPE": "DATETIME"},
773 {"TABLE_NAME": "products", "COLUMN_NAME": "search_keywords", "DATA_TYPE": "TEXT"},
774 {"TABLE_NAME": "products", "COLUMN_NAME": "availability", "DATA_TYPE": "TEXT"},
775 {"TABLE_NAME": "products", "COLUMN_NAME": "availability_description", "DATA_TYPE": "TEXT"},
776 {"TABLE_NAME": "products", "COLUMN_NAME": "gift_wrapping_options_type", "DATA_TYPE": "TEXT"},
777 {"TABLE_NAME": "products", "COLUMN_NAME": "gift_wrapping_options_list", "DATA_TYPE": "JSON"},
778 {"TABLE_NAME": "products", "COLUMN_NAME": "sort_order", "DATA_TYPE": "INT"},
779 {"TABLE_NAME": "products", "COLUMN_NAME": "condition", "DATA_TYPE": "TEXT"},
780 {"TABLE_NAME": "products", "COLUMN_NAME": "is_condition_shown", "DATA_TYPE": "BOOL"},
781 {"TABLE_NAME": "products", "COLUMN_NAME": "order_quantity_minimum", "DATA_TYPE": "INT"},
782 {"TABLE_NAME": "products", "COLUMN_NAME": "order_quantity_maximum", "DATA_TYPE": "INT"},
783 {"TABLE_NAME": "products", "COLUMN_NAME": "page_title", "DATA_TYPE": "TEXT"},
784 {"TABLE_NAME": "products", "COLUMN_NAME": "meta_keywords", "DATA_TYPE": "JSON"},
785 {"TABLE_NAME": "products", "COLUMN_NAME": "meta_description", "DATA_TYPE": "VARTEXTCHAR"},
786 {"TABLE_NAME": "products", "COLUMN_NAME": "date_created", "DATA_TYPE": "DATETIME"},
787 {"TABLE_NAME": "products", "COLUMN_NAME": "date_modified", "DATA_TYPE": "DATETIME"},
788 {"TABLE_NAME": "products", "COLUMN_NAME": "view_count", "DATA_TYPE": "INT"},
789 {"TABLE_NAME": "products", "COLUMN_NAME": "preorder_release_date", "DATA_TYPE": "DATETIME"},
790 {"TABLE_NAME": "products", "COLUMN_NAME": "preorder_message", "DATA_TYPE": "DECIMAL"},
791 {"TABLE_NAME": "products", "COLUMN_NAME": "is_preorder_only", "DATA_TYPE": "BOOL"},
792 {"TABLE_NAME": "products", "COLUMN_NAME": "is_price_hidden", "DATA_TYPE": "BOOL"},
793 {"TABLE_NAME": "products", "COLUMN_NAME": "price_hidden_label", "DATA_TYPE": "DECIMAL"},
794 {"TABLE_NAME": "products", "COLUMN_NAME": "custom_url", "DATA_TYPE": "JSON"},
795 {"TABLE_NAME": "products", "COLUMN_NAME": "base_variant_id", "DATA_TYPE": "INT"},
796 {"TABLE_NAME": "products", "COLUMN_NAME": "open_graph_type", "DATA_TYPE": "TEXT"},
797 {"TABLE_NAME": "products", "COLUMN_NAME": "open_graph_title", "DATA_TYPE": "TEXT"},
798 {"TABLE_NAME": "products", "COLUMN_NAME": "open_graph_description", "DATA_TYPE": "TEXT"},
799 {"TABLE_NAME": "products", "COLUMN_NAME": "open_graph_use_meta_description", "DATA_TYPE": "BOOL"},
800 {"TABLE_NAME": "products", "COLUMN_NAME": "open_graph_use_product_name", "DATA_TYPE": "BOOL"},
801 {"TABLE_NAME": "products", "COLUMN_NAME": "open_graph_use_image", "DATA_TYPE": "BOOL"},
802 ]
805class BigCommerceCustomersTable(MetaAPIResource):
806 """
807 The table abstraction for the 'customers' resource of the BigCommerce API.
808 """
810 name = "customers"
812 def list(
813 self,
814 conditions: list[FilterCondition] = None,
815 limit: int = None,
816 sort: list[SortColumn] = None,
817 **kwargs,
818 ):
819 """
820 Executes a parsed SELECT SQL query on the 'customers' resource of the BigCommerce API.
822 Args:
823 conditions (list[FilterCondition]): The list of parsed filter conditions.
824 limit (int): The maximum number of records to return.
825 sort (list[SortColumn]): The list of parsed sort columns.
827 Returns:
828 pd.DataFrame: The resulting DataFrame.
829 """
830 # doc: https://developer.bigcommerce.com/docs/rest-management/customers
831 client: BigCommerceAPIClient = self.handler.connect()
833 simple_op_map = {
834 ("id", FilterOperator.EQUAL): "id:in", # custom filter
835 ("id", FilterOperator.IN): "id:in",
836 ("company", FilterOperator.EQUAL): "company:in", # custom filter
837 ("company", FilterOperator.IN): "company:in",
838 ("customer_group_id", FilterOperator.EQUAL): "customer_group_id:in", # custom filter
839 ("customer_group_id", FilterOperator.IN): "customer_group_id:in",
840 ("date_created", FilterOperator.EQUAL): "date_created",
841 ("date_created", FilterOperator.LESS_THAN): "date_created:max",
842 ("date_created", FilterOperator.GREATER_THAN): "date_created:min",
843 ("date_modified", FilterOperator.EQUAL): "date_modified",
844 ("date_modified", FilterOperator.LESS_THAN): "date_modified:max",
845 ("date_modified", FilterOperator.GREATER_THAN): "date_modified:min",
846 ("email", FilterOperator.EQUAL): "email:in", # custom filter
847 ("email", FilterOperator.IN): "email:in",
848 ("name", FilterOperator.IN): "name:in",
849 ("name", FilterOperator.LIKE): "name:like",
850 ("phone", FilterOperator.EQUAL): "phone:in", # custom filter
851 ("phone", FilterOperator.IN): "phone:in",
852 ("registration_ip_address", FilterOperator.EQUAL): "registration_ip_address:in", # custom filter
853 ("registration_ip_address", FilterOperator.IN): "registration_ip_address:in",
854 }
856 filter = _make_filter(conditions, simple_op_map)
858 sortable_columns = ["date_created", "last_name", "date_modified"]
859 sort_condition = _make_sort_condition_v2(sort, sortable_columns)
861 result = client.get_customers(
862 filter=filter,
863 sort_condition=sort_condition,
864 limit=limit,
865 )
866 result = _make_df(result, self)
868 # 'name' is added to use server-side filtering
869 result["name"] = result["first_name"] + " " + result["last_name"]
871 return result
873 def get_columns(self) -> List[str]:
874 """Retrieves the columns names of the 'customers' resource.
876 Returns:
877 list[str]: A list of columns names of the 'customers' resource.
878 """
879 columns = self.meta_get_columns()
880 return [column["COLUMN_NAME"] for column in columns]
882 def meta_get_tables(self, table_name: str) -> dict:
883 client: BigCommerceAPIClient = self.handler.connect()
884 customers_count = client.get_customers_count()
885 return {
886 "table_name": self.name,
887 "table_type": "BASE TABLE",
888 "table_description": "",
889 "row_count": customers_count,
890 }
892 def meta_get_columns(self, *args, **kwargs) -> List[str]:
893 return [
894 {"TABLE_NAME": "customers", "COLUMN_NAME": "id", "DATA_TYPE": "INT"},
895 {"TABLE_NAME": "customers", "COLUMN_NAME": "authentication", "DATA_TYPE": "JSON"},
896 {"TABLE_NAME": "customers", "COLUMN_NAME": "company", "DATA_TYPE": "VARCHAR"},
897 {"TABLE_NAME": "customers", "COLUMN_NAME": "customer_group_id", "DATA_TYPE": "INT"},
898 {"TABLE_NAME": "customers", "COLUMN_NAME": "email", "DATA_TYPE": "VARCHAR"},
899 {"TABLE_NAME": "customers", "COLUMN_NAME": "first_name", "DATA_TYPE": "VARCHAR"},
900 {"TABLE_NAME": "customers", "COLUMN_NAME": "last_name", "DATA_TYPE": "VARCHAR"},
901 # 'name' is added to use server-side filtering: first_name + last_name
902 {"TABLE_NAME": "customers", "COLUMN_NAME": "name", "DATA_TYPE": "VARCHAR"},
903 {"TABLE_NAME": "customers", "COLUMN_NAME": "notes", "DATA_TYPE": "VARCHAR"},
904 {"TABLE_NAME": "customers", "COLUMN_NAME": "phone", "DATA_TYPE": "VARCHAR"},
905 {"TABLE_NAME": "customers", "COLUMN_NAME": "registration_ip_address", "DATA_TYPE": "VARCHAR"},
906 {"TABLE_NAME": "customers", "COLUMN_NAME": "tax_exempt_category", "DATA_TYPE": "VARCHAR"},
907 {"TABLE_NAME": "customers", "COLUMN_NAME": "date_created", "DATA_TYPE": "DATETIME"},
908 {"TABLE_NAME": "customers", "COLUMN_NAME": "date_modified", "DATA_TYPE": "DATETIME"},
909 {
910 "TABLE_NAME": "customers",
911 "COLUMN_NAME": "accepts_product_review_abandoned_cart_emails",
912 "DATA_TYPE": "BOOL",
913 },
914 {"TABLE_NAME": "customers", "COLUMN_NAME": "origin_channel_id", "DATA_TYPE": "INT"},
915 {"TABLE_NAME": "customers", "COLUMN_NAME": "channel_ids", "DATA_TYPE": "JSON"},
916 ]
919class BigCommerceCategoriesTable(MetaAPIResource):
920 """
921 The table abstraction for the 'categories' resource of the BigCommerce API.
922 """
924 name = "categories"
926 def list(
927 self,
928 conditions: list[FilterCondition] = None,
929 limit: int = None,
930 targets: list[str] = None,
931 **kwargs,
932 ):
933 """Executes a parsed SELECT SQL query on the 'categories' resource of the BigCommerce API.
935 Args:
936 conditions (list[FilterCondition]): The list of parsed filter conditions.
937 limit (int): The maximum number of records to return.
938 targets (list[str]): The list of target columns to return.
940 Returns:
941 pd.DataFrame: The resulting DataFrame.
942 """
943 # doc: https://developer.bigcommerce.com/docs/rest-catalog/category-trees/categories#get-all-categories
944 client: BigCommerceAPIClient = self.handler.connect()
946 simple_op_map = {
947 ("category_id", FilterOperator.EQUAL): "category_id:in", # custom filter
948 ("category_id", FilterOperator.IN): "category_id:in",
949 ("category_id", FilterOperator.NOT_IN): "category_id:not_in",
950 ("tree_id", FilterOperator.EQUAL): "tree_id:in", # custom filter
951 ("tree_id", FilterOperator.IN): "tree_id:in",
952 ("tree_id", FilterOperator.NOT_IN): "tree_id:not_in",
953 ("parent_id", FilterOperator.EQUAL): "parent_id:in", # custom filter
954 ("parent_id", FilterOperator.IN): "parent_id:in",
955 ("parent_id", FilterOperator.NOT_IN): "parent_id:not_in",
956 ("page_title", FilterOperator.EQUAL): "page_title",
957 ("page_title", FilterOperator.LIKE): "page_title:like",
958 ("name", FilterOperator.EQUAL): "name",
959 ("name", FilterOperator.LIKE): "name:like",
960 ("keyword", FilterOperator.EQUAL): "keyword",
961 ("is_visible", FilterOperator.EQUAL): "is_visible",
962 }
964 filter = _make_filter(conditions, simple_op_map)
966 if targets:
967 available_columns = self.get_columns()
968 for column_name in targets:
969 if column_name not in available_columns:
970 raise ValueError(f"Field '{column_name}' does not exists")
971 filter["include_fields"] = ",".join(targets)
973 result = client.get_categories(
974 filter=filter,
975 limit=limit,
976 )
977 result = _make_df(result, self)
979 return result
981 def get_columns(self) -> List[str]:
982 """Retrieves the columns names of the 'categories' resource.
984 Returns:
985 list[str]: A list of columns names of the 'categories' resource.
986 """
987 columns = self.meta_get_columns()
988 return [column["COLUMN_NAME"] for column in columns]
990 def meta_get_tables(self, *args, **kwargs) -> dict:
991 client: BigCommerceAPIClient = self.handler.connect()
992 categories_count = client.get_categories_count()
993 return {
994 "table_name": self.name,
995 "table_type": "BASE TABLE",
996 "table_description": "",
997 "row_count": categories_count,
998 }
1000 def meta_get_columns(self, *args, **kwargs) -> List[str]:
1001 return [
1002 {"TABLE_NAME": "categories", "COLUMN_NAME": "category_id", "DATA_TYPE": "INT"},
1003 {"TABLE_NAME": "categories", "COLUMN_NAME": "parent_id", "DATA_TYPE": "INT"},
1004 {"TABLE_NAME": "categories", "COLUMN_NAME": "tree_id", "DATA_TYPE": "INT"},
1005 {"TABLE_NAME": "categories", "COLUMN_NAME": "name", "DATA_TYPE": "TEXT"},
1006 {"TABLE_NAME": "categories", "COLUMN_NAME": "description", "DATA_TYPE": "TEXT"},
1007 {"TABLE_NAME": "categories", "COLUMN_NAME": "views", "DATA_TYPE": "INT"},
1008 {"TABLE_NAME": "categories", "COLUMN_NAME": "sort_order", "DATA_TYPE": "INT"},
1009 {"TABLE_NAME": "categories", "COLUMN_NAME": "page_title", "DATA_TYPE": "TEXT"},
1010 {"TABLE_NAME": "categories", "COLUMN_NAME": "search_keywords", "DATA_TYPE": "TEXT"},
1011 {"TABLE_NAME": "categories", "COLUMN_NAME": "meta_keywords", "DATA_TYPE": "JSON"},
1012 {"TABLE_NAME": "categories", "COLUMN_NAME": "meta_description", "DATA_TYPE": "TEXT"},
1013 {"TABLE_NAME": "categories", "COLUMN_NAME": "layout_file", "DATA_TYPE": "TEXT"},
1014 {"TABLE_NAME": "categories", "COLUMN_NAME": "is_visible", "DATA_TYPE": "BOOL"},
1015 {"TABLE_NAME": "categories", "COLUMN_NAME": "default_product_sort", "DATA_TYPE": "TEXT"},
1016 {"TABLE_NAME": "categories", "COLUMN_NAME": "url", "DATA_TYPE": "JSON"},
1017 {"TABLE_NAME": "categories", "COLUMN_NAME": "image_url", "DATA_TYPE": "VARCHAR"},
1018 ]
1021class BigCommercePickupsTable(MetaAPIResource):
1022 """
1023 The table abstraction for the 'pickups' resource of the BigCommerce API.
1024 """
1026 name = "pickups"
1028 def list(
1029 self,
1030 conditions: List[FilterCondition] = None,
1031 limit: int = None,
1032 **kwargs,
1033 ):
1034 """Executes a parsed SELECT SQL query on the 'pickups' resource of the BigCommerce API.
1036 Args:
1037 conditions (List[FilterCondition]): The list of parsed filter conditions.
1038 limit (int): The maximum number of records to return.
1040 Returns:
1041 pd.DataFrame: The resulting DataFrame.
1042 """
1043 client: BigCommerceAPIClient = self.handler.connect()
1045 simple_op_map = {
1046 ("order_id", FilterOperator.EQUAL): "order_id:in", # custom filter
1047 ("order_id", FilterOperator.IN): "order_id:in",
1048 ("pickup_id", FilterOperator.EQUAL): "pickup_id:in", # custom filter
1049 ("pickup_id", FilterOperator.IN): "pickup_id:in",
1050 }
1052 filter = _make_filter(conditions, simple_op_map)
1054 result = client.get_pickups(
1055 filter=filter,
1056 limit=limit,
1057 )
1058 result = _make_df(result, self)
1060 return result
1062 def get_columns(self) -> List[str]:
1063 """Retrieves the columns names of the 'pickups' resource.
1065 Returns:
1066 list[str]: A list of columns names of the 'pickups' resource.
1067 """
1068 columns = self.meta_get_columns()
1069 return [column["COLUMN_NAME"] for column in columns]
1071 def meta_get_tables(self, *args, **kwargs) -> dict:
1072 client: BigCommerceAPIClient = self.handler.connect()
1073 pickups_count = client.get_pickups_count()
1074 return {
1075 "table_name": self.name,
1076 "table_type": "BASE TABLE",
1077 "table_description": "",
1078 "row_count": pickups_count,
1079 }
1081 def meta_get_columns(self, *args, **kwargs):
1082 return [
1083 {"TABLE_NAME": "pickups", "COLUMN_NAME": "id", "DATA_TYPE": "INT"},
1084 {"TABLE_NAME": "pickups", "COLUMN_NAME": "pickup_method_id", "DATA_TYPE": "INT"},
1085 {"TABLE_NAME": "pickups", "COLUMN_NAME": "order_id", "DATA_TYPE": "INT"},
1086 {"TABLE_NAME": "pickups", "COLUMN_NAME": "ready_at", "DATA_TYPE": "DATETIME"},
1087 {"TABLE_NAME": "pickups", "COLUMN_NAME": "created_at", "DATA_TYPE": "DATETIME"},
1088 {"TABLE_NAME": "pickups", "COLUMN_NAME": "updated_at", "DATA_TYPE": "DATETIME"},
1089 {"TABLE_NAME": "pickups", "COLUMN_NAME": "pickup_items", "DATA_TYPE": "JSON"},
1090 ]
1093class BigCommercePromotionsTable(MetaAPIResource):
1094 """
1095 The table abstraction for the 'promotions' resource of the BigCommerce API.
1096 """
1098 name = "promotions"
1100 def list(
1101 self,
1102 conditions: list[FilterCondition] = None,
1103 limit: int = None,
1104 sort: list[SortColumn] = None,
1105 targets: list[str] = None,
1106 **kwargs,
1107 ):
1108 """Executes a parsed SELECT SQL query on the 'promotions' resource of the BigCommerce API.
1110 Args:
1111 conditions (list[FilterCondition]): The list of parsed filter conditions.
1112 limit (int): The maximum number of records to return.
1113 sort (list[SortColumn]): The list of parsed sort columns.
1114 targets (list[str]): The list of target columns to return.
1116 Returns:
1117 pd.DataFrame: The resulting DataFrame.
1118 """
1119 client: BigCommerceAPIClient = self.handler.connect()
1121 simple_op_map = {
1122 ("id", FilterOperator.EQUAL): "id",
1123 ("name", FilterOperator.EQUAL): "name",
1124 ("currency_code", FilterOperator.EQUAL): "currency_code",
1125 ("redemption_type", FilterOperator.EQUAL): "redemption_type",
1126 ("status", FilterOperator.EQUAL): "status",
1127 ("channels", FilterOperator.EQUAL): "channels", # custom filter
1128 ("channels", FilterOperator.IN): "channels",
1129 }
1131 filter = _make_filter(conditions, simple_op_map)
1133 sortable_columns = ["id", "name", "start_date", "priority"]
1134 sort_condition = _make_sort_condition_v3(sort, sortable_columns)
1136 result = client.get_promotions(
1137 filter=filter,
1138 sort_condition=sort_condition,
1139 limit=limit,
1140 )
1141 result = _make_df(result, self)
1143 return result
1145 def get_columns(self) -> List[str]:
1146 """Retrieves the columns names of the 'promotions' resource.
1148 Returns:
1149 list[str]: A list of columns names of the 'promotions' resource.
1150 """
1151 columns = self.meta_get_columns()
1152 return [column["COLUMN_NAME"] for column in columns]
1154 def meta_get_tables(self, *args, **kwargs) -> dict:
1155 client: BigCommerceAPIClient = self.handler.connect()
1156 promotions_count = client.get_promotions_count()
1157 return {
1158 "table_name": self.name,
1159 "table_type": "BASE TABLE",
1160 "table_description": "",
1161 "row_count": promotions_count,
1162 }
1164 def meta_get_columns(self, *args, **kwargs) -> List[str]:
1165 return [
1166 {"TABLE_NAME": "promotions", "COLUMN_NAME": "id", "DATA_TYPE": "INT"},
1167 {"TABLE_NAME": "promotions", "COLUMN_NAME": "redemption_type", "DATA_TYPE": "TEXT"},
1168 {"TABLE_NAME": "promotions", "COLUMN_NAME": "name", "DATA_TYPE": "TEXT"},
1169 {"TABLE_NAME": "promotions", "COLUMN_NAME": "display_name", "DATA_TYPE": "TEXT"},
1170 {"TABLE_NAME": "promotions", "COLUMN_NAME": "channels", "DATA_TYPE": "JSON"},
1171 {"TABLE_NAME": "promotions", "COLUMN_NAME": "customer", "DATA_TYPE": "JSON"},
1172 {"TABLE_NAME": "promotions", "COLUMN_NAME": "rules", "DATA_TYPE": "JSON"},
1173 {"TABLE_NAME": "promotions", "COLUMN_NAME": "current_uses", "DATA_TYPE": "INT"},
1174 {"TABLE_NAME": "promotions", "COLUMN_NAME": "max_uses", "DATA_TYPE": "INT"},
1175 {"TABLE_NAME": "promotions", "COLUMN_NAME": "status", "DATA_TYPE": "TEXT"},
1176 {"TABLE_NAME": "promotions", "COLUMN_NAME": "start_date", "DATA_TYPE": "DATETIME"},
1177 {"TABLE_NAME": "promotions", "COLUMN_NAME": "end_date", "DATA_TYPE": "DATETIME"},
1178 {"TABLE_NAME": "promotions", "COLUMN_NAME": "stop", "DATA_TYPE": "BOOL"},
1179 {"TABLE_NAME": "promotions", "COLUMN_NAME": "can_be_used_with_other_promotions", "DATA_TYPE": "BOOL"},
1180 {"TABLE_NAME": "promotions", "COLUMN_NAME": "currency_code", "DATA_TYPE": "TEXT"},
1181 {"TABLE_NAME": "promotions", "COLUMN_NAME": "notifications", "DATA_TYPE": "JSON"},
1182 {"TABLE_NAME": "promotions", "COLUMN_NAME": "shipping_address", "DATA_TYPE": "JSON"},
1183 {"TABLE_NAME": "promotions", "COLUMN_NAME": "schedule", "DATA_TYPE": "JSON"},
1184 {"TABLE_NAME": "promotions", "COLUMN_NAME": "created_from", "DATA_TYPE": "TEXT"},
1185 ]
1188class BigCommerceWishlistsTable(MetaAPIResource):
1189 """
1190 The table abstraction for the 'wishlists' resource of the BigCommerce API.
1191 """
1193 name = "wishlists"
1195 def list(
1196 self,
1197 conditions: list[FilterCondition] = None,
1198 limit: int = None,
1199 **kwargs,
1200 ):
1201 """Executes a parsed SELECT SQL query on the 'wishlists' resource of the BigCommerce API.
1203 Args:
1204 conditions (list[FilterCondition]): The list of parsed filter conditions.
1205 limit (int): The maximum number of records to return.
1207 Returns:
1208 pd.DataFrame: The resulting DataFrame.
1209 """
1210 client: BigCommerceAPIClient = self.handler.connect()
1212 simple_op_map = {
1213 ("customer_id", FilterOperator.IN): "customer_id:in",
1214 }
1216 filter = _make_filter(conditions, simple_op_map)
1218 result = client.get_wishlists(
1219 filter=filter,
1220 limit=limit,
1221 )
1222 result = _make_df(result, self)
1224 return result
1226 def get_columns(self) -> List[str]:
1227 """Retrieves the columns names of the 'wishlists' resource.
1229 Returns:
1230 list[str]: A list of columns names of the 'wishlists' resource.
1231 """
1232 columns = self.meta_get_columns()
1233 return [column["COLUMN_NAME"] for column in columns]
1235 def meta_get_tables(self, *args, **kwargs) -> dict:
1236 client: BigCommerceAPIClient = self.handler.connect()
1237 wishlists_count = client.get_wishlists_count()
1238 return {
1239 "table_name": self.name,
1240 "table_type": "BASE TABLE",
1241 "table_description": "",
1242 "row_count": wishlists_count,
1243 }
1245 def meta_get_columns(self, *args, **kwargs) -> List[str]:
1246 return [
1247 {"TABLE_NAME": "wishlists", "COLUMN_NAME": "id", "DATA_TYPE": "INT"},
1248 {"TABLE_NAME": "wishlists", "COLUMN_NAME": "customer_id", "DATA_TYPE": "INT"},
1249 {"TABLE_NAME": "wishlists", "COLUMN_NAME": "name", "DATA_TYPE": "TEXT"},
1250 {"TABLE_NAME": "wishlists", "COLUMN_NAME": "is_public", "DATA_TYPE": "BOOL"},
1251 {"TABLE_NAME": "wishlists", "COLUMN_NAME": "token", "DATA_TYPE": "TEXT"},
1252 {"TABLE_NAME": "wishlists", "COLUMN_NAME": "items", "DATA_TYPE": "JSON"},
1253 ]
1256class BigCommerceSegmentsTable(MetaAPIResource):
1257 """
1258 The table abstraction for the 'segments' (customer segmentation) resource of the BigCommerce API.
1259 """
1261 name = "segments"
1263 def list(
1264 self,
1265 conditions: list[FilterCondition] = None,
1266 limit: int = None,
1267 **kwargs,
1268 ):
1269 """Executes a parsed SELECT SQL query on the 'segments' resource of the BigCommerce API.
1271 Args:
1272 conditions (list[FilterCondition]): The list of parsed filter conditions.
1273 limit (int): The maximum number of records to return.
1275 Returns:
1276 pd.DataFrame: The resulting DataFrame.
1277 """
1278 client: BigCommerceAPIClient = self.handler.connect()
1280 simple_op_map = {
1281 ("id", FilterOperator.IN): "id:in",
1282 }
1284 filter = _make_filter(conditions, simple_op_map)
1286 result = client.get_segments(
1287 filter=filter,
1288 limit=limit,
1289 )
1290 result = _make_df(result, self)
1292 return result
1294 def get_columns(self) -> List[str]:
1295 """Retrieves the columns names of the 'segments' resource.
1297 Returns:
1298 list[str]: A list of columns names of the 'segments' resource.
1299 """
1300 columns = self.meta_get_columns()
1301 return [column["COLUMN_NAME"] for column in columns]
1303 def meta_get_tables(self, *args, **kwargs) -> dict:
1304 client: BigCommerceAPIClient = self.handler.connect()
1305 segments_count = client.get_segments_count()
1306 return {
1307 "table_name": self.name,
1308 "table_type": "BASE TABLE",
1309 "table_description": "",
1310 "row_count": segments_count,
1311 }
1313 def meta_get_columns(self, *args, **kwargs) -> List[str]:
1314 return [
1315 {"TABLE_NAME": "segments", "COLUMN_NAME": "id", "DATA_TYPE": "TEXT"},
1316 {"TABLE_NAME": "segments", "COLUMN_NAME": "name", "DATA_TYPE": "TEXT"},
1317 {"TABLE_NAME": "segments", "COLUMN_NAME": "description", "DATA_TYPE": "TEXT"},
1318 {"TABLE_NAME": "segments", "COLUMN_NAME": "created_at", "DATA_TYPE": "DATETIME"},
1319 {"TABLE_NAME": "segments", "COLUMN_NAME": "updated_at", "DATA_TYPE": "DATETIME"},
1320 ]
1323class BigCommerceBrandsTable(MetaAPIResource):
1324 """
1325 The table abstraction for the 'brands' resource of the BigCommerce API.
1326 """
1328 name = "brands"
1330 def list(
1331 self,
1332 conditions: list[FilterCondition] = None,
1333 limit: int = None,
1334 sort: list[SortColumn] = None,
1335 targets: list[str] = None,
1336 **kwargs,
1337 ):
1338 """Executes a parsed SELECT SQL query on the 'brands' resource of the BigCommerce API.
1340 Args:
1341 conditions (list[FilterCondition]): The list of parsed filter conditions.
1342 limit (int): The maximum number of records to return.
1343 sort (list[SortColumn]): The list of parsed sort columns.
1344 targets (list[str]): The list of target columns to return.
1346 Returns:
1347 pd.DataFrame: The resulting DataFrame.
1348 """
1349 client: BigCommerceAPIClient = self.handler.connect()
1351 simple_op_map = {
1352 ("id", FilterOperator.EQUAL): "id",
1353 ("id", FilterOperator.IN): "id:in",
1354 ("id", FilterOperator.NOT_IN): "id:not_in",
1355 ("id", FilterOperator.GREATER_THAN): "id:greater",
1356 ("id", FilterOperator.LESS_THAN): "id:less",
1357 ("id", FilterOperator.GREATER_THAN_OR_EQUAL): "id:min",
1358 ("id", FilterOperator.LESS_THAN_OR_EQUAL): "id:max",
1359 ("name", FilterOperator.EQUAL): "name",
1360 ("name", FilterOperator.LIKE): "name:like",
1361 ("page_title", FilterOperator.EQUAL): "page_title",
1362 }
1364 filter = _make_filter(conditions, simple_op_map)
1366 if targets:
1367 available_columns = self.get_columns()
1368 for column_name in targets:
1369 if column_name not in available_columns:
1370 raise ValueError(f"Field '{column_name}' does not exists")
1371 filter["include_fields"] = ",".join(targets)
1373 sortable_columns = ["name"]
1374 sort_condition = _make_sort_condition_v3(sort, sortable_columns)
1376 result = client.get_brands(
1377 filter=filter,
1378 sort_condition=sort_condition,
1379 limit=limit,
1380 )
1381 result = _make_df(result, self)
1383 return result
1385 def get_columns(self) -> List[str]:
1386 """Retrieves the columns names of the 'brands' resource.
1388 Returns:
1389 list[str]: A list of columns names of the 'brands' resource.
1390 """
1391 columns = self.meta_get_columns()
1392 return [column["COLUMN_NAME"] for column in columns]
1394 def meta_get_tables(self, *args, **kwargs) -> dict:
1395 client: BigCommerceAPIClient = self.handler.connect()
1396 brands_count = client.get_brands_count()
1397 return {
1398 "table_name": self.name,
1399 "table_type": "BASE TABLE",
1400 "table_description": "",
1401 "row_count": brands_count,
1402 }
1404 def meta_get_columns(self, *args, **kwargs) -> List[str]:
1405 return [
1406 {"TABLE_NAME": "brands", "COLUMN_NAME": "id", "DATA_TYPE": "INT"},
1407 {"TABLE_NAME": "brands", "COLUMN_NAME": "name", "DATA_TYPE": "TEXT"},
1408 {"TABLE_NAME": "brands", "COLUMN_NAME": "page_title", "DATA_TYPE": "TEXT"},
1409 {"TABLE_NAME": "brands", "COLUMN_NAME": "meta_keywords", "DATA_TYPE": "JSON"},
1410 {"TABLE_NAME": "brands", "COLUMN_NAME": "meta_description", "DATA_TYPE": "TEXT"},
1411 {"TABLE_NAME": "brands", "COLUMN_NAME": "search_keywords", "DATA_TYPE": "TEXT"},
1412 {"TABLE_NAME": "brands", "COLUMN_NAME": "image_url", "DATA_TYPE": "TEXT"},
1413 {"TABLE_NAME": "brands", "COLUMN_NAME": "custom_url", "DATA_TYPE": "JSON"},
1414 ]