数据库表的常用操作

数据库相关操作详见https://www.cnblogs.com/ws0316/p/11874311.html

Mysql相关操作

# 1，授权
'''
1,授权：
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'gloryroad' WITH GRANT OPTION;
FLUSH PRIVILEGES;

*.*中的第一个星，表示所有的数据库，如果指定一个数据库test.*
第二个星，表示所有的表
ALL PRIVILEGES：所有的权限都开通
某几个权限：grant select,create,drop,update,alter on *.*
root：给哪个用户开权限
%：表示所有的ip都可以连进来
IDENTIFIED BY 'gloryroad'：设定root用户的密码
'''

# 2，创建库，使用库, 创建表，使用表
'''
# 某库不存在时创建库,COLLATE utf8_general_ci 为字符校验规则，为不区分大小写插入数据。
create database if not exists gloryroad  default  charset utf8 COLLATE utf8_general_ci;

use gloryroad;       #使用库
show databases;     #查看库
select database();   #查看当前使用的库名

show create table test;   #查看创建的表
show variable like 'autocommit';    #查看数据库是否自动提交，on 自动，off 手动

alter table studentinfo add index   id_index (id);   #给表添加索引
desc studentinfo;   #查看表结构
 
alter table grade add column datecoloumn datetime;  #增加列
 
alter table grade drop column datecoloumn;  #删除列

select curtime();    select now()    查看时间
select curdate();  查看日期

'''

# 3,往数据库表里插数据

import pymysql
import random

def insertdata():
    conn = pymysql.connect(             #获取连接对象
    host = '127.0.0.1',
    port = 3306,
    user = 'test',
    password = 'gloryroad',
    charset = 'utf8')
    cur = conn.cursor()                     #获取游标
    conn.select_db('gloryroad')             #选择库
    courselist = ['python','java','mysql','linux','接口测试','自动化测试','性能测试']
    for i in range(1,101):
        student_id = '2020000'+ '0' * (3 - len(str(i))) + str(i)
        name = random.choice(['lucy','lili','Tom','Dave']) + str(i)
        sex = random.choice(['m','w'])
        tel = '1'+ str(random.choice([3,5,8]))+str(random.randint(1,20))[2:11]
        print(tel)
        stuinfo_sql = "insert into studentInfo(student_id, name, sex, tel, AdmissionDate) \
        values('%s', '%s', '%s', '%s', date_sub(now(),interval %s day))" \
        % (student_id, name, sex, tel, random.randint(90, 120))
        cur.execute(stuinfo_sql)
        conn.commit()
        for j in courselist:
            grade_sql = "insert into grade(stuID,course,score) values('%s','%s','%s')" \
                        %(student_id,j,random.randint(60,101))
            cur.execute(grade_sql)
    conn.commit()
    cur.close()


insertdata()
print('数据插入成功')

oracle 相关操作

#encoding = utf-8

import cx_Oracle

class MyPlsq:
    def __init__(self):
        try:
            #连接数据库，下面括号里内容根据自己实际情况填写('用户名,密码,IP:端口号/SERVICE_NAME)
            self.conn = cx_Oracle.connect("test", "111111", "192.168.80.116:1111/orcl")
            self.cursor = self.conn.cursor()   # 使用cursor()方法获取操作游标
        except cx_Oracle.DatabaseError:
            return print("数据库连接错误")

    def select(self,sql):
        try:
            self.cursor.execute(sql)    #使用execute方法执行SQL语句
            data = self.cursor.fetchall()  #获取所有数据   cursor.fetchmany(8)  cursor.fetchone()
            return data
        except Exception as e:
            print('查询数据库错误')
            raise e

    def insert(self,sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()   #插入数据失败时回滚
            print('插入数据失败')
            raise e

    def update(self,sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()   #更新数据失败时回滚
            print('更新数据失败')
            raise e

    def delete(self,sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()   #删除数据失败时回滚
            print('删除数据失败')
            raise e

    def close(self):
        self.cursor.close()
        self.conn.close()




if __name__ == '__main__':
    sq = MyPlsq()
    import random

    for i in range(2):
        id = random.randint(11110, 999999999)
        sql = "select * from PMS_PRODUCT_ATTACHING_LOG order by create_date desc"
        data = sq.select(sql)
        print('目前数据库数据有%s条' %len(data))
        num = len(data)
        for i in data:
            print(i)
        sql1 = "insert into PMS_PRODUCT_ATTACHING_LOG(ID, COMPANY_ID, CREATE_BY, CREATE_DATE,\
                                                  DELETED, LAST_MODIFIED_BY, LAST_MODIFIED_DATE,\
                                                  APPROVE_OBJ_ID, APPROVE_TYPE_ID, COMMENTS,\
                                                  OPERATE_TYPE, OPERATER_ID)\
               values (%s, 00002, '创建人',  SYSDATE, 0, '修改人',SYSDATE,\
                    0005,1,'通过',\
                    1, 1)" %id
        sq.insert(sql1)
        data = sq.select(sql)
        print('插入数据后数据库共有%s条数据' %len(data))
        num1 =len(data)
        if num1-num ==1:
            print('往表 PMS_PRODUCT_ATTACHING_LOG 插入数据id %s成功' %id)

    sql2 = "update PMS_PRODUCT_ATTACHING_LOG set COMPANY_ID=1001 where LAST_MODIFIED_BY ='修改人'"
    sq.update(sql2)
    print('修改数据成功')

    sql3 = "delete from PMS_PRODUCT_ATTACHING_LOG where LAST_MODIFIED_BY ='修改人'"
    sq.delete(sql3)
    print('删除数据成功')

    sq.close()