python连接数据库实验中山大学数据库实验8

艺昔小尹

已于 2022-11-08 01:35:08 修改

阅读量294

点赞数

文章标签：数据库 python

于 2022-11-02 20:39:49 首次发布

本文链接：https://blog.csdn.net/lwj_2333/article/details/127658346

版权

数据库实验 8 — Database Access From a Programming Language

一、实验内容

This lab introduces you to database access from a programming language such as Java or C#. A sample set of exercises can be found here; although phrased using Java/JDBC, the exercise can be done using other languages, OBDC or ADO.NET APIs.In this assignment, you will write a java program which takes any of the following commands as command line argument (not as input), and executes them using JDBC:

insert into relationname value1 value2 value3 …

Inserts a tuple into the specified relation with the specified values; make sure you use a prepared statement; test it with input containing single quotes (if you run from command line, enclose those in double quotes so linux doesn’t interpret the quotes) you can assume that all values are strings for simplicity.
select from relationname

Prints all tuples from the specified relation. For the purpose of this assignment, you should assume that relationname is one of “instructor”, “student” or “takes”, and not use database metadata features to print the tuples.
select from relationname where “condition”

Executes a query with the specified condition. Note the use of double quotes so that the condition comes as a single command line parameter. Again assume relationname is one of those from the previous feature. So this is really a small addition to the code for the previous feature, do NOT make a separate copy of the code.
select from relationname1 relationname

Displays result of natural join of the two relations. This time, use the resultset metadata feature to display all values from the query result; don’t worry about displaying column names, that is optional, we only care about values being displayed.

二、实验过程及结果

本次实验采用python进行数据库的连接和各种操作！

1、安装pymysql

指定清华镜像源安装（注意关掉VPN）：

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pymysql==0.9.3

2、用python编写连接数据库的代码

import pymysql

#连接数据库，
conn=pymysql.connect(
    host = '127.0.0.1', # 连接名称，默认127.0.0.1
    user = 'root', # 用户名
    passwd='password', # 密码
    port= 3306, # 端口，默认为3306
    db='database', # 数据库名称
    charset='utf8' # 字符编码
)

# 建游标对象
cur = conn.cursor()

# 输入mysql的语句
#sql = input()
## 以“;”结束语句输入
sql = ''
s = input()
while True: 
    sql = sql + s + '\n'
    if s[-1] == ';':
        break
    s = input('-> ')
print(sql)

try:
    cur.execute(sql) # 执行插入的sql语句
    data = cur.fetchall() # 通过fetchall方法获得数据
    for i in data:
        print(i)
    
    conn.commit() # 提交到数据库执行
    
except:
    conn.rollback()# 如果发生错误则回滚

# 关闭游标
cur.close()
# 关闭连接
conn.close()