python 操作数据库

MySQL

连接mysql数据库需要引入 mysql-connector

0.首先先安装mysql-connector

pip install mysql-connector

1.创建数据库连接

import mysql.connector
conn=mysql.connector.connect(host="数据库ip地址",user="数据库用户名",password="数据库密码",database="库名",port="端口号默认3306")

也可以将参数创建一个对象传入

import mysql.connector
config={
"host"="数据库ip地址",
"user":"数据库用户名",
"password":"数据库密码",
"database":"库名",
"port":"端口号默认3306"
}
conn=mysql.connector.connect(**config)

2.然后获取游标

cursor=conn.cursor()

3.通过游标操作

#创建表
cursor.execute("create table user(id int auto_increment primary key,name varchar(20),age int)")
#插入值MySql占位符号为%s
cursor.execute("insert into user(name,age) value(%s,%s)",['xxx','12'])

4.事务提交并关闭游标

conn.commit()
cursor.close()

5.查询

cursor.execute("select * from  user")
list=cursor.fetchall();
print(user)

具体操作如下

import mysql.connector
config={
    "host":"ip地址",
    "user":"root",
    "password":"root",
    "database":"库名"
}
conn=mysql.connector.connect(**config)
#conn=mysql.connector.connect(host="ip地址",user="root",password="root",database="库名")
cursor=conn.cursor()
try:
#创建表
    cursor.execute("create table if not exists user3(id int auto_increment primary key,name varchar(20),age int)default character set = 'utf8'")
#插值
    cursor.execute("insert into user3(name,age) value(%s,%s)",['xxx','11'])
    cursor.execute("insert into user3(name,age) value(%s,%s)",['xxx','12'])
    cursor.execute("insert into user3(name,age) value(%s,%s)",['xxx','13'])
    cursor.execute("insert into user3(name,age) value(%s,%s)",['xxx','14'])
    cursor.execute("insert into user3(name,age) value(%s,%s)",['xxx','15'])
    cursor.execute("insert into user3(name,age) value(%s,%s),(%s,%s)",['xxx','16',"xxx",'17'])
    print(cursor)
    print("插入%s条数据" %cursor.rowcount)
#查询
    cursor.execute("select * from user3")
    list=cursor.fetchall();#获取查询数据
    print("一共查询得到了%s条数据显示如下:\n %s" % (len(list),list))
#删除
    cursor.execute("delete from user3 where age>14")
    print("删除了age大于14的数据共计%s条" %cursor.rowcount)
    cursor.execute("select * from user3")
    list=cursor.fetchall();#获取查询数据
    print("删除后得到了%s条数据显示如下:\n %s" % (len(list),list))
#更新
    cursor.execute("update  user3 set name=%s where age=%s",("张三",11))
    print("更新了%s条数据" %cursor.rowcount)
    cursor.execute("select * from user3")
    list=cursor.fetchall();#获取查询数据
    print("修改后%s条数据显示如下:\n %s" % (len(list),list))
except BaseException as e:
      print("出现了错误")
finally:
    conn.commit()
    cursor.close()
    conn.close()

运行结果如下：

Oracle

Oracle与Mysql的操作极度相似便不加赘述。

0.首先安装ox_Oracle模块

pip install ox_Oracle

1.创建连接

import ox_Oracle
conn=cx_Oracle.connect('{username}','{password}','{host}[:{port}]/{service_name}')

2.具体操作

import ox_Oracle
conn=cx_Oracle.connect('{username}','{password}','{host}[:{port}]/{service_name}')
cursor = conn.cursor()
sql = "select * from user_col_comments where TABLE_NAME=user"
cursor.execute(sql)
value = cursor.fetchall()
print(value)