Database

##MariaDB / MySQL Database

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

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

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

Note: when using MariaDB, the python-mysqldb connector is used.

All MySQL connectors (PHP, Perl, Python, Java, .NET, MyODBC, Ruby, MySQL C connector etc) work unchanged with MariaDB.

• https://mariadb.com/kb/en/mariadb/faq/mariadb-vs-mysql-compatibility/

###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()

###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()