Python MySQLdb.ProgrammingError() Examples

The following are code examples for showing how to use MySQLdb.ProgrammingError(). They are from open source Python projects. You can vote up the examples you like or vote down the ones you don't like.

Example 1
Project: sqlibrist   Author: condograde   File: engines.py    MIT License 6 votes vote down vote up
def apply_migration(self, name, statements, fake=False):
        import MySQLdb
        connection = self.get_connection()
        cursor = connection.cursor()

        try:
            if not fake and statements.strip():
                cursor.execute(statements)
        except (MySQLdb.OperationalError, MySQLdb.ProgrammingError) as e:
            print('\n'.join(map(str, e.args)))
            from sqlibrist.helpers import ApplyMigrationFailed

            raise ApplyMigrationFailed
        else:
            cursor.execute('INSERT INTO sqlibrist_migrations '
                           '(migration) VALUES (%s);',
                           [name.split('/')[-1]]) 
Example 2
Project: sqlibrist   Author: condograde   File: engines.py    MIT License 6 votes vote down vote up
def unapply_migration(self, name, statements, fake=False):
        import MySQLdb
        connection = self.get_connection()
        cursor = connection.cursor()

        try:
            if not fake:
                cursor.execute(statements)
        except (MySQLdb.OperationalError, MySQLdb.ProgrammingError) as e:
            print('\n'.join(map(str, e.args)))
            from sqlibrist.helpers import ApplyMigrationFailed

            raise ApplyMigrationFailed
        else:
            cursor.execute('DELETE FROM sqlibrist_migrations '
                           'WHERE migration = (%s); ', [name]) 
Example 3
Project: Computable   Author: ktraunmueller   File: test_sql.py    MIT License 6 votes vote down vote up
def setUp(self):
        _skip_if_no_MySQLdb()
        import MySQLdb
        try:
            # Try Travis defaults.
            # No real user should allow root access with a blank password.
            self.db = MySQLdb.connect(host='localhost', user='root', passwd='',
                                    db='pandas_nosetest')
        except:
            pass
        else:
            return
        try:
            self.db = MySQLdb.connect(read_default_group='pandas')
        except MySQLdb.ProgrammingError as e:
            raise nose.SkipTest(
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf. ")
        except MySQLdb.Error as e:
            raise nose.SkipTest(
                "Cannot connect to database. "
                "Create a group of connection parameters under the heading "
                "[pandas] in your system's mysql default file, "
                "typically located at ~/.my.cnf or /etc/.my.cnf. ") 
Example 4
Project: Computable   Author: ktraunmueller   File: test_sql.py    MIT License 6 votes vote down vote up
def test_tquery(self):
        try:
            import MySQLdb
        except ImportError:
            raise nose.SkipTest("no MySQLdb")
        frame = tm.makeTimeDataFrame()
        drop_sql = "DROP TABLE IF EXISTS test_table"
        cur = self.db.cursor()
        cur.execute(drop_sql)
        sql.write_frame(frame, name='test_table', con=self.db, flavor='mysql')
        result = sql.tquery("select A from test_table", self.db)
        expected = frame.A
        result = Series(result, frame.index)
        tm.assert_series_equal(result, expected)

        try:
            sys.stdout = StringIO()
            self.assertRaises(MySQLdb.ProgrammingError, sql.tquery,
                              'select * from blah', con=self.db)

            self.assertRaises(MySQLdb.ProgrammingError, sql.tquery,
                              'select * from blah', con=self.db, retry=True)
        finally:
            sys.stdout = sys.__stdout__ 
Example 5
Project: Computable   Author: ktraunmueller   File: test_sql.py    MIT License 6 votes vote down vote up
def test_uquery(self):
        try:
            import MySQLdb
        except ImportError:
            raise nose.SkipTest("no MySQLdb")
        frame = tm.makeTimeDataFrame()
        drop_sql = "DROP TABLE IF EXISTS test_table"
        cur = self.db.cursor()
        cur.execute(drop_sql)
        sql.write_frame(frame, name='test_table', con=self.db, flavor='mysql')
        stmt = 'INSERT INTO test_table VALUES(2.314, -123.1, 1.234, 2.3)'
        self.assertEqual(sql.uquery(stmt, con=self.db), 1)

        try:
            sys.stdout = StringIO()

            self.assertRaises(MySQLdb.ProgrammingError, sql.tquery,
                              'insert into blah values (1)', con=self.db)

            self.assertRaises(MySQLdb.ProgrammingError, sql.tquery,
                              'insert into blah values (1)', con=self.db,
                              retry=True)
        finally:
            sys.stdout = sys.__stdout__ 
Example 6
Project: NanoTipBot   Author: mitche50   File: db.py    GNU General Public License v3.0 6 votes vote down vote up
def set_db_data(db_call, values):
    """
    Enter data into DB
    """
    db = MySQLdb.connect(host=DB_HOST, port=3306, user=DB_USER, passwd=DB_PW, db=DB_SCHEMA, use_unicode=True,
                         charset="utf8mb4")
    try:
        db_cursor = db.cursor()
        db_cursor.execute(db_call, values)
        db.commit()
        db_cursor.close()
        db.close()
        logging.info("{}: record inserted into DB".format(datetime.now()))
        return None
    except MySQLdb.ProgrammingError as e:
        logging.info("{}: Exception entering data into database".format(datetime.now()))
        logging.info("{}: {}".format(datetime.now(), e))
        return e 
Example 7
Project: supremm   Author: ubccr   File: xdmodaccount.py    GNU Lesser General Public License v3.0 6 votes vote down vote up
def detectXdmodSchema(self):
        """ Query the XDMoD datawarehouse to determine which version of the data schema
            is in use """

        xdmod_schema_version = 7

        testconnection = getdbconnection(self.dbsettings, True)
        curs = testconnection.cursor()
        try:
            curs.execute('SELECT 1 FROM `modw`.`job_tasks` LIMIT 1')
            curs.close()
            xdmod_schema_version = 8
        except ProgrammingError:
            pass

        testconnection.close()

        return xdmod_schema_version 
Example 8
Project: deep_data_bench   Author: DeepFound   File: QueryGenerator.py    GNU General Public License v3.0 6 votes vote down vote up
def do_query(self,sql,cur,conn):
		rows_affected 	= None
		e 				= None
		row 			= None
		w 				= None
		with warnings.catch_warnings(record=True) as w:
			try:
	  			rows_affected = cur.execute(sql)
	  			row = self.__cur.fetchone()
			except MySQLdb.Error, e:
				#self.statistics.processError(e)
				#print self.name + " - An Error occured running query. %s" %e
				#print sql
				#print "----------------------------"
				if e[1] == 'MySQL server has gone away':
					#print self.name + " - An Error occured running query. %s" %e
					pass
				#print sql;
				#conn.send((rows_affected,e,w,row))
			except MySQLdb.ProgrammingError, e:
				print self.name + " - A ProgrammingError occured running query. %s" %e
				#print sql;
				#print "----------------end----------------" 
Example 9
Project: tornado-zh   Author: tao12345666333   File: blog.py    MIT License 5 votes vote down vote up
def maybe_create_tables(self):
        try:
            self.db.get("SELECT COUNT(*) from entries;")
        except MySQLdb.ProgrammingError:
            subprocess.check_call(['mysql',
                                   '--host=' + options.mysql_host,
                                   '--database=' + options.mysql_database,
                                   '--user=' + options.mysql_user,
                                   '--password=' + options.mysql_password],
                                  stdin=open('schema.sql')) 
Example 10
Project: mysql_utils   Author: pinterest   File: mysql_lib.py    GNU General Public License v2.0 5 votes vote down vote up
def show_create_table(instance, db, table, standardize=True):
    """ Get a standardized CREATE TABLE statement

    Args:
    instance - a hostAddr object
    db - the MySQL database to run against
    table - the table on the db database to run against
    standardize - Remove AUTO_INCREMENT=$NUM and similar

    Returns:
    A string of the CREATE TABLE statement
    """
    conn = connect_mysql(instance)
    cursor = conn.cursor()

    try:
        cursor.execute('SHOW CREATE TABLE `{db}`.`{table}`'.format(table=table,
                                                                   db=db))
        ret = cursor.fetchone()['Create Table']
        if standardize is True:
            ret = re.sub('AUTO_INCREMENT=[0-9]+ ', '', ret)
    except MySQLdb.ProgrammingError as detail:
        (error_code, msg) = detail.args
        if error_code != MYSQL_ERROR_NO_SUCH_TABLE:
            raise
        ret = ''

    return ret 
Example 11
Project: dockerizeme   Author: dockerizeme   File: snippet.py    Apache License 2.0 5 votes vote down vote up
def do_command(command):
    while True:
        try:
            cursor.execute(command) ### Executes the command
            reply = cursor.fetchall() ### Grabs data
            conn.commit()
            break
        except (OperationalError, InterfaceError):
            print "Connection timed out ("+command+"). Reconnecting..." ### If MySQL went away, reconnect
            host = "www.sophosacademics.com"
            user = "sophos_vocuser"
            try:
                conn.close()
            except ProgrammingError: ### Try closing the connection, but catch an error if it's already closed
                pass
            try:
                global conn
                conn = MySQLdb.connect(host = host, user = user, passwd = sophos_password) ### Reconnect globally
                global cursor
                cursor = conn.cursor()

                cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED") ### Allow different reads based on in-moment changes
                cursor.execute("SET SESSION WAIT_TIMEOUT = 60")
                cursor.execute("USE %s" % db)
            except OperationalError:
                pass
            except ProgrammingError:
                pass

    return reply ### Return data


### There's a file called vocab-log.csv in the same folder, which tracks activity. Three columns: student name, file name (changes depending on what operation you're in, obviously) and timestamp 
Example 12
Project: Causality   Author: vcla   File: dealWithDBResults.py    MIT License 5 votes vote down vote up
def getColumns(conn, connType, tableName, exampleNameForDB):
	retval = False
	if connType == DBTYPE_MYSQL:
		query = "SHOW COLUMNS FROM {}".format(tableName)
	else:
		query = "PRAGMA table_info({})".format(tableName)
	try:
		cursor = conn.cursor()
		cursor.execute(query)
		retval = cursor.fetchall()
		cursor.close()
	except (MySQLdb.ProgrammingError, sqlite3.Error,) as e:
		print "TABLE {} not found for example {}: {}".format(tableName,exampleNameForDB, e.args)
	if connType == DBTYPE_SQLITE:
		retval = [(x[1],) for x in retval] # weird unSELECTable tuple retval
	return retval 
Example 13
Project: sqlibrist   Author: condograde   File: engines.py    MIT License 5 votes vote down vote up
def apply_migration(self, name, statements, fake=False):
        import psycopg2
        connection = self.get_connection()
        with connection.cursor() as cursor:
            try:
                if not fake and statements.strip():
                    cursor.execute(statements)
            except (
                    psycopg2.OperationalError,
                    psycopg2.ProgrammingError) as e:
                connection.rollback()
                print(e.message)
                from sqlibrist.helpers import ApplyMigrationFailed

                raise ApplyMigrationFailed
            else:
                cursor.execute('INSERT INTO sqlibrist.migrations '
                               '(migration) VALUES (%s);',
                               [name.split('/')[-1]])
                connection.commit() 
Example 14
Project: sqlibrist   Author: condograde   File: engines.py    MIT License 5 votes vote down vote up
def unapply_migration(self, name, statements, fake=False):
        import psycopg2
        connection = self.get_connection()
        with connection.cursor() as cursor:
            try:
                if not fake:
                    cursor.execute(statements)
            except (
                    psycopg2.OperationalError,
                    psycopg2.ProgrammingError) as e:
                connection.rollback()
                print(e.message)
                from sqlibrist.helpers import ApplyMigrationFailed

                raise ApplyMigrationFailed
            else:
                cursor.execute('DELETE FROM sqlibrist.migrations '
                               'WHERE migration = (%s); ', [name])
                connection.commit() 
Example 15
Project: ops   Author: xiaomatech   File: mysqlops.py    MIT License 5 votes vote down vote up
def show_create_table(instance, db, table, standardize=True):
    """ Get a standardized CREATE TABLE statement

    Args:
    instance - a hostAddr object
    db - the MySQL database to run against
    table - the table on the db database to run against
    standardize - Remove AUTO_INCREMENT=$NUM and similar

    Returns:
    A string of the CREATE TABLE statement
    """
    conn = connect_mysql(instance)
    cursor = conn.cursor()

    try:
        cursor.execute('SHOW CREATE TABLE `{db}`.`{table}`'.format(
            table=table, db=db))
        ret = cursor.fetchone()['Create Table']
        if standardize is True:
            ret = re.sub('AUTO_INCREMENT=[0-9]+ ', '', ret)
    except MySQLdb.ProgrammingError as detail:
        (error_code, msg) = detail.args
        if error_code != MYSQL_ERROR_NO_SUCH_TABLE:
            raise
        ret = ''

    return ret 
Example 16
Project: annotated-py-tornado   Author: hhstore   File: blog.py    MIT License 5 votes vote down vote up
def maybe_create_tables(self):
        try:
            self.db.get("SELECT COUNT(*) from entries;")
        except MySQLdb.ProgrammingError:
            subprocess.check_call(['mysql',
                                   '--host=' + options.mysql_host,
                                   '--database=' + options.mysql_database,
                                   '--user=' + options.mysql_user,
                                   '--password=' + options.mysql_password],
                                  stdin=open('schema.sql')) 
Example 17
Project: OpenVPN_UAM   Author: Turgon37   File: mysql.py    GNU General Public License v3.0 5 votes vote down vote up
def __queryHelper(self, cursor, query, args=None):
    """Execute a basic query on the given cursor

    This helper execute a query on the given cursor and handle error reporting
    if exception occur
    @param cursor [MySQLdb.cursors] the cursor on which to execute the query
    @param query [str] the query to execute
    @param args [tuple] OPTIONNAL : a tuple of replacement argument to put
      instead string control character into query string
    @return [MySQLdb.cursors] the cursor after query execution or None if fail
    """
    # try at most two time to execute query
    try:
      if args is None:
        cursor.execute(query)
      else:
        cursor.execute(query, args)
    # DEVELOPPER error
    except MySQLdb.ProgrammingError as e:
      helper_log_fatal(g_sys_log, '#error_database.mysql.fatal')
      return None
    # SYSTEM error
    except MySQLdb.OperationalError as e:
      # if server or network link has fail
      if e.args[0] == MySQLdb.constants.CR.SERVER_GONE_ERROR:
        try:
          # try to relaunch the connection
          self.__connection.ping(True)
        except MySQLdb.MySQLError as e:
          # if error close defintively the connection
          self.close()
        except Exception as e:
          g_sys_log.error('Error with server connection %s', str(e))
      else:
        g_sys_log.error('Error with server %s',
                        str(e))
      return None
    except Exception as e:
      g_sys_log.error('Error during execution of this query %s', str(e))
      return None
    return cursor 
Example 18
Project: forum-xblock   Author: DANCEcollaborative   File: introspection.py    MIT License 5 votes vote down vote up
def get_key_columns(self, cursor, table_name):
        """
        Returns a list of (column_name, referenced_table_name, referenced_column_name) for all
        key columns in given table.
        """
        key_columns = []
        try:
            cursor.execute("""
                SELECT column_name, referenced_table_name, referenced_column_name
                FROM information_schema.key_column_usage
                WHERE table_name = %s
                    AND table_schema = DATABASE()
                    AND referenced_table_name IS NOT NULL
                    AND referenced_column_name IS NOT NULL""", [table_name])
            key_columns.extend(cursor.fetchall())
        except (ProgrammingError, OperationalError):
            # Fall back to "SHOW CREATE TABLE", for previous MySQL versions.
            # Go through all constraints and save the equal matches.
            cursor.execute("SHOW CREATE TABLE %s" % self.connection.ops.quote_name(table_name))
            for row in cursor.fetchall():
                pos = 0
                while True:
                    match = foreign_key_re.search(row[1], pos)
                    if match == None:
                        break
                    pos = match.end()
                    key_columns.append(match.groups())
        return key_columns 
Example 19
Project: supremm   Author: ubccr   File: ingest_jobscripts.py    GNU Lesser General Public License v3.0 5 votes vote down vote up
def __init__(self, dwconfig, schema):

        # The database schema should be created with utf8-unicode encoding.
        self.con = getdbconnection(dwconfig, False, {'charset': 'utf8', 'use_unicode': True})
        self.tablename = "`{0}`.`batchscripts`".format(schema)
        self.xdmod_schema_version = 7

        try:
            cur = self.con.cursor()
            cur.execute('SELECT tg_job_id FROM `{0}`.`job_scripts` LIMIT 1'.format(schema))
            cur.close()
            self.xdmod_schema_version = 8
            self.tablename = "`{0}`.`job_scripts`".format(schema)
        except ProgrammingError:
            pass

        if self.xdmod_schema_version == 7:
            self.query = "INSERT IGNORE INTO " + self.tablename + " (resource_id,local_job_id,script) VALUES(%s,%s,%s)"
        else:
            self.query = "INSERT IGNORE INTO " + self.tablename + """ (tg_job_id, resource_id, start_date, script)
                        SELECT 
                            job_id AS tg_job_id,
                            resource_id,
                            DATE(FROM_UNIXTIME(start_time_ts)) AS start_date,
                            %s AS script
                        FROM
                            `modw`.`job_tasks`
                        WHERE
                            resource_id = %s 
                            AND local_job_id_raw = %s
                            AND DATE(FROM_UNIXTIME(start_time_ts)) = %s"""

        self.buffered = 0 
Example 20
Project: ethoscope   Author: gilestrolab   File: mysql_backup.py    GNU General Public License v3.0 5 votes vote down vote up
def update_roi_tables(self):
        """
        Fetch new ROI tables and new data points in the remote and use them to update local db

        :return:
        """

        src = MySQLdb.connect(host=self._remote_host, user=self._remote_user,
                                         passwd=self._remote_pass, db=self._remote_db_name)

        with sqlite3.connect(self._dst_path, check_same_thread=False) as dst:

            dst_cur = src.cursor()
            command = "SELECT roi_idx FROM ROI_MAP"
            dst_cur.execute(command)
            rois_in_src = set([c[0] for c in dst_cur])
            for i in rois_in_src :
                self._update_one_roi_table("ROI_%i" % i, src, dst)


            self._update_one_roi_table("CSV_DAM_ACTIVITY", src, dst, dump_in_csv=True)
            try:
                self._update_one_roi_table("START_EVENTS", src, dst)
            except MySQLdb.ProgrammingError:
                pass

            try:
                self._update_img_snapshot_table("IMG_SNAPSHOTS", src, dst)

            except Exception as e:
                logging.error("Cannot mirror snapshots. Probably no snapshot table")
                logging.error(e) 
Example 21
Project: ethoscope   Author: gilestrolab   File: mysql_backup.py    GNU General Public License v3.0 5 votes vote down vote up
def _update_img_snapshot_table(self, table_name, src, dst):

        src_cur = src.cursor()
        dst_cur = dst.cursor()

        try:
            dst_command= "SELECT MAX(id) FROM %s" % table_name
            dst_cur.execute(dst_command)
        except (sqlite3.OperationalError, MySQLdb.ProgrammingError):
            logging.warning("Local table %s appears empty. Rebuilding it from source" % table_name)
            self._replace_img_snapshot_table(table_name, src, dst)
            return

        last_id_in_dst = 0
        for c in dst_cur:
            last_id_in_dst = c[0]
            if last_id_in_dst is None:
                logging.warning("There seem to be no data in %s, %s stopping here" % (os.path.basename(self._dst_path), table_name))
                return
        src_command = "SELECT id,t,img FROM %s WHERE id > %d" % (table_name, last_id_in_dst)
        src_cur.execute(src_command)


        for sc in src_cur:
            id,t,img = sc
            command = "INSERT INTO %s (id,t,img) VALUES(?,?,?);" % table_name
            dst_cur.execute(command, [id,t,sqlite3.Binary(img)])
            dst.commit() 
Example 22
Project: ethoscope   Author: gilestrolab   File: sqlite_mysql_mirror.py    GNU General Public License v3.0 5 votes vote down vote up
def _update_one_roi_table(self, table_name, src, dst):

        src_cur = src.cursor()
        dst_cur = dst.cursor()

        try:
            dst_command= "SELECT t FROM %s ORDER BY t DESC LIMIT 1" % table_name
            dst_cur.execute(dst_command)
        except (sqlite3.OperationalError, MySQLdb.ProgrammingError):
            self._copy_table(table_name, src, dst)
            return

        last_t_in_dst = 0
        for c in dst_cur:
            last_t_in_dst = c[0]

        src_command = "SELECT * FROM %s WHERE t > %d" % (table_name, last_t_in_dst)

        src_cur.execute(src_command)

        for sc in src_cur:
            tp = tuple([str(v) for v in sc ])
            dst_command = "INSERT INTO %s VALUES %s" % (table_name, tp)
            dst_cur.execute(dst_command)

        dst.commit() 
Example 23
Project: ibdata-shrinker   Author: giacomolozito   File: ibdata-shrinker.py    GNU General Public License v3.0 5 votes vote down vote up
def query(self, query_str):
		try:
			self.dbcurs.execute(query_str);
		except MySQLdb.ProgrammingError as e:
			if self.exit_if_query_fails:
				sys.stderr.write("\nERROR: a DB error occurred: %s\n" % (e))
				sys.exit(10)
			else:
				raise MySqlConnException(e)
		return self.dbcurs.fetchall() 
Example 24
Project: bilibili_member_crawler   Author: cwjokaka   File: worker.py    MIT License 5 votes vote down vote up
def _crawl(self, mid, cur):
        """
        抓取并持久化用户信息
        :param mid: B站用户id
        :param cur: mysql游标
        :return: None
        """
        if self._is_member_exist(cur, mid):
            print(f'数据库中已存在此用户mid:{mid}, 忽略')
            return
        member_info = self._get_member_by_mid(mid)
        if member_info is None:
            return
        mid = member_info['mid']
        name = member_info['name']
        sign = member_info['sign'].replace("'", "\\\'")
        rank = member_info['rank']
        level = member_info['level']
        jointime = member_info['jointime']
        moral = member_info['moral']
        silence = member_info['silence']
        birthday = member_info['birthday']
        coins = member_info['coins']
        fans_badge = member_info['fans_badge']
        vip_type = member_info['vip']['type']
        vip_status = member_info['vip']['status']
        try:
            cur.execute(f"INSERT INTO bilibili_member "
                        f"(mid, name, sign, `rank`, `level`, jointime, moral, silence, birthday, coins, fans_badge, vip_type, vip_status) "
                        f"VALUES "
                        f"({mid}, '{name}', '{sign}', {rank}, {level}, {jointime}, {moral}, {silence}, '{birthday}', "
                        f"{coins}, {fans_badge}, {vip_type}, {vip_status})"
                        )
            print(f'成功插入用户数据: {mid}, 当前代理:{self.cur_proxy["https"]}')
        except MySQLdb.ProgrammingError as e:
            print(f'插入用户: {mid} 数据出错:{e}')
            raise SqlInsertException(str(e))
        except MySQLdb.IntegrityError:
            print(f'用户: {mid} 数据已存在,不作插入')
            raise SqlAlreadyExistsException('数据已存在') 
Example 25
Project: bilibili_member_crawler   Author: cwjokaka   File: worker.py    MIT License 5 votes vote down vote up
def _insert_failure_record(cur, mid, state, remark):
        remark = remark.replace("'", "\\\'")
        try:
            cur.execute(
                "INSERT INTO failure_record (mid, remark, state) "
                f"VALUES ({mid}, '{remark}', '{state}')"
            )
        except MySQLdb.ProgrammingError as e:
            print(f'插入失败日志: {mid} 数据出错:{e}')
        except MySQLdb.IntegrityError:
            print(f'失败日志: {mid} 数据已存在,不作插入') 
Example 26
Project: dockerizeme   Author: dockerizeme   File: snippet.py    Apache License 2.0 4 votes vote down vote up
def exists(self, connection=None):
        if connection is None:
            connection = self.connect()
            connection.autocommit(True)
        cursor = connection.cursor()
        try:
            cursor.execute("""SELECT 1 FROM {marker_table}
                WHERE update_id = %s
                LIMIT 1""".format(marker_table=self.marker_table),
                (self.update_id,)
            )
            row = cursor.fetchone()
        except MySQLdb.ProgrammingError as e:
            if e[0] == errorcode.NO_SUCH_TABLE:
                row = None
            else:
                raise
        return row is not None 
Example 27
Project: dockerizeme   Author: dockerizeme   File: snippet.py    Apache License 2.0 4 votes vote down vote up
def run(self):
        """Inserts data generated by rows() into target table.

        If the target table doesn't exist, self.create_table will be called to
        attempt to create the table.

        Normally you don't want to override this.
        """
        if not (self.table and self.columns):
            raise Exception("table and columns need to be specified")

        connection = self.output().connect()
        tmp_dir = luigi.configuration.get_config().get(
            'mysql', 'local-tmp-dir', None)
        tmp_file = tempfile.NamedTemporaryFile(dir=tmp_dir)
        n = 0
        for row in self.rows():
            n += 1
            if n % 100000 == 0:
                logger.info("Wrote %d lines", n)
            rowstr = '\t'.join(
                self.map_column(val) for val in row)
            tmp_file.write(rowstr + '\n')

        logger.info("Done writing, importing at %s", datetime.datetime.now())
        tmp_file.seek(0)

        # attempt to copy the data into mysql
        # if it fails because the target table doesn't exist
        # try to create it by running self.create_table
        for attempt in xrange(2):
            try:
                cursor = connection.cursor()
                self.init_copy(connection)
                self.copy(cursor, tmp_file)
            except MySQLdb.ProgrammingError, e:
                if e[0] == errorcode.NO_SUCH_TABLE and \
                        attempt == 0:
                    # if first attempt fails with "relation not found", try
                    # creating table
                    logger.info("Creating table %s", self.table)
                    connection.rollback()
                    self.create_table(connection)
                else:
                    raise
            else:
                break

        # mark as complete in same transaction 
Example 28
Project: diamond-next   Author: diamond-next   File: mysqlstat.py    MIT License 4 votes vote down vote up
def collect(self):

        if MySQLdb is None:
            self.log.error('Unable to import MySQLdb')
            return False

        for host in self.config['hosts']:
            matches = re.search(
                '^([^:]*):([^@]*)@([^:]*):?([^/]*)/([^/]*)/?(.*)', host)

            if not matches:
                self.log.error(
                    'Connection string not in required format, skipping: %s',
                    host)
                continue

            params = {'host': matches.group(3)}

            try:
                params['port'] = int(matches.group(4))
            except ValueError:
                params['port'] = 3306
            params['db'] = matches.group(5)
            params['user'] = matches.group(1)
            params['passwd'] = matches.group(2)

            nickname = matches.group(6)
            if len(nickname):
                nickname += '.'

            if params['db'] == 'None':
                del params['db']

            try:
                metrics = self.get_stats(params=params)
            except Exception as e:
                try:
                    self.disconnect()
                except MySQLdb.ProgrammingError:
                    pass
                self.log.error('Collection failed for %s %s', nickname, e)
                continue

            # Warn if publish contains an unknown variable
            if 'publish' in self.config and metrics['status']:
                for k in self.config['publish'].split():
                    if k not in metrics['status']:
                        self.log.error("No such key '%s' available, issue " +
                                       "'show global status' for a full " +
                                       "list", k)
            self._publish_stats(nickname, metrics) 
Example 29
Project: ethoscope   Author: gilestrolab   File: mysql_backup.py    GNU General Public License v3.0 4 votes vote down vote up
def _update_one_roi_table(self, table_name, src, dst, dump_in_csv=False):
        src_cur = src.cursor()
        dst_cur = dst.cursor()

        try:
            dst_command= "SELECT MAX(id) FROM %s" % table_name
            dst_cur.execute(dst_command)
        except (sqlite3.OperationalError, MySQLdb.ProgrammingError):
            logging.warning("Local table %s appears empty. Rebuilding it from source" % table_name)
            self._replace_table(table_name, src, dst)
            return

        last_id_in_dst = 0
        for c in dst_cur:
            if c[0] is None:
                logging.warning("There seem to be no data in %s, %s. Recreating it" % (os.path.basename(self._dst_path), table_name))
                self._replace_table(table_name, src, dst)
            else:
                last_id_in_dst = c[0]
        src_command = "SELECT * FROM %s WHERE id > %d" % (table_name, last_id_in_dst)
        src_cur.execute(src_command)

        to_insert = []
        i = 0
        for sc in src_cur:
            i+=1
            tp = tuple([str(v) for v in sc ])
            to_insert.append(str(tp))
            if len(to_insert) > self._max_n_rows_to_insert:
                value_string = ",".join(to_insert)
                dst_command = "INSERT INTO %s VALUES %s" % (table_name, value_string )
                dst_cur.execute(dst_command)
                dst.commit()
                to_insert = []

            if dump_in_csv:
                with open(self._dam_file_name,"a") as f:
                    row = "\t".join(["{0}".format(val) for val in sc])
                    f.write(row)
                    f.write("\n")

        if len(to_insert) > 0:
            value_string = ",".join(to_insert)
            dst_command = "INSERT INTO %s VALUES %s" % (table_name, value_string )
            dst_cur.execute(dst_command)
        dst.commit() 
Example 30
Project: saliweb   Author: salilab   File: __init__.py    GNU Lesser General Public License v2.1 4 votes vote down vote up
def _check_mysql(env):
    """Make sure that we can connect to the database as both the frontend and
       backend users."""
    c = env['config']
    c._read_db_auth('back')
    backend = dict(c.database)
    c._read_db_auth('front')
    frontend = dict(c.database)

    try:
        db = MySQLdb.connect(db=c.database['db'], user=backend['user'],
                             unix_socket=c.database['socket'],
                             passwd=backend['passwd'])
        cur = db.cursor()
        for table in ('jobs', 'dependencies'):
            cur.execute('DESCRIBE ' + table)
            _check_mysql_schema(env, c, cur, table)
        cur.execute('SHOW GRANTS FOR CURRENT_USER')
        _check_mysql_grants(env, cur, c.database['db'], backend['user'],
                            'SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, '
                            'INDEX')

        db = MySQLdb.connect(db=c.database['db'], user=frontend['user'],
                             unix_socket=c.database['socket'],
                             passwd=frontend['passwd'])
        cur = db.cursor()
        cur.execute('SHOW GRANTS FOR CURRENT_USER')
        if c.track_hostname:
            hostname = 'hostname, '
        else:
            hostname = ''
        _check_mysql_grants(env, cur, c.database['db'], frontend['user'],
                            'SELECT, INSERT (submit_time, contact_email, url, '
                            'passwd, user, directory, %sname)' % hostname,
                            table='jobs')
        _check_mysql_grants(env, cur, c.database['db'], frontend['user'],
                            'SELECT, INSERT, UPDATE, DELETE',
                            table='dependencies')
    except (MySQLdb.OperationalError, MySQLdb.ProgrammingError) as detail:
        # Only complain about possible too-long DB usernames if MySQL
        # itself first complained
        _check_sql_username_length(env, frontend, 'front')
        _check_sql_username_length(env, backend, 'back')
        outfile = _generate_admin_mysql_script(c.database['db'], backend,
                                               frontend)
        print("""
** Could not query the jobs table in the %s database using both the
** frontend and backend users. The actual error message follows:
** %s
** This either means that you have mistyped the names or passwords of the
** frontend or backend users in the configuration file, or that the web
** service's MySQL accounts are not set up correctly. If the latter, please
** ask a sysadmin to run the commands in the file
** %s to set this up properly.
""" % (c.database['db'], str(detail), outfile), file=sys.stderr)
        env.Exit(1)