MySQL Workbench & mylatlon_4

6 replies [Last post]
DougB
User offline. Last seen 5 years 2 weeks ago. Offline
Joined: 04/29/2013

Great book! Alas, some obstacles crop up with MySQL that current Tips and Forum entries don't seem to solve.

1. Workbench.
The hand-entered MySQL 'specimen' table worked fine in Terminal. In Workbench, the table is listed on the left and in the query box, but its contents are not displayed (even when the commands are copied from the pcfb examples folder). Compared to Fig 15.3, the Workbench display has no Refresh button, the tabs below "100% 1:1" are absent, and there are just empty lines below this. On the line that says "100%", it says: "10:1 1 error found".
I'm using System = OSX 10.6.8 64-bit, MySQL = 5.6.11, Workbench = 5.2.47
(I also tried OSX 10.5.8 with MySQL 5.5.? August 2012 and Workbench 5.2.38-42.)
The reply to another inquiry mentions needing Java to be active. Changing the checkbox in OSX's Java Preferences for activating/inactivating it didn't make the table appear, and at least one of the three computers I've tried this on does not have this checkbox.

2. mylatlon_4.py.
a) On p. 279, line 1, it is not clear what "From" means. Do we, in bash, cd to the examples folder rather going to our scripts folder as usual? Do we move mylatlon_4.py to the examples folder? In any event, neither of these seemed to work, nor did trying to run the program from within Python or MySQL.
b) The program did run and print on the screen if the txt file and program [v. 6/24/11, which contains the MyConnection.commit() line] were moved to my scripts folder, but this didn’t change the contents of the specimens table unless the Cursor.execute and commit lines were inside the Infile loop. Moving them both outside the loop modified the specimens table but it seems to have overwritten each line so that only the last remained.

Suggestions?

Many thanks.

DougB
User offline. Last seen 5 years 2 weeks ago. Offline
Joined: 04/29/2013
Workbench solved

It works great to type into the Workbench upper pane --
USE midwater;
SELECT * FROM specimens;
Click on lightning bolt button.
At least in OSX 10.6.8, MySQL 5.6.11, Workbench 5.2.47.
In OSX 10.5.8, MySQL5.5.x, Workbench 5.2.42, it tries but the tabs appear briefly and then disappear behind a gray screen. Probably some mis-combination of old versions.

Despite the .bash_shell notices about Python version 3.2, it was running 2.6.1 and now 2.7.4, and I don't seem to have 3.2 on the computer.

Thanks!

Steve
User offline. Last seen 4 years 7 weeks ago. Offline
Joined: 08/15/2010
Workbench solved

Glad things are working now. If you "right click" (command click) on an item in the list at the left, it will show options including select rows (which will enter the queries you typed) and even let you edit table data. (Be careful if you do so, no Undo as far as I can tell!) Have fun and thanks for the feedback.

DougB
User offline. Last seen 5 years 2 weeks ago. Offline
Joined: 04/29/2013
Workbench suggestion

Thanks, Steve.
The Query tab is present. Typing in SELECT * FROM specimens; and hitting the lightning bolt produced an interesting result: Action is "SELECT * FROM specimens LIMIT 0,1000" but Response is "Error Code: 1146. Table 'test.specimens' doesn't exist"
Not sure where it got "test" unless it was from the "test" database that is there with midwater.
This was on OSX 10.5.8 and oldish MySQL so could be dicey; I will try it on 10.6.8 at work tomorrow.
Perhaps it is relevant that I found a .bash_profile.save in my home folder; it and the .bash_profile contain (in addition to what is supposed to be there):
# Setting PATH for Python 3.2
# The orginal version is saved in .bash_profile.pysave
PATH="/Library/Frameworks/Python.framework/Versions/3.2/bin:${PATH}"
export PATH

Steve
User offline. Last seen 4 years 7 weeks ago. Offline
Joined: 08/15/2010
Getting closer

Try specifying the database to use, then running the query:

USE midwater;
SELECT * FROM specimens;

The Python 3.2 setting is interesting. You are ahead of the game... ;^)
That could certainly cause some issues with programs running or not.
If you type
python --version
Does it say 2.x or 3.x?

Steve
User offline. Last seen 4 years 7 weeks ago. Offline
Joined: 08/15/2010
MySQL workbench

See the other reply for questions 2a and 2b... Regarding Workbench display, do you see a Query tab to the right of the database list?
If you type SELECT * FROM specimens; in that window and press the lightning bolt button (or Command-Return), it should show you the contents of the database.

I posted a screenshot of what I see...

Let us know!
Steve

Steve
User offline. Last seen 4 years 7 weeks ago. Offline
Joined: 08/15/2010
MySQL and python

Hi Doug —
1. We will have to do some investigations of the latest MySQL workbench to see why it is not updating to reflect your changes to the database. I am going to defer that question for now.

2a. Yes, you should run it after cding to your examples folder or wherever you are keeping the Marrus example file. You pretty much never should have to cd to the scripts folder to execute a program — that is what setting the PATH does for you.

2b. In the example script, the Cursor.execute() line is commented out initially, so that it doesn't keep trying to add the same record to the database while you are testing the script. When you first run it, it will just print the command. If you uncomment the Cursor.execute() line, then it should write to the database. The .commit() line should remain outside the for loop so it is just done once before closing.