koa使用数据库(mysql-pro,此模块可以使用事务,占位符等功能)
//核心代码
const koa = require('koa');
const pathlib = require('path');
const router = require('koa-router')
const mysql = require('mysql-pro');
//使用new来初始化mysql
const db = new mysql({
mysql:{
host:'localhost',
port:3306,
user:'root',
password:'root',
database:'koadb'
}
});
//初始化router
let r1 = router();
let server = new koa();//初始化koa
server.listen(8087);
server.use(r1.routes());
r1.get('/user',async ctx=>{
let id = ctx.request.query.id;//http://localhost:8087/user?id=1获取?后面的参数id的值
try{
await db.startTransaction();//开启事务
let data = await db.executeTransaction('select * from user');
let query = await db.executeTransaction('select * from user where id=?',[id]);//占位符,防止sql注入
await db.stopTransaction();//关闭事务,此处应该是提交事务
ctx.response.body=[data,query];
}catch(e){
ctx.response.body='出错';
}
})
知乎实战项目(往数据库导入数据)
//将数据导入mysql的核心代码
const fs=require('fs');//文件模块
const Mysql=require('mysql-pro');//数据库模块
//创建数据库连接信息
const db=new Mysql({
mysql: {
host: 'localhost',
port: 3306,
user: 'root',
password: 'root',
database: 'zhihu'
}
});
//读取.topics数据文件,读出为二进制文件,toString方法转化为string;使用json.parse函数将json字符串转化为对象
const arr=JSON.parse(fs.readFileSync('.topics').toString());
//创建数据库的映射对象,以及初始ID
let topics={}, topic_ID=1;
let authors={}, author_ID=1;
let questions={}, question_ID=1;
let answers={}, answer_ID=1;
// console.log(arr[0]);
//循环读取的数据文件
arr.forEach(question=>{
//topic,每个问题的标签,每个问题有多个标签,我们的数据库表中,topic表只有id与title
question.topics=question.topices.map(json=>{
let {title}=json;//解构赋值,json中有title与url,此处获取title
title=title.replace(/^\s+|\s+$/g, '');//将title中前后空格处理掉
//判读如果topics中没有title,便设置该title对应的id即topic_ID++;
if(!topics[title]){
topics[title]=topic_ID++;
}
return topics[title];//return 当前question中的topics的title的值
}).join(',');//将topics用逗号分隔,返回字符串
//author,作者,取最佳回答中的作者与其他回答中的所有作者,此处用剩余参数(见1-11笔记);
[question.bestAnswer.author, ...question.answers.map(answer=>answer.author)].forEach((author,index)=>{
let old_id=author.id;//最佳答案的作者id
//判读如果authors中没有当前作者id,便设置该作者的id对应的author对象;
if(!authors[old_id]){
authors[author.id]=author;
author.id=author_ID++;//把原author对象的id设置为我们的定义的ID
if(index==0){
//delete 操作符用于删除对象的某个属性；如果没有指向这个属性的引用，那它最终会被释放。
delete question.bestAnswer.author;//删掉原有的对象
question.bestAnswer.author_ID=author.id;//用对象ID代替
}else{
delete question.answers[index-1].author;
question.answers[index-1].author_ID=author.id;
}
}
return authors[old_id];//return 原始ID的值
});
//question
let ID=question_ID;
questions[question_ID++]=question;//直接将问题复制给id
// console.log(questions);
//answers,同上,循环赋值给id
[question.bestAnswer, ...question.answers].forEach(answer=>{
answer.id=answer_ID;
answer.question_ID=ID;
answers[answer_ID++]=answer;
});
});
(async()=>{
function dataJoin(...args){
return "('"+args.map(item=>{
item=item||'';
item=item.toString().replace(/'/g, '\\\'');
return item;
}).join("','")+"')";
}
//topics
let aTopics=[];
for(let title in topics){
let ID=topics[title];
aTopics.push(dataJoin(ID, title));
}
let topic_sql=`INSERT INTO topics VALUES${aTopics.join(',')}`;
//authors
let aAuthors=[];
for(let id in authors){
let author=authors[id];
aAuthors.push(dataJoin(author.id, author.type, author.name, author.gender, author.userType, author.img_url, author.headline, author.followerCount));
}
let author_sql=`INSERT INTO author VALUES${aAuthors.join(',')}`;
//questions
let aQuestions=[];
for(let ID in questions){
let question=questions[ID];
// console.log(ID);
aQuestions.push(dataJoin(ID, question.title, question.question_content, question.topics, question.attention_count, question.view_count, question.bestAnswer.id));
}
let question_sql=`INSERT INTO question VALUES${aQuestions.join(',')}`;
//answers
let aAnswers=[];
for(let ID in answers){
let answer=answers[ID];
// console.log(answer.author_ID);
aAnswers.push(dataJoin(ID,answer.author_ID, answer.question_ID, answer.content, answer.createdTime));
}
let answer_sql=`INSERT INTO answer VALUES${aAnswers.join(',')}`;
//插入数据
// await db.startTransaction();
// await db.executeTransaction(topic_sql);
// await db.executeTransaction(author_sql);
// await db.executeTransaction(question_sql);
// await db.executeTransaction(answer_sql);
// await db.stopTransaction();
//将数据打印到文件中
// fs.writeFileSync('topic_sql',topic_sql,'utf8')
// fs.writeFileSync('author_sql',author_sql,'utf8')
// fs.writeFileSync('question_sql',question_sql,'utf8')
// fs.writeFileSync('answer_sql',answer_sql,'utf8')
console.log('完成');
})();
扩展
事务
事务拥有ACID四个特性
A 原子性;只会全部发生或者全部不发生;
C 持久性;只要事务提交了,就是永久性生效的;
I 隔离性;各个事务之间是独立的;
D 一致性;食事务前后状态是一致的;
控制台命令 >
node a.js>a.txt
运行a.js将js中consloe.log的内容打印到a.txt中
网友评论