gff-sql/gff_to_sqlite3.py at main · KorfLab/gff-sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
#!/usr/bin/env python3
import argparse
import gzip
import os
import re
import sqlite3
import sys


def getfp(filename):
	"""Returns a file pointer for reading based on file name"""
	if   filename.endswith('.gz'):
		return gzip.open(filename, 'rt')
	elif filename == '-':
		return sys.stdin
	else:
		return open(filename)

def create_gff_database(db):
	"""create a new instance of database"""

	if not(os.path.exists(db)):# sys.exit(f'aborting: database {db} exists')
		con = sqlite3.connect(db)
		cur = con.cursor()
		con.commit()


def create_sql_table(db, table):
	"""create a new table in an existing database"""

	if os.path.exists(db):
		con = sqlite3.connect(db)
		cur = con.cursor()
		cur.execute('CREATE TABLE IF NOT EXISTS ' +	table + '(' +
					'seqid TEXT, ' +
					'source TEXT, ' +
					'type TEXT, ' +
					'start INTEGER, ' +
					'end INTEGER, ' +
					'score NUMERIC, ' +
					'strand TEXT, ' +
					'phase TEXT, ' +
					'att TEXT' +
					# 'id TEXT, ' +
					# 'pid TEXT' +
					');')
		con.commit()
	else:
		sys.exit(f'aborting: database {db} does not exist')

def populate_table(filename, db, table):
    # if os.path.exists(db): sys.exit(f'aborting: database {db} exists')
	con = sqlite3.connect(db)
	cur = con.cursor()

	fp = getfp(filename)

	row_count = 0
	# with open(db + "_inserts", "w") as insert_cmds_file:
	for line in fp:
		row_count += 1
		# print(row_count)

		if line.startswith('#'): continue
		fields = line.rstrip().split('\t')
		if len(fields) != 9: sys.exit('GFF3 requires 9 fields')
		# sid, src, typ, beg, end, scr, st, ph, att = fields

		att = fields[-1]
		info = {}
		for tv in att.rstrip(';').split(';'):
			tag, value = tv.split('=')
			info[tag] = value

		insert_command = "insert into " + table + " values('"

		if "'" in att:
			insert_command += "', '".join(fields[:8])
			insert_command += "', ?);"

			# if "ID" in info:
			# 	insert_command += ", '" + info["ID"] + "'"
			# else:
			# 	insert_command += ", '.'"

			# if "Parent" in info:
			# 	insert_command += ", '" + info["Parent"] + "');"
			# else:
			# 	insert_command += ", '.');"
			# print(type(att))
			cur.execute(insert_command, (att,))
			con.commit()
			continue
		else:
			insert_command += "', '".join(fields)

		# if "ID" in info:
		# 	insert_command += "', '" + info["ID"]
		# else:
		# 	insert_command += "', '."

		# if "Parent" in info:
		# 	insert_command += "', '" + info["Parent"] + "');"
		# else:
		# 	insert_command += "', '.');"

		insert_command += "');"
		# insert_cmds_file.write(insert_command)
		cur.execute(insert_command)
		if row_count % 300 == 0:
			con.commit()
			print(row_count)#, '  ', insert_command[:20], ' ... ', insert_command[-20:])#, '     field 9: ', fields[8:9])
	con.commit()
	return 0
		# self ID in attributes
		# here its a transcript ID

def empty_sql_table(db, table):
	if os.path.exists(db):# sys.exit(f'aborting: database {db} exists')
		con = sqlite3.connect(db)
		cur = con.cursor()
		cur.execute('DELETE from ' + table + ';')
		con.commit()
	else:
		sys.exit(f'aborting: database {db} does not exist')


gff_file = "dm1pct.gff3.gz"
dbname = "dm1pct.db"
table = "dm1pct"


create_gff_database(dbname)
create_sql_table(dbname, table)

empty_sql_table(dbname, table)

populate_table(gff_file, dbname, table)