CODEWITHSUNDEEP
pythonodbc
홍형기
홍형기

Reputation: 63

Python : odbc data load error

I use pyodbc to import sql big data. but there is no error,no saved file and no message.(data has 300000 rows and 600 columns)

how to load big data in python odbc?

this is my code: 

import pyodbc
import pandas as pd

H2=pyodbc.connect('DRIVER={SQL Server}; SERVER={192.168.x.x};DATABASE={H2};UID=hoho;PWD=haha')
cursor=H2.cursor()
cursor.execute("Select  * From [dbo].[HIST_UTDATA001_201506]")
fieldnames1=[f[0] for f in cursor.description]
result=[]
result=cursor.fetchmany(1000)
b1=result
while b1:

      b1=cursor.fetchmany(1000)
      if not b1:
            break
      result.extend(b1)


df = pd.DataFrame(result,columns=[fieldnames1])
df.to_csv('TEST1.csv')

Upvotes: 1

Views: 220

Answers (1)

Michael Koenig
Michael Koenig

Reputation: 364

You are most likely running out of memory. A column with just an integer (8 bytes) will require (approx.) 16 additional bytes for Python objects and pointers to them. Makes 24 bytes per column. Adding up the numbers, you would require at least 24 * 600 * 300,000 = 4,320,000,000 bytes. You will need more for strings.

You could try turbodbc. It is faster than pyodbc and offers built-in NumPy support to avoid Python objects for numbers. This may reduce your memory consumption.

Upvotes: 2

Related Questions