数据库实验测试数据生成代码和 JDBC数据导入代码

原创于 2022-05-29 21:45:20 发布 · 459 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #python #database

实验代码存档。

（一）测试数据生成代码

1.生成学生数据的代码


import random
import datetime
import pandas as pd
from faker.providers import BaseProvider
from faker import Faker
fake = Faker('zh_CN')


class sid_provider(BaseProvider):
    def sid(self):
        sid = '21'+str(random.randint(7, 9)) + \
            str(random.randint(0, 9))+str(random.randint(0, 2)) + \
            str(random.randint(0, 9))+str(random.randint(0, 9)) + \
            str(random.randint(0, 9))

        return sid


class dorm_provider(BaseProvider):
    def dorm(self):
        dorm = '东'+str(random.randint(1, 16))+'舍' + \
            str(random.randint(1, 9))+str(random.randint(0, 2)) + \
            str(random.randint(0, 9))
        return dorm


class sex_provider(BaseProvider):
    def sex(self):
        sex = ['男', '女']
        return sex[random.randint(0, 1)]


fake.add_provider(dorm_provider)
fake.add_provider(sex_provider)
fake.add_provider(sid_provider)


class Create_Data(object):
    def __init__(self):
        # 选择中文
        #fake = Faker('zh_CN')
        # 生成数据改变循环体来控制数据量rang(?)
        self.data_total = [
            [fake.unique.sid(), fake.name(), fake.sex(), fake.date_time_between(start_date="-25y", end_date="-20y", tzinfo=None),
             round(random.uniform(1.50, 2.10), 2), fake.dorm()]
            for x in range(5500)]
        print(self.data_total)

    # 写入excel
    def deal_excel(self):
        df = pd.DataFrame(self.data_total,
                          columns=['S#', 'SNAME', 'SEX', 'BDATE', 'HEIGHT', 'DORM'])
        # 保存到本地excel
        df.to_excel("student_data_5000.xlsx", index=False)
        print("Processing completed to excel")


if __name__ == '__main__':
    data = Create_Data()
    data.deal_excel()

2.生成课程信息的代码


import xlrd
import random
import datetime
import pandas as pd
from faker.providers import BaseProvider
from faker import Faker
fake = Faker('zh_CN')
course_book = xlrd.open_workbook('course_name.xls')
course_name = course_book.sheets()[0]


class cid_provider(BaseProvider):
    def cid(self):
        name = ['AUTO', 'COMP', 'CHEM', 'BIOL', 'MATH', 'MACH', 'ENGL', 'PHYS']
        cid = name[random.randint(0, 7)]+'-'+str(random.randint(0, 9)) + \
            str(random.randint(0, 9))+str(random.randint(0, 2)) + \
            str(random.randint(0, 9))

        return cid


class course_provider(BaseProvider):
    def course(self):
        course = course_name.cell(random.randint(1, 2070), 0).value

        return course


class period_provider(BaseProvider):
    def period(self):
        period = random.randint(8, 20)*4

        return period


class credit_provider(BaseProvider):
    def credit(self):
        credit = random.randint(2, 5)

        return credit


fake.add_provider(cid_provider)
fake.add_provider(course_provider)
fake.add_provider(period_provider)
fake.add_provider(credit_provider)


class Create_Data(object):
    def __init__(self):
        # 选择中文
        #fake = Faker('zh_CN')
        # 生成数据改变循环体来控制数据量rang(?)
        self.data_total = [
            [fake.unique.cid(), fake.unique.course(), fake.period(),
             fake.credit(), fake.name()]
            for x in range(1300)]
        print(self.data_total)

    # 写入excel
    def deal_excel(self):
        df = pd.DataFrame(self.data_total,
                          columns=['C#', 'CNAME', 'PERIOD', 'CREDIT', 'TEACHER'])
        # 保存到本地excel
        df.to_excel("course_data_1000.xlsx", index=False)
        print("Processing completed to excel")


if __name__ == '__main__':
    data = Create_Data()
    data.deal_excel()

3.生成选课信息的代码

（因为主键是S#和C#，生成的信息可能有重复，需要在excel里删除重复数据）


import xlrd
import random
import datetime
import pandas as pd
from faker.providers import BaseProvider
from faker import Faker
fake = Faker('zh_CN')
#student_book = xlrd.open_workbook('student_data_1000.xls')
student_book = xlrd.open_workbook('student_data_5000.xls')
student_data = student_book.sheets()[0]
#course_book = xlrd.open_workbook('course_data_100.xls')
course_book = xlrd.open_workbook('course_data_1000.xls')
course_data = course_book.sheets()[0]


class sid_provider(BaseProvider):
    def sid(self):
        sid = student_data.cell(random.randint(1, 1000), 0).value
        return sid


class cid_provider(BaseProvider):
    def cid(self):
        cid = course_data.cell(random.randint(1, 100), 0).value
        return cid


class grade_provider(BaseProvider):
    def grade(self):
        flag = random.randint(0, 9)
        if(flag == 0):
            grade = random.randint(0, 60)
        else:
            grade = random.randint(60, 100)
        return grade


fake.add_provider(sid_provider)
fake.add_provider(cid_provider)
fake.add_provider(grade_provider)


class Create_Data(object):
    def __init__(self):
        # 选择中文
        #fake = Faker('zh_CN')
        # 生成数据改变循环体来控制数据量rang(?)
        self.data_total = [
            [fake.sid(), fake.cid(), fake.grade()]
            for x in range(40000)]
        print(self.data_total)

    # 写入excel
    def deal_excel(self):
        df = pd.DataFrame(self.data_total,
                          columns=['S#', 'C#', 'GRADE'])
        # 保存到本地excel
        df.to_excel("SC_data_30000.xlsx", index=False)
        print("Processing completed to excel")


if __name__ == '__main__':
    data = Create_Data()
    data.deal_excel()

（二）JDBC的数据导入代码



import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook; 
public class datain {
 
    static final String JDBC_DRIVER = "org.postgresql.Driver";  
    //登录demo数据库
    static final String DB_URL = "jdbc:postgresql://192.168.118.137:26000/demo?ApplicationName=app1";
   static final String USER = "dbuser";
  static final String PASS = "Gauss#3demo";
      
     public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{

            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            //String sql="insert into S1132(S#,SNAME,SEX,BDATE,HEIGHT,DORM)  values(?,?,?,?,?,?)";
            //PreparedStatement pstmt = conn.prepareStatement(sql);
             stmt = conn.createStatement();
            
            //File file = new File("D:\\STUDY\\python_code\\faker_code\\student_data_0.xls");
            //File file = new File("D:\\STUDY\\python_code\\faker_code\\course_data_0.xls");
            File file = new File("D:\\STUDY\\python_code\\faker_code\\sc_data_0.xls");
            Workbook wb =  Workbook.getWorkbook(file);
            Sheet sheet = wb.getSheet("Sheet1");
            
            
//            //导入学生数据表
//            for(int times = 1; times < sheet.getRows(); times++) 
//            {
//            	Cell cell = null;
//            	cell = sheet.getCell(0,times);
//            	String sno =cell.getContents();
//            	cell = sheet.getCell(1,times);
//            	String sname =cell.getContents();
//            	cell = sheet.getCell(2,times);
//            	String sex =cell.getContents();
//            	cell = sheet.getCell(3,times);
//            	String bdate =cell.getContents();
//            	cell = sheet.getCell(4,times);
//            	String height =cell.getContents();
//            	cell = sheet.getCell(5,times);
//            	String dorm =cell.getContents();
//            	String sql = "insert into S113(S#,SNAME,SEX,BDATE,HEIGHT,DORM)  values('"+ sno +"','"+ sname +"','" + 
//            	sex + "','" + bdate + "','" + height + "','" + dorm +"')";
//            	stmt.addBatch(sql);
//            	System.out.println("已加入第"+times+"条插入命令");
//            	
//            	
//            }
//            
//            System.out.println("插入命令正在批量执行");
//            long startTime =  System.currentTimeMillis();
//            int[] count = stmt.executeBatch();
//            long endTime =  System.currentTimeMillis();
//            long usedTime = (endTime-startTime);
//            System.out.println("执行结束，用时:"+usedTime+"ms");
//            
            
            
            
            
            
            
            
            
            
//            //导入课程数据表
//            for(int times = 1; times < sheet.getRows(); times++) 
//            {
//            	Cell cell = null;
//            	cell = sheet.getCell(0,times);
//            	String cno =cell.getContents();
//            	cell = sheet.getCell(1,times);
//            	String cname =cell.getContents();
//            	cell = sheet.getCell(2,times);
//            	String period =cell.getContents();
//            	cell = sheet.getCell(3,times);
//            	String credit =cell.getContents();
//            	cell = sheet.getCell(4,times);
//            	String teacher =cell.getContents();
//            	
//            	String sql = "insert into C113(C#,CNAME,PERIOD,CREDIT,TEACHER)  values('"+ cno +"','"+ cname +"','" + 
//            	period + "','" + credit + "','" + teacher  +"')";
//            	stmt.addBatch(sql);
//            	System.out.println("已加入第"+times+"条插入命令");
//            	
//            	
//            }
//            
//            System.out.println("插入命令正在批量执行");
//            long startTime =  System.currentTimeMillis();
//            int[] count = stmt.executeBatch();
//            long endTime =  System.currentTimeMillis();
//            long usedTime = (endTime-startTime);
//            System.out.println("执行结束，用时:"+usedTime+"ms");
               
            
            
            
            
            
            
            
            
            
          //导入选课数据表
            for(int times = 1; times < sheet.getRows(); times++) 
            {
            	Cell cell = null;
            	cell = sheet.getCell(0,times);
            	String sno =cell.getContents();
            	cell = sheet.getCell(1,times);
            	String cno =cell.getContents();
            	cell = sheet.getCell(2,times);
            	String grade =cell.getContents();
            	
            	String sql = "insert into SC113(S#,C#,GRADE)  values('"+ sno +"','"+ cno +"','" + 
            	grade + "')";
            	stmt.addBatch(sql);
            	System.out.println("已加入第"+times+"条插入命令");
            	
            	
            }
            
            System.out.println("插入命令正在批量执行");
            long startTime =  System.currentTimeMillis();
            int[] count = stmt.executeBatch();
            long endTime =  System.currentTimeMillis();
            long usedTime = (endTime-startTime);
            System.out.println("执行结束，用时:"+usedTime+"ms");
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
//             //使用安全性更好的prepared statement 进行对话        
//            for(int times = 1; times < sheet.getRows(); times++) {
//            	Cell cell = null;
//            	
//            	
//            	cell = sheet.getCell(0,times);
//            	String str=cell.getContents();
//                pstmt.setString(1, str);
//                
//                cell = sheet.getCell(1,times);
//            	str=cell.getContents();
//                pstmt.setString(2, str);
//                
//                cell = sheet.getCell(2,times);
//            	str=cell.getContents();
//                pstmt.setString(3, str);
//                
//                cell = sheet.getCell(3,times);
//            	str=cell.getContents();
//            	//System.out.println(str);
//            	SimpleDateFormat sdf =   new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
//            	Date date=sdf.parse(str);    
//            	//System.out.println(date);
//            	java.sql.Date d = new java.sql.Date(date.getTime() );
//            	//System.out.println(d);
//                pstmt.setDate(4,  d);
//                
//                cell = sheet.getCell(4,times);
//            	str=cell.getContents();
//                pstmt.setFloat(5, Float.parseFloat(str));
//                
//                cell = sheet.getCell(5,times);
//            	str=cell.getContents();
//                pstmt.setString(6, str);
//                
//                //System.out.println("已加入第"+times+"条插入命令");
//                pstmt.addBatch();
//             }
//             pstmt.executeBatch();

             
             
            

            //pstmt.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try{
            	if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }// 什么都不做
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        System.out.println("Goodbye!");
    }
}