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