Fminer 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
Prashant December 10, 2015
Do you know how to approach this topic “Python code to read data from MS-SQL database into a feature service”. Thank you!!