Python : Accesssing a MySQL Database

The MySQLdb module makes this task extremely easy:

import MySQLdb

# Create a connection object, then use it to create a cursor
Con = MySQLdb.connect(host="127.0.0.1", port=3306, 
    user="joe", passwd="egf42", db="tst")
Cursor = Con.cursor(  )

# Execute an SQL string
sql = "SELECT * FROM Users"
Cursor.execute(sql)

# Fetch all results from the cursor into a sequence and close the connection
Results = Cursor.fetchall(  )
Con.close(  )

Youcan get the MySQLdb module from http://sourceforge.net/projects/mysql-python. It is a plain and
simple implementation of the Python DB API 2.0 that is suitable for all  Python versions from 1.5.2 to2.2.1 and MySQL Versions 3.22 to 4.0. 

Python : Errors and Exceptions

Until now error messages haven’t been more than mentioned, but if you have tried out the examples you have probably seen some. There are (at least) two distinguishable kinds of errors: syntax errors and exceptions.

Syntax Errors

Syntax errors, also known as parsing errors, are perhaps the most common kind of complaint you get while you are still learning Python:

>>> while True print 'Hello world'
  File "<stdin>", line 1, in ?
    while True print 'Hello world'
                   ^
SyntaxError: invalid syntax

he parser repeats the offending line and displays a little ‘arrow’ pointing at the earliest point in the line where the error was detected. The error is caused by (or at least detected at) the token preceding the arrow: in the example, the error is detected at the keyword print, since a colon (':') is missing before it. File name and line number are printed so you know where to look in case the input came from a script.

Python : Input and Output

Fancier Output Formatting

So far we’ve encountered two ways of writing values: expression statements and the print statement. (A third way is using the write() method of file objects; the standard output file can be referenced as sys.stdout
Luckily, Python has ways to convert any value to a string: pass it to the repr() or str() functions.
The str() function is meant to return representations of values which are fairly human-readable, while repr() is meant to generate representations which can be read by the interpreter (or will force a SyntaxError if there is not equivalent syntax). For objects which don’t have a particular representation for human consumption, str() will return the same value as repr(). Many values, such as numbers or structures like lists and dictionaries, have the same representation using either function. Strings and floating point numbers, in particular, have two distinct representations.
Some examples:

>>> s = 'Hello, world.'
>>> str(s)
'Hello, world.'
>>> repr(s)
"'Hello, world.'"
>>> str(1.0/7.0)
'0.142857142857'
>>> repr(1.0/7.0)
'0.14285714285714285'

PHP String Functions

explode

Description :

array explode ( string $delimiter , string $string [, int $limit ] )

Returns an array of strings, each of which is a substring of string formed by splitting it on boundaries formed by the string delimiter.   

 Examples  explode() 

<?php
// Example 1
$pizza  = "piece1 piece2 piece3 piece4 piece5 piece6";
$pieces = explode(" ", $pizza);
echo $pieces[0]; // piece1
echo $pieces[1]; // piece2

// Example 2
$data = "foo:*:1023:1000::/home/foo:/bin/sh";
list($user, $pass, $uid, $gid, $gecos, $home, $shell) = explode(":", $data);
echo $user; // foo
echo $pass; // *

?>

Work Shop at Drishti School, Ludhiana

In Drishti School, Near Ludhiana, we  give a Workshop . It was very interesting to teach little children and their teachers Linux Softwares and to solve their problems. We are six students with Dr. H.S Rai. Drishti School has bought 40 Linux based Tablet-PC which has default ubuntu 8.10 installed on them. we all gave presentations on different things.

I have given my presentation on KGeography  for kids.

KGeography is a Free and open source educational software game that tests students geographical knowledge

Besides for educational purposes, KGeography can be used for evaluating ones knowledge. Right now it has five games:

Display an image from a MySQL database in a web page via PHP

Here's a sample HTML page we're going to include an image in:

<html>
<head>
<title>Demo of Database Image in a page</title>
</head>
<body>
Here is your picture:<br>
<img src=picscript.php?imname=potwoods><br>
Example by Well House Consultants
</body>
</html>

Then you need the PHP script - called picscript.php in the same directory in my example:

<?php
mysql_connect("localhost","wellho","xxxxxxx");
mysql_select_db("wellho");
$image = stripslashes($_REQUEST[imname]);
$rs = mysql_query("select * from im_library where filename=\"".
addslashes($image).".jpg\"");
$row = mysql_fetch_assoc($rs);
$imagebytes = $row[imgdata];
header("Content-type: image/jpeg");
print $imagebytes;
?>

Basic Guide lines for python coding

For Python, has emerged as the style guide that most projects adhere to; it promotes a very readable and eye-pleasing coding style. Every Python developer should read it at some point; here are the most important points extracted for you:

• Use 4-space indentation, and no tabs.
  4 spaces are a good compromise between small indentation (allows greater nesting depth) and large indentation (easier to read). Tabs introduce confusion, and are best left out.
  
• Wrap lines so that they don’t exceed 79 characters.
  This helps users with small displays and makes it possible to have several code files side-by-side on larger displays.
  
• Use blank lines to separate functions and classes, and larger blocks of code inside functions.
  
• When possible, put comments on a line of their own.
  
• Use docstrings.
  
• Use spaces around operators and after commas, but not directly inside bracketing constructs: a = f(1, 2) + g(3, 4).
  
• Name your classes and functions consistently; the convention is to use CamelCase for classes and lower_case_with_underscores for functions and methods. Always use self as the name for the first method argument .
  
• Don’t use fancy encodings if your code is meant to be used in international environments. Plain ASCII works best in any case.
  

PHP function for PostGreSQL database

pg_connect() opens a connection to a PostgreSQL database specified by the connection_string.
If a second call is made to pg_connect() with the same connection_string as an existing connection, the existing connection will be returned unless you pass PGSQL_CONNECT_FORCE_NEW as connect_type.


The old syntax with multiple parameters $conn = pg_connect("host", "port", "options", "tty", "dbname") has been deprecated.

For example :-

$con = pg_connect("host=$dbhost user=$dbuser password=$dbpasswd dbname=$dbname" )

pg_close() closes the non-persistent connection to a PostgreSQL database associated with the given connection resource.

For example :-

pg_close($con);

pg_connection_status() returns the status of the specified connection.

For example :-

<?php
  $dbconn = pg_connect("dbname=publisher") or die("Could not connect");
  $stat = pg_connection_status($dbconn);
  if ($stat === PGSQL_CONNECTION_OK) {
      echo 'Connection status ok';
  } else {
      echo 'Connection status bad';
  }  
?>

pg_fetch_all() returns an array that contains all rows (records) in the result resource.

For example :-

$arr = pg_fetch_all($result);

print_r($arr);

pg_fetch_result() returns the value of a particular row and field (column) in a PostgreSQL result resource.

<?php
$db = pg_connect("dbname=users user=me") || die();

$res = pg_query($db, "SELECT 1 UNION ALL SELECT 2");

$val = pg_fetch_result($res, 1, 0);

echo "First field in the second row is: ", $val, "\n";

?>

pg_fetch_array() returns an array that corresponds to the fetched row (record).
pg_fetch_array() is an extended version of pg_fetch_row(). In addition to storing the data in the numeric indices (field number) to the result array, it can also store the data using associative indices (field name). It stores both indicies by default.

For example :-

$arr = pg_fetch_array($result);
echo $arr["author"] . " <- Row 3 Author\n";
echo $arr[1] . " <- Row 3 E-mail\n";

pg_query() executes the query on the specified database connection.

For example :-

$result = pg_query($con, "SELECT * FROM $tablename where sr_no = 10");

pg_num_rows() will return the number of rows in a PostgreSQL result resource.

For example :-

<?php
$result = pg_query($conn, "SELECT 1");

$rows = pg_num_rows($result);

echo $rows . " row(s) returned.\n";
?>

Basic points about Python

Python is a high-level, interpreted, interactive and object oriented-scripting language.

• Python is Interpreted
• Python is Interactive
• Python is Object-Oriented
• Python is Beginner's Language

Python was developed by Guido van Rossum in the late eighties and early nineties at the National Research Institute for Mathematics and Computer Science in the Netherlands.


Python's Feature highlights include:

• Easy-to-learn
• Easy-to-read
• Easy-to-maintain
• A broad standard library
• Interactive Mode
• Portable
• Extendable
• Databases
• GUI Programming
• Scalable
  
  
  
  

  Reserved Words:

  
  The following list shows the reserved words in Python. These reserved words may not be used as constant or variable or any other identifier names.
  Keywords contain lowercase letters only.
  
  
  
  

  and	exec	not
  assert	finally	or
  break	for	pass
  class	from	print
  continue	global	raise
  def	if	return
  del	import	try
  elif	in	while
  else	is	with
  except	lambda	yield

  
  
  

  Standard Data Types:

  
  

  
  

  Python has five standard data types:

  • Numbers
  • String
  • List
  • Tuple
  • Dictionary
  
  
  

  
  

  The search Function

  
  

  This function search for first occurrence of RE pattern within string with optional flags.
  Here is the syntax for this function:
  
  

  re.string(pattern, string, flags=0)

  Here is the description of the parameters:
  
  
  
  

  Parameter	Description
  pattern	This is the regular expression to be matched.
  string	This is the string which would be searched to match the pattern
  flags	You can specifiy different flags using exclusive OR (|). These are modifiers which are listed in the table below.

  The re.search function returns a match object on success, None on failure. We would use group(num) or groups() function of match object to get matched expression.
  
  
  
  

  Match Object Methods	Description
  group(num=0)	This methods returns entire match (or specific subgroup num)
  groups()	This method return all matching subgroups in a tuple (empty if there weren't any)

  Matching vs Searching:

  Python offers two different primitive operations based on regular expressions: match checks for a match only at the beginning of the string, while search checks for a match anywhere in the string (this is what Perl does by default).
  
  
  

  
  

  Search and Replace:

  
  Some of the most important re methods that use regular expressions is sub.
  
  

  
  

  Syntax:

  sub(pattern, repl, string, max=0)

  This method replace all occurrences of the RE pattern in string with repl, substituting all occurrences unless max provided. This method would return modified string.

  Built-In Class Attributes:

  Every Python class keeps following built-in attributes and they can be accessed using dot operator like any other attribute:
  
  
  
  
  • __dict__ : Dictionary containing the class's namespace.
  • __doc__ : Class documentation string, or None if undefined.
  • __name__: Class name.
  • __module__: Module name in which the class is defined. This attribute is "__main__" in interactive mode.
  • __bases__ : A possibly empty tuple containing the base classes, in the order of their occurrence in the base class list.
    
    

Quick Python Tutorial

Python is an easy to learn, powerful programming language. It has efficient high-level data structures and a simple but effective approach to object-oriented programming. Python’s elegant syntax and dynamic typing, case sensitive , together with its interpreted nature, make it an ideal language for scripting and rapid application development in many areas on most platforms

The Python interpreter is easily extended with new functions and data types implemented in C or C++ (or other languages callable from C). Python is also suitable as an extension language for customizable applications.

Getting Help


Help in Python is always available right in the interpreter. If you want to know how an object works, all you have to do is call help(<object>)! Also useful are dir(), which shows you all the object’s methods, and <object>.doc, which shows you its documentation string:

>>> help(5)
Help on int object: (etc etc)
>>> dir(5)
['__abs__', '__add__', ...]


Syntax 


Python has no mandatory statement termination characters and blocks are specified by indentation. Indent to begin a block, dedent to end one. Statements that expect an indentation level end in a colon (:). Comments start with the pound (#) sign and are single-line, multi-line strings are used for multi-line comments. Values are assigned (in fact, objects are bound to names) with the equals sign (”=”), and equality testing is done using two equals signs (”==“). You can increment/decrement values using the += and -= operators respectively by the right-hand amount. This works on many datatypes, strings included. You can also use multiple variables on one line. For example:
>>> myvar = 3
>>> myvar += 2
>>> myvar 5
>>> myvar -= 1
>>> myvar 4
"""This is a multiline comment.
The following lines concatenate the two strings."""
>>> mystring = "Hello"
>>> mystring += " world."
>>> print mystring
Hello world.


DataTypes


The data structures available in python are lists, tuples and dictionaries. Sets are available in the sets library (but are built-in in Python 2.5 and later). Lists are like one-dimensional arrays (but you can also have lists of other lists), dictionaries are associative arrays (a.k.a. hash tables) and tuples are immutable one-dimensional arrays (Python “arrays” can be of any type, so you can mix e.g. integers, strings, etc in lists/dictionaries/tuples). The index of the first item in all array types is 0. Negative numbers count from the end towards the beginning, -1 is the last item. Variables can point to functions. The usage is as follows:

>>> sample = [1, ["another", "list"], ("a", "tuple")]
>>> mylist = ["List item 1", 2, 3.14]
>>> mylist[0] = "List item 1 again"
>>> mylist[-1] = 3.14
>>> mydict = {"Key 1": "Value 1", 2: 3, "pi": 3.14}
>>> mydict["pi"] = 3.15
>>> mytuple = (1, 2, 3)
>>> myfunction = len
>>> print myfunction(mylist)
3

You can access array ranges using a colon (:). Leaving the start index empty assumes the first item, leaving the end index assumes the last item. Negative indexes count from the last item backwards (thus -1 is the last item) like so:

>>> mylist = ["List item 1", 2, 3.14]
>>> print mylist[:]
['List item 1', 2, 3.1400000000000001]
>>> print mylist[0:2]
['List item 1', 2]
>>> print mylist[-3:-1]
['List item 1', 2]
>>> print mylist[1:]
[2, 3.14]

Strings

Its strings can use either single or double quotation marks, and you can have quotation marks of one kind inside a string that uses the other kind (i.e. “He said ‘hello’.” is valid). Multiline strings are enclosed in triple double (or single) quotes (”“”). Python supports Unicode out of the box, using the syntax u“This is a unicode string”. To fill a string with values, you use the % (modulo) operator and a tuple. Each %s gets replaced with an item from the tuple, left to right, and you can also use dictionary substitutions, like so:

>>>print "Name: %s\nNumber: %s\nString: %s" % (myclass.name, 3, 3 * "-")
Name: Poromenos
Number: 3
String: ---

strString = """This is
a multiline
string."""

Flow control statements

Flow control statements are if, for, and while. There is no select; instead, use if. Use for to enumerate through members of a list. To obtain a list of numbers, use range(<number>). These statements’ syntax is thus:

rangelist = range(10)
>>> print rangelist
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
for number in rangelist:
    # Check if number is one of
    # the numbers in the tuple.
    if number in (3, 4, 7, 9):
        # "Break" terminates a for without
        # executing the "else" clause.
        break
    else:
        # "Continue" starts the next iteration
        # of the loop. It's rather useless here,
        # as it's the last statement of the loop.
        continue
else:
    # The "else" clause is optional and is
    # executed only if the loop didn't "break".
    pass # Do nothing

if rangelist[1] == 2:
    print "The second item (lists are 0-based) is 2"
elif rangelist[1] == 3:
    print "The second item (lists are 0-based) is 3"
else:
    print "Dunno"

while rangelist[1] == 1:
    pass

Functions

Functions are declared with the “def” keyword. Optional arguments are set in the function declaration after the mandatory arguments by being assigned a default value. For named arguments, the name of the argument is assigned a value. Functions can return a tuple (and using tuple unpacking you can effectively return multiple values). Lambda functions are ad hoc functions that are comprised of a single statement. Parameters are passed by reference, but immutable types (tuples, ints, strings, etc) cannot be changed. This is because only the memory location of the item is passed, and binding another object to a variable discards the old one, so immutable types are replaced. For example:

# Same as def f(x): return x + 1
functionvar = lambda x: x + 1
>>> print functionvar(1)
2

# an_int and a_string are optional, they have default values
# if one is not passed (2 and "A default string", respectively).
def passing_example(a_list, an_int=2, a_string="A default string"):
    a_list.append("A new item")
    an_int = 4
    return a_list, an_int, a_string

>>> my_list = [1, 2, 3]
>>> my_int = 10
>>> print passing_example(my_list, my_int)
([1, 2, 3, 'A new item'], 4, "A default string")
>>> my_list
[1, 2, 3, 'A new item']
>>> my_int
10

Classes

Python supports a limited form of multiple inheritance in classes. Private variables and methods can be declared (by convention, this is not enforced by the language) by adding at least two leading underscores and at most one trailing one (e.g. “__spam”). We can also bind arbitrary names to class instances. An example follows:

class MyClass:
    common = 10
    def __init__(self):
        self.myvariable = 3
    def myfunction(self, arg1, arg2):
        return self.myvariable

    # This is the class instantiation
>>> classinstance = MyClass()
>>> classinstance.myfunction(1, 2)
3
# This variable is shared by all classes.
>>> classinstance2 = MyClass()
>>> classinstance.common
10
>>> classinstance2.common
10
# Note how we use the class name
# instead of the instance.
>>> MyClass.common = 30
>>> classinstance.common
30
>>> classinstance2.common
30
# This will not update the variable on the class,
# instead it will bind a new object to the old
# variable name.
>>> classinstance.common = 10
>>> classinstance.common
10
>>> classinstance2.common
30
>>> MyClass.common = 50
# This has not changed, because "common" is
# now an instance variable.
>>> classinstance.common
10
>>> classinstance2.common
50

# This class inherits from MyClass. Multiple
# inheritance is declared as:
# class OtherClass(MyClass1, MyClass2, MyClassN)
class OtherClass(MyClass):
    # The "self" argument is passed automatically
    # and refers to the class instance, so you can set
    # instance variables as above, but from inside the class.
    def __init__(self, arg1):
        self.myvariable = 3
        print arg1

>>> classinstance = OtherClass("hello")
hello
>>> classinstance.myfunction(1, 2)
3
# This class doesn't have a .test member, but
# we can add one to the instance anyway. Note
# that this will only be a member of classinstance.
>>> classinstance.test = 10
>>> classinstance.test
10

Exceptions

Exceptions in Python are handled with try-except [exceptionname] blocks:

def some_function():
    try:
        # Division by zero raises an exception
        10 / 0
    except ZeroDivisionError:
        print "Oops, invalid."
    else:
        # Exception didn't occur, we're good.
        pass
    finally:
        # This is executed after the code block is run
        # and all exceptions have been handled, even
        # if a new exception is raised while handling.
        print "We're done with that."

>>> some_function()
Oops, invalid.
We're done with that.

Importing

External libraries are used with the import [libname] keyword. You can also use from [libname] import [funcname] for individual functions. Here is an example:

import random
from time import clock
randomint = random.randint(1, 100)
>>> print randomint
64

File I/O

Python has a wide array of libraries built in. As an example, here is how serializing (converting data structures to strings using the pickle library) with file I/O is used:

import pickle
mylist = ["This", "is", 4, 13327]
# Open the file C:\binary.dat for writing. The letter r before the
# filename string is used to prevent backslash escaping.
myfile = file(r"C:\binary.dat", "w")
pickle.dump(mylist, myfile)
myfile.close()

myfile = file(r"C:\text.txt", "w")
myfile.write("This is a sample string")
myfile.close()

myfile = file(r"C:\text.txt")
>>> print myfile.read()
'This is a sample string'
myfile.close()

# Open the file for reading.
myfile = file(r"C:\binary.dat")
loadedlist = pickle.load(myfile)
myfile.close()
>>> print loadedlist
['This', 'is', 4, 13327]

Miscellaneous

• Conditions can be chained. 1 < a < 3 checks that a is both less than 3 and more than 1.
• You can use del to delete variables or items in arrays.

• List comprehensions provide a powerful way to create and manipulate lists. They consist of an expression followed by a for clause followed by zero or more if or for clauses, like so:

>>> lst1 = [1, 2, 3]
>>> lst2 = [3, 4, 5]
>>> print [x * y for x in lst1 for y in lst2]
[3, 4, 5, 6, 8, 10, 9, 12, 15]
>>> print [x for x in lst1 if 4 > x > 1]
[2, 3]
# Check if an item has a specific property.
# "any" returns true if any item in the list is true.
>>> any([i % 3 for i in [3, 3, 4, 4, 3]])
True
# This is because 4 % 3 = 1, and 1 is true, so any()
# returns True.

# Check how many items have this property.
>>> sum(1 for i in [3, 3, 4, 4, 3] if i == 4)
2
>>> del lst1[0]
>>> print lst1
[2, 3]
>>> del lst1

• Global variables are declared outside of functions and can be read without any special declarations, but if you want to write to them you must declare them at the beginning of the function with the “global” keyword, otherwise Python will bind that object to a new local variable (be careful of that, it’s a small catch that can get you if you don’t know it). For example:

number = 5

def myfunc():
    # This will print 5.
    print number

def anotherfunc():
    # This raises an exception because the variable has not
    # been bound before printing. Python knows that it an
    # object will be bound to it later and creates a new, local
    # object instead of accessing the global one.
    print number
    number = 3

def yetanotherfunc():
    global number
    # This will correctly change the global.
    number = 3

PostgreSQL

Introduction

PostgreSQL is a powerful object-relational database management system, provided under a flexible BSD-style license.^[1] PostgreSQL contains many advanced features, is very fast and standards compliant.

PostgreSQL has bindings for many programming languages such as C, C++, Python, Java, PHP, Ruby... It can be used to power anything from simple web applications to massive databases with millions of records.

Installation in Ubuntu Server

To install Postgresql  you may use the command line and type:

$sudo apt-get install postgresql

Administration

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

sudo apt-get install pgadmin3

Basic Server Setup

To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

Create New User

In a terminal, type: 

@-desktop:~$ sudo -s

root@-desktop:~# su - postgres

postgres@-desktop:~$ createuser jagdeep

Shall the new role be a superuser? (y/n) y

Shall the new role be allowed to create databases? (y/n) y

Shall the new role be allowed to create more new roles? (y/n) y

postgres@-desktop:~$ exit

logout

root@-desktop:~# exit

exit

@-desktop:~$ sudo -u postgres psql postgres
psql (8.4.4)
Type "help" for help.

Set a password for the "postgres" database role using the command:

\password postgres

and give your password when prompted. The password text will be hidden from the console for security purposes.

Type Control+D to exit the posgreSQL prompt. 

Create database

To create the first database, which we will call "mydb", simply type :

sudo -u postgres createdb mydb

Install phppgadmin for GUI Inter-phase 

sudo apt-get install phppgadmin

How to login using phppgadmin

First of all enable login of postgres user via phppgadmin by editing
the /etc/phppgadmin/config.inc.php or {your phppgadmin install
path}/conf/config.inc.php file change the following line-:

$conf['extra_login_security'] = true;
to:
$conf['extra_login_security'] = false;

Now you will be able to login in phppdadmin using postgres username.

After login using postgres or any other superuser account. Create a
new role (by clicking on Roles and then Create role).Give desired
username, password and tick on following checkboxes-:

Inherits privileges?
Can login?

Then press create button to create new user.

Now give the select privilege on the tables to the newly created
user.This can be done by clicking on the table name and grant
permissions under privileges tab.Give select privileges to newly
created user.Now that user can browse through the table and see
individual values.
Logout from superuser account and login again using newly created user
to verify the results.