Michael Bayer wrote:
>> INFO:sqlalchemy.engine.base.Engine.0x...dbd4:{'OS_version': u'RHEL 5
>> 64
>> ZSERIES', 'id': 8L}
>> INFO:sqlalchemy.engine.base.Engine.0x...dbd4:select
>> nextval('"os_version_id_seq"')
>> INFO:sqlalchemy.engine.base.Engine.0x...dbd4:None
>
>
> there seems to be rows inserted there.
Except when I browse the database (using external tool, phpPGAdmin),
those tables, incl. 'os_version', are empty.
> this email doesn't seem to
> describe anything actually going wrong except "doesn't work". can you
> be more specific please ?
OK I'll try though I'm not sure how, because some tables are just empty,
anyway here's more detailed version:
tables empty: architecture, hosts, os_kind, os_version, reservation
(although that can be empty, the converter utility I've been running
here does not add any data to that table), reservation_hosts (the same,
it should be empty), table locks(empty as well), virtualization (empty,
while it should be filled by converter)
tables with some data: email, hwrep, project, project_hwreps
Entire sqla programming:
#####################################################
import codecs
from sqlalchemy import create_engine, Table, Column, String, Integer,
MetaData, ForeignKey, desc, asc
from sqlalchemy.orm import mapper, relation, sessionmaker,
clear_mappers, join
from sqlalchemy.databases.sqlite import SLBoolean, SLDate
import sqlalchemy
import sqlalchemy.exc
import logging
import os
import ConfigParser
globalpath = os.path.dirname(__file__)
conf = ConfigParser.SafeConfigParser()
conf.read(globalpath + os.sep + 'status.conf')
postgresdbname=conf.get('main','postgresdbname')
postgreshost=conf.get('main','postgreshost')
postgresuser=conf.get('main', 'postgresuser')
postgrespassword=conf.get('main','postgrespassword')
postgresport=conf.get('main','postgresport')
class Host(object):
def
__init__(self,IP,HostName,Location,Additional_info,Column_11,Column_12,Username,Password,Alias,Shareable,Shareable_between_projects,Notes,CPU,RAM,Column_23,Batch,ASSET,Owner,SSH_KEY_PRESENT,Machine_Type_Model,MAC_ADDRESS_ETH_0,Physical_Box,Up_n_running,Available):
self.IP = IP
self.HostName = HostName
self.Location = Location
self.Additional_info = Additional_info
#self.End_Date = End_Date
self.Column_11 = Column_11
self.Column_12 = Column_12
self.Username = Username
self.Password = Password
self.Alias = Alias
self.Shareable = Shareable
self.Shareable_between_projects = Shareable_between_projects
self.Notes = Notes
self.CPU = CPU
self.RAM = RAM
self.Batch = Batch
self.ASSET = ASSET
self.Owner = Owner
self.SSH_KEY_PRESENT = SSH_KEY_PRESENT
self.Machine_Type_Model = Machine_Type_Model
self.MAC_ADDRESS_ETH_0 = MAC_ADDRESS_ETH_0
self.Physical_Box = Physical_Box
self.Up_n_running = Up_n_running
self.Available = Available
class Architecture(object):
def __init__(self,Architecture):
self.Architecture = Architecture
class OS_Kind(object):
def __init__(self,os_kind):
self.OS_Kind = os_kind
class OS_version(object):
def __init__(self,os_version):
self.OS_version = os_version
class Email(object):
def __init__(self,email):
self.Email = email
class Virtualization(object):
def __init__(self,virtualization, color):
self.Virtualization = virtualization
self.color = color
class Project(object):
def __init__(self, project):
self.Project = project
class HWRep(object):
def __init__(self, hwrepname):
self.HWRep = hwrepname
class Reservation(object):
def __init__(self, startdate, enddate, status, businessneed,
notetohwrep):
self.Start_Date = startdate
self.End_Date = enddate
self.Status = status
self.Businessneed = businessneed
self.Notetohwrep = notetohwrep
#eng = create_engine('sqlite:////' + globalpath + os.sep +
'hosts.db',echo=True)
eng = create_engine('postgres://%s:%...@%s:%s/%s' % (postgresuser,
postgrespassword, postgreshost, postgresport, postgresdbname), echo=True)
md = MetaData()
md.bind = eng
#clear_mappers()
hosts_table = Table('hosts',md,
Column('id',Integer,primary_key=True),
Column('IP',String),
Column('HostName',String),
Column('Location',String),
Column('Architecture_id',Integer,ForeignKey('architecture.id')),
Column('OS_Kind_id',Integer,ForeignKey('os_kind.id')),
Column('OS_version_id',Integer,ForeignKey('os_version.id')),
Column('Additional_info',String),
#Column('End_Date',SLDate),
Column('Column_12',String),
Column('Column_13',String),
Column('Email_id',Integer,ForeignKey('email.id')),
Column('Username',String),
Column('Password',String),
Column('Alias',String),
Column('Virtualization_id',Integer,ForeignKey('virtualization.id')),
Column('Shareable',SLBoolean),
Column('Shareable_between_projects',SLBoolean),
Column('Notes',String),
Column('CPU',String),
Column('RAM',String),
Column('Column_24',String),
Column('Batch',String),
Column('ASSET',String),
Column('Owner',String),
Column('SSH_KEY_PRESENT',String),
Column('Machine_Type_Model',String),
Column('MAC_ADDRESS_ETH_0',String),
Column('Physical_Box',SLBoolean),
Column('Up_n_running',SLBoolean),
Column('Available',SLBoolean),
Column('Earliest_reservation_id',Integer),
Column('Earliest_reservation_start_date',SLDate),
Column('Earliest_reservation_end_date',SLDate),
Column('Earliest_reservation_status',String),
Column('Earliest_reservation_businessneed',String),
Column('Project_id',Integer,ForeignKey('project.id')))
# for col in columnlist:
# hosts_table = hosts_table + "Column('%s',String)," % col
# hosts_table = hosts_table.rstrip(',') + ')'
# exec(hosts_table)
architecture_table = Table('architecture',md,
Column('id',Integer,primary_key=True),
Column('Architecture',String))
os_kind_table = Table('os_kind',md,
Column('id',Integer,primary_key=True),
Column('OS_Kind',String))
os_version_table = Table('os_version',md,
Column('id',Integer,primary_key=True),
Column('OS_version',String))
email_table = Table('email',md,
Column('id',Integer,primary_key=True),
Column('Email',String))
virtualization_table = Table('virtualization',md,
Column('id',Integer,primary_key=True),
Column('Virtualization',String),
Column('color',String))
project_table = Table('project',md,
Column('id',Integer,primary_key=True),
Column('Project',String))
hwrep_table = Table('hwrep',md,
Column('id',Integer,primary_key=True),
Column('HWRep',String),
Column('Email_id',Integer,ForeignKey('email.id')),
Column('Password',String))
# project <-> hwrep many-to-many association table
project_hwreps_assoc_table = Table('project_hwreps', md,
Column('Project_id',Integer,ForeignKey('project.id')),
Column('HWRep_id',Integer,ForeignKey('hwrep.id')))
reservation_table = Table('reservation', md,
Column('id',Integer,primary_key=True),
Column('Start_Date',SLDate),
Column('End_Date',SLDate),
Column('Status', String),
Column('Businessneed', String),
Column('Notetohwrep',String),
Column('Email_id',Integer,ForeignKey('email.id')),
Column('Project_id',Integer,ForeignKey('project.id'))
)
reservation_hosts_assoc_table = Table('reservation_hosts', md,
Column('Reservation_id',Integer,ForeignKey('reservation.id')),
Column('Host_id',Integer,ForeignKey('hosts.id'))
)
tablelocks_table = Table('tablelocks', md,
Column('tablename', String, primary_key=True))
mapper(Virtualization, virtualization_table)
mapper(Email,email_table)
mapper(OS_version, os_version_table)
mapper(Architecture, architecture_table)
mapper(OS_Kind, os_kind_table)
mapper(Project, project_table, properties={'HWReps':relation(HWRep,
secondary=project_hwreps_assoc_table, backref='project')})
mapper(HWRep, hwrep_table, properties={'Projects':relation(Project,
secondary=project_hwreps_assoc_table, backref='hwrep'),
'Email':relation(Email, order_by=Email.id, backref='hwrep')})
mapper(Reservation, reservation_table,
properties={'Email':relation(Email,order_by=Email.id,backref='reservation'),
'Project':relation(Project, order_by=Project.id, backref='reservation'),
'Hosts':relation(Host, secondary=reservation_hosts_assoc_table,
backref='reservation')})
mapper(Host, hosts_table,
properties={'Architecture':relation(Architecture,
order_by=Architecture.id, backref='hosts'),
'OS_Kind':relation(OS_Kind, order_by=OS_Kind.id, backref='hosts'),
'OS_version':relation(OS_version, order_by=OS_version.id,
backref='hosts'), 'Email':relation(Email, order_by=Email.id,
backref='hosts'), 'Virtualization':relation(Virtualization,
order_by=Virtualization.id, backref='hosts'),
'Project':relation(Project, order_by=Project.id, backref='hosts'),
'Reservations':relation(Reservation,
secondary=reservation_hosts_assoc_table, backref='hosts')
})
Session=sessionmaker(bind=eng)
session=Session()
logging.basicConfig(filename=globalpath + os.sep + 'sql.log')
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
logging.getLogger('sqlalchemy.orm.unitofwork').setLevel(logging.DEBUG)
#####################################################
The log shows this:
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#999999',
'Virtualization': u'LPAR', 'id': 18L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#FAFAFA',
'Virtualization': u'BOX ZONE HOST', 'id': 19L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#9966CC',
'Virtualization': u'NPAR', 'id': 20L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#9966CC',
'Virtualization': u'VPAR', 'id': 21L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#CC99FF',
'Virtualization': u'IVM GUEST', 'id': 22L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#FAFAFA',
'Virtualization': u'BOX MSVS HOST', 'id': 23L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#FAFAFA',
'Virtualization': u'LDOM ZONE HOST', 'id': 24L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#FAFAFA',
'Virtualization': u'BOX ESX HOST', 'id': 25L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#656565',
'Virtualization': u'WPAR', 'id': 26L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#FAFAFA',
'Virtualization': u'BOX VMWARE HOST', 'id': 27L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#FAFAFA',
'Virtualization': u'OTHER', 'id': 28L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#0066CC',
'Virtualization': u'VMWARE GUEST', 'id': 29L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#3399FF',
'Virtualization': u'ESX GUEST', 'id': 30L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
INFO:sqlalchemy.engine.base.Engine.0x...97f4:INSERT INTO virtualization
(id, "Virtualization", color) VALUES (%(id)s, %(Virtualization)s, %(color)s)
INFO:sqlalchemy.engine.base.Engine.0x...97f4:{'color': '#CC9933',
'Virtualization': u'ZONE GUEST', 'id': 31L}
INFO:sqlalchemy.engine.base.Engine.0x...97f4:select
nextval('"virtualization_id_seq"')
INFO:sqlalchemy.engine.base.Engine.0x...97f4:None
Even though this log shows what it shows, the 'virtualization' table is
completely empty. I have rechecked this again, using psql this time:
postgres=# \c reservations
You are now connected to database "reservations".
reservations=# select * from virtualization;
id | Virtualization | color
----+----------------+-------
(0 rows)
Perhaps this is a problem specific to RH bundled Postgres 8.1 ???
Regards,
mk
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
