美文网首页
MySQL操作

MySQL操作

作者: 271828182845904 | 来源:发表于2019-12-17 09:31 被阅读0次

    1.创建数据库

    mysql>CREATE DATABASE NR_name_list;
    
    mysql> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | NR_name_list       |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.37 sec)
    

    2.在数据库下创建表,删除表

    mysql>use NR_name_list;  #指定数据库
    Database changed
    mysql>CREATE TABLE NR_tab ( id VARCHAR(100) PRIMARY KEY, longname VARCHAR(5000)); #创建表 id长度100 作为主键,longname长度5000
    Query OK, 0 rows affected (0.01 sec)
    mysql> show tables;
    +------------------------+
    | Tables_in_NR_name_list |
    +------------------------+
    | NR_tab                 |
    +------------------------+
    1 row in set (0.00 sec)
    mysql> DROP TABLE NR_tab; #删除表
    Query OK, 0 rows affected (0.23 sec)
    mysql> show tables;
    Empty set (0.00 sec)
    

    3.载入数据
    直接载入文件

    LOAD DATA INFILE '/mnt/sdb/chenyw/software/mysql-5.7.21-linux-glibc2.12-x86_64/data/gi.nr.func.xls' INTO TABLE NR_tab;
    

    添加条目到表

    mysql> INSERT INTO NR_tab (id,longname) VALUES ('value1','value2');
    
    

    4.查询数据
    PyMySQL

    import pymysql
    connection = pymysql.connect(host='192.168.0.101',user='root',password='mypassword',db='NR_name_list',port=3336) #创建连接
    cursor = connection.cursor() #创建游标
    ##单条查询
    sql = "select * from NR_tab WHERE id='gi|1442330305|ref|YP_009505146.1|'" #定义mysql命令
    cursor.execute(sql) #执行命令
    result_1 = cursor.fetchone() #获取查询的内容
    print result_1
    ('gi|1442330305|ref|YP_009505146.1|', 'Replication helicase subunit (plastid)')
    ###多条查询
    sql="select  *  from  NR_tab where id in('gi|1442330305|ref|YP_009505146.1|','gi|1442330388|ref|YP_009505229.1|')"
    cursor.execute(sql)
    result_2 = cursor.fetchall()
    print result_2
    (('gi|1442330305|ref|YP_009505146.1|', 'Replication helicase subunit (plastid)'), ('gi|1442330388|ref|YP_009505229.1|', 'Cytochrome b6-f complex subunit 4 (plastid)'))
    

    5.Navicat 访问Mysql

    mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; #授权任何ip都能用root访问数据库
    
    图片.png
    图片.png

    6.脚本实现

    def flat_gen(x):
        import collections
        def iselement(e):
            return not(isinstance(e, collections.Iterable) and not isinstance(e, str))
        for el in x:
            if iselement(el):
                yield el
            else:
                yield from flat_gen(el)
    def add_long_name(tab,pref):
        ##connect mysql
        import pymysql
        connection = pymysql.connect(host='192.168.0.101',\
                                     user='root',password='mypassword',db='NR_name_list',port=3336)
        cursor = connection.cursor()
        ##add name
        f = open(tab,'r')
        f1 = open(pref,'w')
        f1.write('GeneID\tIdent-rate\tIdent-len\tQ-Begin\tQ-End\tH-Begin\tH-End\tevalue\tscore\tgiID\tfunc\n')
        for i in f:
            tmp = i.strip().split('\t')
            short_name = i.strip().split('\t')[1]
            sql_comm ="select longname from NR_tab WHERE id='{}'".format(short_name)
            cursor.execute(sql_comm)
            tmp.append(cursor.fetchone()[0])
            tmp=list(flat_gen([tmp[0],tmp[2:4],tmp[6:12],tmp[1],tmp[12]]))
            f1.write('\t'.join(tmp)+'\n')
        f.close()
        f1.close()
        cursor.close()
        connection.close()
    
    if __name__ == "__main__":
        import argparse
        parser = argparse.ArgumentParser(description="Scrpit for add NR longname to tab")
        parser.add_argument("rawtab", type=str, help="table without NR longname")
        parser.add_argument("prefix", type=str, help="output prefix")
        args = parser.parse_args()
        tab1 = args.rawtab
        prefix = args.prefix
        add_long_name(tab1,prefix)
    

    相关文章

      网友评论

          本文标题:MySQL操作

          本文链接:https://www.haomeiwen.com/subject/nxgrnctx.html