Tuesday, October 20, 2020

More SQL Commands

 More SQL Commands


SELECT DISTINCT column_name_1, column_name_2


is useful for grabbing all the unique fields elements and is often used with a


WHERE BETWEEN/LIKE/IN with operators >=, <=, <> and connectors OR/AND/NOT.


BETWEEN: inclusive of the start and endpoints

LIKE: pattern matching such as '%s' to find fields that end in the letter s.

IN: find members of a set. Think of it as multiple ORs.


SELECT * FROM table_name ORDER BY  column1, column2

first orders ASC by default on column1, then any ties are broken by sorting on column2. Note: you need to put the ASC or DESC after each column name.


INSERT INTO on less than all columns of a table will put a NULL into any column not specified with the command. So if you have a table with 10 columns, but only insert data into 4 of the columns, the 6 unassigned columns would automatically be filled with NULL (assuming the column is declared to be nullable in the design).


Test for NULL with IS NULL or IS NOT NULL.


UPDATE table_name SET column1=value1, column2=value2 WHERE conditions


This is a good way to change the timestamps that cross over the midnight boundary because your timezone is different than UTC.


SELECT MIN/MAX() AS variable_name FROM table_name

In MySQL Workbench, functions like MIN/MAX() will appear in gray.


produces a new variable.


COUNT/AVG/SUM() does not include any NULLs.


LIKE can take wildcards:


                                    % -> 0 more more characters in that position of the pattern

                                    _ -> a single placeholder for a character

                                        % ... % -> the ... in any position

Those wildcards also work against an INT.


ESCAPE: allows you to use special characters as literals in the pattern. For example, LIKE '543#%' means match 543% exactly, not wildcarded.


A powerful function in MySQL is REGEXP_LIKE() because it permits traditional regular expression syntax and pattern matching.


NOT IN is useful when followed by (SELECT ...)


(NOT) BETWEEN accepts numbers, text or dates and is often used with IN. If you are using it for dates, use a pair of # to bracket the date.


ALIAS only exists for the duration of the query.


UNION combines two or more SELECTs.


1. Each SELECT needs the same number of columns

2. All columns must be of the similar type to what it is lined up with.


UNION ALL will preserve multiplicity.


Resultant column names will inherit from the names of the columns from the first SELECT clause.


GROUP BY breaks down the various elements in a column into N subsets, gathered into any nth subset all together, for N distinct field values available.


HAVING is a pseudo-where to go with aggregation keywords such as GROUP BY, ORDER BY


EXISTS returns a boolean if any row survives the joins and conditions.


ANY and ALL are restricted to use with WHERE or HAVING only. ANY or ALL must be preceded by a compariosn operator such as > or <>.


SELECT INTO copies data into a new table. Supplementing with AS will provide new column names.


SELECT * INTO backupTable FROM old_table creates a copy in the same schema.


SELECT * INTO backupTable IN 'BackupSchema.mdb' FROM old_table creates a copy in a different schema.


TRICK: Create a new empty table with the same column design:


add WHERE 1=0 to force no matching.


INSERT INTO table_name(col names) SELECT another_table's columns FROM another_table


CASE

WHEN cond1 THEN res1

..........

ELSE default_res

END AS new column

Important: If all WHENs fail and there is no ELSE, then a NULL is returned. Otherwise it quits at the first true condition.

If there is no AS clause after the END keyword, the column is named in very verbose ugly way with all of the WHENs' result string concatened together.??!!

IFNULL() or COALESCE() allow you to substitute any non-null values for NULL in math calculations.


Stored Procedures:

CREATE PROCEDURE proc_name

AS

sql-commands

GO


Run it with:


EXEC proc_name


Procedures can receive parameters too.


use @param_name data_type


EXEC proc_name @param_name='...'


and add more parameters with commas


Comments are either -- or /* */


See the full list of SQL operator symbols at w3schools.com/sql/sql_operators.asp























Monday, October 19, 2020

eBay Finding API issues

 eBay Finding API issues


1. eBay on-line docs say that the string length for the primary and secondary category names are max at 30 characters but I found several names longer than 30.

2. A watchCount field is populated in the response object under the listingInfo grouping. This is not documented by eBay.

3. eBay has substituted the erstwhile CurrentPriceLowest Sort By Order argument with PricePlusShippingLowest.

4. Sometimes the API call hangs even after 3 retries. Change the timeout setting to be longer than the 20 second default in the Connection argument list.

5. A primary category number may have more than one primary category name. Fore example, primary category number 7921 is both "Collections, Lots" and "Collections/Mixtures".



Thursday, October 1, 2020

Database enhancements and updates

 

Database Enhancements and Updates


1. To the flat Excel CSV files I'm now saving the command line options on the metadata header line.


2. I also now added the primary and secondary category name descriptors to both the flat files and the database files, as well as saving the sort by order enum values database files and the metadata.


3. In Task Scheduler I setup runs spaced 20 minutes apart to do current price highest,  watch count decrease sort or price plus shipping lowest; all three sort by order choices will be in the same flat file and in the same database and metadata table.


WARNING: If you have database writing errors to the MySQL server this will often times hang the table with a lock and also leave a hanging external connection to DB server from your python script. To remedy this go to the MySQL workbench, choose "Server" from the main menu and then choose "Client connections". Look for a message that says something like "waiting for metadata lock process" and then using a right mouse click with the context pop-up menu either do a "kill query" or "kill connection". Do the same for any Windows users that show a connection; do a kill connection. If you don't do this, any select/delete/truncate SQL commands will hang or timeout at the Query tab in my MySQL Workbench. Trying to issue commit and rollback commands won't help either.


WARNING: Note that using an unsigned property for a float or decimal column datatype in MySQL is deprecated as of version 8.0. When you are creating a decimal database field type with a plus or minus sign allow an extra digit otherwise you should expect a Data Type error from Python saying "Out of Range" if you did not allocate enough space.

decimal(x,y) where x is ALL of the digits along with any expected sign (+ or -) and y is only the digits to the RIGHT of the decimal point.


example: -123.45 would be decimal(6,2)


WARNING: If you get an error in the python while using the MySQL connector module functions that goes something like a "your column name" not in "your field name", that likely means you are trying to write values to the wrong table.

4. I ended up changing bestOfferEnabled values as false or true with 0 or 1 in the database.


5. It turns out the JSON object does return a watch count for all sorting types; it's under the item listingInfo dictionary field. I save watch counts on all my queries however if the watch count is such that no one is watching, eBay's SDK API will not give you a zero in the dictionary for listingInfo; instead the watch count field will be completely missing. So you're going to have to accommodations for that by making sure that you define the watchCount column in MySQL to be nullable and have the python script pass in None.


CAUTION: when using ALTER to change enum values, make sure to kill all connections and queries first, even for the host. Then after you do your ALTER command, truncate everything in the modified table or otherwise your python script may raise an exception for a hung table or database.


Here is the newest EER showing the latest database design