CODEWITHSUNDEEP
pythonsql
BlackHat
BlackHat

Reputation: 755

Export Python Dataframe to SQL Table

I'm trying to export a python dataframe to a SQL Server table. Is there a better way to do this? I'm getting errors. Dataframe - results_out Output SQL table - FraudCheckOutput

cnn_out = pyodbc.connect('driver={SQL Server};server=XYZ;database=BulkLog;uid=sa;pwd=test')

results_out.to_sql(con=cnn_out, name='FraudCheckOutput', if_exists='replace', flavor='sqlite_master')

Thanks.

Upvotes: 0

Views: 4963

Answers (1)

k.rozycki
k.rozycki

Reputation: 635

Ok, this supposed to make the work done:

import pypyodbc

def database_insert(query, params=())
    conn_params = 'driver={SQL Server};server=XYZ;database=BulkLog;uid=sa;pwd=test'
    try:
        conn = pypyodbc.connect(conn_params)
    except pypyodbc.Error, e:
        print str(e)
    else:
        if conn.connected:
            db = conn.cursor()
            db.execute(query, params).commit()
    finally:
        if conn:
            conn.close()

SQL_INSERT_QUERY = """
    INSERT INTO table_name (
        [field_name1],
        [field_name2]
    )
    VALUES (
        1,
        'vale string'
    )
    WHERE
        field_name3 = ?
    """

database_insert(SQL_INSERT_QUERY, ('field_name3_value',))

in pyodbc usage is very similar

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()
cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.commit()

more on http://code.google.com/p/pyodbc/wiki/GettingStarted

Upvotes: 1

Related Questions