python操作mysql和oracle数据库

最新推荐文章于 2023-06-29 22:29:14 发布

weixin_33693070

最新推荐文章于 2023-06-29 22:29:14 发布

阅读量125

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3636678/blog/2244805

版权

2019独角兽企业重金招聘Python工程师标准>>>

1、mysql

连接数据库：

def open_spider(self, spider):
    self.conn = MySQLdb.connect(host="localhost", user="username", passwd="password",db="databasename",charset="utf8")
    self.cursor = self.conn.cursor()

关闭连接：

def close_spider(self, spider):
    #print "close_spider............................"
    self.cursor.close()
    self.conn.close()

创建表(主键自增)：

self.cursor.execute("create table tablename(pid number(15) primary key auto_increment, id number(6) not null, name varchar2(30) not null)")
self.conn.commit()

查询操作：

def getCompID(self):
    self.cursor.execute("select * from tablename")
    self.conn.commit()
    self.compIDList = self.cursor.fetchall()

批量插入操作：

tableprop = "tablename (id, name) values(%s, %s)"
bulkdata = []

def bulk_insert_to_mysql(self, bulkdata, tableprop):
    try:
        sql = "insert into "+tableprop
        self.cursor.executemany(sql, bulkdata) #bulkdata的结构是列表
        self.conn.commit()
    except:
        self.conn.rollback()

更新操作：

updateprop = "update tablename set time = '%s' where comp = 'sse'" % (temptime)
self.cursor.execute(updateprop)
self.conn.commit()

删除表、清空表：

self.cursor.execute("delete from tablename where id = 'cn'")
self.conn.commit()

self.cursor.execute("truncate table tablename")
self.conn.commit()

2、oracle

连接数据库：

import cx_Oracle

def open_spider(self, spider):
    #print "open_spider............................"
    self.conn = cx_Oracle.connect('username', 'password', 'id:port/databasename') #访问远程oracle方案
    self.cursor = self.conn.cursor()

批量插入操作：

#插入到主键自增的表中，不用管主键，只需依次插入其他
tableprop = "insert into tablename(id, name) values(:ID, :NAME)"
bulkdata = []

def bulk_insert_to_mysql(self, bulkdata, tableprop):
    try:
        self.cursor.executemany(tableprop, bulkdata)
        self.conn.commit()
    except Exception as e:
        print "insert error!"
        print 'repr(e):', repr(e)

更新表操作：

#单字段更新
updateprop = "update tablename set time = :time where comp = 'aa'"
self.cursor.execute(updateprop, {'time':temptime})
#多字段更新
updateprop = "update tablename set time = :time, atime = :atime where comp = 'aa'"
self.cursor.execute(updateprop, {'time':temptime, 'atime':atime})

创建表操作(主键自增)：

self.cursor.execute("create table tablename(pid number(15) primary key, id number(6) not null, name varchar2(30) not null)")
self.conn.commit()
self.cursor.execute("CREATE SEQUENCE table_Sequence INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE")
self.conn.commit()
self.cursor.execute("CREATE TRIGGER book_increase BEFORE insert ON tablename FOR EACH ROW begin select BOOK_SEQUENCE.nextval into :New.pid from dual;end;") #此处结尾分号不能省
self.conn.commit()

查询表是否存在：

def table_exists(self, con, table_name):
    sql = "select table_name from all_tables"
    con.execute(sql)
    tables = con.fetchall()
    for table in tables:
        temptable = str(table[0]).lower() #查出来的表名有大写有小写
        if  temptable == table_name:
            return 1
    return 0

转载于:https://my.oschina.net/u/3636678/blog/2244805