Hi list,
I've tried, lots of interpreter testing and google grepping to figure
this out and I think I'm missing something fundamental.
I have an ascii data dump from a POS system that has 131 fields in a
single column in a flat file. I can easily open the file, read in the
data and assemble it into various formats. okay. what I *want* to do
is insert each of these fields into a mysql database that has 132
columns that correspond to the 131 fields in the ascii file (plus one
for the date).
I can successfully connect to mysql and do stuff to my tables my
specific problem is how to efficiently put those 132 fields into the
thing. All I have been able to figure out is really ugly stuff like:
build the mysql statement out of various pieces with appropriate
commas and quote included. stuff like (not tested)
for field in f.read():
row+=field[:-2]+", "
stmt="insert into daily values "+row")"
cursor.execute( stmt)
(the slice is to kill a cr/lf on each one)
that seems really kludgey to me.
I've also tried building tuples and lists and then using this
cursor.execute( "insert into daily values (%s)", values)
with no luck. it appears to me that I have to put in all 132 '%s' in
order to make that work and that just seems stupid.
I suppose I could build a list of the column names:
columns=('Asale s', 'Bsales', 'Csales' ...)
and bring in the data as a list and then
for col in range(len(colum ns)):
cursor.execute( "insert into daily (%s) values (%s)",
(columns[col], data[col]))
but again, that doesn't seem too pythonic.
any suggestions are greatly appreciated.
A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFFiIMsaIe IEqwil4YRArkGAK CHY6UG/NT+9fFTo61/GfVVuPJN3wCbB8m h
pW4FBdDs7FfUD0l EpIm8A4M=
=kAWM
-----END PGP SIGNATURE----- 5 1321
Andrew Sackville-West wrote:
>
I can successfully connect to mysql and do stuff to my tables my
specific problem is how to efficiently put those 132 fields into the
thing. All I have been able to figure out is really ugly stuff like:
build the mysql statement out of various pieces with appropriate
commas and quote included. stuff like (not tested)
I just started looking into Python myself, so someone can probably
clean this up or suggest a better way, but this may work:
import MySQLdb
fields = ["field1\r\n","f ield2\r\n","fie ld3\r\n"]
def escapeAndQuote( x):
return "\"%s\"" % MySQLdb.escape_ string(x)
values = ", ".join([escapeAndQuote( f[:-2]) for f in fields])
q = "insert into daily values(%s)" % values
In testing I got:
>>fields = ["field1\r\n","f ield2\r\n","fie ld3\r\n"] values = ", ".join([escapeAndQuote( f[:-2]) for f in fields]) values
'"field1", "field2", "field3"'
>>q = "insert into daily values(%s)" % values
'insert into daily values("field1" , "field2", "field3")'
Todd
On Tue, Dec 19, 2006 at 07:34:58PM -0800, Todd Neal wrote:
Andrew Sackville-West wrote:
I can successfully connect to mysql and do stuff to my tables my
specific problem is how to efficiently put those 132 fields into the
thing. All I have been able to figure out is really ugly stuff like:
build the mysql statement out of various pieces with appropriate
commas and quote included. stuff like (not tested)
I just started looking into Python myself, so someone can probably
clean this up or suggest a better way, but this may work:
okay, let me run through this and see if I understand:
import MySQLdb
fields = ["field1\r\n","f ield2\r\n","fie ld3\r\n"]
build a list of data fields to be inserted (whatever method)
def escapeAndQuote( x):
return "\"%s\"" % MySQLdb.escape_ string(x)
not at the right machine to read up on this but obviously it cleans up
the strings and inserts the quotes around each field.
values = ", ".join([escapeAndQuote( f[:-2]) for f in fields])
crap. I knew about .join. that was really the part I was missing.
q = "insert into daily values(%s)" % values
make the query statement.
In testing I got:
>fields = ["field1\r\n","f ield2\r\n","fie ld3\r\n"] values = ", ".join([escapeAndQuote( f[:-2]) for f in fields]) values
'"field1", "field2", "field3"'
>q = "insert into daily values(%s)" % values
'insert into daily values("field1" , "field2", "field3")'
cool! thanks Todd.
A
Todd
-- http://mail.python.org/mailman/listinfo/python-list
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFFiMgAaIe IEqwil4YRAiDNAK DmGHmUZCR/9UXARWcL1VTvs/jc8ACgk8FV
WKjfs0lVHjDhxf/EUHv0uLY=
=La5Z
-----END PGP SIGNATURE-----
Andrew Sackville-West schrieb:
I have an ascii data dump from a POS system that has 131 fields in a
single column in a flat file. I can easily open the file, read in the
data and assemble it into various formats. okay. what I *want* to do
is insert each of these fields into a mysql database that has 132
columns that correspond to the 131 fields in the ascii file (plus one
for the date).
I can successfully connect to mysql and do stuff to my tables my
specific problem is how to efficiently put those 132 fields into the
thing. All I have been able to figure out is really ugly stuff like:
build the mysql statement out of various pieces with appropriate
commas and quote included. stuff like (not tested)
Haven't tested it, but maybe http://dev.mysql.com/doc/refman/5.0/en/load-data.html is your friend.
On Dec 20, 5:20 am, Andrew Sackville-West <and...@farwest billiards.com>
wrote:
>>values = ", ".join([escapeAndQuote( f[:-2]) for f in fields])
Obviously this is the appropriate choice since this is a database app.
In general the strip() group of string methods do what you want in a
safe way - assuming you don't care about whitespace:
>>s = " test \r\n" s.strip()
'test'
>>s.rstrip()
' test'
>>s.lstrip()
'test \r\n'
If you are concerned about whitespace:
>>s.strip("\n\r ")
' test '
strips any \n's or \r's from the ends of the line.
This way it doesn't matter what your line endings are - you won't be
surprised by missing characters if the data dump changes for any
reason.
On Wed, Dec 20, 2006 at 07:00:38AM -0800, Ant wrote:
On Dec 20, 5:20 am, Andrew Sackville-West <and...@farwest billiards.com>
wrote:
>values = ", ".join([escapeAndQuote( f[:-2]) for f in fields])
Obviously this is the appropriate choice since this is a database app.
In general the strip() group of string methods do what you want in a
safe way - assuming you don't care about whitespace:
>s = " test \r\n" s.strip()
'test'
perfect!
[...]
This way it doesn't matter what your line endings are - you won't be
surprised by missing characters if the data dump changes for any
reason.
well, no great chance of the data dump changing, but its a good
point.
thanks
A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFFiYZFaIe IEqwil4YRAmzNAJ 9oMpauskfPkli0Q DZubDfIAgtF7ACe NNzd
g+++qAbfhLSyfwt +suSvc94=
=rBBV
-----END PGP SIGNATURE----- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dave Harrison |
last post by:
Hi all, got a problem combinging mx and MySQLdb, when I build and install both for my Python2.1 install on a Solaris 9 box I can import mx fine, but importing MySQLdb causing python to core dump.
I am using egenenix 2.0.4 and MySQLdb 0.9.2.
I have also replaced the -shared flag in the Makefile is /usr/local/lib/Python2.1/config with -G (a recommended solaris change to let the build of the modules work in the first place) -> notably I also...
|
by: Peter Nikolaidis |
last post by:
Greetings,
I am attempting to get MySQLdb 0.9.2 installed on Mac OS 10.2 with a
Fink distribution of Python 2.2.2. I have seen only a few posts on the
subject, some of them relate to "conflicting header files," but I don't
know what to do about conflicting header files, or where I would find
them, and once I found them, which ones to remove.
I have compiled MySQL 4.1 and installed into /usr/local/mysql, but
since have moved to a Fink...
|
by: Tim Williams |
last post by:
I'm trying to write a simple python program to access a MySQL
database. I'm having a problem with using MySQLdb to get the results
of a SQL command in a cursor. Sometimes the cursor.execute works,
sometimes not.
From mysql:
mysql> show databases;
+-----------+
| Database |
|
by: m0226065 |
last post by:
Ok
so I have a mySQL server
and mySQLdb as an interface (do I us this term correct here?) to this server
from python
now I have a game:
I want to read a file into the db
I want other data to be kept in the db from now on
problem:
|
by: Achim Domma (Procoders) |
last post by:
Hi,
I try to write unicode strings to a MySQL database via MySQLdb.
According to the documentation I should pass 'utf-8' as keyword
parameter to the connect method. But then I get the following error:
Traceback (most recent call last):
File "C:\develop\SyynX\unicode_test.py", line 7, in ?
con=MySQLdb.connect(host='localhost',db='unicode_test',unicode='utf-8')
| |
by: yaffa |
last post by:
dear group,
i have a python script that scrapes contents from an html file and i
would like to have the script write values to a mysql db. do you
recommend i go with the sqlobject or the mysqldb module?
thank you
yaffa
|
by: Steve |
last post by:
Darwin steve.local 8.3.0 Darwin Kernel Version 8.3.0: Mon Oct 3
20:04:04 PDT 2005; root:xnu-792.6.22.obj~2/RELEASE_PPC Power Macintosh
powerpc
MacOSX 10.4.3
mysql Ver 14.7 Distrib 4.1.14, for apple-darwin8.2.0 (powerpc) using
readline 4.3
runing the software gives me
steve:~/MySQL-python-1.2.0 steve$ python setup.py build
|
by: Yi Xing |
last post by:
Hi,
I met the following error when I tried to install MySQLdb. I had no
problem installing numarray, Numeric, Rpy, etc. Does anyone know
what's the problem? Thanks!
running install
running build
running build_py
creating build
|
by: Nikhil |
last post by:
I am using the MySQLdb python module. I have a table named 'testing'
with few columns, under the 'test' database, what is hosted on a remote
mysql server.
I want to run the following query to get a comma-seperated information
from the table
LOCK TABLES foo READ;
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
|
by: Edwin.Madari |
last post by:
db module properly formats arguments, if input arguments are separated fromtable_name and colum_names.....
columns = ('tID', 'tNote')
table_name = 'tmp'
sql = 'select %s from %s where tID=:1' % ( ', '.join(columns), table_name)
cursor.execute(sql, (1,))
# sql is now 'select tID, tNote from tmp where tID=:1'
# note the comma in argument tuple to execute (1,)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |