Mar 222015 Tagged with , , , 1 Response

Python code to connect with MySQL and SQL Server database in Fminer

Fminer Run CodeFminer is powerful web scraping tool as well as best browser automation tool that support many features that web scraping software needs. Some of the Fminer’s key feature are Support of Multithreading, Captcha solving feature, set actions to deal with browser automation like Input text into text field, select option from drop down, choose radio button and check boxes, project scheduling and email feature, custom  Proxy support and many more. You can read detail on each feature of fminer on my previous post Fminer – Visual Web Scraping Software With Macro Recorder and Diagram Designer

In this post I am going light on one of the very powerful feature of Fminer which is called “Run Code action”  using this feature one can run custom python code to manipulate scraped data, to upload scraped data directly into MySQL or SQL server from fminer  and you can do any tricky thing using python code you can write.

Most of the web scraper do not have this feature using which you can put your own code in scraping workflow for data manipulation, to control the scraping process or any other tricky work. Some of the data extraction software that have this feature are Visual Web Ripper and Fminer. I am sharing python code which you can use inside Fminer and connect it with the MySQL or SQL Server.

Python code to read data from MySQL database and insert into Fminer table

import sys
import pyodbc
table1=tables['student']
connection_str ="""Driver={MySQL ODBC 5.3 ANSI Driver};Server=localhost;Database=student;User=root; Password=;Option=3;"""
cnxn = pyodbc.connect(connection_str)
cnxn.autocommit = True
cursor=cnxn.cursor()
sql="""select name from students"""
cursor.execute(sql)
rows=cursor.fetchall()
for row in rows:
   	r=[{'name':row.name}]
	   table1.add_rows(r)
	   tables.update_ui()
cursor.close()
del cursor
cnxn.close()

 

Python code to read data from MySQL database and insert into Fminer table

import sys
import pyodbc
table1 = tables['keywords']
connection_str ="""Driver={SQL Server Native Client 11.0};Server=SR1\SQLEXPRESS;Database=dbname;Trusted_Connection=yes;"""
db_connection = pyodbc.connect(connection_str)
db_connection.autocommit = True
db_cursor = db_connection.cursor()
sql_command="""select * from search_keywords"""
db_cursor.execute(sql_command)
rows = db_cursor.fetchall()
for row in rows:
    	v=row.keyword
	    row = [{'key': v}]        
	    table1.add_rows(row)
db_cursor.close()
del db_cursor
db_connection.close()

Both of above code reads data from database table either MySQL or SQL server and insert into Fminer table and then Fminer can use it further based on scraping workflow you designed.

You can also use read table run code template and add database code into it with insert  into query when you want to save scraped data back to database.  There are many  web scraping scenarios where you can utilize this code. For example you have list of keywords available in database and you want to search that keyword on some website and then want to scrape data based on that keywords regularly and want to save scraped data back to database so in that case you just need to schedule fminer scrape and all scraping and saving back to database goes smooth way.

One Response to Python code to connect with MySQL and SQL Server database in Fminer
  1. Prashant Reply

    Do you know how to approach this topic “Python code to read data from MS-SQL database into a feature service”. Thank you!!

Leave a Reply

Your email address will not be published. Please enter your name, email and a comment.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>