Re: Insert database rows from CSV file
"Steve Holden" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Dennis Lee Bieber wrote: > > On Mon, 4 Apr 2005 15:54:37 -0700, "3c273" <[EMAIL PROTECTED]> declaimed > > the following in comp.lang.python: > > > > > >>Thanks for the link, but this is the step I am trying to save (for someone > >>else). Every time he goes to run a report, he must stop and import any new > >>csv files. Since the files are generated by a Python script, I thought I > > > > > > That information wasn't supplied in the original message. Your > > original post implied that the data source /was/ the CSV file... > > > > Show us the code segment that is writing the CSV file, and we > > can probably show you the DB-API equivalent for "writing" a new record > > to the table. > > > > For short however: > > > > aCustomer = "Customer1" > > theWidget = "Widget1" > > aQuantity = 1000 > > > > # I'm presuming the table only has the three columns, since you didn't > > list fields > > cursor.execute(""" INSERT INTO "Table1" Values (%s, %s, %s) """, > > (aCustomer, theWidget, aQuantity)) > > Beware, however, that the parameter markers ("%s" in the example above) > will depend on which database module you use - some modules will expect > "?", for example. This depends on the module's "paramstyle". > > Also, don't forget to commit the changes! > > regards > Steve Thanks for the heads-up. I think I'm well on my way now. Louis -- http://mail.python.org/mailman/listinfo/python-list
Re: Insert database rows from CSV file
"Dennis Lee Bieber" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Mon, 4 Apr 2005 15:54:37 -0700, "3c273" <[EMAIL PROTECTED]> declaimed > the following in comp.lang.python: > > > Thanks for the link, but this is the step I am trying to save (for someone > > else). Every time he goes to run a report, he must stop and import any new > > csv files. Since the files are generated by a Python script, I thought I > > That information wasn't supplied in the original message. Your > original post implied that the data source /was/ the CSV file... > > Show us the code segment that is writing the CSV file, and we > can probably show you the DB-API equivalent for "writing" a new record > to the table. > > For short however: > > aCustomer = "Customer1" > theWidget = "Widget1" > aQuantity = 1000 > > # I'm presuming the table only has the three columns, since you didn't > list fields > cursor.execute(""" INSERT INTO "Table1" Values (%s, %s, %s) """, > (aCustomer, theWidget, aQuantity)) Ah.. Many thanks. This is what I was looking for. Louis -- http://mail.python.org/mailman/listinfo/python-list
Re: Insert database rows from CSV file
Dennis Lee Bieber wrote: On Mon, 4 Apr 2005 15:54:37 -0700, "3c273" <[EMAIL PROTECTED]> declaimed the following in comp.lang.python: Thanks for the link, but this is the step I am trying to save (for someone else). Every time he goes to run a report, he must stop and import any new csv files. Since the files are generated by a Python script, I thought I That information wasn't supplied in the original message. Your original post implied that the data source /was/ the CSV file... Show us the code segment that is writing the CSV file, and we can probably show you the DB-API equivalent for "writing" a new record to the table. For short however: aCustomer = "Customer1" theWidget = "Widget1" aQuantity = 1000 # I'm presuming the table only has the three columns, since you didn't list fields cursor.execute(""" INSERT INTO "Table1" Values (%s, %s, %s) """, (aCustomer, theWidget, aQuantity)) Beware, however, that the parameter markers ("%s" in the example above) will depend on which database module you use - some modules will expect "?", for example. This depends on the module's "paramstyle". Also, don't forget to commit the changes! regards Steve -- Steve Holden+1 703 861 4237 +1 800 494 3119 Holden Web LLC http://www.holdenweb.com/ Python Web Programming http://pydish.holdenweb.com/ -- http://mail.python.org/mailman/listinfo/python-list
Re: Insert database rows from CSV file
Thanks for the link, but this is the step I am trying to save (for someone else). Every time he goes to run a report, he must stop and import any new csv files. Since the files are generated by a Python script, I thought I could just insert them into his table and save him some steps. I'm also just trying to learn the basics Python and SQL . Thanks again. Louis "Larry Bates" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > You may want to take a look at this link. It should > be much faster than any programmatic technique. > > http://www.its.niu.edu/its/CSupport/tipoftheweek/tip_080502.shtml > > If you still want to do it programmatically, you will need to > look at csv module to parse the lines. > > Larry Bates > > > > 3c273 wrote: > > Hello, > > I have a really simple Access database table with a format similar to this: > > CustomerName - ProductOrdered - QtyOrdered > > > > I have a CSV file with the appropriate values as follows: > > Customer1, Widget1, 1000 > > Customer2, Widget2, 3000 > > etc > > > > I have figured out how to insert the data manually from the interactive > > prompt: > > cursor.execute(""" INSERT INTO "Table1" Values ('Customer1', "Widget1', > > '1000') """) > > > > What I would like to do is iterate over the CSV file like this: > > for lines in file: > > cursor.execute(""" INSERT INTO "Table1" lines """) > > > > I have googled and found some examples that use string formatting, but > > without documentation I can't seem to find the right formula. I don't have > > any problem with the iteration part, I just can't seem to figure out how to > > use a variable to insert an entire row with the INSERT statement. Can anyone > > get me going in the right direction? I'm using odbc from win32all, > > Python2.3, and Access2000 if it matters. Thanks. > > Louis > > > > -- http://mail.python.org/mailman/listinfo/python-list
Re: Insert database rows from CSV file
You may want to take a look at this link. It should be much faster than any programmatic technique. http://www.its.niu.edu/its/CSupport/tipoftheweek/tip_080502.shtml If you still want to do it programmatically, you will need to look at csv module to parse the lines. Larry Bates 3c273 wrote: > Hello, > I have a really simple Access database table with a format similar to this: > CustomerName - ProductOrdered - QtyOrdered > > I have a CSV file with the appropriate values as follows: > Customer1, Widget1, 1000 > Customer2, Widget2, 3000 > etc > > I have figured out how to insert the data manually from the interactive > prompt: > cursor.execute(""" INSERT INTO "Table1" Values ('Customer1', "Widget1', > '1000') """) > > What I would like to do is iterate over the CSV file like this: > for lines in file: > cursor.execute(""" INSERT INTO "Table1" lines """) > > I have googled and found some examples that use string formatting, but > without documentation I can't seem to find the right formula. I don't have > any problem with the iteration part, I just can't seem to figure out how to > use a variable to insert an entire row with the INSERT statement. Can anyone > get me going in the right direction? I'm using odbc from win32all, > Python2.3, and Access2000 if it matters. Thanks. > Louis > > -- http://mail.python.org/mailman/listinfo/python-list
Insert database rows from CSV file
Hello, I have a really simple Access database table with a format similar to this: CustomerName - ProductOrdered - QtyOrdered I have a CSV file with the appropriate values as follows: Customer1, Widget1, 1000 Customer2, Widget2, 3000 etc I have figured out how to insert the data manually from the interactive prompt: cursor.execute(""" INSERT INTO "Table1" Values ('Customer1', "Widget1', '1000') """) What I would like to do is iterate over the CSV file like this: for lines in file: cursor.execute(""" INSERT INTO "Table1" lines """) I have googled and found some examples that use string formatting, but without documentation I can't seem to find the right formula. I don't have any problem with the iteration part, I just can't seem to figure out how to use a variable to insert an entire row with the INSERT statement. Can anyone get me going in the right direction? I'm using odbc from win32all, Python2.3, and Access2000 if it matters. Thanks. Louis -- http://mail.python.org/mailman/listinfo/python-list