def check_cast_handler(self, datatype_class, relation, column):
"""
For each of the custom types register the built-in str class
as the psycopg cast class so the SQL literal the pgsql backend
returns can be parsed into an appropriate Python datastucture
by our datatype and column class
"""
if datatype_class.sql_name:
type_name = datatype_class.sql_name
else:
type_name = datatype_class.__name__
if not _typeoid.has_key(type_name):
# Run a query on the database that will return
# the SQL type's oid. We only need the cursor's
# description, really
query = "SELECT %s FROM %s WHERE 0=1" % (column, relation)
cursor = self.execute(query)
type_oid = cursor.description[0][1]
# register the string class with psycopg so it always
# returns a SQL literal as a Python string
TYPE = psycopg.new_type((type_oid,),
upper(type_name), str)
psycopg.register_type(TYPE)
# store the Oid in our dict
_typeoid[type_name] = type_oid
def check_cast_handler(self, datatype_class, relation, column):
"""
For each of the custom types register the built-in str class
as the psycopg cast class so the SQL literal the pgsql backend
returns can be parsed into an appropriate Python datastucture
by our datatype and column class
"""
if datatype_class.sql_name:
type_name = datatype_class.sql_name
else:
type_name = datatype_class.__name__
if not _typeoid.has_key(type_name):
# Run a query on the database that will return
# the SQL type's oid. We only need the cursor's
# description, really
query = "SELECT %s FROM %s WHERE 0=1" % (column, relation)
cursor = self.execute(query)
type_oid = cursor.description[0][1]
# register the string class with psycopg so it always
# returns a SQL literal as a Python string
TYPE = psycopg.new_type((type_oid, ), upper(type_name), str)
psycopg.register_type(TYPE)
# store the Oid in our dict
_typeoid[type_name] = type_oid
style.SQL_FIELD('%s_id_seq' % table_name),
style.SQL_KEYWORD('RESTART'),
style.SQL_KEYWORD('WITH'),
style.SQL_FIELD('1')
)
)
return sql
else:
return []
# Register these custom typecasts, because Django expects dates/times to be
# in Python's native (standard-library) datetime/time format, whereas psycopg
# use mx.DateTime by default.
try:
Database.register_type(Database.new_type((1082,), "DATE", util.typecast_date))
except AttributeError:
raise Exception, "You appear to be using psycopg version 2. Set your DATABASE_ENGINE to 'postgresql_psycopg2' instead of 'postgresql'."
Database.register_type(Database.new_type((1083,1266), "TIME", util.typecast_time))
Database.register_type(Database.new_type((1114,1184), "TIMESTAMP", util.typecast_timestamp))
Database.register_type(Database.new_type((16,), "BOOLEAN", util.typecast_boolean))
OPERATOR_MAPPING = {
'exact': '= %s',
'iexact': 'ILIKE %s',
'contains': 'LIKE %s',
'icontains': 'ILIKE %s',
'gt': '> %s',
'gte': '>= %s',
'lt': '< %s',
'lte': '<= %s',
def typecast_string(s):
"""
Cast all returned strings to unicode strings.
"""
if not s and not isinstance(s, str):
return s
return smart_unicode(s)
# Register these custom typecasts, because Django expects dates/times to be
# in Python's native (standard-library) datetime/time format, whereas psycopg
# use mx.DateTime by default.
try:
Database.register_type(
Database.new_type((1082, ), "DATE", util.typecast_date))
except AttributeError:
raise Exception(
"You appear to be using psycopg version 2. Set your DATABASE_ENGINE to 'postgresql_psycopg2' instead of 'postgresql'."
)
Database.register_type(
Database.new_type((1083, 1266), "TIME", util.typecast_time))
Database.register_type(
Database.new_type((1114, 1184), "TIMESTAMP", util.typecast_timestamp))
Database.register_type(
Database.new_type((16, ), "BOOLEAN", util.typecast_boolean))
Database.register_type(
Database.new_type((1700, ), "NUMERIC", util.typecast_decimal))
Database.register_type(
Database.new_type(Database.types[1043].values, 'STRING', typecast_string))
conn_string += " port=%s" % settings.DATABASE_PORT
self.connection = Database.connect(conn_string, **self.options)
self.connection.set_isolation_level(1) # make transactions transparent to all cursors
cursor = self.connection.cursor()
if set_tz:
cursor.execute("SET TIME ZONE %s", [settings.TIME_ZONE])
cursor.execute("SET client_encoding to 'UNICODE'")
cursor = UnicodeCursorWrapper(cursor, 'utf-8')
return cursor
def typecast_string(s):
"""
Cast all returned strings to unicode strings.
"""
if not s and not isinstance(s, str):
return s
return smart_unicode(s)
# Register these custom typecasts, because Django expects dates/times to be
# in Python's native (standard-library) datetime/time format, whereas psycopg
# use mx.DateTime by default.
try:
Database.register_type(Database.new_type((1082,), "DATE", util.typecast_date))
except AttributeError:
raise Exception("You appear to be using psycopg version 2. Set your DATABASE_ENGINE to 'postgresql_psycopg2' instead of 'postgresql'.")
Database.register_type(Database.new_type((1083,1266), "TIME", util.typecast_time))
Database.register_type(Database.new_type((1114,1184), "TIMESTAMP", util.typecast_timestamp))
Database.register_type(Database.new_type((16,), "BOOLEAN", util.typecast_boolean))
Database.register_type(Database.new_type((1700,), "NUMERIC", util.typecast_decimal))
Database.register_type(Database.new_type(Database.types[1043].values, 'STRING', typecast_string))
return s
def show(self):
"""Format a description of the box."""
s = "X: %d\tY: %d\tWidth: %d\tHeight: %d" % (
self.x, self.y, self.width, self.height)
return s
# here we select from the empty table, just to grab the description
curs.execute("SELECT b FROM test_cast WHERE 0=1")
boxoid = curs.description[0][1]
print "Oid for the box datatype is", boxoid
# and build the user cast object
BOX = psycopg.new_type((boxoid,), "BOX", Rect)
psycopg.register_type(BOX)
# now insert 100 random data (2 points and a box in each row)
for i in range(100):
p1 = (whrandom.randint(0,100), whrandom.randint(0,100))
p2 = (whrandom.randint(0,100), whrandom.randint(0,100))
b = Rect()
b.from_points(whrandom.randint(0,100), whrandom.randint(0,100),
whrandom.randint(0,100), whrandom.randint(0,100))
curs.execute("INSERT INTO test_cast VALUES ('%(p1)s', '%(p2)s', %(box)s)",
{'box':b, 'p1':p1, 'p2':p2})
# select and print all boxes with at least one point inside
curs.execute("SELECT b FROM test_cast WHERE p1 @ b OR p2 @ b")
boxes = curs.fetchall()
print "Found %d boxes with at least a point inside:" % len(boxes)
Send mail til brukeren med oppfordring om å kontakte sin LITA for å
bli korrekt registrert. (TODO)
"""
if True: # Do not use if database has numeric(X,N) where N > 0
import psycopg
def float_to_int(v):
if v is None:
return v
try:
return int(v)
except ValueError:
return long(v)
MY_INT = psycopg.new_type((1700,), "MY_INT", float_to_int)
psycopg.register_type(MY_INT)
cereconf.CLASS_DBDRIVER = ['Cerebrum.Database/PsycoPG']
db = Factory.get('Database')()
db.cl_init(change_program="upd_old_affs")
co = Factory.get('Constants')(db)
ac = Factory.get('Account')(db)
sko = Factory.get("OU")(db)
person = Factory.get('Person')(db)
logger = Factory.get_logger("cronjob")
threshold = time.time() - 3600*24*90 # 90 days
aff_mapping = {
int(co.affiliation_manuell_inaktiv_ansatt): int(co.affiliation_ansatt),
int(co.affiliation_manuell_inaktiv_student): int(co.affiliation_student)
def set_type_casts(self):
"Make changes to psycopg default typecast list"
if self.zdatetime:
#use zope internal datetime routines
ZDATETIME=new_type((1184,1114), "ZDATETIME", cast_DateTime_Better)
ZDATE=new_type((1082,), "ZDATE", cast_Date_Better)
ZTIME=new_type((1083,), "ZTIME", cast_Time)
ZINTERVAL=new_type((1186,), "ZINTERVAL", cast_Interval)
register_type(ZDATETIME)
register_type(ZDATE)
register_type(ZTIME)
register_type(ZINTERVAL)
else:
#use the standard. WARN: order is important!
register_type(DATETIME)
register_type(DATE)
register_type(TIME)
register_type(INTERVAL)
if getattr(self, "ustrings", 0) and self.encoding:
USTRING = psycopg.new_type(psycopg.STRING.values, "USTRING",
cast_String_factory(self.encoding))
register_type(USTRING)
else:
register_type(STRING)
return "'t'"
else:
return "'f'"
def __nonzero__(self):
return self.value
# here we select from the empty table, just to grab the description
curs.execute("SELECT v FROM test_bool WHERE 0=1")
booloid = curs.description[0][1]
print "Oid for the boolean datatype is", booloid
# and build the user cast object
BOOLEAN = psycopg.new_type((booloid,), "BOOLEAN", Boolean)
psycopg.register_type(BOOLEAN)
# now insert some data
curs.execute("INSERT INTO test_bool VALUES ('t', 1)")
curs.execute("INSERT INTO test_bool VALUES ('f', 0)")
# select and print all value, then re-insert them
curs.execute("SELECT * FROM test_bool")
for row in curs.fetchall():
print row
curs.execute("INSERT INTO test_bool VALUES (%s, %d)", row)
# re-select
print
curs.execute("SELECT * FROM test_bool")
for row in curs.fetchall():
style.SQL_FIELD('%s_id_seq' % table_name),
style.SQL_KEYWORD('RESTART'),
style.SQL_KEYWORD('WITH'),
style.SQL_FIELD('1')
)
)
return sql
else:
return []
# Register these custom typecasts, because Django expects dates/times to be
# in Python's native (standard-library) datetime/time format, whereas psycopg
# use mx.DateTime by default.
try:
Database.register_type(
Database.new_type((1082, ), "DATE", util.typecast_date))
except AttributeError:
raise Exception, "You appear to be using psycopg version 2. Set your DATABASE_ENGINE to 'postgresql_psycopg2' instead of 'postgresql'."
Database.register_type(
Database.new_type((1083, 1266), "TIME", util.typecast_time))
Database.register_type(
Database.new_type((1114, 1184), "TIMESTAMP", util.typecast_timestamp))
Database.register_type(
Database.new_type((16, ), "BOOLEAN", util.typecast_boolean))
OPERATOR_MAPPING = {
'exact': '= %s',
'iexact': 'ILIKE %s',
'contains': 'LIKE %s',
'icontains': 'ILIKE %s',
'gt': '> %s',
