diff options
Diffstat (limited to 'pb/lib')
| -rw-r--r-- | pb/lib/Db/__init__.py | 110 | ||||
| -rw-r--r-- | pb/lib/Db/sqlalchemy_example_code.py | 105 |
2 files changed, 168 insertions, 47 deletions
diff --git a/pb/lib/Db/__init__.py b/pb/lib/Db/__init__.py index 91b0fcf..ac7ca17 100644 --- a/pb/lib/Db/__init__.py +++ b/pb/lib/Db/__init__.py @@ -1,55 +1,71 @@ -import MySQLdb -import time +# coding: utf-8 +import time, sys +HOST = "lalalizard.com" USER = "asdfus" PASSWORD = "gTYgT&M6q" DATABASE = "asdfus" -class Db(object): - def __init__ (self): - self.conn = None - self.cursor = None - self.connect() - def connect (self): - self.conn = MySQLdb.connect (host = "localhost", - user = USER, - passwd = PASSWORD, - db = DATABASE - ) - self.cursor = self.conn.cursor () +from sqlalchemy import Column, Integer, LargeBinary, String, create_engine, sql +from sqlalchemy.ext.declarative import declarative_base +from sqlalchemy.orm import sessionmaker +_NULL = sql.null() + +Base = declarative_base() +metadata = Base.metadata - def execute (self,sql,args=()): - try: - self.cursor.execute(sql,args) - except MySQLdb.Error, e: - print "Error %d: %s" % (e.args[0], e.args[1]) - # sys.exit (1) - self.connect() - self.cursor.execute(sql,args) +class ImCmd(Base): + __tablename__ = 'im_cmd' + id = Column(Integer, primary_key=True) + date = Column(Integer) + remote_addr = Column(String(16)) + name = Column(String(16)) + url = Column(String(256)) + dir = Column(String(2)) + oldfile = Column(String(256)) + newfile = Column(String(256)) + cmd = Column(LargeBinary) + dataobj = Column(LargeBinary) + tag = Column(String(50)) - def lastinsertid (self): - return self.conn.insert_id() +class Db(object): + def __init__(self): + engine = create_engine('mysql://{}:{}@{}/{}'.format( + USER, + PASSWORD, + HOST, + DATABASE + )) + Session = sessionmaker(bind=engine) + self.session = Session() - def insert_cmd ( - self, - date=time.time(), - remote_addr="NULL", - username="NULL", - url="NULL", - directory="NULL", - oldfile="NULL", - newfile="NULL", - cmd="NULL", - dataobj="NULL", - tag="NULL"): - try: - sql = "INSERT INTO im_cmd " - sql += "(date, remote_addr, name, url, dir, oldfile, newfile, cmd, dataobj, tag) " - sql += "VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" - #or "NULL" - args = (date, remote_addr, username, url, directory, oldfile, newfile, cmd, dataobj, tag) - #args = (now(), os.environ['REMOTE_ADDR'], name, url, dir, oldfile, newfile, " ".join(cmd),dataobj) - self.execute(sql, args) - except Exception as e: - sys.stderr.write(str(e)) - return + def insert_cmd ( + self, + date=time.time(), + remote_addr=_NULL, + username=_NULL, + url=_NULL, + directory=_NULL, + oldfile=_NULL, + newfile=_NULL, + cmd=_NULL, + dataobj=_NULL, + tag=_NULL): + try: + entry = ImCmd( + date=date, + remote_addr=remote_addr, + name=username, + url=url, + dir=directory, + oldfile=oldfile, + newfile=newfile, + cmd=cmd, + dataobj=dataobj, + tag=tag + ) + self.session.add(entry) + self.session.commit() + except Exception as e: + sys.stderr.write("Unable to commit database entry"); + sys.stderr.write(str(e)) diff --git a/pb/lib/Db/sqlalchemy_example_code.py b/pb/lib/Db/sqlalchemy_example_code.py new file mode 100644 index 0000000..fc38b38 --- /dev/null +++ b/pb/lib/Db/sqlalchemy_example_code.py @@ -0,0 +1,105 @@ +# coding: utf-8 +import MySQLdb +import time, sys +USER = "asdfus" +PASSWORD = "gTYgT&M6q" +DATABASE = "asdfus" + + +from sqlalchemy import Column, Integer, LargeBinary, String, create_engine +from sqlalchemy.ext.declarative import declarative_base +from sqlalchemy.orm import sessionmaker +engine = create_engine('mysql://{}:{}@localhost/{}'.format(USER, PASSWORD, DATABASE)) +Session = sessionmaker(bind=engine) +session = Session() +connection = engine.connect() +#result = connection.execute("select username from users") +#for row in result: +# print "username:", row['username'] +#connection.close() + + +Base = declarative_base() +metadata = Base.metadata + + +class ImCmd(Base): + __tablename__ = 'im_cmd' + id = Column(Integer, primary_key=True) + date = Column(Integer) + remote_addr = Column(String(16)) + name = Column(String(16)) + url = Column(String(256)) + dir = Column(String(2)) + oldfile = Column(String(256)) + newfile = Column(String(256)) + cmd = Column(LargeBinary) + dataobj = Column(LargeBinary) + tag = Column(String(50)) +#def __repr__(self): +#... return "<User(name='%s', fullname='%s', password='%s')>" % ( +#... self.name, self.fullname, self.password) + + +for instance in session.query(ImCmd).order_by(ImCmd.id): + print instance.name, instance.date + + +ed_user = User(name='ed', fullname='Ed Jones', password='edspassword') +session.add(ed_user) +#session.add_all([ +#... User(name='wendy', fullname='Wendy Williams', password='foobar'), +#... User(name='mary', fullname='Mary Contrary', password='xxg527'), +#... User(name='fred', fullname='Fred Flinstone', password='blah')]) + +#session.commit() + +class Db(object): + def __init__ (self): + self.conn = None + self.cursor = None + self.connect() + + def connect (self): + self.conn = MySQLdb.connect (host = "lalalizard.com", + user = USER, + passwd = PASSWORD, + db = DATABASE + ) + self.cursor = self.conn.cursor () + + def execute (self,sql,args=()): + try: + self.cursor.execute(sql,args) + except MySQLdb.Error, e: + print "Error %d: %s" % (e.args[0], e.args[1]) + # sys.exit (1) + self.connect() + self.cursor.execute(sql,args) + + def lastinsertid (self): + return self.conn.insert_id() + + def insert_cmd ( + self, + date=time.time(), + remote_addr="NULL", + username="NULL", + url="NULL", + directory="NULL", + oldfile="NULL", + newfile="NULL", + cmd="NULL", + dataobj="NULL", + tag="NULL"): + try: + sql = "INSERT INTO im_cmd " + sql += "(date, remote_addr, name, url, dir, oldfile, newfile, cmd, dataobj, tag) " + sql += "VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" + #or "NULL" + args = (date, remote_addr, username, url, directory, oldfile, newfile, cmd, dataobj, tag) + #args = (now(), os.environ['REMOTE_ADDR'], name, url, dir, oldfile, newfile, " ".join(cmd),dataobj) + self.execute(sql, args) + except Exception as e: + sys.stderr.write(str(e)) + return |