python的mysql小代码

时间：2024-01-17 11:16:32

我因为懒，就想写个批量insert数据的小代码 这里是代码

# _*_ encoding:utf-8 _*_

import os

import MySQLdb

import numpy as np

dbname = 'spj'

db = MySQLdb.connect(host="localhost", port=3306, user="root", passwd="", db=dbname,charset="utf8")  # 进行插入操作时需要声明编码类型

cursor = db.cursor()

def dp_insert(file_name,putin):

    file = open(file_name, "r")

    for line in file.readlines():#插入

        arr = line.split()

        sql = putin+' values '+'('+"'"+arr[0]+"'"+','+"'"+arr[1]+"'"+','+"'"+arr[2]+"'"+','+"'"+arr[3]+"'"')'+';'

        try:

            cursor.execute(sql)

            db.commit()

        except:

            print("Error")

    file.close()

def select(putin):

    sql = putin

    try:

        cursor.execute(sql)

        results = cursor.fetchall()

        for row in results:

            print(row)

        db.commit()

    except:

        print("Error: unable to fecth data")

def describe(putin):

    sql = putin

    try:

        cursor.execute(sql)

        results = cursor.fetchall()

        for row in results:

            print(row)

        db.commit()

    except:

        print("Error: unable to fecth data")

def delete_line(infile,outfile):

    infopen = open(infile,'r')

    outfopen = open(outfile,'w')

    lines = infopen.readlines()

    for line in lines:

        if line.split():

            outfopen.writelines(line)

        else:

            outfopen.writelines("")

    infopen.close()

    outfopen.close()

    os.remove(infile)

    os.rename(outfile,infile)

def main():

    file_name = 'p.txt'

    print("please input sql:")

    putin = input()

    delete_line(file_name, 'new.txt')  # test pass

    if putin.startswith('insert'):  # +','+"'"+arr[2]+"'"

        print("数据的path")

        file_name = input()

        dp_insert(file_name, putin)

    elif putin.startswith('select'):

        select(putin)

    elif putin.startswith('describe'):

        describe(putin)

    else:

        db.close()

        os._exit()

if __name__ == '__main__':

    main()

    print("next?")

    if input()=='Y':

        main()

    else:

        db.close()

        os._exit()

    db.close()

    #insert 模板

    #insert into p(pno, sname, color, weight)

（insert语句：insert into j(jno,jname,city)）

python的mysql小代码

这里是一些文件处理的代码，之前放在代码里，看着难受,放在这里

# 读取path目录下的文件名，返回文件名list列表

def readFileName(path):

 lists = []

 for root, dirs, files in os.walk(path):

 for file in files:

 lists.append(os.path.join(root, file))

 return lists

# 删除路径为filepath的文件

def delFile(filepath):

 os.remove(filepath)

 print

 "ok"

'''

'''

#转化为数组

for line in open("p.txt","r"):

 arr =line.split()

 print(arr)

#转化为列表

file = open("p.txt", "r")

list_arr = file.readlines()

for i in range(len(list_arr)):

 list_arr[i] = list_arr[i].strip()

a = np.array(list_arr)

print(a)

file.close()

'''

#sql=''+''+''+'' 加号连接 sql=‘%s%s%s’ % ('','','')

相关文章