Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Python functions for easy working with docker mysql - via http://www.luiselizondo.net/a-tutorial-on-how-to-use-mysql-with-docker/
#Create a container mot2013 with MySQL pwd: mot
#/host/path/to/ should contain:
## mot_boot.sql
## test_result_2013.txt #download and uncompress: http://data.dft.gov.uk/anonymised-mot-test/12-03/test_result_2013.txt.gz
## test_item_2013.txt #download and uncompress: http://data.dft.gov.uk/anonymised-mot-test/12-03/test_item_2013.txt.gz
## mdr_test_lookup_tables/ #download and unzip: http://data.dft.gov.uk/anonymised-mot-test/mdr_test_lookup_tables.zip
dmysql-server mot2013 mot
dmysql-create-database mot2013 motdata
dmysql-import-database mot2013 /host/path/to/mot_boot.sql --database motdata
#!/usr/bin/python
#Via https://github.com/luiselizondo/docker-mysql-scripts/
import sys
from subprocess import call
import argparse
def runContainer(containerName):
''' Runs the mysql client using the containerName provided
'''
call("docker run -it --link="+containerName+":mysql --rm mysql sh -c 'exec mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p'", shell=True)
return
def main():
''' Creates the parser and executes the function runContainer
'''
parser = argparse.ArgumentParser(
description="Run mysql client on a docker container",
prog="dmysql")
parser.add_argument("container", help="The mysql container to connect to")
args = parser.parse_args()
runContainer(args.container)
return
# Execute the main function
main()
#!/usr/bin/python
#Via https://github.com/luiselizondo/docker-mysql-scripts/
import sys
from subprocess import call
import argparse
def runContainer(containerName, databaseName):
''' Runs the command to create the database given using the container given
'''
call("docker run -it --link="+containerName+":mysql --rm mysql sh -c 'exec mysqladmin -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p create "+ databaseName +" '", shell=True)
return
def main():
''' Parse the options from the command line and run the function runContainer
'''
parser = argparse.ArgumentParser(
description="Import a .sql file into a database",
prog="dmysql-import-database")
# Create the options
parser.add_argument("container", help="The mysql container to use")
parser.add_argument("database", help="Database to create")
args = parser.parse_args()
runContainer(containerName=args.container, databaseName=args.database)
return
# Execute the main function
main()
#!/usr/bin/python
#Via https://github.com/luiselizondo/docker-mysql-scripts/
import sys
from subprocess import call
import os
import argparse
def runContainer(containerName, path, database = ""):
''' Runs the container calling docker run and passing
the necessary arguments. The database is optional since
we can import an .sql file without creating first a database or
specifying one.
'''
# Split the path given and create a list out of it
pathList = path.split("/")
# Take the last element of the list because this is the file
fileName = pathList[-1]
# Read the path and get the directory
dirname = os.path.dirname(path)
if database:
print 'Importing {file} into database {database} using container {container}'.format(file=fileName, database=database, container=containerName)
call("docker run -it --link="+containerName+":mysql -v "+ dirname +":/tmp/import --rm mysql sh -c 'exec mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p "+ database +" < /tmp/import/" + fileName + "'", shell=True)
else:
print 'Importing {file} into container {container}'.format(file=fileName, container=containerName)
call("docker run -it --link="+containerName+":mysql -v "+ dirname +":/tmp/import --rm mysql sh -c 'exec mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p < /tmp/import/" + fileName + "'", shell=True)
return
def main():
''' Interpret the command line arguments
and pass the options to runContainer
'''
parser = argparse.ArgumentParser(
description="Import a .sql file into a database",
prog="dmysql-import-database")
# Create the options
parser.add_argument("container", help="The mysql container to use")
parser.add_argument("filepath", help="File to the sql file you want to import")
parser.add_argument("--database", help="Database to import the file into")
args = parser.parse_args()
if args.database:
runContainer(args.container, args.filepath, args.database)
else:
runContainer(args.container, args.filepath)
return
# Execute main
main()
#!/usr/bin/env python
#Via https://github.com/luiselizondo/docker-mysql-scripts/
import sys
from subprocess import call
import argparse
def createVolume(containerVolumeName):
''' Create a data only container
'''
print 'Creating the container using the volumes-from strategy. Using container {container} as the container name'.format(container=containerVolumeName)
call("docker run -v /var/lib/mysql --name "+ containerVolumeName+" busybox true", shell=True)
return
def runContainer(containerName, rootPassword, hasVolume=False):
''' Creates the container
'''
if hasVolume:
# Create the container name variable
containerVolumeName = containerName.upper() + '_DATA'
# Create data container
createVolume(containerVolumeName)
call('docker run --volumes-from ' + containerVolumeName + ' --name ' + containerName + ' -e MYSQL_ROOT_PASSWORD="' + rootPassword + '" -d mysql', shell=True)
else:
call('docker run --name ' + containerName + ' -e MYSQL_ROOT_PASSWORD="' + rootPassword + '" -d mysql', shell=True)
return
def main():
parser = argparse.ArgumentParser(
description="Creates a new MySQL container using the mysql image",
prog="dmysql-server")
parser.add_argument("containerName", help="The name of the mysql container to create")
parser.add_argument("rootPassword", help="The root password to define when creating the container")
parser.add_argument("--with-volume", help="Creates a data-only container", action='store_true')
args = parser.parse_args()
# If there's a volume
if args.with_volume:
runContainer(containerName=args.containerName, rootPassword=args.rootPassword, hasVolume=True)
else:
runContainer(containerName=args.containerName, rootPassword=args.rootPassword)
return
# Execute main
main()
CREATE TABLE IF NOT EXISTS TESTRESULT (
TESTID INT UNSIGNED
,VEHICLEID INT UNSIGNED
,TESTDATE DATE
,TESTCLASSID CHAR(2)
,TESTTYPE CHAR(2)
,TESTRESULT CHAR(3)
,TESTMILEAGE INT UNSIGNED
,POSTCODEREGION CHAR(2)
,MAKE CHAR(30)
,MODEL CHAR(30)
,COLOUR CHAR(16)
,FUELTYPE CHAR(1)
,CYLCPCTY INT UNSIGNED
,FIRSTUSEDATE DATE
,PRIMARY KEY (TESTID)
,INDEX IDX1 (TESTDATE, TESTTYPE, TESTRESULT, TESTCLASSID)
)
;
CREATE TABLE IF NOT EXISTS TESTITEM (
TESTID INT UNSIGNED
,RFRID SMALLINT UNSIGNED
,RFRTYPE CHAR(1)
,LATLOCATIONID CHAR(1)
,LONGLOCATIONID CHAR(1)
,VERTLOCATIONID CHAR(1)
,DMARK CHAR(1)
,INDEX IDX1 (TESTID)
,INDEX IDX2 (RFRID)
)
;
CREATE TABLE IF NOT EXISTS TESTITEM_DETAIL (
RFRID SMALLINT UNSIGNED
,TESTCLASSID CHAR(2)
,TSTITMID SMALLINT UNSIGNED
,MINORITEM CHAR(1)
,RFRDESC CHAR(250)
,RFRLOCMARKER CHAR(1)
,RFRINSPMANDESC TEXT(500)
,RFRADVISORYTEXT CHAR(250)
,TSTITMSETSECID SMALLINT UNSIGNED
,PRIMARY KEY (RFRID, TESTCLASSID)
,INDEX IDX1 (TSTITMID, TESTCLASSID)
,INDEX IDX2 (TSTITMSETSECID, TESTCLASSID)
)
;
CREATE TABLE IF NOT EXISTS TESTITEM_GROUP (
TSTITMID SMALLINT UNSIGNED
,TESTCLASSID CHAR(2)
,PARENTID SMALLINT UNSIGNED
,TSTITMSETSECID SMALLINT UNSIGNED
,ITEMNAME CHAR(100)
,PRIMARY KEY (TSTITMID, TESTCLASSID)
,INDEX IDX1 (PARENTID, TESTCLASSID)
,INDEX IDX2(TSTITMSETSECID, TESTCLASSID)
)
;
CREATE TABLE IF NOT EXISTS FUEL_TYPES (
FUELTYPE CHAR(1)
,FUELTYPEDESC CHAR(30)
,PRIMARY KEY (FUELTYPE)
);
CREATE TABLE IF NOT EXISTS TEST_OUTCOME (
TESTRESULT CHAR(3),
TESTRESULTDESC CHAR(35)
,PRIMARY KEY (TESTRESULT)
);
CREATE TABLE IF NOT EXISTS TEST_TYPES (
TESTTYPE CHAR(2)
,TESTTYPEDESC CHAR(60)
,PRIMARY KEY (TESTTYPE)
);
LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_fuel_types.txt'
INTO TABLE FUEL_TYPES
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;
LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_test_item_detail.txt'
INTO TABLE TESTITEM_DETAIL
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;
LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_test_item_group.txt'
INTO TABLE TESTITEM_GROUP
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;
LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_test_outcome.txt'
INTO TABLE TEST_OUTCOME
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;
LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_test_types.txt'
INTO TABLE TEST_TYPES
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;
LOAD DATA LOCAL INFILE '/tmp/import/test_item_2013.txt'
INTO TABLE TESTITEM
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;
LOAD DATA LOCAL INFILE '/tmp/import/test_result_2013.txt'
INTO TABLE TESTRESULT
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment