-2

I have a very simple csv file having 3 columns having names 'a', 'b', 'c' of integer types having 5 columns. I want to import this data into SQL server in a database with pymysql. can somebody provide me the code for this? Also is PHPMyAdmin is required to do this?

This is my actual code:

import pymysql

f = open(r"a.csv", "r")
fString = f.read()
print(fString)
fList = []

for line in fString.split('\n'):
    fList.append(line.split(','))
    del(fList[0])
conn = pymysql.connect(host='localhost', user='root',
                           password='ajit2910@', database='mydatabase')
cur = conn.cursor()
cur.execute('CREATE TABLE jogi4(a INT,b INT,c INT)')
for i in range(len(fList)-1):
        sqlquery = "INSERT INTO jogi3(a,b,c) VALUES(%s,%s,%s)"
        cur.execute(sqlquery, (fList[i][0], fList[i][1], fList[i][2])) conn.close()
anadi
  • 63
  • 1
  • 6
  • 1
    Unfortunately, this is not a "please provide me the code" kind of community. We're happy to help you solve your programming problems if you can show you've tried and what you've tried. – Gregoire Lodi Jun 06 '19 at 08:06
  • this seems to create the table but it's not importing data @Gregoire Lodi – anadi Jun 06 '19 at 08:09
  • Can you provide a CSV file you'd use. If would allow us to reproduce the issue. – Gregoire Lodi Jun 06 '19 at 08:14
  • so here is the link to the csv : https://drive.google.com/open?id=1lgretJSH1LjbRcOEwsAIGaxa0g0crdoT – anadi Jun 06 '19 at 08:21

1 Answers1

0

Basically, your problem is you're creating the table jogi4 and inserting into jogi3. I'm writing a more detailed answer though.

Here is a corrected code with comments.

Basically:

  • Improvement: using CSV module to avoid parsing
  • Fix: use the jogi4 table everywhere
  • Fix: COMMIT the SQL transaction.
import pymysql
import csv

# create the connection BEFORE to avoid recreating it at each loop
conn = pymysql.connect(host='localhost', user='root', database='stackoverflow')
cur = conn.cursor()
# Adds an 'IF NOT EXISTS' clause to avoid an Exception if the table already exists
cur.execute('CREATE TABLE IF NOT EXISTS jogi4(a INT,b INT,c INT)')

csvfile = open('a.csv')
# Use a CSV reader to avoid having to parse the CSV file myself
reader = csv.reader(csvfile)
# Skip the header
next(reader, None)

for row in reader:
    sqlquery = "INSERT INTO jogi4(a,b,c) VALUES(%s,%s,%s)"
    s = cur.execute(sqlquery, (row[0], row[1], row[2]))

cur.close()

# You forgot to commit the inserts, see "Transactions" in SQL
conn.commit()

conn.close()
Gregoire Lodi
  • 557
  • 2
  • 10
  • Hi, your code worked. Thanks a lot :) I want to ask one more thing is that if I have a date column in another csv, then %s would work for this column? – anadi Jun 06 '19 at 08:59
  • Yes, but you need to format the date as a string using eg strftime. If my code worked feel free to mark the answer as a solution :) – Gregoire Lodi Jun 06 '19 at 09:02