scienceoss.com

The default engine in MySQL, MyISAM, does not support foreign keys. You need foreign keys to use the SQLAlchemy Python package effectively.

In order to use foreign keys, you need to convert your tables to the InnoDB engine:

ALTER TABLE mytable ENGINE = INNODB;

To add a foreign key to mytable where the unique keys are coming from othertable, use this:

ALTER TABLE mytable ADD FOREIGN KEY (otherID) REFERENCES othertable (otherID);

If you run that same line several times, several identical foreign keys will be created, which will confuse SQLAlchemy. In that case you need to delete the keys. To do so, you need their name. To see the name, use

SHOW CREATE TABLE mytable;

The foreign key’s label will be something that ends in something similar to fk_1 or fk_2. Using that label, you can then delete the foreign key:

ALTER TABLE mytable DROP FOREIGN KEY mytable_dbfk_2;

MySQL is a popular open-source database engine, and Python interfaces quite nicely with MySQL with the MySQLdb package. For more on why you would want to use a database for your data, check out this post. Here I’ll show you how to connect to your existing MySQL database with Python. (more…)

Quick answer: when you have large amounts of inter-related data.

Long answer: (more…)

Add a column to a table in the database:

ALTER TABLE table_name
ADD column_name datatype

Delete a column from a table in the database:

ALTER TABLE table_name
DROP COLUMN column_name

Step 1: SSH forwarding

First, forward the local port 3307 to 3306. That is, when you access the local port 3307, it will redirect it to port 3306 on the remote host.

ssh -fNg4 -L 3307:127.0.0.1:3306 user@hostname

-f sends SSH to the background
-g allows remote hosts to connect to local forwarded ports
-N don’t execute a remote command
-4 this was key! Forces IPv4. Kept getting “bind: Address already in use” errors because I didn’t have this.
-L the forwarding magic happens here . . . syntax is localport:localhost:remoteport

Step 2: Connect to mysql on port 3307

. . . which will redirect to port 3306 on remote host.

mysql -u root -h 127.0.0.1 -P 3307 -p

and you’re in!