目录
（一）测试数据生成代码
1.生成学生数据的代码
2.生成课程信息的代码
3.生成选课信息的代码
（二）JDBC的数据导入代码
实验代码存档。
（一）测试数据生成代码
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()
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!");
}
}
