mysql db

1. install

sudo apt-get install mysql-server

sudo mysql_secure_installation

1. start mysql mysql -u root -p, then input the password

2. show database SHOW DATABASES;

3. create a database called db_test CREATE DATABASE db_test;

4. use created database db_test USE db_test;

5. create a table in the database db_test CREATE TABLE tweets_raw_tb (tid INT(20), tweets VARCHAR(32500));

6. check the columns name / structure of the table DESCRIBE tweets_raw_tb

7. insert / update / delete / select INSERT INTO tweets_raw_tb VALUES(1234567890, "asdfoasdfasdljfsdfoasdfjlasdfjosaidfjasdljlsdfijasdf")

8. stop the service service mysqld stop

9. restart the service sudo service mysql restart

python MySQLdb

install

sudo apt install python-mysqldb

after install, there might be some errors like "cannot find MySQLdb". usually it is because the path is not added. To solve this, first check where it is installed dpkg --get-selections | grep mysql then add the path to system

import sys
sys.path.append('/usr/lib/python2.7/dist-packages')

there might be some other errors. like I got "cannot find _mysql".

Then I tried to install in another way: use easy_install MySQL-python and it issues error 'EnvironmentError: mysql_config not found'. To solve this, I need ro install sudo apt-get install libmysqlclient-dev first.

So the second way to install is:

sudo apt-get install libmysqlclient-dev
easy_install MySQL-python

import module and create a connection function

import MySQLdb
def connection():
    conn = MySQLdb.connect(host="localhost", user="root", password="password", db="tweets_raw")
    c = conn.cursor()
    return c, conn

uninstall

Uninstall just python-mysqldb

sudo apt-get remove python-mysqldb

Uninstall python-mysqldb and it's dependencies

sudo apt-get remove --auto-remove python-mysqldb