Database

##Overview

The MySQL, and its successor MariaDB allow complex relationships, and concurrent rewrites. When choosing these relational database management system, remember to login as root, and create a new user.

$ mysql -u root -p
MariaDB [(none)]> CREATE USER 'authenticated'@'localhost' IDENTIFIED BY '[USER_PASSWORD]';
MariaDB [(none)]> GRANT CREATE, INSERT, DELETE, UPDATE, DROP, EXECUTE, SELECT, SHOW DATABASES ON *.* TO 'authenticated'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;

Note: this wiki page assumes MariaDB has been installed.

Note: the created mysql user can be used within code, and helps prevent the root user from being compromised.

Note: all MySQL connectors work unchanged in MariaDB. Therefore, the python-mysqldb connector can be used for MariaDB.

###Create Database

The following creates a database, your_database from a python script:

#!/usr/bin/python
import MySQLdb, sys
...
try:
  con = MySQLdb.connect( host='localhost', user='username', passwd='***' )
  cursor = con.cursor()
  sql = "CREATE DATABASE IF NOT EXISTS your_database"
  cursor.execute( sql )
  con.close()
except MySQLdb.Error, e:
  print "Error %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  if con:
    con.close()

###Create Table

The following creates a table, your_table in your_database from a python script:

#!/usr/bin/python
import MySQLdb, sys
...
try:
  con = MySQLdb.connect( host='localhost', user='username', passwd='***', db='your_database' )
  cur = con.cursor()
  sql = '''
        CREATE TABLE IF NOT EXISTS your_table (
          id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          variable_1 TEXT,
          variable_2 INT,
          variable_3 FLOAT,
          variable_4 BLOB
        );
        '''
  cur.execute( sql )
except MySQLdb.Error, e:
  print "Error %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  if con:
    con.close()

###Insert Data

The following inserts data into an existing table, your_table from a python script:

#!/usr/bin/python
import MySQLdb, sys
...
try:
  con = MySQLdb.connect( host='localhost', user='user', passwd='***', db='your_database' )
  cur = con.cursor()
  sql = 'INSERT INTO your_table ( variable_1, variable_2, variable_3, variable_4 ) VALUES ( %s, %d, %.2f, %s )'
  cur.execute( sql, (self.variable_1, self.variable_2, self.variable_3, self.variable_4))
except MySQLdb.Error, e:
  print "Error %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  if con:
    con.close()

###Update Data

The following updates data within an existing table, your_table from a python script:

#!/usr/bin/python
import MySQLdb, sys
...
try:
  con = MySQLdb.connect( host='localhost', user='user', passwd='***', db='your_database' )
  cur = con.cursor()
  sql = 'UPDATE your_table SET col_1=%s, col_2=UTC_TIMESTAMP() WHERE col_3=%s'
  cur.execute( sql, (variable_1, variable_3) )
except MySQLdb.Error, e:
  print "Error %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  if con:
    con.close()

###Select Data

The following selects data from an existing table, your_table from a python script:

try:
  con = MySQLdb.connect( host='localhost', user='user', passwd='***', db='your_database' )
  cur = con.cursor()
  sql = "SELECT * from your_table"
  cur.execute( sql )

  # fetch all the rows from the query, then print them
  result = cur.fetchhall()
  for row in result:
    print row
except MySQLdb.Error, e:
  print "Error %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  if con:
    con.close()

###Delete Data

The following deletes data from an existing table, your_table from a python script:

try:
  con = MySQLdb.connect( host='localhost', user='user', passwd='***', db='your_database' )
  cur = con.cursor()
  sql = "DELETE FROM your_table WHERE col_1=%s AND col_2=%s"
  cur.execute( sql, (variable_1, variable_3) )
except MySQLdb.Error, e:
  print "Error %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  if con:
    con.close()