Existing MySQL database structure to Web2Py table definitions

Web2Py is a nice Python framework for rapid development of web-based applications. I’ve been using it quite a bit in the past two years, and got accostumed to the ease and quickness of development with it.

Recently I’ve been tasked with the rewrite of a big PHP-based website, with an existing huge database (60+ tables, millions of rows). Since the database seemed to be well-designed, I decided that I’d base my rewrite around it.

For this, I needed the whole database’s structure as a web2py model definition. Doing this by hand would be too tedious, so I decided to write a small Python script to do the work for me.

This is of course far from a full converter, as it doesn’t include all possible variable types that a column might have, only the ones I encountered in the database I processed, but I hope that this can save some time to someone who’s trying to do the same thing. Feel free to use my script as a starting point, or unmodified. Just don’t hold me reliable for any damage that might occur. :)

It also ignores defaults and other extra info.


import os, sys
import MySQLdb

#use your own settings here
conn = MySQLdb.connect (host = 'localhost',
  user = 'root',
  passwd = '',
  db = 'shop')

cur = conn.cursor ()
cur.execute ("SHOW TABLES")
tables = cur.fetchall()
for t in tables:
  print 'db.define_table ("%s",' % t
  cur.execute ("DESCRIBE %s" % t)
  cols = cur.fetchall ()
  for c in cols:
    n = c[0]
    t = "UNKNOWN"
    l = ''
  if c[1][:3] == 'int' or c[1][:7] in [ "decimal", "tinyint", "mediumi", "bigint(" ] or c[1][:8] == "smallint": t = 'integer'
  if c[1][:7] == 'varchar':
    t = 'string'
    l = c[1][8:-1]
  if c[1][:4] == 'char':
    t = 'string'
    l = c[1][5:-1]
   if c[1] in [ 'text', 'datetime', 'date', 'time' ]: t = c[1]
   if c[1] in [ "mediumtext", "longtext" ]: t = "text"
   if c[1] == 'float': t = "double"
   if l:
     print '     Field ("%s", "%s", length = %s),' % (n, t, l)
     print '     Field ("%s", "%s"),' % (n, t)
   if t == "UNKNOWN":
     print c[1] # to review unknown col types
   print "     migrate = False)"

cur.close ()

conn.close ()

IRC Uno bot for Phenny

In my free time I like to hang out on a particular IRC channel, where we occasionally play some simple games. One of our favorite was Uno, a simple mIRC script that hosted Uno games. Since it is a mIRC script, it’s difficult to keep on the channel all the time, so I decided to clone it for Phenny, the Python IRC bot.

It was a nice exercise, and I think the end result is not so bad, so I’m making the code public.

– slightly modified UNO gameplay
– unlimited number of players in a round (minimum 2)
– logging of total score, games played, won, and total time spent playing per nickname
– separated strings, so localization to other languages should be easy

How to set up:
1. Download the unobot.zip file below, and extract the unobot.py file from it.
2. Install phenny. Edit the ~/.phenny/default.py config, add the unobot.py file with path to the ‘extra’ modules. Set up the other settings as desired.
3. Edit the unobot.py file, set the channel’s name (same as in default.py), and change the score file path. Make sure that Phenny has the proper rights for writing the file.
4. All set, start phenny and enjoy playing, by typing the .uno command in the channel!

Known issues:
Note that ordinary IRC nicknames are used to identify the players. The bot cannot react to a player leaving the channel or timeouting, either the game must be stopped (if the game owner quit then someone will have to take their nick temporarly to do the .unostop action) or someone must step in with the same nickname.

Download unobot.zip

Uno Bot for Phenny is licensed under the FreeBSD license.

For more games for Phenny visit Phenny Games!

LazyClick won in the Firefox Mobile Add-on Challenge!

It was a great surprise when I got the news that my humble Firefox Mobile (aka Fennec) add-on was among the ten best add-ons submitted for the challenge, especially since I saw that there are some really great and innovative creations emerging in the AMO site.

I never won anything worth mentioning, so it will be very exciting when I get the new N900, a gadget that is every geek’s dream! It’s called Mobile Computer and not Mobile Phone for a reason, after all! :)

I’m planning some new things for LazyClick, first of all supporting the desktop version of Firefox (it will only support version 3.6+, because the addon uses some newer APIs that are not available for current versions).

The next thing will be to try to port LazyClick to the default Maemo browser, which shouldn’t be too hard since that browser also uses Mozilla technology (there’s already a bunch of add-ons that are common to the two browsers). I’ll wait with this one until I have the actual N900, it will be easier to test that way.

Anti stress tips

If you’ve been working a lot or are under a lot of stress (kids, too many hours, responsible position, people complaining to you a lot) your mind may simply be locked up. This is a panic reaction, a last defense mechanism for a mind running with hormonal imbalances. It will mean your cortisol is too high early on, then drops off at the time you need it most. Your body’s stress response is worn out and depleted and needs time to regenerate. That’s why burn-out victims are usually told to go home and do nothing for a year. It takes a LOT of time.

Ask yourself the following questions:

– Do I drink more than two cups of coffee every day? Too much caffeine will elevate cortisol and make you more sensitive to stress. Cut down on coffee and take Ginseng supplements – this will help your body restore its decreased cortisol capacity. I personally like green tea with ginseng. It takes a bit of time to switch, just do it gradually and you won’t run into problems. Try to cut smoking and alcohol as well as much as you can.

– Can I work for a few hours only, and then feel like my mind “locks up”? This is definitely a stress sign. If that’s the case, take a 10 minute break for a walk OUTSIDE, EVERY HOUR. (I mean it! I had the same problem, and an external management bureau with lots of hands-on experience gave us these tips, and they worked.) This will help you de-stress and over longer time will help reset your body’s hormone system. Also: cut away any other stress factors. Are you busy two days a week helping your kids’ soccer teams do their paperwork? Let it go. This will be difficult if you are indeed tottering near burnout – it makes you more emotional since your “logical” brain is less active – but it is vital. Also, do you jump out of your skin every time your mobile phone rings? Also a very strong indicator of stress. Be wary and get to know your own fear signs. If it is all too much, consider if you can let it all go and rest for at least half a year. The project will still be there then, or if you are overtaken by competition, there will be other projects. Rule one of life: we all want to feel good. Only then can we help others and be productive. If you’re living only for others and don’t feel good doing it, something is wrong. Some people – especially those with a large sense of responsibility – are extremely sensitive to stress, and you don’t know for certain until you have crashed through and are well beyond your limits. That’s what it took for me to realise something was wrong: I could barely do the dishes without being told how to do it. Don’t let it get to that.

– Do I feel miserable? If so, that might indicate not only burnout, but also dysthemia/depression. GET PROFESSIONAL HELP. And I mean a real shrink, not just a psychologist. He can temporarily set you up on medication which will help you recuperate; after some time you can then build down and get rid of the meds once you’re standing on your own feet again. But for the time being, medication is probably essential as a crutch. Note: it is of VITAL importance that you find one you’re comfortable with; if you don’t feel OK with the shrink after a few sessions, thank him/her and just tell them that you don’t feel allright and try another one. It can take a few tries to find the right person.

– Do I sleep enough? If you’re not getting at least 6 hours of clean sleep, you’re probably stressed or burned out. I remember a “burning” feeling from that period and only sleepint 2-3 hours a night.

– Do I have a real work place, and are there distractions? I ran my own company for seven years, and in the beginning I worked from home. In doing so I could never really “get away” from it. Once I had an office outside the door it made a real difference: I went to the office to work, and psychologically that also did a trick – it set me into “work mode”. I left the browser and Minesweeper alone and really went for it. When I worked at home I tended to loiter and surf and delay, but my office computer and surroundings were set up for work only: no games, simple browser, multiple computer screens for lots of info processing, good large desk, no distractions (other than iTunes on an ambient radio station, and that only occasionally). It takes around 8 minutes for the mind to get into “focus mode”, and distractions – including the phone – disturb this.

– Can I daydream? Daydreaming is actually your creativity running rampant. I use daydreaming as a trick to get into “creative mode”: first I daydream about something simple, then once it’s active I start dreaming in the direction of the challenge I’m working on (e.g. how to set up some project or other, or what I want to include in a business plan). It’s old-fashioned brainstorming and should work well for coding also. See if you can come up with a trick to get your creativity flowing.

– Have I drawn it out? The brain has 30something different processing sections for visual information. Draw it out! Draw lines, bubbles with ideas, connect them together, make up a start for a database, jot down everything you’re planning. This will also help release your creativity as you see more and more links and possibilities.

All in all it will take a bit of puzzling to work out what the problem is, and if your brain is overstressed this may of itself be a challenge. Ask trusted friends to help you – their objective views can be vital. Ask doctors and shrinks what they think. It may be that you need a good vacation before getting started again. Keep in mind that evolutionally speaking, the brain is now in a period of great challenge – it’s never had to deal with large society issues, jobs, taxes, mortgages, programming and whatnot before. The demand on the mind is immense and there’s bound to be limits and problems. Give yourself the space to breathe and don’t overdo it, and you should find that not only will the problems solve themselves, you’ll also enjoy life much more and be much more motivated do get things done, to such a degree that life becomes automatic. I recommend reading Mihaly Csikszentmihalyi’s books, Flow and Creativity, as well. It has a lot of good info on creativity, the mind, and what we call “flow”. Good luck!

Original article is a slashdot comment by El Jynx, LINK.


– have a BIG work desk. A big desk (everything in easy reach) is like computer memory to a person, and your brain is the processor and the filing cabinet is the hard drive (spacous, but slow).

– There are many different people with different reactions to stress. A friend of mine used to faint regularly, and feel dizzy all the time. After she stopped her second job, a few weeks later she felt a LOT better. Listen to what your body is telling you; if you aren’t sure, try changing things to see if it helps. Keeping a diary also helps. Note down what you did generally throughout every day – eat, drink, smoke, work, etc – and try to discover patterns. You may, for example, work out that the acne you get twice a month is actually connected to those few times you carpool with someone. Bad fibers in his car?

– be wary of other peoples’ expectations. Are you a YES-sayer? Teach yourself to say NO or, at least, “I’ll get back to you on that”. I remember having immense problems resisting people who wanted me to do something for them because they projected their problems on me: “my computer HAS to work this weekend! My kids want to play games! I need to check e-mail!” It took me the burnout session to realise that that is THEIR problem. If that’s an issue they’d best get a second computer. One of the best ways to get burned out is by assuming too much responsibility. If you’re taking projects yourself, jot down how much time you think something will take, and double it. Use that number to plan your time. If you’re in a corporate environment, tell your boss you’re full and that you either need an assistant, or an intern, or more colleagues who can help you with the work; if he’s got a dinosaur brain (like many managers do), write down your predictions and don’t be afraid to give him the I-told-you-so after the $#!7 hits the fan because he didn’t listen. However, do it constructively: tell him you’d like to discuss things with him to ensure it works better in the future. Destructive commenting is too easy and doesn’t achieve anything at all aside from a few seconds of self-vindication. It may get you fired, but usually you’re just doing your job and your managers’ superiors will realise this; you can’t help it you have a better view of things than your manager.

– If something seems “too big” or “too complicated”, once again you’re in the danger zone of a burnout; check my earlier post to see if you can recognise more warning signs. If you feel good and fit and are productive, on the other hand, it may not be burn-out but it may only mean you need to chop the project up into smaller parts to give you a good overview. If you can oversee all the steps in each of the parts, and can oversee how they all connect, you can see the whole project. This makes running it MUCH easier since you can keep an eye on both micro and macro goals.

– Set limits to your project beforehand. Make estimates of how long you plan to invest in a certain segment of the project, what you want to achieve, and STICK TO IT. If you have time left you can add extra stuff later. If you get extra ideas en-route, write them down but don’t integrate them unless it’s absolutely vital. You can review the achievements and extra ideas later. Also, plan in time for EVERYTHING: time for planning, time for arguing with colleagues, time spent in traffic, time on the phone, time reviewing the code or project. Try to hook a number on everything. You might not get it correct right away, but you will develop a much better feel for what you are doing and how much time each part takes, because you are consciously trying to manage them. A famous saying is: “who fails to plan, plans to fail.” This is exponentially true of corporate environments with many people in it. On your own, or with two people – three max – you can do everything ad-hoc and take things as they come; above that and you need structure, work procedures, flowcharts, and people chasing each other’s asses around. It sucks, but that’s the way it is.

– Try to stick a bit of extra time organising yourself and your ideas. It takes effort and the rewards aren’t immediate, but on the long run it will really pay off because of many different reasons: among others, it helps you structure your thoughts more easily, which in turns means it is easier to communicate them to others; it gives you overview; it means others can more easily take on your work if you ever get sick; it makes you more aware of all the aspects surrounding what you are doing; it gives you insight into why managers, coders, and other people think and act the way they do; the list goes on and on. I even made a “life management plan” which I revise twice a year. It tells what I want to achieve the coming year, where I want to be in 2 years, 5 years, 20 years. The 5+ year sections are only a page each, and are often unrealistic or need to be adjusted. That’s fine. It means I’m consciously trying to achieve certain goals, and allows me to alter my course should something jeopardise my reaching them, or maybe they will turn out to be impractical. By structuring it, I help keep my short-term and long-term planning united and clear, and in that sense, the process and the goal are one and the same: clarity.

– WORK OUT. Sports are invaluable. They help you unwind, get you outside, clear kinks in muscles and bones, and prevents any number of ailments. Lower back problems? Go swim twice a week, or go rowing. I guarantee that 90% of you will no longer have ‘em a few weeks later. Upper back and neck feeling locked up after too much computer work? Get some weights in the house (not too heavy, but not too light either), get on your knees and then on all fours (like a cat normally walks, lets say), put the weight under your hand, and with one arm at a time, pull the weight upwards with your elbow going at 90 degrees outward. This forces you to use the muscles more intensively. RSI is caused by the buildup of waste materials in your muscles; the trick then is to increase the blood flow to make them get carried away as they should be. The best way for this is to use the locked muscles. Two times 15 pulls on each arm is usually enough to get rid of most of the problems. If you aren’t sure which muscles are used for what, consult a therapist or Google it.