PSQL SELECT QUERY results automatically fits records to the width of the screen

PostgreSQL SELECT QUERY results automatically fits records to the width of the screen
 

1)
Login to psql command prompt
#psql -d dbname -U username

2)
Run
#\x on 
OR
#\x auto

3)
Try your select query
Select * from your_table;

python postgresql migration script example three# -*- coding: utf-8 -*- import sys import psycopg2 pgsql_source_connection = None pgsql_source_curs

# -*- coding: utf-8 -*-

import sys
import psycopg2


pgsql_source_connection = None
pgsql_source_cursor = None
pgsql_source_queries = 0


# Gets the PostgreSQL Source credentials
def get_pg_source_credentials():
c = {}
c['host'] = 'localhost'
c['database'] = 'library'
c['user'] = 'saju'
c['password'] = 'xxx'
c['port'] = '5432'
return c


# Opens a connection to the Source PostgreSQL database
def get_pgsql_source_connection():
try:
credentials = get_pg_source_credentials()
conn = psycopg2.connect("dbname='" + credentials['database'] + "' " + \
"user='" + credentials['user'] + "' " + \
"host='" + credentials['host'] + "' " + \
"port='" + credentials['port'] + "' " + \
"password='" + credentials['password'] + "'")
print "connected to Source db"
except Exception, e:
print "I am unable to connect to the Source PostgreSQL database"
raise e
return conn


def copy_data():

pgsql_source_connection = get_pgsql_source_connection()
pgsql_source_cursor = pgsql_source_connection.cursor()

c = pgsql_source_connection.cursor()
c.execute('begin;')

c.execute('CREATE TABLE "lib_contributor" (\
"id" serial NOT NULL PRIMARY KEY,\
"library_id" integer NOT NULL REFERENCES "lib_library" ("id") DEFERRABLE INITIALLY DEFERRED,\
"name" varchar(1000) NOT NULL\
)')


c.execute('CREATE TABLE "lib_book_author" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_editor" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_reviser" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_compiler" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_translator" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_illustrator" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_forward" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_interpreter" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_cover_designer" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_commentry" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_preface" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_introduction" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_prologue" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_reteller" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')

c.execute('CREATE TABLE "lib_book_cover_photographer" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"contributor_id" integer NOT NULL REFERENCES "lib_contributor" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "contributor_id")\
)')


c.execute('CREATE TABLE "lib_publisher" (\
"id" serial NOT NULL PRIMARY KEY,\
"library_id" integer NOT NULL REFERENCES "lib_library" ("id") DEFERRABLE INITIALLY DEFERRED,\
"name" varchar(300) NOT NULL\
)')

c.execute('CREATE TABLE "lib_book_publisher" (\
"id" serial NOT NULL PRIMARY KEY,\
"book_id" integer NOT NULL REFERENCES "lib_book" ("id") DEFERRABLE INITIALLY DEFERRED,\
"publisher_id" integer NOT NULL REFERENCES "lib_publisher" ("id") DEFERRABLE INITIALLY DEFERRED,\
UNIQUE ("book_id", "publisher_id")\
)')


c.execute('select * from lib_book;')
for book in list(c.fetchall()):
#book[4] author
#book[5] editor
#book[6] reviser
#book[7] compiler
#book[8] translator
#book[9] illustrator
#book[10] forward
#book[11] interpreter
#book[12] cover_designer
#book[44] commentry
#book[45] preface
#book[46] introduction
#book[47] prologue
#book[49] reteller
#book[50] cover_photographer
#book[19] publisher

if book[4]:
print "author", book[4]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[4],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_author (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[5]:
print "editor", book[5]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[5],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_editor (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[6]:
print "reviser", book[6]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[6],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_reviser (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[7]:
print "compiler", book[7]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[7],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_compiler (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[8]:
print "translator", book[8]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[8],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_translator (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[9]:
print "illustrator", book[9]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[9],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_illustrator (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[10]:
print "forward", book[10]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[10],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_forward (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[11]:
print "interpreter", book[11]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[11],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_interpreter (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[12]:
print "cover_designer", book[12]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[12],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_cover_designer (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[44]:
print "commentry", book[44]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[44],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_commentry (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[45]:
print "preface", book[45]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[45],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_preface (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[46]:
print "introduction", book[46]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[46],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_introduction (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[47]:
print "prologue", book[47]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[47],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_prologue (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[49]:
print "reteller", book[49]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[49],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_reteller (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[50]:
print "cover_photographer", book[50]
c.execute("insert into lib_contributor (library_id, name) values (%s,%s) returning id", (book[1], book[50],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_cover_photographer (book_id, contributor_id) values(%s,%s)", (book[0], id))

if book[19]:
c.execute("insert into lib_publisher (library_id, name) values (%s,%s) returning id", (book[1], book[19],))
id = c.fetchone()[0]
c.execute("INSERT INTO lib_book_publisher (book_id, publisher_id) values(%s,%s)", (book[0], id))

c.execute('commit;')



c.execute('begin;')
c.execute('ALTER TABLE lib_book DROP COLUMN author')
c.execute('ALTER TABLE lib_book DROP COLUMN editor')
c.execute('ALTER TABLE lib_book DROP COLUMN reviser')
c.execute('ALTER TABLE lib_book DROP COLUMN compiler')
c.execute('ALTER TABLE lib_book DROP COLUMN translator')
c.execute('ALTER TABLE lib_book DROP COLUMN illustrator')
c.execute('ALTER TABLE lib_book DROP COLUMN forward')
c.execute('ALTER TABLE lib_book DROP COLUMN interpreter')
c.execute('ALTER TABLE lib_book DROP COLUMN cover_designer')
c.execute('ALTER TABLE lib_book DROP COLUMN commentry')
c.execute('ALTER TABLE lib_book DROP COLUMN preface')
c.execute('ALTER TABLE lib_book DROP COLUMN introduction')
c.execute('ALTER TABLE lib_book DROP COLUMN prologue')
c.execute('ALTER TABLE lib_book DROP COLUMN reteller')
c.execute('ALTER TABLE lib_book DROP COLUMN cover_photographer')
c.execute('commit;')


c.execute('begin;')
c.execute('ALTER TABLE lib_physicalbook RENAME COLUMN accessionNo TO accession_no')
c.execute('ALTER TABLE lib_member RENAME COLUMN memberID TO member_id')
c.execute('ALTER TABLE lib_member RENAME COLUMN joinDate TO join_date')
c.execute('ALTER TABLE lib_member RENAME COLUMN expiryDate TO expiry_date')
c.execute('ALTER TABLE lib_member RENAME COLUMN numTickets TO num_tickets')
c.execute('ALTER TABLE lib_member RENAME COLUMN numAvailTickets TO num_avail_tickets')

c.execute('ALTER TABLE lib_reservation RENAME COLUMN reservedDate TO reserved_date')
c.execute('ALTER TABLE lib_reservation RENAME COLUMN expiryDate TO expiry_date')
c.execute('ALTER TABLE lib_reservation RENAME COLUMN lentOut TO lent_out')

c.execute('ALTER TABLE lib_lending RENAME COLUMN physicalBook_id TO physical_book_id')
c.execute('ALTER TABLE lib_lending RENAME COLUMN previousState_id TO physical_book_id')
c.execute('ALTER TABLE lib_lending RENAME COLUMN nextState_id TO next_state_id')
c.execute('ALTER TABLE lib_lending RENAME COLUMN reservedDate TO reserved_date')
c.execute('ALTER TABLE lib_lending RENAME COLUMN issueDate TO issue_date')
c.execute('ALTER TABLE lib_lending RENAME COLUMN returnDate TO return_date')
c.execute('ALTER TABLE lib_lending RENAME COLUMN returnedDate TO returned_date')

c.execute('ALTER TABLE lib_memberprofile_bookProfiles RENAME TO lib_memberprofile_book_profile')
c.execute('commit;')


c.close()



def main():
copy_data()


if __name__ == "__main__":
main()

python postgresql migration script example two

python postgresql migration script example two

# -*- coding: utf-8 -*-

import sys
import psycopg2
#from migrate_users import *


pgsql_source_connection = None
pgsql_source_cursor = None

Publish Post

pgsql_source_queries = 0


# Gets the PostgreSQL Source credentials
def get_pg_source_credentials():
c = {}
c['host'] = 'localhost'
c['database'] = 'olddb1'
c['user'] = 'kane'
c['password'] = 'xxx'
c['port'] = '5432'
return c





# Opens a connection to the Source PostgreSQL database
def get_pgsql_source_connection():
try:
credentials = get_pg_source_credentials()
conn = psycopg2.connect("dbname='" + credentials['database'] + "' " + \
"user='" + credentials['user'] + "' " + \
"host='" + credentials['host'] + "' " + \
"port='" + credentials['port'] + "' " + \
"password='" + credentials['password'] + "'")
print "connected to Source db"
except Exception, e:
print "I am unable to connect to the Source PostgreSQL database"
raise e
return conn


def copy_data():
#ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
#ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

# -------- #

alt_message_number = "alter table siren_message add column message_number integer NOT NULL;"
alt_milestone_number = "alter table siren_milestone add column milestone_number integer NOT NULL DEFAULT 0;"
alt_todolist_number = "alter table siren_todolist add column todolist_number integer NOT NULL DEFAULT 0;"
alt_note_number = "alter table siren_note add column note_number integer NOT NULL DEFAULT 0;"
alt_mockupset_number = "alter table siren_mockupset add column mockupset_number integer NOT NULL;"
alt_category_number = "alter table siren_category add column category_number integer NOT NULL DEFAULT 0;"


alt_ticket_number = "ALTER TABLE siren_ticket ALTER COLUMN ticket_number SET NOT NULL;"


pgsql_source_connection = get_pgsql_source_connection()
pgsql_source_cursor = pgsql_source_connection.cursor()

c = pgsql_source_connection.cursor()
c.execute('begin;')

c.execute(alt_message_number)
c.execute(alt_milestone_number)
c.execute(alt_todolist_number)
c.execute(alt_note_number)
c.execute(alt_mockupset_number)
c.execute(alt_category_number)

c.execute(alt_ticket_number)

c.execute('select * from siren_project;')
for project in list(c.fetchall()):
c.execute('select * from siren_milestone where project_id='+str(project[0])+';')
m = {}
for milestone in list(c.fetchall()):
print milestone[9]
if milestone[9] not in m:
m[milestone[9]] = 1
#print milestone[milestone[9]]
c.execute('update siren_milestone set milestone_number = %d where id = %d' %(m[milestone[9]], milestone[0]))
m[milestone[9]] = m[milestone[9]] + 1
#print m[milestone[9]]
print m


c.execute('select * from siren_project;')
for project in list(c.fetchall()):
c.execute('select * from siren_todolist where project_id='+str(project[0])+';')
t = {}
for todolist in list(c.fetchall()):
print todolist[9]
if todolist[9] not in t:
t[todolist[9]] = 1
c.execute('update siren_todolist set todolist_number = %d where id = %d' %(t[todolist[9]], todolist[0]))
t[todolist[9]] = t[todolist[9]] + 1
print t


c.execute('select * from siren_project;')
for project in list(c.fetchall()):
c.execute('select * from siren_note where project_id='+str(project[0])+';')
n = {}
for note in list(c.fetchall()):
print note[8]
if note[8] not in n:
n[note[8]] = 1
c.execute('update siren_note set note_number = %d where id = %d' %(n[note[8]], note[0]))
n[note[8]] = n[note[8]] + 1
print n


c.execute('select * from siren_project;')
for project in list(c.fetchall()):
c.execute('select * from siren_category where project_id='+str(project[0])+';')
cat = {}
for category in list(c.fetchall()):
print category[5]
if category[5] not in cat:
cat[category[5]] = 1
c.execute('update siren_category set category_number = %d where id = %d' %(cat[category[5]], category[0]))
cat[category[5]] = cat[category[5]] + 1
print cat


alt_milestone_number_drop = "alter table siren_milestone ALTER column milestone_number DROP DEFAULT;"
alt_todolist_number_drop = "alter table siren_todolist ALTER column todolist_number DROP DEFAULT;"
alt_note_number_drop = "alter table siren_note ALTER column note_number DROP DEFAULT;"
alt_category_number_drop = "alter table siren_category ALTER column category_number DROP DEFAULT;"

c.execute(alt_milestone_number_drop)
c.execute(alt_todolist_number_drop)
c.execute(alt_note_number_drop)
c.execute(alt_category_number_drop)

c.execute('commit;')
c.close()



def main():
copy_data()
#print "PostgreSQL queries: {0}".format(pgsql_queries)


if __name__ == "__main__":
main()

-------------------------------------------------

python postgresql migration script example one

python postgresql migration script example one

# -*- coding: utf-8 -*-
import sys
import psycopg2
#from migrate_users import *


pgsql_source_connection = None
pgsql_source_cursor = None
pgsql_source_queries = 0


# Gets the PostgreSQL Source credentials
def get_pg_source_credentials():
c = {}
c['host'] = 'localhost'
c['database'] = 'olddb1'
c['user'] = 'kane'
c['password'] = 'xxx'
c['port'] = '5432'
return c





# Opens a connection to the Source PostgreSQL database
def get_pgsql_source_connection():
try:
credentials = get_pg_source_credentials()
conn = psycopg2.connect("dbname='" + credentials['database'] + "' " + \
"user='" + credentials['user'] + "' " + \
"host='" + credentials['host'] + "' " + \
"port='" + credentials['port'] + "' " + \
"password='" + credentials['password'] + "'")
print "connected to Source db"
except Exception, e:
print "I am unable to connect to the Source PostgreSQL database"
raise e
return conn


def copy_data():

# add column #
alt_todo = "alter table siren_todo add column ticket_id integer;"
alt_ticket = "alter table siren_ticket add column ticket_number integer;"
alt_event = "alter table siren_event add column item_id integer;"
alt_project = "alter table siren_project drop column is_dispaly;"
alt_project_repo = "alter table siren_project drop column code_repository_id;"

#alt_message_number = "alter table siren_message add column message_number integer NOT NULL;"
#alt_milestone_number = "alter table siren_milestone add column milestone_number integer;"
#alt_todolist_number = "alter table siren_todolist add column todolist_number integer;"
#alt_note_number = "alter table siren_note add column note_number integer NOT NULL;"
#alt_mockupset_number = "alter table siren_mockupset add column mockupset_number integer NOT NULL;"
#alt_category_number = "alter table siren_category add column category_number integer NOT NULL;"

pgsql_source_connection = get_pgsql_source_connection()
pgsql_source_cursor = pgsql_source_connection.cursor()

c = pgsql_source_connection.cursor()
c.execute('begin;')
c.execute(alt_todo)
c.execute(alt_ticket)
c.execute(alt_event)
c.execute(alt_project)
c.execute(alt_project_repo)

#c.execute(alt_message_number)
#c.execute(alt_milestone_number)
#c.execute(alt_todolist_number)
#c.execute(alt_note_number)
#c.execute(alt_mockupset_number)
#c.execute(alt_category_number)


c.execute('select * from siren_ticket order by created_on;')
tickets = {}
for ticket in list(c.fetchall()):
if ticket[8] not in tickets:
tickets[ticket[8]] = 1
#print tickets[ticket[8]]
c.execute('update siren_ticket set ticket_number = %d where id = %d' %(tickets[ticket[8]], ticket[0]))
tickets[ticket[8]] = tickets[ticket[8]] + 1
print tickets[ticket[8]]
print tickets


c.execute("update siren_event set type='TK' where type='TKC'")


c.execute("select * from siren_event where type='TK'")
for event in list(c.fetchall()):
e = event[7].split('/')
#print e[5]
c.execute('select ticket_number, title, project_id from siren_ticket where id=%s' %(e[5]))
for t in list(c.fetchall()):
print t[1]
c.execute('select name from siren_project where id=%s' %(t[2]))
for p in list(c.fetchall()):
print p[0]

c.execute("update siren_event set description = '#%s %s (%s)', action='U' where type='TK' and description='Comment:' and url='/siren/projects/1/tickets/%s/'" %(e[5], t[1], p[0], e[5]))
c.execute("update siren_event set description = '#%s %s (%s)' where type='TK' and action='A' and url='/siren/projects/1/tickets/%s/'" %(e[5], t[1], p[0], e[5]))

c.execute("update siren_event set description = '#%s %s (%s)', action='U' where type='TK' and description='Comment:' and url='/siren/projects/4/tickets/%s/'" %(e[5], t[1], p[0], e[5]))
c.execute("update siren_event set description = '#%s %s (%s)' where type='TK' and action='A' and url='/siren/projects/4/tickets/%s/'" %(e[5], t[1], p[0] ,e[5]))

#--------------#

c.execute('CREATE TABLE "siren_projectrepository" (\
"id" serial NOT NULL PRIMARY KEY,\
"code_repository_id" integer NOT NULL REFERENCES "django_vcs_coderepository" ("id") DEFERRABLE INITIALLY DEFERRED,\
"project_id" integer NOT NULL REFERENCES "siren_project" ("id") DEFERRABLE INITIALLY DEFERRED\
)')




c.execute('commit;')
c.close()



def main():
copy_data()
#print "PostgreSQL queries: {0}".format(pgsql_queries)


if __name__ == "__main__":
main()
-------------------------------------------------

Postgresql database initialization and Start in Debian

Postgresql database initialization and Start in Debian Linux

===================Solved=======================
Error:
createdb: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

================================================1
* Find the location of "initdb" binary and run it with -D option and directory "data" where we want to install datas of postgres database.For that run the following command.

root@localhost:~# su postgres -c "/usr/lib/postgresql/8.3/bin/initdb -D /usr/lib/postgresql/8.3/data"

could not change directory to "/home/root"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

creating directory /usr/lib/postgresql/8.3/data ... initdb: could not create directory "/usr/lib/postgresql/8.3/data": Permission denied

================================================2
* change the permission of "/usr/lib/postgresql/"
if we could not create directory "/usr/lib/postgresql/8.3/data"

root@localhost:~# chmod -R 777 /usr/lib/postgresql/

================================================3
* Running the above command again.

root@localhost:~# su postgres -c "/usr/lib/postgresql/8.3/bin/initdb -D /usr/lib/postgresql/8.3/data"

could not change directory to "/home/root"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

creating directory /usr/lib/postgresql/8.3/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 24MB/153600
creating configuration files ... ok
creating template1 database in /usr/lib/postgresql/8.3/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

/usr/lib/postgresql/8.3/bin/postgres -D /usr/lib/postgresql/8.3/data
or
/usr/lib/postgresql/8.3/bin/pg_ctl -D /usr/lib/postgresql/8.3/data -l logfile start

root@localhost:~#

================================================4
* start the database server.

root@localhost:~# su postgres -c "/usr/lib/postgresql/8.3/bin/postgres -D /usr/lib/postgresql/8.3/data"

LOG: database system was shut down at 2009-11-07 00:37:47 EST
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
FATAL: database "root" does not exist
FATAL: database "saju" does not exist

================================================5
* take a new terminal and type following command.

saju@localhost:/home/root$ psql -U postgres

could not change directory to "/home/root"
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#

================================================6
* To solve phpPgAdmin Login Problem 'Login disallowed for security reasons' set $conf['extra_login_security'] to false;.
* To solve postgres user login problem 'Login disallowed for security reasons' in phpPgAdmin.

vim /var/www/phpPgAdmin/conf/config.inc.php

$conf['extra_login_security'] = false;


================================================7
* To access postgres database from terminal.

periyar:~# psql dbname -U username

Welcome to psql 8.3.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

dbname=#

================================================8
* Postgresql database Backup and Restore commands.

Postgresql database backup command.
----------------------------------
#pg_dump -d shopdb -U username -f /home/user/shopdbbkp.txt


Postgresql database restore command.
-----------------------------------
#create database shopdb
#psql shopdb -U username -f /home/user/shopdbbkp.txt

================================================