MySQL操作

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)