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)
