pycharm 连接mysql+ 数据库设计

一、对于数据库操作：

step1:创建数据库 并且添加用户

mysql> create database sms_info;

mysql> grant all on sms_info.* to 'stefan' identified by '1qaz@WSXm';

 

step2:

用创建的用户名和密码进入刚刚创建的数据库：

mysql -u stefan -p sms_info

库中创建表：sms_01

字段：   id   sms_from   sms_content   sms_date  insert_date

 

二、pycharm 通过mysql.connector  操作数据库

直接上代码：

# -*- coding: utf-8 -*-
# @Time: 2019/4/21 11:22
# @Author: lishaoshu
# @File: database
# @profile:

import  mysql.connector
import  datetime
from tool.tools import  *

cursor = None
conn = None

"""
function: 初始化Mysql， 返回数据库游标、连接器
date: 2019/04/21
"""
def init_db():
    global  cursor
    global  conn
    mysql_config = {
        'host': '129.211.117.198',
        'user': 'stefan',
        'password': '1q45@WSddXm',
        'database': 'sms_info'
    }
    try:
        conn = mysql.connector.connect(**mysql_config)
        cursor = conn.cursor()
    except mysql.connector.Error as e:
        write_log('connect fails!{}'.format(e))

    return cursor, conn

"""
function: 获取游标
date: 2019/04/21
"""
def cursor_get():
    global cursor
    return cursor
"""
function: 获取连接器
date: 2019/04/21
"""
def conn_get():
    global  conn
    return conn

"""
function: 关闭数据库游标、连接器
date: 2019/04/21
"""
def close_db():
    cursor = cursor_get()
    conn = conn_get()
    if cursor != None:
        cursor.close()
    if conn != None:
        conn.close()

"""
function: 插入数据： 
date: 2019/04/21
"""
def insert_sms_data(param):
    cursor = cursor_get()
    sql = 'insert into sms_01 (sms_from, sms_content, sms_date, insert_date) ' \
          'values (%s, %s, %s, %s)'
    try:
        cursor.execute(sql, param)
        conn.commit()
    except mysql.connector.Error as e:
        write_log('connect fails!{}'.format(e))

    return

def  select_test():
    cursor = cursor_get()
    sql_0 = 'select * from sms_01 '
    cursor.execute(sql_0)
    res = cursor.fetchall()
    print(res)


if __name__ == '__main__':
    init_db()
    param = ('2393939**', '【锐ff接】验证码：2603。您正在注册向日葵帐号，如非本人操作，请忽略该短信。', '2019/04/21 13:52', date_time_get())
    insert_sms_data(param)
    select_test()
    close_db()