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.
