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

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".

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

More About MySQL Workbench

 More About 

MySQL Workbench

I have revisited my python script to add in a lot of error handling functions and consistency checks versus standards. I decided to leave the 16-member list describing the 16 World Regions from the SQL code and instead put it into the python code. This will keep the database to a minimum number of columns and SQL statements. Also, I have - when talking to the DB server - more flexibility using the Python language eBaySDK. In case eBay changes for example "British Colonies and Territories" into two separate columns of "British Colonies" and "British Territories", I can catch that and raise an exception BEFORE piling values into the database.

I also dropped down to one primary key because I really don't need "year month day hour minute second" as a primary key; a bigint primary key starting at 1 incrementing by 1 is fine. If I really need to do an exact date match I can do a select-where-like date in the date column without it being a primary key.

SQL workbench usability tips

There is a pair of downward-pointing arrow heads next to the schema name in the model tabsheet. Click next to those double heads to expand and allow editing comments to the database.

If you want to change the order of columns from left to right highlight the column to be moved, then right mouse click to use the move up or move down option from the pop-up context menu.

I found that trying to mess with Edit → Preferences … lead to the Workbench crashing. I don't have an answer for this; there are other places besides preferences where I can set up my configurations.

If you want to see all of your databases in MySQL workbench, launch the MyInstance80 or whatever you've named your instance of the MySQL Server and you'll see all your databases alphabetically on the left margin.

Forward Engineering is a safe tool to export your database model design onto the live DB server as a working schema. Some options that will be useful has you forward engineer are the following:

Generate a drop before creating the table --- this will give you a clean slate.

Skip anything related to foreign keys for now I don't need them.

Check the box to show warnings.

Check the box that you do not want to create users when you forward engineer the schema.

Check drop schema when making modifications.

Another tip I have for you is to save frequently to different file names while working on all models because as I mentioned before the system is a little bit unstable.

When I talked about error trapping in the python code, here are some examples of what I mean:

First of all, very early in the code, ensure that you can make a connection to the live DB server and that the tables exist.

As I stated earlier, before populating columns with data, check the number and names of the world regions in master list.

Warn if eBaySDK has changed versions but continue anyhow.

Check the category ID and subcategory ID match versus a standard list.

Make sure your JSON is valid.

Some potential gotchas when using either my SQL or python:

You must use a pair of single quotes to wrap around a JSON as a python string that you feed to the .loads() JSON module function, as it automatically checks to make sure it's valid and will raise an exception if not.

Unfortunately double quotes and even the  string constructor method will not wrap a JSON properly for the .loads() JSON module function.

I should get in the habit of visually scanning for these common errors before submitting the script to The Interpreter:

Look for colons at the end of def and if statements.

Check your indentation on commands that are heavily nested inside a conditional.

Dictionary variables need a .get() method to get values from the dictionary if that dictionary is inside a list. [][] won't work.

global variable has to be on a line by itself; use a second line to actually assign a value to the variable.

The element types inside a set must match the way they will be processed later - meaning if you're going to be processing them as ints, set them up as ints now.

More Gotchas:

To avoid SQL injection when using the connector module function requiring a SQL command, use a %s. It does not matter if it is a float, a bool, an int or any other SQL supported datatype. The Connector module functions behind the scenes are smart enough to automatically convert into the appropriate format expected on handshake to the my SQL database server … pretty impressive I think ... but not obvious or well-documented.

Again some quirks in MySQLworkbench:

If you have to edit the fundamental nature of your database, it's best to go ahead and drop the entire database in the instance tabsheet, close the instance, save your work, close Workbench and reopen. Do NOT forward engineer on top of an existing schema, even if you set the option to drop a schema on modification; you will timeout. Another clue that you've got a problem with a database is that when in the instance tabsheet view, the tables will keep fetching, then timeout.

Don't use two primary keys because from python's point-of-view, it won't like the second one, especially if it's a date string; you'll get error messages that don't have anything to do with the problem.

Make sure you are auto-incrementing in the workbench model design with some type of int and the left-most column will populate on its own. The default values to start at number one. 

TRUNCATE TABLE table_name will remove all rows faster than DELETE * and restart the counter at 1.

Use the query tab in the instance tab of workbench to ad hoc run SQL commands against the database. You don't need trailing semicolons for this if you are issuing only one command. First double check that the schema that you want to use on the left hand side of Navigator panel is engaged by double-clicking to see it as bold; that way you do not have to specify the schema in the SQL query.

The New Script tool would you see in the middle of a model tab creation page is only for attaching to a model before forward engineered; it will not be running at the workbench GUI.

Make sure the auto-commit button is depressed so that statements in the Query box take effect.

To change the column name in a table in an already established schema, use:

ALTER TABLE table_name CHANGE old_column new_column datatype

NOTE: the datatype is required, even if it the same as for the old_column.

MySQL Workbench

 MySQL Workbench

I've taken a useful detour to install and configure MySQL so that I can model and forward engineer a  model to a live DB server. That task is now complete for two data tables and two metadata tables for storing the results of parsing eBay data using the ebaysdk and Finding API for python. In particular, I am mining right now for world region and stamp quality data to understand trends over time in the eBay sales of stamps and to improve the performance of my eBay Stamps store.

This MySQL database is where we'll store the results once per day of the number of stamp Lots offered for sale broken down by regions and also separately in a different table broken down by grades.

The Regions table and the Grades table will be populated with big integers, both Alpha and incremental numeric timestamps and a descriptor that tells you about how all of the columns relate to each other.

 I will be looking for chronological trends in each of the columns in both the world regions and grades and this will be compared for databases - yet to be modeled - for statistics on the items and the sellers of the items themselves.

 

 Advantages of model building with MySQL workbench:

 1. Extensive comments and notes can be attached to the database, tables ... even to individual columns.

 2. Column defaults and keys are readily set.

 3. An Enhanced Entity Relationship EER diagram can be created in one click from model tables and it's GUI editable right within the diagram.

4.  Only available in Commercial version: schema validation plugins - dozens of them - in one report to check your design.

 5. Warnings if you configure columns with invalid or missing parameters.

6. Once a model is complete you can forward engineer to a live connection to create a live database.

 

 7. There is a tool to autogenerate SQL code mapped to your model.

 8. Server connections are easy to set up, test and are persistent across Workbench program launches.

Here is my EER Diagram for the World Regions Database: