Python操作Mysql模塊的安裝

Windows:

    https://dev.mysql.com/downloads/connector/python/2.1.html

SQL基本使用

1、數(shù)據(jù)庫操作

    show databases;
    use database_name;

1、2創(chuàng)建數(shù)據(jù)表

      create table student(
          id int(4) not null auto_increment primary key,
          name varchar(32) not null,
          sex char(4) not null,
          age tinyint unsigned not null);

Python-connector操作數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)表之后，我們通過Python來操作數(shù)據(jù)庫，執(zhí)行代碼后，通過sql語句查看數(shù)據(jù)庫的變動。

import mysql.connector

config = {
    'host': '127.0.0.1', # 連接的IP地址
    'user': 'root',
    'password': '123456',
    'port': 3306,
    'database': 'data',
    'charset': 'utf8', # 編碼格式,防止查出來的數(shù)據(jù)中文亂碼
}

conn = mysql.connector.Connect(**config)
cor = conn.cursor()

# 新增
cor.execute("insert into student values(%s,%s,%s,%s)",(2,'胖多','女',36))

# 修改
cor.execute("update student SET age=%s WHERE age=36",(18,))

# 查詢
cor.execute("select name,age from student WHERE ID=1;")

# 刪除
cor.execute("delete  from student WHERE ID=1;")

conn.commit() # 提交

# 切記一定要關閉游標和連接
cor.close()
conn.close()

Python操作數(shù)據(jù)庫數(shù)據(jù)

# _author_='shaojie'
# -*- coding:utf-8 -*-

import mysql.connector


config = {
    'host': '127.0.0.1', # 連接的IP地址
    'user': 'root',
    'password': '123456',
    'port': 3306,
    'database': 'data',
    'charset': 'utf8', # 編碼格式,防止查出來的數(shù)據(jù)中文亂碼
}

conn = mysql.connector.Connect(**config)
cor = conn.cursor()

# 查詢數(shù)據(jù)是
cor.execute("select * from student;")

conn.commit() # 提交

cor.fetchone() # 返回一條數(shù)據(jù),元祖形式

cor.fetchall() # 返回所有數(shù)據(jù),元祖嵌套元祖



# 關閉游標和連接
cor.close()

最終優(yōu)化版

import mysql.connector


config = {
    'host': '127.0.0.1', # 連接的IP地址
    'user': 'root',
    'password': '123456',
    'port': 3306,
    'database': 'data',
    'charset': 'utf8', # 編碼格式,防止查出來的數(shù)據(jù)中文亂碼
}

conn = mysql.connector.Connect(**config)
cor = conn.cursor()

# 查詢數(shù)據(jù)是
try:
    cor.execute("select * from student;")
    
    conn.commit() # 提交
    
    cor.fetchone() # 返回一條數(shù)據(jù),元祖形式
    
    cor.fetchall() # 返回所有數(shù)據(jù),元祖嵌套元祖
except ConnectionError as e:
    print(e)
except Exception as msg:
    print(msg)
finally:
    # 關閉游標和連接
    cor.close()
    conn.close()