Python mysql

玩玩 python 操作 mysql数据库吧

热身

练习下读文件内容和文件传参

hello.py

# -*- coding: utf-8 -*-
import sys
from sys import argv

def readfile(filename):
    '给定文件名，读取文件内容'
    with open(filename) as f:
        while True:
            line = f.readline()
            if len(line) == 0:
                break
            print(line)

def test_sys_argv():
    '测试传递参数，注意这里必须传3个参数， 因为第一个默认的参数就是文件名称'
    script,first,second,third = argv
    # script 默认的参数就是该文件的名称， 所以通常都是从第一个开始取参数 sys.argv[1]
    print ("The script is called: %s"% script)
    print ("Your first variable is: %s"% first)
    print ("Your second variable is: %s"% second)
    print ("Your third variable is: %s"% third)

#script start
if __name__ == '__main__':
    if len(sys.argv) == 4:
        test_sys_argv()
        sys.exit()

    if len(sys.argv) < 2:
        print("没有行为哦")
        sys.exit()

    if sys.argv[1].startswith("--"):
        option = sys.argv[1][2:]
        if option == "version":
            print("version123456")
        elif option == "help":
            print("help asdfasdf")
        else:
            print("other unknown")
        sys.exit()
    else:
        for filename in sys.argv[1:]:
            readfile(filename)
'''
当脚本作为执行脚本时__name__的值为__main__当脚本作为模块时__name__为模块文件名。
举个例子，a.py作为执行脚本时__name__的值是__main__。
有2个脚本，a.py和b.py，a中引入b,执行a.py时，在b中模块的__name__就是b.py
这样等价于在每个模块中写上if __name__ == '__main__': 可以在该模块内做我们自己的事情。
而别的模块调用该模块时，只能调用该模块下的方法或者类，而无法执行if __name__ == '__main__':下的脚本。
'''

开始

简单封装下 数据库的crud操作

db.py

# -*- coding: utf-8 -*-
import os, sys
import pymysql

def connect_mysql(hostname, username, passwd, database, db_port):
    try:
        conn = pymysql.connect(host=hostname,user=username,
        password=passwd,db=database,port=db_port, charset='utf8')
    except Exception as e:
        print(e)
        print(">> 连接数据库失败")
        sys.exit()
    print(">> 连接数据库成功")
    return conn;

def close_resouce(conn):
    conn.commit()
    conn.close()
    print(">> 连接资源已释放")

def insert_data(conn, sql, value):
    cursor=conn.cursor()
    try:
        cursor.executemany(sql,value)
    except Exception as e:
        print(e)
        print(">> 插入数据失败")
        sys.exit()
    print(">> 插入数据成功")
    cursor.close()

def select_data(conn, sql):
    cursor=conn.cursor()
    try:
        cursor.execute(sql)
    except Exception as e:
        print(">> 查询数据失败")
        print(e)
        sys.exit()
    print(">> 查询数据成功")
    data=cursor.fetchall()
    cursor.close()
    return data;

if __name__=="__main__":
    hostname = "localhost"
    username = "root"
    passwd = "123456"
    database = "address"
    db_port = 3306
    conn = connect_mysql(hostname, username, passwd, database, db_port)

    insert_sql="insert into address(name, address) values(%s, %s)"
    insert_value=(("zhangsan","haidian"),("lisi","haidian"))
    insert_data(conn, insert_sql, insert_value)

    select_sql="select * from address"
    data = select_data(conn, select_sql)
    print(">> 查询数据如下")
    if data:
        for x in data:
            print(x)

    close_resouce(conn)

调用

调用db.py内容

caller.py

# -*- coding: utf-8 -*-

from test import readfile as rfile
from sys import argv
from db import connect_mysql, select_data, close_resouce

if __name__=="__main__":

    hostname = "localhost"
    username = "root"
    passwd = "123456"
    database = "app"
    db_port = 3306
    conn = connect_mysql(hostname, username, passwd, database, db_port)

    select_sql="select * from t_todolist"
    data = select_data(conn, select_sql)
    print(">> 查询数据如下")
    if data:
        for x in data:
            print(x)

    close_resouce(conn)