第三题:(数据库) 1.在MySQL数据库中建表student,添加name,stundentlD,birt h, class,remarks字段 2.读取第二题的数据文件document.txt 3.连接数据库,将文件中的内容全部存入student表中,学号作为主键 4.查询数据库中student中的信息内容,并打印 5.查询student中的信息并重新写入到新的文件student.txt中
时间: 2024-02-09 20:10:36 浏览: 67
以下是MySQL数据库中建表student并添加字段的SQL语句:
```sql
CREATE TABLE student (
studentID INT PRIMARY KEY,
name VARCHAR(50),
birth DATE,
class VARCHAR(20),
remarks VARCHAR(100)
);
```
读取文本文件document.txt并将内容存入student表中的Python代码如下:
```python
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)
mycursor = mydb.cursor()
with open('document.txt', 'r') as file:
for line in file:
student_info = line.strip().split(',')
sql = "INSERT INTO student (studentID, name, birth, class, remarks) VALUES (%s, %s, %s, %s, %s)"
val = (int(student_info[0]), student_info[1], student_info[2], student_info[3], student_info[4])
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
```
查询数据库中student表的信息并打印的Python代码如下:
```python
mycursor.execute("SELECT * FROM student")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
```
将student表中的信息重新写入到新的文件student.txt中的Python代码如下:
```python
with open('student.txt', 'w') as file:
mycursor.execute("SELECT * FROM student")
myresult = mycursor.fetchall()
for x in myresult:
file.write(str(x[0]) + ',' + x[1] + ',' + str(x[2]) + ',' + x[3] + ',' + x[4] + '\n')
```
阅读全文