diff options
Diffstat (limited to 'Db')
| -rw-r--r-- | Db/__init__.py | 81 | ||||
| -rw-r--r-- | Db/sqlalchemy_example_code.py | 105 |
2 files changed, 186 insertions, 0 deletions
diff --git a/Db/__init__.py b/Db/__init__.py new file mode 100644 index 0000000..c42a7c5 --- /dev/null +++ b/Db/__init__.py @@ -0,0 +1,81 @@ +# coding: utf-8 +import time, sys +HOST = "lalalizard.com" +USER = "asdfus" +PASSWORD = "gTYgT&M6q" +DATABASE = "asdfus" + +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 + +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)) + +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): + +# sys.stderr.write("date={}".format(str(date)) + "\n") +# sys.stderr.write("remote_addr={}".format(str(remote_addr)) + "\n") +# sys.stderr.write("name={}".format(str(username)) + "\n") +# sys.stderr.write("url={}".format(str(url)) + "\n") +# sys.stderr.write("dir={}".format(str(directory)) + "\n") +# sys.stderr.write("oldfile={}".format(str(oldfile)) + "\n") +# sys.stderr.write("newfile={}".format(str(newfile)) + "\n") +# sys.stderr.write("cmd={}".format(str(cmd)) + "\n") +# sys.stderr.write("dataobj={}".format(str(dataobj)) + "\n") +# sys.stderr.write("tag={}".format(str(tag)) + "\n") + 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/Db/sqlalchemy_example_code.py b/Db/sqlalchemy_example_code.py new file mode 100644 index 0000000..fc38b38 --- /dev/null +++ b/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 |