import data into database - python script

Kamaraj Subramanian
Kamaraj Subramanian used Ask the Experts™
on
Hi all,

I just need a simple python script to insert a records into my database. Currently i am using a php. But it takes more time. Because i have huge data.

So, i like to try with python script. But i dont have much hands on experience in python. So, i need all your help.

A python script which can be compiled on linux via "make" "make install" which will create a binary that can import a csv into mysql from parameters.

Example:
/usr/bin/importer -db_name "my_database_name" -db_user "username" -db_passsword "password" -db_fields "title,description,contents,etc" -csv_fields "0,1,2,3" -input "/path/to/csv.csv"

The csv_field numbers represents the column in the csv (such as 0 is the first column, 1 is the 2nd column etc) and matches to the fields (0 csv field imports into 'title', 1 imports into 'description', etc).

If any possible solution to make this insertion faster, i can go for that also. I have nearly 10 million records in csv file.

thanks
kamaraj.s
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
Commented:
I'm not familiar with python or php, but am with perl.  Are you also interested in perl (you also posted in the perl zone).

If using perl, you could prepare the statement once (which is slow), then execute it once for each piece of data.  Not sure if php/python allow you to do this.  If you'd like to try perl, let me know.
Top Expert 2009

Commented:
Which database are you using?
Kamaraj SubramanianApplication Support Analyst

Author

Commented:
Thanks for looking my post and throwing some light on my question

But, how about the performance in the perl ?

In php it takes nearly 1 hour for inserting 1 million records.

thanks
kamaraj.s
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Kamaraj SubramanianApplication Support Analyst

Author

Commented:
@mrjoltcola :

I am using mysql
Top Expert 2009
Commented:
PHP, as with any scripting language, will be much slower than using the native tools to import data.

I recommend you try mysqlimport or one of the custom native programs out there to import this size data. I also do loads with Oracle and when the data volumes get high, I only use Perl to pre-process data, but not not to insert. Using the scripting language DB layer to do the inserts is 1-2 orders of magnitudes slower than the native database utilities.

Something like this:

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --u myusername -p DATABASENAME infile.csv


See the MySQL online guide for more tips:

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html
Top Expert 2008
Commented:
mysqlimport is allready mentioned by mrjoltcola, you could also use the SQL statement LOAD DATA INFILE from your PHP (or Python) script:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

mysqlimport is using LOAD DATA INFILE, the two methods should be equally fast.
Also, bear in mind that doing the insertions over a remote network connection (even at T3 speeds) will be far slower than doing it on localhost.  The speed discrepancy is non-trivial.
Kamaraj SubramanianApplication Support Analyst

Author

Commented:
Thanks for your suggestions

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial