· 8 years ago · Oct 16, 2017, 08:52 PM
1#!/usr/bin/python
2import smtplib
3import base64
4import os
5import sys
6import xlswriter
7import xlwt
8import datetime
9import MySQLdb
10from pyh import *
11from email.MIMEMultipart import MIMEMultipart
12from email.MIMEText import MIMEText
13db = MySQLdb.connect("192.168.1.118","stp","stp","STP")
14cursor = db.cursor()
15query = ("""select * from stp_automation_output""")
16cursor.execute(query)
17myresults = cursor.fetchall()
18workbook = xlwt.Workbook()
19worksheet = workbook.add_sheet("My Sheet")
20#date_format = workbook.add_format({'num_format': 'd mmmm yyyy'})
21bold = workbook.add_format({'bold': 1})
22worksheet.write('A1','Sno',bold)
23worksheet.write('B1','function_name',bold)
24worksheet.write('C1','input1',bold)
25worksheet.write('D1','input2',bold)
26worksheet.write('E1','input3',bold)
27worksheet.write('F1','Expected_output',bold)
28worksheet.write('G1','Actual_output',bold)
29worksheet.write('H1','Result',bold)
30row = 1
31col = 0
32for Sno,function_name,input1,input2,input3,Expected_output,Actual_output,Result in (myresults):
33 Sno = row[0]
34 function_name = row[1]
35 input1 = row[2]
36 input2 = row[3]
37 input3 = row[4]
38 Expected_output = row[5]
39 Actual_output = row[6]
40 Result = row[7]
41workbook.save()
42
43import MySQLdb
44import csv
45
46user = '' # your username
47passwd = '' # your password
48host = '' # your host
49db = '' # database where your table is stored
50table = '' # table you want to save
51
52con = MySQLdb.connect(user=user, passwd=passwd, host=host, db=db)
53cursor = con.cursor()
54
55query = "SELECT * FROM %s;" % table
56cursor.execute(query)
57
58with open('outfile','w') as f:
59 writer = csv.writer(f)
60 for row in cursor.fetchall():
61 writer.writerow(row)
62
63import MySQLdb
64from xlsxwriter.workbook import Workbook
65
66user = '' # your username
67passwd = '' # your password
68host = '' # your host
69db = '' # database where your table is stored
70table = '' # table you want to save
71
72con = MySQLdb.connect(user=user, passwd=passwd, host=host, db=db)
73cursor = con.cursor()
74
75query = "SELECT * FROM %s;" % table
76cursor.execute(query)
77
78workbook = Workbook('outfile.xlsx')
79sheet = workbook.add_worksheet()
80for r, row in enumerate(cursor.fetchall()):
81 for c, col in enumerate(row):
82 sheet.write(r, c, col)
83
84import mysql.connector
85from openpyxl import Workbook
86
87def main():
88
89 # Connect to DB -----------------------------------------------------------
90 db = mysql.connector.connect( user='root', password='', host='127.0.0.1')
91 cur = db.cursor()
92
93 # Create table ------------------------------------------------------------
94 database = 'test_database'
95 SQL = 'CREATE DATABASE IF NOT EXISTS ' + database + ';'
96 cur.execute(SQL)
97 db.commit()
98
99 SQL = 'USE ' + database + ';'
100 cur.execute(SQL)
101
102 # Create car data ---------------------------------------------------------
103 cars_table_name = 'cars'
104 SQL = (
105 'CREATE TABLE IF NOT EXISTS ' + cars_table_name +
106 '('
107 ' model_year YEAR, '
108 ' manufacturer VARCHAR(40), '
109 ' product VARCHAR(40)'
110 ');')
111 cur.execute(SQL)
112 db.commit()
113
114 # Python list of dictionaries
115 # More info at:
116 # https://stackoverflow.com/questions/8653516/python-list-of-dictionaries-search
117 car_data = [
118 { 'model_year': '2010', 'manufacturer': 'Toyota', 'product': 'Prius' },
119 { 'model_year': '2010', 'manufacturer': 'Honda', 'product': 'CR-V' },
120 { 'model_year': '1998', 'manufacturer': 'Honda', 'product': 'Civic' },
121 { 'model_year': '1997', 'manufacturer': 'Ford', 'product': 'F-150' },
122 { 'model_year': '2017', 'manufacturer': 'Tesla', 'product': 'Model 3' },
123 ]
124
125 # Code adapted from:
126 # https://dev.mysql.com/doc/connector-python/en/connector-python-example-cursor-transaction.html
127 add_cars = ('INSERT INTO ' + cars_table_name + ' (model_year, manufacturer, product) '
128 ' VALUES (%(model_year)s, %(manufacturer)s, %(product)s)')
129
130 for car_datum in car_data:
131 cur.execute(add_cars, car_datum);
132 db.commit()
133
134 # Create manufacturer data -----------------------------------------------
135 manufacturer_table_name = 'manufacturer'
136 SQL = (
137 'CREATE TABLE IF NOT EXISTS ' + manufacturer_table_name +
138 '('
139 ' name VARCHAR(40), '
140 ' headquarters VARCHAR(40), '
141 ' number_of_employees INT, '
142 ' website VARCHAR(40)'
143 ');')
144 cur.execute(SQL)
145 db.commit()
146
147 add_manufacturer = (
148 'INSERT INTO ' + manufacturer_table_name +
149 ' (name, headquarters, number_of_employees, website) '
150 ' VALUES (%s, %s, %s, %s)')
151
152 # Python list of lists
153 # More info at:
154 # https://stackoverflow.com/questions/18449360/access-item-in-a-list-of-lists
155 # Data from:
156 # https://en.wikipedia.org/wiki/Toyota
157 # Honda data from: https://en.wikipedia.org/wiki/Honda
158 # Ford data from: https://en.wikipedia.org/wiki/Ford
159 # Tesla data from: https://en.wikipedia.org/wiki/Tesla,_Inc.
160 manufacture_data = [
161 [ 'Toyota', 'Toyota, Aichi, Japan', '364445', 'http://toyota-global.com/' ],
162 [ 'Honda', 'Minato, Tokyo, Japan', '208399', 'http://world.honda.com/' ],
163 [ 'Ford', 'Dearborn, Michigan, U.S.', '201000', 'http://www.ford.com/' ],
164 [ 'Tesla, Inc.', 'Palo Alto, California, US', '33000', 'http://www.tesla.com/' ],
165 ]
166
167 for manufacturer_datum in manufacture_data:
168 cur.execute(add_manufacturer, manufacturer_datum);
169 db.commit()
170
171 # Create Excel (.xlsx) file -----------------------------------------------
172 wb = Workbook()
173
174 SQL = 'SELECT * from '+ cars_table_name + ';'
175 cur.execute(SQL)
176 results = cur.fetchall()
177 ws = wb.create_sheet(0)
178 ws.title = cars_table_name
179 ws.append(cur.column_names)
180 for row in results:
181 ws.append(row)
182
183 SQL = 'SELECT * from '+ manufacturer_table_name + ';'
184 cur.execute(SQL)
185 results = cur.fetchall()
186 ws = wb.create_sheet(0)
187 ws.title = manufacturer_table_name
188 ws.append(cur.column_names)
189 for row in results:
190 ws.append(row)
191
192 workbook_name = "test_workbook"
193 wb.save(workbook_name + ".xlsx")
194
195 # Remove tables and database ----------------------------------------------
196 SQL = 'DROP TABLE ' + manufacturer_table_name + ';'
197 cur.execute(SQL)
198 db.commit()
199 SQL = 'DROP TABLE ' + cars_table_name + ';'
200 cur.execute(SQL)
201 db.commit()
202 SQL = 'DROP DATABASE ' + database + ';'
203 cur.execute(SQL)
204 db.commit()
205
206if __name__ =='__main__':main()