[大数据]python--操作数据库

import pymysql #导入pymysql模块
from com.study.common.readConfig import getConfig  #导入读取配置方法

# 获取mysql配置信息
host = getConfig("Config.txt", 'mysql', 'host')
port = getConfig("Config.txt", 'mysql', 'port')
user = getConfig("Config.txt", 'mysql', 'user')
password = getConfig("Config.txt", 'mysql', 'password')
database = getConfig("Config.txt", 'mysql', 'db')
# print(host, port, user, password, database)

# 建立链接
db = pymysql.connect(host=host, port=int(port),
                     user=user, password=password, db=database)
                     
# 使用cursor()方法获取操作游标
cur = db.cursor()

# 创建test表SQL
sql = """create table test (
  id int(10) NOT NULL AUTO_INCREMENT,
  name char(20) NOT NULL,
  age int(11) DEFAULT NULL,
  sex char(1) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""

# 执行创建表SQL
cur.execute(sql)

# 封装插入
def mysql_insert(sql_insert):
    try:
        # 执行sql
        cur.execute(sql_insert)
        db.commit()
    except:
        # 发生异常
        db.rollback()

# 封装查询
def mysql_select(sql_select):
    try:
        cur.execute(sql_select)
        # 通过游标的description属性获取列信息
        for col in cur.description:
            print(col[0], end='\t')
        print('\n--------------------------------')
        # 直接使用for循环来遍历游标中的结果集
        for row in cur:
            print(row)
        cur.close()  # 关闭游标
        db.close()  # 关闭链接
    except:
        # 查询报错
        print("Error: unable to fecth data")
        
        
        
sql2 = """
  insert into test values(2, "陈阳", 22, "男");
"""

sql3 = """
  select * from test
"""

# mysql_insert(sql2)
mysql_select(sql3)