Lets Play With SQL

In My knowledge SQL or MySQL (Structured Query Language) is a tool for storing data in form of rows and columns. But when I started working deeply with SQL I realized that it is not only a query language ,we can use it as a power full programming tool for data manipulation.

We all know about basic SQL commands like ‘Create Table’,'Insert Into’, ‘Select’, ‘Delete’,'Drop’ etc. Here I am try to introduce some commands that not regularly use, but very use full.

Usually LIKE is using for pattern matching. But we can use ‘LIKE’ also for creating a new table structure as an existing table.

"CREATE TABLE new_table_name LIKE existing_table"

After creating table structure we can copy contents from other table using INSERT INTO command

"INSERT INTO new_table SELECT * FROM existing_table"

It copy all data from one table to another. If we need to copy only specific column we can specify column names

"INSERT INTO new_table (col1,col2,......)SELECT col1,col2,.... FROM existing_table"

we can also give specify copy data with in a limit.

"INSERT INTO new_table SELECT * FROM existing_table LIMIT 1000 OFFSET 1000"

It copies data from 1000th raw up to 2000th row.

We can rename a table using rename command;

"RENAME TABLE tanle_name TO new_name"

There may be some situations that we need to back up all the data from a table or back up entire data base. For this purpose we use MySQL dump command. These are very use full system commands for creating back up.

MySQL Dump Export command for backing up:

Dump Database:

"mysqldump --user='username' --password='password' db_name > filename.sql"

Only Database Structure:

"mysqldump --user='username' --password='password' --no-data db_name > filename.sql"

Database Data:

"mysqldump --user='username' --password='password' --no-create-info db_name > filename.sql"

Dump Severel Databases in to a single file:

"mysqldump --user='username' --password='password' --databases  db_name1,db_name2 > filename.sql"

Dump All databases in Sever:

"mysqldump --user='username' --password='password' --all-databases  > filename.sql"

Restore and Import Databases:

"mysql --user='username' --password='password'  db_name < filename.sql"

To Export A Table Structure:

"mysqldump --user='username' --password='password' db_name table_name >filename.sql"

To Export A Table data:

"mysqldump --no-create-info --compact --user='username' --password='password' db_name table_name > table_name.sql"

Import Table:

"mysql --user='username' --password='password'  db_name < table_name.sql"

Power Of Walk

Os.path.walk is a fruitful system command in python. Using walk we can easily parse recursively through the folder.It gives the file names with in the folder and in the sub folders.  The syntax of walk is very simple. os.path.walk takes exactly three arguments. Syntax of os.path. walk is-

os.path.walk(parent_folder,function_call,pattern)

The first argument ‘parent_folder’ is the directory name that we need to parse. function_call is a function call that used to parse each folder and sub folder. It also takes three arguments. The third one pattern is actually a filtering parameter. We can give the extensions of file that we need to manipulate. If we need to get only mp3 files then we can give ‘*.mp3′ for pattern. Like we can give any extension. For getting all the files we can give ‘*.*’ for pattern.

Here am giving a small example to show the power of walk.

import sys
import shutil
import re
from fnmatch import fnmatch
import os, os.path

global destntn_dir
destntn_dir = None

def mp3_finder(pattern, dir, files):
 for filename in files:
  if fnmatch(filename, pattern):
   file_path =  os.path.join(dir, filename)
   #Copying files to destination folder
   shutil.copy(file_path,destntn_dir)

def main():

 ## Collect  command line arguments
 parent_folder = sys.argv[1]
 global destntn_dir
 destntn_dir = sys.argv[2]
 #Creating Destination Folder
 os.mkdir(destntn_dir)
 ## Walk folder
 os.path.walk(parent_folder,mp3_finder, '*.mp3')

if __name__=="__main__":
  main()

The above example copy all the mp3 files from the given folder and its sub folders to another folder. Here we are creating the destination folder inside the program.

Usage:
Run the program in the console using python command. We can give the source folder and destination folder as command line arguments. Please give the folder names along with their paths. Here the destination folder will create according to the path.