MySQL: How to export data to csv with column headers

I recently had to export a whole number of tables from a MySQL database into .csv files, but to my surprise (or should I say disgust), exporting to .csv with headers is not possible by simply using to the “SELECT … INTO OUTFILE” syntax.  Fortunately all is not lost, although to me this is more proof that MySQL is not really suitable for large applications. Here is how I solved the problem.

I created a table (`tables_to_export`) with a single `tablename` column which I populated with the names of the tables that I wanted to export.  I then created a procedure to traverse this table by means of a cursor so I could build the sql statements required to get the desired export result.

Basically the trick is to create a text string of comma separated column names that is used to create a select union statement to combine the column names with the data.  There are quite a few examples of doing this with a single table and known column names, but I wanted to do this for a whole lot of tables in a loop.  I also need to repeat this export in future, so it made sense to automate the process.

Note: The ‘information_scheme’ database contains a ‘columns’ table that has all the column names, so I used that to get the names to create the string.  The

CREATE DEFINER=`root`@`localhost` PROCEDURE `export_important_tables` _
     (IN `quote` TINYTEXT, IN `delimiter` TINYTEXT, IN `export_dir` VARCHAR(200))
 LANGUAGE SQL
 NOT DETERMINISTIC
 CONTAINS SQL
 SQL SECURITY DEFINER
 COMMENT 'Export in csv format with headers'
cont:BEGIN
/* This procedure has been created to add the column names as a header column 
   when exporting mysql tables.
   A table, 'tables_to_export', contains the list of tables to be exported

Usage example: CALL export_important_tables('"',',','/tmp/');

*/
-- Declare some variables
   DECLARE done INT DEFAULT FALSE;
   DECLARE myFile CHAR(30);
   DECLARE curs CURSOR FOR SELECT * FROM `foodmedb`.`tables_to_export`;
-- Declare a cursor
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- There don't need to be set (@variables don't exist outside the procedure, 
-- but I set them for easy reference anyway
   SET @myResult = '';
   SET @myHeader = '';
   SET @myCommand = '';
   SET @myCombined = '';

-- Do some very simple checking for valid parameters
   IF LENGTH(`quote`) > 1 THEN 
       SELECT 'Parameter Error:  may not be more than 1 character';
       LEAVE cont;
   ELSEIF `quote` = '' THEN SET `quote` = '"';
   END IF;

   IF LENGTH(`delimiter`) > 1 THEN 
       SELECT 'Parameter Error:  may not be more than 1 character';
       LEAVE cont;
   ELSEIF `delimiter` = '' THEN SET `delimiter` = ',';
   END IF;

   IF RIGHT(`export_dir`,1) != '/' THEN 
       SELECT 'Paramater Error:  has to end in a /';
       LEAVE cont;
   ELSEIF LENGTH(`export_dir`) = 0 THEN SET `export_dir` = '/tmp';
   END IF;

-- Initialise the cursor query
OPEN curs;

-- Set up a loop so we can traverse all the row in the cursor table
table_loop: LOOP

-- Get a filename
   FETCH curs INTO myFile;
   IF done THEN
      LEAVE table_loop;
   END IF;
-- Build a sql statement string that concatenates the column names from the 
-- information schema's 'columns' table to build a header for the csv file
   SET @myHeader = concat('SELECT GROUP_CONCAT(',char(39),`quote`,char(39),',
   COLUMN_NAME, ', char(39),`quote`,char(39), ' SEPARATOR ', char(39),`delimiter`,
   char(39),') FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE table_schema=',char(39),
   'foodmedb',char(39),' and table_name=',char(39),myFile,char(39),
   ' INTO @myResult');
   PREPARE stmt1 FROM @myHeader;
   EXECUTE stmt1;
-- Build the the sql statement string that will dump the data into the csv file
   SET @myCommand = concat('SELECT * FROM foodmedb.', myFile, ' into OUTFILE ', 
   char(39), `export_dir`, myFile, '-', DATE_FORMAT(now(),'%Y%m%d-%H%i%s'), '.csv',
   char(39), ' FIELDS TERMINATED BY ', char(39), `delimiter`, char(39), 
   ' OPTIONALLY ENCLOSED BY ', char(39), `quote`, char(39), ' LINES TERMINATED BY ',
   char(39),'\n',char(39));
-- Use Union to combine the two parts
   SET @myCombined = concat('SELECT ', @myResult,' UNION ALL ',@myCommand);
   PREPARE stmt2 FROM @myCombined;
-- Sweet, now the files are being written!
   EXECUTE stmt2;

END LOOP;

CLOSE curs;

END

This stored procedure should function as it is above.  I have created parameters and some simple error checking to assist with using it more generally than just my particular use.

Please make suggestions and improvements and post them in a comment if you’d like.

on basic “facts” vs “skills”

I had a few tweets with Samantha Laing, Helen Zille & JC re facts vs skills and thought I’d write down my thoughts in a more “agile” medium that twitter, so here goes!

Firstly, in education in general, we must approach things differently if we wish to have different results from what we’re currently having.

I made a point about basics.

That boils down to really basics.

I find many kids, a large number of black South Africans, but also, to a much a larger extent than previously, coloured and white kids (if I may generalise so brashly for this purpose), that do not have the basic ability to communicate, express their thoughts or write in any language. They cannot grow their skills by reading, unless they really really want to or have to, and then it is with great difficulty, simply because they cannot read. Of course, the under-stimulated mind is not interested in much, so this is a self-defeating cycle and few manage to break out of it. It’s a culture of entitlement, supported by the acts and talk of national government, not a culture of learning, self-improvement and development, that we currently have. Having said that, there are notable exceptions and sterling examples of quite the opposite, but sadly they are the exception, not the norm.

Kids Reading

Reading kids rule!

I personally endeavour to motivate people (of all races & incomes) to read to their young children daily and to let the children read to other young ones again. I encourage them to use our public libraries and improving their own reading in process, starting with simple books, advancing as they make progress. I find, however, that there is a resistance to this, that watching TV is less effort, and therefore progress is slow.  It doesn’t help, of course, that we have a culture of laziness among many teachers in public schools (that for example sit and drink tea till first break or involving themselves in political activism during school-time, while primary school kids entertain themselves) while others work their fingers to the bone in an effort to somehow equip kids to be able to learn more and “make a living”. The same goes for DoE officials who love spending money on futile pursuits and unproven educational experiments, or even worse, educational exploits that have been undeniably proven a failure. Instead of a pragmatic simple approach, they diddle-daddle with trivial and peripheral issues (attempting to bring high technology to kids for example), while kids come out of school being neither able to read nor write, let alone reason, spot logical fallacies or think creatively. The latter, is of course often due to the stifling of creativity in the schooling system.

My wife and I have home-schooled two kids and have two in public school, so we have seen how certain approaches just don’t work and how others have great results.  We know how much kids from the same parents and home differ in learning style, interest and ability in different areas, let alone kids from different parents, homes and environments.  I am therefore by no means closed to alternative views or approaches to education and skills development! I also taught at a high-school for a while and have seen the practical side from a teachers perspective and know the challenges faced in teaching children other that your own.

I furthermore firmly believe education is not primarily a function of Government, but one of the community.  Once solution could be this: The elderly are often excellent teachers and have a lifetime of wisdom and experience to draw on in transferring skills, developing young people into achieving their dreams and making a meaningful contribution to society at large and individuals in particular. As it is now, many are simple shoved aside and considered a burden. Instead of giving social grants to the unemployed, maybe grants should be given to all elderly people that develop young people. The control that National Government attempts to exercise more and more in a clear attempt to maintain is power-base, it in itself shameful and deplorable, but by now means unique to South Africa. I saw with my own eyes as a teacher the unbelievable lows that the DoE stooped to during the teachers strike in 2004 in an all out attempt to not pay teachers more than their measly salaries – straight lies, deceptive tactics and every dirty trick in the book, that I would not have believed, had I not seen it with my own eyes.  The correlation between education and controlling poorly educated masses may not seem clear to all readers, but as I have pointed out elsewhere, it is much easier to control hungry, needy masses, than educated, enlightened free-thinking people.

We can however break this stranglehold, starting in our communities by grow a culture of learning, breaking the culture of entitlement, developing the live-long ability and desire to grow, learn and go where we haven’t been before.  How is not easily answered, but one thing is certain: As soon a the DoE stops their monopolistic attitude towards education, especially since they’re failing dismally at it, and the national government actively supports educational efforts outside of their government box, we will make huge progress with this.  Some points to consider on this regard:

  1. Why are donations to schools and school fees not tax-deductible to start with?  How can anyone in government claim their are serious about education while strangling the development of schools in the community?
  2. Why are schools forced into languages which are foreign to them and their communities?  How many Afrikaans schools, with Afrikaans learners are forced to start teaching in English?  Has the ANC learned nothing from (or rather since) the struggle? The same applies to Xhosa speaking kids that are forced in English medium at a young age – many never catch up again.
  3. Why are people that choose to home-school (and generally speaking having excellent results!) persecuted by the DoE, when in fact both the Constitution and the Education Act specifically protect the right of parents to home-school?  Why does the DoE waste millions of Rands on these efforts, when they have so many schools that are ill equipped, even some with no classrooms?
  4. By what right does the DoE pay subsidies to public schools, yet when the same tax-paying citizens send their children to a Waldorf, Montessori or other private school, they do not receive their fair share of the tax collected for education?  Are most of these schools not superior in the level of education, skills and ability that they “produce”?  Should they not therefore be supported even more?
  5. Why are well-education, highly skilled teachers, that we’re “fired” because of BEE, not brought back to alleviate the desperate shortage of skilled teachers?  So what if they’re the “wrong” colour!?  When will the short sighted racism of the ANC stop and will they start serving South Africa, as their election mandate requires of them?  Actually, when will the people who put them in power, remove them if they don’t deliver?  This is not about politics, but when politics starts interfering with the development of the future of our country, it becomes all about politics.
I suppose there are more points that can be added here, but the principle is, simple.  Government but stop their nanny state, preventive approach and support and encourage communities to grow, learn and thrive.

I’d like to hear from people that have ideas and experience in this!

Youthday! Youthday. Youthday?

Celebration and rememberance.

Both are good and both are needed.

But what have the youth of today learned from the past and do people in general learn from history?

Unfortunately history is passed on mostly in the way the rulers of the day want it to be remembered. So to learn from history requires effort. Effort is rarely what youths want to exert.

One thing we know for sure is that lack of education and skills makes one poorer. An illiterate is easy to con into believing lies, a hungry person is easy to “buy” with the promise of food, a bamboozled person thinks he’s been given something, when in reality he’s been used.

In our recent past there were those leading the charge that said “liberation before education”!  What has that left us with?  Neither education nor liberation!  Yes, there is the illusion of liberty because we have laws that say we are free (to an extent), but is someone who doesn’t have a house, who doesn’t have a job, who can’t do much in terms of economic activity free? Is someone who can barely read and doesn’t understand what is being written or said free? Is someone who receives a measly grant from government in order to survive (and has his vote purchased by the ruling party in the process) free? Are the people who are forced to pay the taxes to support the uneducated and government’s numerous failed attempt to save their doomed plan free?

The lesson to take away from youth day this year is this: South Africa is not free. Not until all are reasonably educated, until higher standards are set for our government, not until people start realising their mistakes of the past and learn from it, not until education comes before any other ideal, we will have a reasonable chance to be free. It is difficult to lie to someone who is well informed, hardly possible to cheat someone that understands what is happening and to enslave a wise person with good understanding are not easily achieved.

So who is brave enough to acknowledge that some of the fervour of the leaders of the past was misled?  As a result of those choices we now have uneducated government leaders who make foolish choices and policies that enslave our children even more by keeping them in the dark (litererally and figuratively). “Liberation before education” was wrong and we’re paying the price for it and will be for many years.

The first step in rectifying that is to acknowledge that it was wrong.

The second would be to change our attitude from one of entitlement, to one of achievement. Personally I endevour to teach little ones that we can learn what we set out to learn, that we can read and write without government paying huge amounts of money to little effect, that watching tv is mind numbing, that reading books is better than drinking beer, that we can learn from other’s mistakes and if we don’t we will more painfully learn from our own, and that praying to God for wisdom, courage and strength is never in vain and is heard every time.

Finally, it’s good to know the past and to consider it, but reminiscing past glories is foolish if you aren’t ready and equipped yet to face the future.

AGW circus links

When I find something that I think is worthwhile I add the link here.  That’s all, take or leave it.

The inglorious end of the scientific method & peer review? Phil Jones

Some good weather data links: La Colline Observatory, Stellenbosch, South Africa

WWF Living Planet Report: Only global poverty can save the planet, insists WWF – and the ESA!

Ice levels increasing: Himalayan glaciers actually GAINING ice, space scans show

No hockey-stick type graph exists: There is no sharp hockey-stick shaped end to the graph , so there is no cause for alarm, we’re not artificially warming the planet.

Tree rings show cooling: Orbital forcing of tree-ring data

Earth Temperature analysis by Richard A. Muller, professor of physics at the University of California, Berkeley:  http://berkeleyearth.org/

File system root rights affect the whole file system

Check the rights for the file system root if “access denied” errors occur when logs, pid files, temp files or others are needed, but cannot he written.

I recently had to troubleshoot a system where the file access rights got messed, although all the rights for /var/log, /tmp and more where correct.  It turned out eventually that the fs root (/) didn’t have the right for others to read, which had a knock-on effect disallowing things like /var/run/… to be accessed by mysql, postfix, ntpd, etc, thus preventing the pid file to be written.
There were more strange access denied errors, but once the root right was set allow others to read, all these errors went away.