### Navigation

- index
- modules |
- CCHR 1.0 documentation »
- Module code »
- src »

# Source code for src.db

    import os, sys, re, MySQLdb
    from os.path import normpath, join
    from sys import path
    from contextlib import closing

    import cardsharp as cs

    from configuration import db_info

    def create_db(db, drop_db=True):
        """Create an output relational database. Will drop existing database unless the drop_db parameter is set to False.
        @param db: Dictionary containing the database metadata including the database name and connection parameters.
        @param drop_db: If set to True will cause the database to be dropped if it already exists, default is True.
        """
        with closing(MySQLdb.connect(user=db['user'], passwd=db['pass'])) as cnx:
            with closing(cnx.cursor()) as c:
                c.execute('show databases')
                response = c.fetchall()
                if db['name'] not in [db_list[0] for db_list in response]:
                    c.execute("""CREATE DATABASE %s CHARACTER SET utf8 COLLATE utf8_unicode_ci""" % db['name'])
                elif drop_db:
                    c.execute("DROP DATABASE %s" % db['name'])
                    print """CREATE DATABASE %s CHARACTER SET utf8 COLLATE utf8_unicode_ci""" % db['name']
                    c.execute("""CREATE DATABASE %s CHARACTER SET utf8 COLLATE utf8_unicode_ci""" % db['name'])

    def create_lookups(db):
        """Helper function to add lookup tables to a database.
        The lookup tables reside within a directory db_info['dir'],
        each table is stored as a file in the lookup directory with the filename
        being the name of the table."""

        for file in os.listdir(join(db['dir'], 'lookups')):
            try:
                if re.match('[a-zA-Z0-9]+\.xls', file):
                    name, ext = file.split('.')

            except ValueError:
                print (''.join(["ValueError: too many values to unpack.", " Make sure all your files are closed."]))
                sys.exit(1)

            ext = '.' + ext
            table = cs.load(source=join(db['dir'], 'lookups', file), format='excel')

            table['id'].convert('integer')
            table['id'].flags.primary_key = 'id'

            cs.wait()

            table.save(source=db['name'], format='mysql', dataset=name,
                       user = db['user'], pwd = db['pass'],
                       overwrite=True)
        cs.wait()

### Navigation

- index
- modules |
- CCHR 1.0 documentation »
- Module code »
- src »