1

I want to send csv data to a sql table that does not exist.
Ideally i want to send all variables without specifying anything except whether i want to replace or append a table. Is that possible?

Is it as simple as using 'create table' instead of 'insert into', and somehow dynamically deriving the data type info e.g. (varchar, int(20)).

can Pandas do this yet? I can not find it anywhere.

I started from Writing a csv file into SQL Server database using python

how i came up with this

import pyodbc, csv
def sendTOSQL(csvLocation,sqlTableName):
    with open (csvLocation, 'r') as f:
        reader = csv.reader(f)
        columns = next(reader) 
        query = 'insert into {0}({1}) values ({2})'
        query = query.format(sqlTableName,','.join(columns), ','.join('?' * len(columns)))
        print query
        cnxn= pyodbc.connect("DSN=dashboarddatadev")
        cursor = cnxn.cursor()
        for data in reader:
            cursor.execute(query, data)
        cursor.commit()
            for data in reader:
                cursor.execute(query, data)
            cursor.commit()

which works as long as i create the table first, and i don't know what the data types will be so i have to do it manually.

cnxn= pyodbc.connect("DSN=dashboarddatadev")
cursor = cnxn.cursor()
cursor.execute("create table test(agency varchar(100), ethnicity varchar(30))")
cursor.commit()

My question is... is that the problem in the first place? And if so, how do i dynamically fill in the data type info?

do i need...

with open('k:/temp/vars.csv','w') as vars:
   for item in formatList:
      p = re.compile('\d+')
      varlength=p.findall(item)
      if item[0]=='A':
         print 'Varchar(%r)' % varlength
      elif item[0]=='D':
         print 'Date(%r)' % varlength
      elif item[0]=='N':
         print 'Int(%r)' % varlength
      elif item[0]=='F':
         print 'Int(%r)' % varlength
Community
  • 1
  • 1
Chet Meinzer
  • 1,691
  • 2
  • 21
  • 35
  • Can you be more specific? You can query to see if the table exists, and if not `create` it. Or you can use `create or replace` to always re-create it. – Elliott Frisch Feb 14 '14 at 20:30
  • do i need read through the csv file to obtain the max length and data type in order to supply the info for the statement, or can i write a generic statement? – Chet Meinzer Feb 14 '14 at 20:54
  • I don't know, what are your requirements? You might declare the max length at the max your database supports (or using a `TEXT` field or even a `CLOB`). – Elliott Frisch Feb 14 '14 at 20:56

0 Answers0