How to use psycopg - 10 common examples

if not (hasattr(options, name) and getattr(options, name)):
            if value in ('true', 'True'):
                value = True
            if value in ('false', 'False'):
                value = False
            setattr(options, name, value)

# -----

host = hasattr(options, 'db_host') and "host=%s" % options.db_host or ''
port = hasattr(options, 'db_port') and "port=%s" % options.db_port or ''
name = "dbname=%s" % options.db_name
user = hasattr(options, 'db_user') and "user=%s" % options.db_user or ''
password = hasattr(options, 'db_password') and "password=%s" % options.db_password or ''

db = psycopg.connect('%s %s %s %s %s' % (host, port, name, user, password), serialize=0)
cr = db.cursor()

# ---------------------------------------------------------------- #
# move user id from hr_analytic_timesheet to account_analytic_line #
# ---------------------------------------------------------------- #

cr.execute("UPDATE account_analytic_line SET user_id = hr_analytic_timesheet.user_id FROM hr_analytic_timesheet WHERE hr_analytic_timesheet.line_id = account_analytic_line.id")
cr.commit()

# --------------- #
# remove old menu #
# --------------- #

while True:
    cr.execute("select id from ir_ui_menu where (id NOT IN (select parent_id from ir_ui_menu where parent_id is not null)) and (id NOT IN (select res_id from ir_values where model='ir.ui.menu'))")
    if not cr.rowcount:

value = True
            if value in ('false', 'False'):
                value = False
            setattr(options, name, value)

raise Exception('This script is provided as an example, you must custom it before')

# -----

host = hasattr(options, 'db_host') and "host=%s" % options.db_host or ''
port = hasattr(options, 'db_port') and "port=%s" % options.db_port or ''
name = "dbname=%s" % options.db_name
user = hasattr(options, 'db_user') and "user=%s" % options.db_user or ''
password = hasattr(options, 'db_password') and "password=%s" % options.db_password or ''

db = psycopg.connect('%s %s %s %s %s' % (host, port, name, user, password), serialize=0)
cr = db.cursor()

# fix country


cr.execute('SELECT code from res_country where code is not null group by code')
res = cr.fetchall()

for c in res:
    cr.execute('SELECT max(id) from res_country where code = %s group by code', (c[0],))
    res2 = cr.fetchone()
    cr.execute('SELECT id from res_country where code = %s', (c[0],))
    ids = ','.join(map(lambda x: str(x[0]), cr.fetchall()))
    cr.execute('UPDATE res_partner_address set country_id = %d where country_id in ('+ids+')', (res2[0],))
    cr.execute('DELETE FROM res_country WHERE code = %s and id <> %d', (c[0], res2[0],))
cr.commit()

if not (hasattr(options, name) and getattr(options, name)):
            if value in ('true', 'True'):
                value = True
            if value in ('false', 'False'):
                value = False
            setattr(options, name, value)

# -----

host = hasattr(options, 'db_host') and "host=%s" % options.db_host or ''
port = hasattr(options, 'db_port') and "port=%s" % options.db_port or ''
name = "dbname=%s" % options.db_name
user = hasattr(options, 'db_user') and "user=%s" % options.db_user or ''
password = hasattr(options, 'db_password') and "password=%s" % options.db_password or ''

db = psycopg.connect('%s %s %s %s %s' % (host, port, name, user, password), serialize=0)
cr = db.cursor()

# --------------- #
# remove old menu #
# --------------- #

cr.execute("delete from ir_ui_menu where (id not in (select parent_id from ir_ui_menu where parent_id is not null)) and (id not in (select res_id from ir_values where model='ir.ui.menu'))")
cr.commit()

# --------------- #
# remove ir_value #
# --------------- #

cr.execute("delete from ir_values where model = 'ir.ui.menu' and res_id is null")
cr.commit()

if not (hasattr(options, name) and getattr(options, name)):
            if value in ('true', 'True'):
                value = True
            if value in ('false', 'False'):
                value = False
            setattr(options, name, value)

# -----

host = hasattr(options, 'db_host') and "host=%s" % options.db_host or ''
port = hasattr(options, 'db_port') and "port=%s" % options.db_port or ''
name = "dbname=%s" % options.db_name
user = hasattr(options, 'db_user') and "user=%s" % options.db_user or ''
password = hasattr(options, 'db_password') and "password=%s" % options.db_password or ''

db = psycopg.connect('%s %s %s %s %s' % (host, port, name, user, password), serialize=0)
cr = db.cursor()

# ------------------------------ #
# drop not null on ir_attachment #
# ------------------------------ #

cr.execute('ALTER TABLE ir_attachment \
        ALTER COLUMN res_model DROP NOT NULL, \
        ALTER COLUMN res_id DROP NOT NULL')
cr.commit()

# ---------------------------------- #
# change case date_deadline rounding #
# ---------------------------------- #

cr.execute("""SELECT

cr.commit()

# ----------------------------------------------------- #
# add some fields (which cannot be added automatically) #
# ----------------------------------------------------- #

for line in (
        "alter table ir_model_fields add group_name varchar(64)",
        "alter table ir_model_fields add view_load boolean",
        "alter table ir_model_fields alter group_name set default ''",
        "alter table ir_model_fields alter view_load set default False",
        "delete from ir_values where value like '%,False'",
    ):
    try:
        cr.execute(line)
    except psycopg.ProgrammingError, e:
        cr.commit()
        print e

cr.commit()
cr.close()

# No file entry. Could be a .sig file
                continue
            # make sure we're working with a number
            downloads[filename] = record[0] or 0
        # add a download
        downloads[filename] += 1

    if not downloads:
        return

    # Update the download counts
    for filename, count in downloads.items():
        cursor.execute("update release_files set downloads=%s "
                       "where filename=%s", (count, filename))
    # Update the download timestamp
    date = psycopg.TimestampFromMx(date)
    cursor.execute("update timestamps set value=%s "
                   "where name='http'", (date,))
    dbconn.commit()

dispatcher = netsvc.Dispatcher()
dispatcher.monitor(signal.SIGINT)

#---------------------------------------------------------------
# connect to the database and initialize it with base if needed
#---------------------------------------------------------------
logger.notifyChannel("init", netsvc.LOG_INFO, 'connecting to database')

import psycopg
import pooler

# try to connect to the database
try:
#	pooler.init()
	pass
except psycopg.OperationalError, err:
	logger.notifyChannel("init", netsvc.LOG_ERROR, "could not connect to database '%s'!" % (tools.config["db_name"],))

	msg = str(err).replace("FATAL:","").strip()
	db_msg = "database \"%s\" does not exist" % (tools.config["db_name"],)
	
	# Note: this is ugly but since psycopg only uses one exception for all errors
	# I don't think it's possible to do differently
	if msg == db_msg:
		print """
    this database does not exist

You need to create it using the command:

    createdb --encoding=UNICODE '%s'

When you run tinyerp-server for the first time it will initialise the

def load(self):
        '''Load state from database'''
        import psycopg
        if options["globals", "verbose"]:
            print >> sys.stderr, 'Loading state from',self.db_name,'database'
        self.db = psycopg.connect('dbname=' + self.db_name)
        c = self.cursor()
        try:
            c.execute("select count(*) from bayes")
        except psycopg.ProgrammingError:
            self.db.rollback()
            self.create_bayes()
        if self._has_key(self.statekey):
            row = self._get_row(self.statekey)
            self.nspam = row["nspam"]
            self.nham = row["nham"]
            if options["globals", "verbose"]:
                print >> sys.stderr, ('%s is an existing database,'
                                      ' with %d spam and %d ham') \
                      % (self.db_name, self.nspam, self.nham)
        else:
            if options["globals", "verbose"]:

raise ConfigException("JAVA is not installed into %s" % self.javapath)
        if not os.path.exists(self.jarpath):
            raise ConfigException("Additional JAVA jar are not available into %s" % self.jarpath)
        if not os.path.exists(os.path.join(self.jarpath, "postgresql.jar")):
            raise ConfigException("postgresql.jar is not available into %s" % self.jarpath)
        # Verify if Open-Xchange LDAP schema is available in the directory
        try:
            ldapObj = ldapUserGroupControl()
        except ldap.INVALID_CREDENTIALS:
            raise ConfigException("Can't bind to LDAP: invalid credentials.")
        schema = ldapObj.getSchema("OXUserObject")
        if len(schema) <= 0:
            raise ConfigException("OX schema seems not be included into LDAP directory")
        # DB access check
        try:
            db = psycopg.connect("dbname=%s user=%s password=%s" % (self.oxdbname, self.oxdbuser, self.oxdbpassword))
        except psycopg.OperationalError, e:
            raise ConfigException("Can't connect to Open-Xchange database: " + str(e))
        db.close()

def load(self):
        '''Load state from database'''
        import psycopg
        if options["globals", "verbose"]:
            print >> sys.stderr, 'Loading state from',self.db_name,'database'
        self.db = psycopg.connect('dbname=' + self.db_name)
        c = self.cursor()
        try:
            c.execute("select count(*) from bayes")
        except psycopg.ProgrammingError:
            self.db.rollback()
            self.create_bayes()
        if self._has_key(self.statekey):
            row = self._get_row(self.statekey)
            self.nspam = row["nspam"]
            self.nham = row["nham"]
            if options["globals", "verbose"]:
                print >> sys.stderr, ('%s is an existing database,'
                                      ' with %d spam and %d ham') \
                      % (self.db_name, self.nspam, self.nham)
        else:
            if options["globals", "verbose"]: